question

DharmanDave avatar image
DharmanDave asked

When does a remote query timeout ?

If I have Server A which is running a remote query in an SP like - "select * from ServerB.DBname.dbo.TableName", and the ServerB is configured as a linked server, when is this query assumed to be timed out? The remote query timeout period on Server A is 600 sec (default). My query is - does the query timeout if no rows are returned in 600 sec or does it timeout if the complete dataset is not returned in 600 sec. Please advise. Thanks !
sql serverlinked-server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
It will time out with either situation, there have been no rows or the complete data set is not returned.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for that Grant. What would be the best way to determine the root cause of the timeout ? I've set up traces but all they indicate is the duration for which it ran. Does the number of 'Reads' in the trace indicate the exact number or rows that the query fetched in that duration ?
0 Likes 0 ·
The cause of the timeout could be two basic scenarios (with a million derivations from there). You could be in a situation where you have long running queries due to the nature of the query, the data returned, the execution plan, that sort of thing. Or, you could be experiencing some type of resource issue that causes queries that are normally fast enough to run long because they're either blocked on a resource or can't get enough memory, CPU, etc. So, either bad T-SQL or contention. Both are answered by monitoring. First, if you know which queries it is, are there tuning opportunities? Do you need to adjust the T-SQL, add an index, something. Check the execution plans. Second, if not, you need to observe the system, look for blocking, memory issues, CPU, I/O contention. Start by checking the wait statistics and then capture the wait stats for the queries being run.
0 Likes 0 ·
Thanks for all your inputs Grant ! we're still looking into our issue. Will post a comment once we have a resolution :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.