Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
 Posted - 2009-07-21 : 15:17:38
|
| I am receiving occasional timeout errors when executing a stored procedure via a linked server. If I execute the procedure directly on the linked server it has a worst case execution time of about 4-6 seconds. If I run it remotely it can timeout after 30 seconds. I'm expecting some overhead due to the linked server but not this much.The stored procedure only accesses objects that are local to itself and, hopefully, returns a record set. I don't know if it's germane but the timeouts seem to be when the results return no records.I think that I have ruled out resource contention since when I run the procedure via the linked server I can run the same procedure locally (several times) before the linked server times out.Both the servers involved are SQL 2005 SP2 64-bit.I haved tried to find some explanation for the discrepancy in execution times but, so far, have come up empty. If anyone has a suggestion for resolution or what configuration changes could be impacting this, I'd appreciate it.Thank-you in advance for your time.=======================================Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-07-21 : 17:10:08
|
| There is a difference in the execution plans between the two execution points. Here is the relevant subset:(Works fine local - 2-3 seconds):Table Insert(OBJECT:([tempdb].[dbo].[#tmp]), SET:([#tmp].[PropertyStatus] = [RealtySearch].[dbo].[SearchByMap].[PropertyStatus])) |--Top(ROWCOUNT est 0) |--Parallelism(Gather Streams) |--Nested Loops(Left Anti Semi Join, WHERE:([RealtySearch].[dbo].[SearchByMap].[Supplier] IS NULL OR [Supplier] IS NULL OR [RealtySearch].[dbo].[SearchByMap].[Supplier]=[Supplier])) |--Index Scan(OBJECT:([RealtySearch].[dbo].[SearchByMap].[IX_SearchByMap_PropertyStatus_Latitude_Longitude]), WHERE:([RealtySearch].[dbo].[SearchByMap].[Latitude]>=[@LatitudeMin] AND [RealtySearch].[dbo].[SearchByMap].[Latitude]<=[@LatitudeMax] AND [RealtySearch].[dbo].[SearchByMap].[Longitude]>=[@LongitudeMin] AND [RealtySearch].[dbo].[SearchByMap].[Longitude]<=[@LongitudeMax])) |--Table Scan(OBJECT:(@ParsedSupplierExclude))(Executed via linked server - 30-40 seconds):Table Insert(OBJECT:([tempdb].[dbo].[#tmp]), SET:([#tmp].[PropertyStatus] = [RealtySearch].[dbo].[SearchByMap].[PropertyStatus])) |--Top(ROWCOUNT est 0) |--Nested Loops(Left Anti Semi Join, WHERE:([RealtySearch].[dbo].[SearchByMap].[Supplier] IS NULL OR [Supplier] IS NULL OR [RealtySearch].[dbo].[SearchByMap].[Supplier]=[Supplier])) |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1018], [Expr1019], [Expr1020])) | |--Merge Interval | | |--Concatenation | | |--Compute Scalar(DEFINE:(([Expr1013],[Expr1014],[Expr1012])=GetRangeWithMismatchedTypes([@LatitudeMin],NULL,(22)))) | | | |--Constant Scan | | |--Compute Scalar(DEFINE:(([Expr1016],[Expr1017],[Expr1015])=GetRangeWithMismatchedTypes(NULL,[@LatitudeMax],(42)))) | | |--Constant Scan | |--Clustered Index Seek(OBJECT:([RealtySearch].[dbo].[SearchByMap].[IDX_Latitude]), SEEK:([RealtySearch].[dbo].[SearchByMap].[Latitude] > [Expr1018] AND [RealtySearch].[dbo].[SearchByMap].[Latitude] < [Expr1019]), WHERE:([RealtySearch].[dbo].[SearchByMap].[Longitude]>=[@LongitudeMin] AND [RealtySearch].[dbo].[SearchByMap].[Longitude]<=[@LongitudeMax]) ORDERED FORWARD) |--Table Scan(OBJECT:(@ParsedSupplierExclude))My real puzzlement lies in why there would be ANY difference in execution plan and why it is consistently different. The local execution always takes a few seconds to complete and the linked server always takes the 30-35 seconds to do the same.=======================================Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727) |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-07-21 : 18:07:17
|
| There is a difference in the execution plans between the two execution points. Here is the relevant subset:(Works fine local - 2-3 seconds):Table Insert(OBJECT:([tempdb].[dbo].[#tmp]), SET:([#tmp].[PropertyStatus] = [RealtySearch].[dbo].[SearchByMap].[PropertyStatus])) |--Top(ROWCOUNT est 0) |--Parallelism(Gather Streams) |--Nested Loops(Left Anti Semi Join, WHERE:([RealtySearch].[dbo].[SearchByMap].[Supplier] IS NULL OR [Supplier] IS NULL OR [RealtySearch].[dbo].[SearchByMap].[Supplier]=[Supplier])) |--Index Scan(OBJECT:([RealtySearch].[dbo].[SearchByMap].[IX_SearchByMap_PropertyStatus_Latitude_Longitude]), WHERE:([RealtySearch].[dbo].[SearchByMap].[Latitude]>=[@LatitudeMin] AND [RealtySearch].[dbo].[SearchByMap].[Latitude]<=[@LatitudeMax] AND [RealtySearch].[dbo].[SearchByMap].[Longitude]>=[@LongitudeMin] AND [RealtySearch].[dbo].[SearchByMap].[Longitude]<=[@LongitudeMax])) |--Table Scan(OBJECT:(@ParsedSupplierExclude))(Executed via linked server - 30-40 seconds):Table Insert(OBJECT:([tempdb].[dbo].[#tmp]), SET:([#tmp].[PropertyStatus] = [RealtySearch].[dbo].[SearchByMap].[PropertyStatus])) |--Top(ROWCOUNT est 0) |--Nested Loops(Left Anti Semi Join, WHERE:([RealtySearch].[dbo].[SearchByMap].[Supplier] IS NULL OR [Supplier] IS NULL OR [RealtySearch].[dbo].[SearchByMap].[Supplier]=[Supplier])) |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1018], [Expr1019], [Expr1020])) | |--Merge Interval | | |--Concatenation | | |--Compute Scalar(DEFINE:(([Expr1013],[Expr1014],[Expr1012])=GetRangeWithMismatchedTypes([@LatitudeMin],NULL,(22)))) | | | |--Constant Scan | | |--Compute Scalar(DEFINE:(([Expr1016],[Expr1017],[Expr1015])=GetRangeWithMismatchedTypes(NULL,[@LatitudeMax],(42)))) | | |--Constant Scan | |--Clustered Index Seek(OBJECT:([RealtySearch].[dbo].[SearchByMap].[IDX_Latitude]), SEEK:([RealtySearch].[dbo].[SearchByMap].[Latitude] > [Expr1018] AND [RealtySearch].[dbo].[SearchByMap].[Latitude] < [Expr1019]), WHERE:([RealtySearch].[dbo].[SearchByMap].[Longitude]>=[@LongitudeMin] AND [RealtySearch].[dbo].[SearchByMap].[Longitude]<=[@LongitudeMax]) ORDERED FORWARD) |--Table Scan(OBJECT:(@ParsedSupplierExclude))My real puzzlement lies in why there would be ANY difference in execution plan and why it is consistently different. The local execution always takes a few seconds to complete and the linked server always takes the 30-35 seconds to do the same.=======================================Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727) |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-07-21 : 18:37:02
|
| Here is what I have done and the results that I am now receiving:1) I changed the processing of the stored procedure parameters so that they are first copied to equivalent local variables (parameter sniffing) and then used the local variables throughout the code.2) The stored procedure is still taking about ten seconds to execute via the linked server versus three seconds when executed directly. That does pull me within our thirty second timeout window.I would feel a lot better if I could resolve why the use of the linked server impacted the type of execution plan generated.=======================================Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Linked Server - Performance Issue