Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

PREEMPTIVE_OLEDBOPS cause of slow down remote query Expand / Collapse
Author
Message
Posted Wednesday, January 26, 2011 1:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 17, 2014 1:54 AM
Points: 16, Visits: 262
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.

Post #1053684
Posted Wednesday, January 26, 2011 8:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:30 AM
Points: 834, Visits: 5,327
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.
Post #1053924
Posted Thursday, January 27, 2011 3:19 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, January 27, 2014 7:07 PM
Points: 959, Visits: 2,879
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
Post #1054983
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse