January 26, 2011 at 1:05 am
I've got two machines:
Server A: SQL 2008: contains the database on which the query is executed.
Server B: SQL 2005: the server from which the query is executed,
When I execute the query directly on Server A, it takes 7 seconds to complete.
Executed on Server B, it will complete in 2,5 minutes.
When I monitored it simply in the activity monitor (on server B), I saw a lot of PREEMPTIVE_OLEDBOPS waits alternating ODBC waits.
Does anyone know where to look for the bottleneck?
I presume it's got something to do with the connection between server B to Server A.
January 26, 2011 at 8:45 am
spidey73 (1/26/2011)
I've got two machines:Server A: SQL 2008: contains the database on which the query is executed.
Server B: SQL 2005: the server from which the query is executed,
When I execute the query directly on Server A, it takes 7 seconds to complete.
Executed on Server B, it will complete in 2,5 minutes.
When I monitored it simply in the activity monitor (on server B), I saw a lot of PREEMPTIVE_OLEDBOPS waits alternating ODBC waits.
Does anyone know where to look for the bottleneck?
I presume it's got something to do with the connection between server B to Server A.
You are probably using the four part naming convention to run the remote query. This will pull back all the data from Server A and filter it on Server B. You should use OPENQUERY() as this will filter on Server A and only return the results.
January 27, 2011 at 3:19 pm
Another easy way to handle this is create a stored procedure for the query on the remote server. Then execute it from the local server using the 4-part procedure name. All filtering and such will take place on the remote server and you'll only get back the results.
Todd Fifield
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply