I was given an assignment to understand timeout parameter in SQL Server for one of our business need, So I gathered some details which I'm posting here....
Linked Server Timeout Parameters
Connection Timeout
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" (discussed below) is used. By default the server option default is 20 seconds for the "remote login timeout".
Query 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" (discussed below) value defaults to 600 (10 minutes).
Remote login timeout
Number of seconds to wait before returning from a failed attempt to log in to a remote server. For example, if you are attempting to log in to a remote server and that server is down, remote login timeout ensures that you do not have to wait indefinitely before your computer ceases its attempts to log in.
The remote login timeout option affects connections to OLE DB providers made for heterogeneous queries. The default setting for remote login timeout is 20 seconds. A value of 0 allows for an infinite wait.
T-SQL Code to Configure
Exec sp_configure N' remote login timeout (s)', 1000
Remote query timeout
How long, in seconds, a remote operation can take before Microsoft SQL Server times out. The default is 600, which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine.
For heterogeneous queries, remote query timeout specifies the number of seconds (initialized in the command object using the DBPROP_COMMANDTIMEOUT rowset property) that a remote provider should wait for result sets before the query times out. This value is also used to set DBPROP_GENERALTIMEOUT if supported by the remote provider. This will cause any other operations to time out after the specified number of seconds.
For remote stored procedures, remote query timeout specifies the number of seconds that must elapse after sending a remote EXEC statement before the remote stored procedure times out.
T-SQL Code to Configure
Exec sp_configure N'remote query timeout (s)', 1000
Query wait
Use the query wait option to specify the time in seconds (from 0 through 2147483647) that a query waits for resources before timing out. If the default value of -1 is used, or if –1 is specified, then the time-out is calculated as 25 times of the estimated query cost.
In Microsoft SQL Server, memory-intensive queries (such as those involving sorting and hashing) are queued when there is not enough memory available to run the query. The query times out after a set time calculated by SQL Server (25 times the estimated cost of the query) or the time specified by the nonnegative value of the query wait.
If used incorrectly, can hide other errors related to deadlocking
Will not stop/cancel blocking issues
T-SQL Code to Configure
Exec sp_configure 'query wait', 5
LOCK_TIMEOUT
Will timeout the executing command after N milliseconds if it is waiting for locks.
Typically called at the top of in stored procedures, along with set nocount
Is not Pandora’s box for solving deadlock issues
Check value via select @@LOCK_TIMEOUT
Over linked servers, test carefully. You may find that the 4-part naming doesn’t not work, worst still setting the value before using OPENQUERY may also not work. If you experience this problem, try this syntax:
select * from openquery([myremoteserver], ‘set lock_timeout 1000 select col1 from myremotetable’)
Linked Server Timeout Parameters
Connection Timeout
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" (discussed below) is used. By default the server option default is 20 seconds for the "remote login timeout".
Query 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" (discussed below) value defaults to 600 (10 minutes).
Remote login timeout
Number of seconds to wait before returning from a failed attempt to log in to a remote server. For example, if you are attempting to log in to a remote server and that server is down, remote login timeout ensures that you do not have to wait indefinitely before your computer ceases its attempts to log in.
The remote login timeout option affects connections to OLE DB providers made for heterogeneous queries. The default setting for remote login timeout is 20 seconds. A value of 0 allows for an infinite wait.
T-SQL Code to Configure
Exec sp_configure N' remote login timeout (s)', 1000
Remote query timeout
How long, in seconds, a remote operation can take before Microsoft SQL Server times out. The default is 600, which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine.
For heterogeneous queries, remote query timeout specifies the number of seconds (initialized in the command object using the DBPROP_COMMANDTIMEOUT rowset property) that a remote provider should wait for result sets before the query times out. This value is also used to set DBPROP_GENERALTIMEOUT if supported by the remote provider. This will cause any other operations to time out after the specified number of seconds.
For remote stored procedures, remote query timeout specifies the number of seconds that must elapse after sending a remote EXEC statement before the remote stored procedure times out.
T-SQL Code to Configure
Exec sp_configure N'remote query timeout (s)', 1000
Query wait
Use the query wait option to specify the time in seconds (from 0 through 2147483647) that a query waits for resources before timing out. If the default value of -1 is used, or if –1 is specified, then the time-out is calculated as 25 times of the estimated query cost.
In Microsoft SQL Server, memory-intensive queries (such as those involving sorting and hashing) are queued when there is not enough memory available to run the query. The query times out after a set time calculated by SQL Server (25 times the estimated cost of the query) or the time specified by the nonnegative value of the query wait.
If used incorrectly, can hide other errors related to deadlocking
Will not stop/cancel blocking issues
T-SQL Code to Configure
Exec sp_configure 'query wait', 5
LOCK_TIMEOUT
Will timeout the executing command after N milliseconds if it is waiting for locks.
Typically called at the top of in stored procedures, along with set nocount
Is not Pandora’s box for solving deadlock issues
Check value via select @@LOCK_TIMEOUT
Over linked servers, test carefully. You may find that the 4-part naming doesn’t not work, worst still setting the value before using OPENQUERY may also not work. If you experience this problem, try this syntax:
select * from openquery([myremoteserver], ‘set lock_timeout 1000 select col1 from myremotetable’)
Hope this post answers your question on
1. What is timeout or what is connection timeout or what is login timeout.
2. How to configure SQL Server Connection timeout, Login Timeout.
3. How SQL Server timeout option works.....
No comments:
Post a Comment
Please do not spam!