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

《如何诊断和调优，才能轻松与数据库“timeout”说再见？》要点：
本文介绍了如何诊断和调优，才能轻松与数据库“timeout”说再见？，希望对您有用。如果有疑问，可以联系我们。

作者介绍

许昌永,高级DBA,微软SQLServerMVP,十年以上SQLServer使用经验.曾就职于腾讯公司,从事了六年游戏行业SQLServer数据库开发和管理.目前就职于跨境电商DX.COM三年多,负责公司SQLServer和MongoDB的数据库架构设计、高可用部署、运维管理和性能优化等工作.翻译出版了书籍《PowerShellV3——SQLServer2012数据库自动化运维权威指南》,文末将有许老师的新书赠送,不容错过~

一、超时分析

下面是用户访问一个Web站点的常见错误：

详细错误描述如下：

以上输出非常清晰地描述了,对于这个操作的超时时间结束,而实际上工作并没有完成.

我们常常会发现“timeout”错误,那么具体是在哪个访问阶段、受哪个设置影响报出来的呢?下面,我们通过典型的Web应用架构来分析下超时问题.

结合上面的访问关系图,可以看到：我们从数据库实例的角度出发,它会收到来自Web端的访问、用户的直接访问,它也可能同时访问其他数据库实例.那么,连接就分为传入连接(Incomingconnection)访问和传出连接(Outgoingconnection)访问.那我们就根据不同阶段的访问来分类超时问题.

传入连接：Web端访问超时

首先,针对传入连接,我们来看看Web端的访问超时：有ASP.NET请求超时、WebService请求超时、IIS请求超时、数据库连接超时和查询超时.

ASP.NET请求超时

ASP.NET页面的运行超时时间可以在多个地方设置.

来自MSDN的解释：

httpRuntime是配置asp.Nethttp运行时设置,以确定如何处理对asp.Net应用程序的请求.

executionTimeout：表示允许执行请求的最大时间限制,单位为秒.默认值为90秒.

maxRequestLength：指示ASP.Net支持的最大文件上载大小.该限制可用于防止因用户将大量文件传递到该服务器而导致的拒绝服务攻击.指定的大小以KB为单位.默认值为4096KB(4MB).

1、全局超时时间

服务器上如果有多个网站,希望统一设置一下超时时间,则需要设置Machine.config文件中的ExecutionTimeout属性值.Machine.config文件位于%SystemRoot%\Microsoft.NET\Framework\%VersionNumber%\CONFIG\目录中.

例如：

<httpRuntimeexecutionTimeout=”90″maxRequestLength=”4096″

useFullyQualifiedRedirectUrl=”false”minFreeThreads=”8″

minLocalRequestFreeThreads=”4″appRequestQueueLimit=”100″/>

2、单个站点超时时间

Web.config配置文件中设置http请求运行时间：

<system.web>

<httpRuntimemaxRequestLength=”102400″executionTimeout=”720″/>

</system.web>

这里设置的为720秒,前面的属性maxRequestLength一般用于用户上传文件限制大小!默认一般为4096KB(4MB).

3、单个页面请求超时时间

对于单个页面,可以使用Server.ScriptTimeout来设定超时.

Server.ScriptTimeout=120;

注意：如果在Web.config里设置了debug属性,例如：

<compilationdebug=”true”targetFramework=”4.0″>

此时,ScriptTimeout会被忽略.

WebService请求超时

扩大代理类的超时限制,默认是90秒,即在调用方法前指定超时时间.

YourWebServiceyws=newYourWebService();

yws.Timeout=1200000;//20分钟,单位是毫秒

如果将Timeout属性设置为Timeout.Infinite,则指示该请求无超时.即使XMLWebservices客户端可以将Timeout属性设置为无超时,Web服务器仍可以在服务器端使请求超时.

IIS请求超时

在IISManager中,选中Sites,点击右侧的WebsiteDefaults,在Limits属性列表中,设置连接超时时间ConnectionTime-out(seconds).默认值为120秒.

连接超时有助于减少由空闲连接消耗的处理资源损失.启用连接超时时,IIS会在连接级别执行以下类型的连接超时：客户端已向服务器发送了数据,现处于空闲状态造成的连接超时.

已建立了与服务器的连接,但客户端未发送数据时造成的服务器侦听超时.响应超时(基于可配置的最小字节数/秒的值).请求超时,它禁止客户端向服务器发送不合理的慢速请求(例如,1比特/秒).

数据库连接超时

在.NET的SqlConnection类,有ConnectionTimeout属性,获取终止尝试并生成错误之前在尝试建立连接时所等待的时间.等待连接打开所需的时间(以秒为单位).默认值为15秒.在这个时间内,如果连接没有建立,我们将会看到这个错误.值为0表示无限制.

主要通过连接字符串中的ConnectTimeout来进行控制,如下：

<connectionStrings>

<addname=”conn”connectionString=”userid=crm;Password=crmpwd;initialcatalog=DBName;Server=DBServerFQDN;ConnectTimeout=30;”providerName=”System.Data.SqlClient”/>

</connectionStrings>

数据库查询超时

在.NET的SqlCommand类,有CommandTimeout属性,获取或设置在终止尝试执行命令并生成错误之前的等待时间.等待命令执行所需的时间(以秒为单位).默认值为30秒.如果请求正在运行,并且没有在超时时间内完成,那么我们将看到这个错误.值为0表示无限制.

主要是通过SqlCommand.CommandTimeout来进行控制.如下：

SqlCommandcommand=newSqlCommand(queryString,connection);

//Settingcommandtimeoutto1second

command.CommandTimeout=1;

我们另外再介绍一种超时,在.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失败的信息.

传入连接：查询超时

对于查询超时,针对SQL Server 2012以下的版本,使用Profiler的TSQL_Duration模板的基础上,添加“Errors and Warnings”下的“Attention”,根据捕获到的Attention结合上下文去查找相应的语句；对于SQL Server 2012及以上版本,直接使用扩展事件监控sqlserver.attention事件,直接输出sql_text.

以下为XE脚本：

调优建议

对于连接耗时,当然务必要找到具体原因,是网络问题还是验证问题；对于查询超时,多为语句性能问题导致,如阻塞、未使用合理的索引、输出数据量太大等原因.对于临时解决问题,可以在连接配置里、或在程序里的语句级参数属性调大配置值.应及时找出问题的根源并解决.

传入连接：数据库跨实例远程访问超时

链接服务器远程访问导致的连接超时和查询超时,我们可以在目标数据库服务器上来使用以上方法来监控和分析.

调优建议

当使用链接服务器(LinkedServers)时,最昂贵的代价就是网络带宽间大量数据的传输.在正确的服务器书写正确的代码是非常重要的,因为每一个错误都会导致在网络带宽上付出非常昂贵的代价.

尽量避免使用链接服务器向远程推送数据,而是使用LinkedServer.DatabaseName.dbo.TableName为源从远程拉取数据.

跨服务器查询时,为了在两台服务器之间的数据集之间执行JOIN操作,SQLServer需要将数据从一台服务器传送到另外一台服务器.如果传送的数据是一个非常大的表,这个过程可能会非常痛苦.通常来说,数据会从远程服务器传送到本地服务器.为了防止大量数据在服务器之间大传送,你可以通过在查询条件中过滤数据,通过一个远程存储过程只取回相关数据来达到目的,万一你需要使用INNERJOIN关联两个不同服务器之间的数据集,而且本地表的数据量远小于远程服务器的那个表.你可以使用REMOTEJOINHINT,这样就会将数据从本地服务器将数据传送到远程服务器,从而提高性能.

正如JOIN操作,UNIION不同服务器之间的两个数据集必定导致从远程服务器传送数据到本地服务器.即使你执行远程查询合并(UNION)同一个远程服务器的两个数据集,还是会先将两个数据集传送到本地服务器,然后UNION两个数据集,可以通过远程存储过程,函数或视图先UNION数据库来阻止这个.

避免书写太复杂的查询语句.优化器不能总是能明白你需要做什么,尤其是你的SQL语句中使用了链接服务器时,保持SQL脚本简单.

当数据库位于同一个实例时不要使用链接服务器.而是直接使用跨库访问Database.dbo.TableName来访问.

链接服务器的滥用可能会导致数据库出现很多ASYNC_NETWORK_IO等待事件.你可以通过发布-订阅或者作业将数据集(表)数据先同步到本地服务器,然后将SQL脚本中的链接服务器去掉,这样对SQL查询性能有非常大的提升,尤其是查询比较频繁或数据量大的SQL语句.

对于链接服务器的对象调用,尽量使用同义词,这样简化了管理.

文章出处：DBAplus社群(订阅号ID：dbaplus)

转载请注明本页网址：
http://www.vephp.com/jiaocheng/4395.html