关于timeout之sqlserver篇
写在开头
与SQLServer相关的timeout，都是由Client端发起的，SQLServer没有针对服务器端的全局查询超时参数
比如说，我们写了个C#小程序，其中使用了SqlCommand.CommandTimeout属性，指定它的值为20秒。那么，当这个query在SQL端执行了20秒后，C#小程序会给SQL Server发送一个TDS Tension数据包，告诉SQL Server我这边超时了，你那边的query不用做了。于是SQL响应client的请求，断掉connection。Client端报出一条exception，说SQL Server端的运行时间太长，超过了我们原定的时限。
服务器端针对timeout的参数，请参考：
SELECT name,value_in_use,description FROM sys.configurations WHERE name like '%timeout%' OR name like '%query%wait%'
--.返回
name value_in_use description
remote login timeout (s) 10 remote login timeout
remote query timeout (s) 600 remote query timeout
query wait (s) -1 maximum time to wait for query memory (s)
PH timeout (s) 60 DB connection timeout for full-text protocol handler (s)
[TOC]
服务端配置
1 服务端配置查询超时
1.1 配置 Remote query timeout
(右键实例) >> Server Properties >> Connections >> Remote query timeout
- 该选项指定在 SQL Server 超时之前远程操作可以持续的时间（秒）
- 此值将应用到由作为远程查询的 数据库引擎 发起的发送连接，此值不会对 数据库引擎接收的查询产生任何影响。
- 该选项对链接服务器有效，指定SQL Server超时之前远程操作可以持续的秒数(默认600s)；
- 对于远程存储过程，remote query timeout 指定在发送一个远程 EXEC 语句之后，在远程存储过程超时前必须等待的秒数；
- 假如在sql02上设置为300s(默认600s)，若sql01远程(比如LinkedServer)访问sql02超过此值则报超时；
--.配置
USE master
exec sp_configure 'remote query timeout (s)', 300
RECONFIGURE WITH OVERRIDE
--.确认
SELECT name,value_in_use FROM sys.configurations WHERE name like '%query%out%'
1.2 配置链接服务器 query timeout
(右键链接服务器) >> 属性 >> 服务器选项 >> query timeout
- 对于某链接服务器设置的 query timeout 优先级高于全局设置的 remote query timeout (s)；
2 服务端配置连接登录超时
2.1 配置 remote login timeout
(右键实例) >> Server Properties >> Advanced >> remote login timeout
- 远程登录超时，对链接服务器有效，客户端连接服务器最长等待的秒数(默认10秒,0表示无限期等待)。
- 如果您尝试登录到一个远程服务器而该服务器已关闭，remote login timeout 帮助确保您在计算机停止登录尝试前不必无限期地等待下去。
--.配置
USE master
exec sp_configure 'remote login timeout (s)', 10
RECONFIGURE WITH OVERRIDE
--.确认
SELECT name,value_in_use FROM sys.configurations WHERE name like '%login%out%'
2.2 配置链接服务器 connection timeout
(右键链接服务器) >> 属性 >> 服务器选项 >> connection timeout
- 对于某链接服务器设置的 connection timeout 优先级高于全局设置的 remote login timeout (s)；
3 服务端配置查询等待值
3.1 配置 query wait
(右键实例) >> Server Properties >> Advanced >> query wait
- 这个是针对客户端的设置，不考虑是否链接服务器。
- 查询等待内存资源时间，默认值为-1，意味着超时值计算为估计的查询开销的 25 倍。如果某个查询估计执行 2 秒，那查询执行 50 秒也不会报错。这个参数涉及内存的分配，暂无测试方法
--.配置
USE master
exec sp_configure 'query wait (s)', 10
RECONFIGURE WITH OVERRIDE
--.确认
SELECT name,value_in_use FROM sys.configurations WHERE name like '%query%wait%'
4 服务端配置锁超时
4.1 配置 SET LOCK TIMEOUT
Tools >> Options >> Query Execution >> SQL Server >> Advanced >> SET LOCK TIMEOUT
Query >> Query Options >> Execution >> Advanced >> Execution SET LOCK TIMEOUT
- 针对当前session设置锁超时时间(毫秒,默认-1无限制等待)，如果没有lock，比如with(nolock)，则不会超时；
- Tools 中的 SET LOCK_TIMEOUT 为全局设置，对当前、也仅限SSMS所在的整个服务器多个实例有效，仅对新的连接生效；
- Query 中的设置仅对当前打开的查询窗口有效，立即生效；
SET LOCK_TIMEOUT 300
SELECT @@LOCK_TIMEOUT
WAITFOR DELAY '00:00:10' --.没有lock无法验证
SELECT TOP 100000 * FROM ip2location..ip2location WITH(UPDLOCK) --.先后起2个此操作可验证
5 服务端配置执行超时
5.1 配置 Execution Time-Out
Query >> Query Options >> Execution >> General >> Execution Time-Out
Tools >> Options >> Query Execution >> Execution Time-Out
- 执行语句前的最长等待时间，这个类似C#种的SqlCommand.CommandTimeout对象属性的超时
- Tools 中的 Execution Time-Out 为全局设置，对当前SSMS所在的整个服务器多个实例有效，设置在新的连接中生效。
- Query 中的设置仅对当前打开的查询窗口有效，立即生效；
6 服务端配置设计器超时
6.1 SSMS修改表设计的Timeout
Tools >> Options >> Designers >> 覆盖表设计器更新的链接字符串超时值
此选项主要针对SSMS修改表字段类型的时候，设置的超时时间。
[最佳实践] 数据库字符串-for开发
- [针对连接字符串] 之 Connect Timeout 选项
connectStr = "Data Source=127.0.0.1,1433;Initial Catalog=db01;uid=sa;pwd=***;Max Pool Size=150;Connect Timeout=180;"
- [针对Web.config] 设置http请求 executionTimeout 选项
<system.web>
<httpRuntime maxRequestLength="102400" executionTimeout="180" />
</system.web>
- [针对C#] 之 SqlCommand.CommandTimeout 对象属性的超时,针对查询
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 180;
强烈推荐设置 LOCK_TIMEOUT
- 指定语句等待锁释放的毫秒数，即在 SQL Server 返回锁定错误前经过的毫秒数。
值为 -1（默认值）时表示没有超时期限（即无限期等待）。
值为 n，则表示当锁等待超过超时n秒将返回错误。
值为 0 时表示根本不等待，一遇到锁就返回消息。 - 在连接开始时，此设置的值为 -1。 设置更改后，新设置在其余的连接时间里一直有效。
--.设置
SET LOCK_TIMEOUT 15