如何诊断和调优，才能轻松与数据库“timeout”说再见？(2)

我们另外再介绍一种超时,在.NET的SqlBulkCopy类,有BulkCopyTimeout属性,超时之前操作完成所允许的秒数.如果操作超时,事务便不会提交,而且所有已复制的行都会从目标表中移除.使用SqlBulkCopy批量加载数据时的默认超时设置为30秒.

每次对数据库连接时,我们有时候会碰到连接超时或者命令超时,这两个超时是不一样的.以ADO.NET为例,当客户端和服务器端连接时,碰到的超时情况主要有下面几种：

• 当从连接池获取一个连接时,碰到超时.
• 当建立一个全新连接(而不是从连接池获取)时,碰到超时.
• 当发送一个命令(command)到SQLServer时,超时.
• 当发送命令(连接字符串带有“contextconnection=true”属性)到SQLServer时,超时.
• 当不是显示的发送一个命令(implicitly)到SQLServer时,碰到超时.
• 当执行异步命令时,(BeginExecute)碰到超时.
• 当从服务器端,获取行时,碰到超时.
• 当用Bulkcopy方式,上传数据时,碰到超时.

这些超时主要是通过连接字符串中的ConnectTimeout和SqlCommand.CommandTimeout来进行控制.前面两种是登录超时由ConnectionTimeout来决定什么时候超时,后面几种是命令超时由CommandTimeout来决定什么时候超时.

特别注意：“超时时间已到.在操作完成之前超时时间已过或服务器未响应”.类似这种错误,一般是SqlCommand.CommandTimeout或者SqlBulkCopy.BulkCopyTimeout的时间超时,而不是SqlConnection.ConnectionTimeout.

传入连接：SSMS访问超时

接着,我们来看看用户通过SQLServerManagementStudio即SSMS访问数据库时的超时设置.

这里,我们可以设置SSMS工具的连接和查询超时时间.连接超时的默认值为15秒.而查询超时的默认值为0,表示查询会一直运行直到完成.

最后,针对传出连接,我们来看看数据库跨实例远程访问的超时设置.

数据库从一个实例访问到另一个实例,可以通过以下方式查看到.

查看配置选项的设置：

远程登录超时

远程登录超时选项指定了,从登录远程服务器失败返回前等待的秒数.例如,如果你尝试登录到一个远程服务器,而服务器宕机了,远程登录超时帮助你在你的机器停止尝试登录前,不用无限等待下去.这个选项的默认值为10秒.值为0表示无限等待.

在SQLServer2008中,这个选项的默认值为20秒.

远程登录超时选项影响了异构查询的OLEDB提供者产生的连接.

这个设置不用重启服务立即生效.

SQLServer2014的远程登录超时时间默认为10秒.下面的脚本可以修改该值：

EXECsp_configure’remotelogintimeout’,35;

GO

RECONFIGURE;

GO

远程查询超时

远程查询超时选项指定了,在SQLServer超时前一个远程操作花费了多少秒.默认值为600秒,允许10分钟的等待.这个值应用于数据库引擎发起的作为远程查询的传出连接.这个值对于数据库引擎收到的查询无效.为了禁止超时,可以设置为为0.那么查询将会一直等待直到取消.

对于异构查询,远程查询超时指定了,在查询超时前,一个远程提供者应该等待结果的秒数.(使用DBPROP_COMMANDTIMEOUT行集属性在命令对象初始化).如果被远程提供者支持,这个值也被用户设置DBPROP_COMMANDTIMEOUT.在指定的数秒后,这将导致任何其他的操作超时.

对于远程存储过程,远程查询超时指定的秒数为,在远程存储过程超时之前,在发送一个远程EXEC语句之后花费的时间.

这个设置不用重启服务立即生效.

SQLServer2014的远程查询超时为10分钟,可以通过以下脚本修改该值：

EXECsp_configure’remotequerytimeout’,0;

GO

RECONFIGURE;

GO

远程服务器和链接服务器的对应选项

在配置远程访问的时候,可以设置链接服务器的超时选项,也分连接超时和查询超时.

设置语法如下：

sp_serveroption[@server=]’server’

,[@optname=]’option_name’

,[@optvalue=]’option_value’;

二、数据库超时诊断和调优

传入连接：连接超时

对于连接超时,首先可以查看ConnectivityRingBuffer中的LoginTimers类型错误来分析,如果想获得更详尽的信息,再通过抓包工具networkmonitor.

SQLServer2008中包含一个新功能,旨在帮助解决特别棘手的连接问题.

这个新功能是ConnectivityRingBuffer,它可以捕捉每一个由服务器发起的连接关闭记录(server-initiatedconnectionclosure),包括每一个session或登录失败事件.为了进行有效的故障排除,RingBuffer会尝试提供客户端的故障和服务器的关闭动作之间的关系信息.只要服务器在线,最高1K的RingBuffer就会被保存,1000条记录后,Buffer开始循环覆盖,即从最老的记录开始覆盖.

ConnectivityRingBuffer的记录是能够使用DMV查询的：

SELECTCAST(recordASXML)FROMsys.dm_os_ring_buffers

WHEREring_buffer_type=’RING_BUFFER_CONNECTIVITY’

首先我们从连接的RingBuffer数据返回的XML来入手.执行上面的语句,得到下面的结果：

点击XML的超链接,打开文件内容看到更可读的内容,包括一条基本的Ring Buffer连接超时记录.

可以看到在XML文档中有许多相当有用的信息.像SniConsumerError,State和RemoteHost这些.

特别注意的是,RecordType节点,对于我们上面的截图来看标识为“LoginTimers”,说明是连接超时信息.为了识别这种类型的连接RingBuffer,我们可以查询SniConsumerError代码号,准确定位是什么错误导致的.

RecordType包含那些值?

Error–连接错误

LoginTimers–连接超时

ConnectionClose–杀掉进程

可以通过如下脚本,将XML数据转化为可读信息：

执行上面的查询后,将得到下面的可读结果.在这个查询中,我们关联Ring Buffer数据和sys.messages视图去抓取Error id的文本.通过这个信息我们可以跟踪到精确的导致Error:Login失败的信息.

（编辑：ASP站长网）