SQL Server 客户端超时设置

我们经常看到MSSQL服务器参数配置及 SSMS 中有很多地方设置超时，但是这些参数即使设置成1秒钟，执行各种查询似乎也正常。所以完全不知道有什么用（疑惑脸……）。之前有遇到也没明白，今晚刚好利用公司升级时间，摸索测试出来了！（豁然开朗~）

不废话了，首先我们得知道，超时影响的是客户端，也是客户端的行为导致。所以我们看到的或者在其他程序语言中设置的，都是客户端的设置。那 SQL Server 客户端哪里可以设置呢？ 打开 SSMS ( microsoft sql server management studio)，现在就把它当做客户端，所以会看到各种超时设置。

哪里可以看到？？

SELECT * FROM sys.configurations WHERE configuration_id IN (1519,1520,1541);

这里，3个
remote login timeout (s)
remote query timeout (s)
query wait (s)

还有！慢慢体会吧？乍一看，好像都差不多！~

(右键实例) >> Server Properties >> connections >> remote query timeout
(右键实例) >> Server Properties >> Advanced >> remote login timeout
(右键实例) >> Server Properties >> Advanced >> query wait

(右键链接服务器) >> 属性 >> 服务器选项 >> query timeout
(右键链接服务器) >> 属性 >> 服务器选项 >> connection timeout

Tools >> Options >> Query Execution >> SQL Server >> General >> Execution Time-Out
Tools >> Options >> Query Execution >> SQL Server >> Advanced >> SET LOCK TIMEOUT

--先打开一个查询窗口才看到菜单“Query”
Query >> Query Options >> Execution >> General >> Execution Time-Out 
Query >> Query Options >> Execution >> Advanced >> Execution SET LOCK TIMEOUT

SELECT @@LOCK_TIMEOUT

一开始，凌乱~~所以就分类如下：

--  对链接服务器有效,客户端连接服务器最长等待时间
exec sp_configure 'remote login timeout (s)'
(右键实例) >> Server Properties >> Advanced >> remote login timeout
(右键链接服务器) >> 属性 >> 服务器选项 >> connection timeout

--  对链接服务器有效，客户端执行服务器语句前最长等待时间
exec sp_configure 'remote query timeout (s)'
(右键实例) >> Server Properties >> connections >> remote query timeout
(右键链接服务器) >> 属性 >> 服务器选项 >> query timeout

--  对链接服务器有效，查询等待内存资源时间（这模拟不到测试，放弃测试）
exec sp_configure 'query wait (s)'
(右键实例) >> Server Properties >> Advanced >> query wait

--  等待锁获取到资源前的最长等待时间
SET LOCK_TIMEOUT 30000; --单位毫秒
Query >> Query Options >> Execution >> Advanced >> Execution SET LOCK TIMEOUT
Tools >> Options >> Query Execution >> SQL Server >> Advanced >> SET LOCK TIMEOUT

--  执行语句前的最长等待时间
Query >> Query Options >> Execution >> General >> Execution Time-Out 
Tools >> Options >> Query Execution >> SQL Server >> General >> Execution Time-Out

一种情况之所以有几种设置，是因为在不同地方都可以设置，有的还可以设置局部。

【第一组】

--当前实例的全局配置，两种设置都是同一个参数。所有链接服务器的默认链接等待超时值。
exec sp_configure 'remote login timeout (s)'  
(右键实例) >> Server Properties >> Advanced >> remote login timeout

--该链接服务器的链接等待超时值。
(右键链接服务器) >> 属性 >> 服务器选项 >> connection timeout

测试前，配置链接服务器。俩个实例如 A 和 B，其中一个实例 A 创建链接服务器连接到 B 服务器。

现在，手动停止 B 服务器中的数据库服务器！

在 A 服务器实例执行链接服务器的查询，15秒等待后报错。

OLE DB provider "SQLNCLI10" for linked server "WIN-AHAU9NO5R6U,14333" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "WIN-AHAU9NO5R6U,14333" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: 由于目标计算机积极拒绝，无法连接。

现在，在A服务器更改远程连接超时值为 5 秒！在查询

exec sp_configure 'remote login timeout (s)',5
reconfigure with override
go
SELECT * FROM [WIN-AHAU9NO5R6U,14333].master.sys.objects

此时发现，在连接成功前，等待5秒就停止了！

再继续测试，右键连接服务器，把 connection timeout 的值改为 10秒！

确定后默认先尝试连接，结果10秒报错！

在查询窗口查询，使用该链接服务器的等待 10 秒则停止报错了！

总结，在链接服务器设置的 “connection timeout” 是可以覆盖全局设置的 “remote login timeout (s)” 的！

【第二组】

--当前实例的全局配置，两种设置都是同一个参数。所有链接服务器的默认查询等待超时值。
exec sp_configure 'remote query timeout (s)'
(右键实例) >> Server Properties >> connections >> remote query timeout

--该链接服务器的链接等待超时值。
(右键链接服务器) >> 属性 >> 服务器选项 >> query timeout

先把刚才 B 服务器的实例启动……并回复刚才客户端 A 的初始配置。

在 B 服务器实例数据库创建一个存储过程，简单如下：

CREATE proc [dbo].[test]
as
waitfor delay '00:00:06'
select * from master.sys.objects
GO

至于上面等待6秒，相当于模拟各种环境的延时，如因为某些因素，该查询导致6秒后才开始执行。

此时A  使用链接服务器调用 B 的存储过程，正常执行出结果。

EXEC [WIN-AHAU9NO5R6U,14333].DemoDB.DBO.[test]

现在客户端 A 设置全局远程连接执行等待超时值为 5 秒，小于上面设置的 6 秒。再执行存储过程。

exec sp_configure 'remote query timeout (s)',5
reconfigure with override
go

OLE DB provider "SQLNCLI10" for linked server "WIN-AHAU9NO5R6U,14333" returned message "Query timeout expired".

发现，查询执行5秒就停止并报错了！服务器B中的存储过程，里面的语句需要第6秒后才能执行，而客户端设置只等待5秒，所以客户端超出等待范围，停止等待！

若客户端设置为 7 秒，则查询正常！

exec sp_configure 'remote query timeout (s)',7
reconfigure with override
go

即使存储过程中的语句执行10秒，也不影响，也不报错，因为查询在第6秒就执行了，不管执行多久。

同样我们配置客户端 A 该单个连接服务器的查询超时值。

结果发现，客户端实例设置虽然为 7 秒，但链接服务器设置为 3 秒，执行远程存储过程3秒就停止并报错了！

所以，链接服务器的设置是覆盖实例全局设置的！

【第三组】

exec sp_configure 'query wait (s)'
(右键实例) >> Server Properties >> Advanced >> query wait

这些参数是对客户端的设置，不考虑是否链接服务器了。因为该参数设置的是查询等待内存资源时间，默认值为-1，意味着超时值计算为估计的查询开销的 25 倍。如果某个查询估计执行 2 秒，那查询执行 50 秒也不会报错。这个参数涉及内存的分配，暂时还没想到测试方法，搁着~也求方法…………^ ^

相关文章：

​​Query Wait Option and query timeouts​​

​​Query Wait Setting​​

​​Query Execution Time outs because of lack of memory​​

【第四组】

--  获取到锁定资源前的最长等待时间
SET LOCK_TIMEOUT -1; --单位毫秒;-1 为不限制
Query >> Query Options >> Execution >> Advanced >> Execution SET LOCK TIMEOUT
Tools >> Options >> Query Execution >> SQL Server >> Advanced >> SET LOCK TIMEOUT

Tools 中的 “SET LOCK_TIMEOUT” 为全局设置，对当前SSMS所在的整个服务器多个实例有效（因为服务器当做客户端了），设置在新的连接中生效，原有的连接还是用之前的设置。

Query 和命令 “SET LOCK_TIMEOUT -1;”  只在当前打开的查询窗口中生效，立即生效 。

(菜单栏选项 "Query" 只有在打开查询窗口时才出现，因为该处的设置只属于某个连接的 )

现在打开一个查询窗口，执行一个事务，并且设置事务等待一段时间后再提交！

--查询窗口（1），事务执行10分钟
BEGIN TRAN
UPDATE DBO.DEMOTAB01 SET INSDATE=GETDATE() WHERE ID=1
WAITFOR DELAY '00:01:00'
COMMIT TRAN
GO

前面说过，超时是客户端的设置，现再打开一个查询窗口，并且设置该客户端执行前的等待时间为 5 秒！（SET LOCK_TIMEOUT 5000;）

Msg 1222, Level 16, State 51, Line 1
Lock request time out period exceeded.

因 5 秒钟未能执行该语句，结果 5 秒后查询窗口（2）报错！

如果超时等待设置无限制（SET LOCK_TIMEOUT -1; ）则查询正常，客户端的查询执行几分钟也不会报错，因为只要它执行，执行多久是另外的事了。

【第五组】

--  执行语句前的最长等待时间  
Query >> Query Options >> Execution >> General >> Execution Time-Out   
Tools >> Options >> Query Execution >> SQL Server >> General >> Execution Time-Out

同样，菜单 Tools 中的 “Execution Time-Out” 为全局设置，对当前SSMS所在的整个服务器多个实例有效，设置在新的连接中生效。

菜单 Query  只在当前打开的查询窗口中生效，立即生效 。

测试前，把之前的设置都改回为默认值。

打开一个查询窗口，执行以下语句，一分钟后事务释放。

--查询窗口（1），事务执行1分钟
BEGIN TRAN
UPDATE DBO.DEMOTAB01 SET INSDATE=GETDATE() WHERE ID=1
WAITFOR DELAY '00:01:00'
COMMIT TRAN
GO

再打开一个窗口，查询该行数据。

--查询窗口（2）
SELECT * FROM dbo.DemoTab01 WHERE id=1

Msg -2, Level 11, State 0, Line 0
超时时间已到。在操作完成之前超时时间已过或服务器未响应。

结果-查询窗口（2）因为在 5 秒内未执行，所以查询报错终止！

这测试好像跟上一组测试没什么区别，下面再用一个更简单的测试！

Execution Time-Out 已经设置为 5 秒了，在同一个窗口执行以下语句，就知道了。

--该查询正常
BEGIN TRAN
WAITFOR DELAY '00:00:04'
SELECT *  FROM dbo.DemoTab01
COMMIT TRAN
GO
--该查询(5秒后)错误
BEGIN TRAN
WAITFOR DELAY '00:00:05'
SELECT *  FROM dbo.DemoTab01
COMMIT TRAN
GO

结果第一个查询正常，第二个查询到时停止操作。这样就更容易理解了。

总结： MSSQL 普遍原理，局部设置可以覆盖全局设置！全局只是通用设置，如各参数配置、查询设置、事务级别等待！上面测试的也是一样！

测试完成！