SSMS - Query Timeout - how to extend timeout & Remote Collation (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 09:43
Joined
Oct 22, 2009
Messages
2,803
After a week, there are no more timeouts. will mark this as solved.
Still no idea if REMOTE COLLATION might have helped.
Will mark this as Solved and add REMOTE COLLATION to a different question.

See article: Does this mean the local table is 20 seconds unless the Linked Server Properties are set to a higher value? I set the Linked Server Query Time Out to 30. Will this solve the 21 seconds issue every fifth query?

Found a good article to describe Remote Servers below. Three Remote Server Tables are joined to a SQL Server Local table. There is a long delay in the connection plus the query takes about 16 seconds total. A Select * against a single Remote Server table (across the VPN) takes about 16 seconds.

About every fifth time, the timeout occurs (at 20 seconds) and times out.

This brings up the next subject of REMOTE COLLATION.
Perhaps someone can explain if the direction of a join from the SQL Server to the Remote Server affects the query timing thus requires attention for the REMOTE COLLATION?

SSMS Query Timeout:
In SSMS query - there are three Oracle tables joined back to a SQL Server table. It is only 10K records returned.

Feed and Care of Remote Servers
Code:
The “Connection Timeout” is used to specify the maximum length of
time the local server should wait to obtain a connection to the linked server
 SQL Server instance. If “0” (zero) is specified for this option then the server
 option “remote login timeout” is used. By default the server option default is
 20 seconds for the “remote login timeout”. The “Query Timeout” option is 
used to specify the length of time a linked server process will be allowed to 
run before it times out. When this option is set to “0” (zero) then the server
 “remote query timeout” is used. The “remote query timeout” value defaults 
to 600 (10 minutes).

Disclaimer: The "about every fifth time" is a gross estimation and not a absolute trend derived from a controlled statistical study.
 
Last edited:

Users who are viewing this thread

Top Bottom