How to diagnose a query with a sub-query against another server.

SELECT
ServiceProvider.[ServiceProviderGuid],
ServiceProvider.[ServiceProviderTypeGuid],
ServiceProvider.[ServiceProviderName],
ServiceProvider.[ServiceProviderId],
ServiceProvider.[MainPlant],
ServiceProvider.[ServiceProviderParentGuid]
FROM ServiceProvider
WHERE ServiceProvider.[ServiceProviderGuid] IN (
SELECT vwCRMServiceProviderAccess.[ServiceProviderGuid]
FROM vwCRMServiceProviderAccess
WHERE [vwCRMServiceProviderAccess].[UserGuid] = '4D569648-C703-47BA-BF66-D8E39F5C9D49' )

SELECT --USE DISTINCT if there are multiple rows in vwCRMServiceProviderAccess
SP.[ServiceProviderGuid],
SP.[ServiceProviderTypeGuid],
SP.[ServiceProviderName],
SP.[ServiceProviderId],
SP.[MainPlant],
SP.[ServiceProviderParentGuid]
FROM ServiceProvider SP
JOIN vwCRMServiceProviderAccess SPA ON SP.[ServiceProviderGuid] = SPA.[ServiceProviderGuid]
WHERE SPA.[UserGuid] = '4D569648-C703-47BA-BF66-D8E39F5C9D49'

Larry Kruse (8/29/2012)

I have a problem with performance of a query embedded in some 3rd party software.

The query (see below) performs a select of some fields on one server WHERE the product_id is IN a sub-query that exists on another server.

SELECT 
	ServiceProvider.[ServiceProviderGuid], 
	ServiceProvider.[ServiceProviderTypeGuid], 
	ServiceProvider.[ServiceProviderName], 
	ServiceProvider.[ServiceProviderId], 
	ServiceProvider.[MainPlant], 
	ServiceProvider.[ServiceProviderParentGuid]
FROM ServiceProvider 
WHERE ServiceProvider.[ServiceProviderGuid] IN (	
	SELECT	vwCRMServiceProviderAccess.[ServiceProviderGuid] 
	FROM	vwCRMServiceProviderAccess 
	WHERE	[vwCRMServiceProviderAccess].[UserGuid] = '4D569648-C703-47BA-BF66-D8E39F5C9D49' )

The sub-query runs against a view which contains a fully qualified reference to a table on another server.

Here is the issue: I can run the main query and it returns in less than 1 second. I can extract just the sub-query and it runs in 1 second when executed from main server, performing a cross-server query. But when these are both run in the full query, it takes 33 seconds to return!

I can't figure out what would cause this. I've attempted to trace this using profiler. I can easily capture information regarding the base query, but I am unable to see the cross-server portion from a profiler trace running on the remote server.

The actual execution plan shows that 98% of the cost of running this query resides in the remote query portion, but I am stumped by the fact that I can just execute that sub-query portion and have it return in 1 second.

Do you have any ideas about how I might be able to trouble-shoot this, or what might be happening?

Thanks in advance for your thoughts and advice.

Larry