December 4, 2013 at 12:37 pm
We're having a slow query problem with SQL server.
We have some software which is using a virtual host name of the SQL Server Listener in the query and this is causing slow query performance.
UPDATE table SET
status = 1
FROM [HOSTNAMEOFSQLLISTENER].database.dbo.things AS things
WHERE bar = foo
Looking at the execution plan it's spending all it's time in the "Remote Scan" stage, I'm not sure why this would be.
The weird thing is when running the below query, replacing the IP address of the listener with the IP address of the SQL server the listener points to (and I've made sure it's the same server you end up on if you connect to SQL server via the listener through the management studio) the execution plan is sensible (uses indexes) and the query completes in a fraction of the time.
UPDATE table SET
status = 1
FROM [HOSTNAMEOFSQLSERVERDIRECTLY].database.dbo.things AS things
WHERE bar = foo
Apparently this behaviour isn't a problem in SQLServer2008 but I haven't had this confirmed yet.
So I have two questions:
1) Why is the execution plan different?
2) Is putting the IP address of the listener considered best practice in these scenarios?
Cheers
Alan
December 4, 2013 at 2:19 pm
Have you set up that as a linked server? What does the sys.servers entry look like?
The probability of survival is inversely proportional to the angle of arrival.
December 4, 2013 at 2:41 pm
Thank you for the replies. Annoyingly I don't have the answers to those questions to hand right now...
I'm assuming from your further questions that in order for sql server to act the "right way" the servers need to be set up as linked servers, and that in turn will allow the remote queries to use the indexes on these tables.
December 9, 2013 at 6:28 am
or can you set it up as a passthru query and let the source server develop an execution plan
December 9, 2013 at 6:53 am
alan.hollis 1097 (12/4/2013)
We're having a slow query problem with SQL server.We have some software which is using a virtual host name of the SQL Server Listener in the query and this is causing slow query performance.
UPDATE table SET
status = 1
FROM [HOSTNAMEOFSQLLISTENER].database.dbo.things AS things
WHERE bar = foo
Looking at the execution plan it's spending all it's time in the "Remote Scan" stage, I'm not sure why this would be.
The weird thing is when running the below query, replacing the IP address of the listener with the IP address of the SQL server the listener points to (and I've made sure it's the same server you end up on if you connect to SQL server via the listener through the management studio) the execution plan is sensible (uses indexes) and the query completes in a fraction of the time.
UPDATE table SET
status = 1
FROM [HOSTNAMEOFSQLSERVERDIRECTLY].database.dbo.things AS things
WHERE bar = foo
Apparently this behaviour isn't a problem in SQLServer2008 but I haven't had this confirmed yet.
So I have two questions:
1) Why is the execution plan different?
2) Is putting the IP address of the listener considered best practice in these scenarios?
Cheers
Alan
the problem with linked servers is they can be horribly slow; way to often, an action like this actually ends up copying the remote data to the local temp database, then performing the joins, update, then telling the remote server what to update based on the data int he temp table clone.....if that's a million row table, that's a lot of unnecessary data movement.
i realize you abstracted out the actual problem for the forum post, but if ALL the changes are occurring at the remote, you want to use the EXECUTE AT feature of linked servers; if you are joining agasint local data, then there's going to be a cross server cost for the data to be matched
EXECUTE ('
UPDATE things SET
status = 1
FROM database.dbo.things AS things
WHERE bar = foo') AT [HOSTNAMEOFSQLLISTENER]
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply