《如何診斷和調(diào)優(yōu)，才能輕松與數(shù)據(jù)庫(kù)“timeout”說(shuō)再見(jiàn)？》要點(diǎn)：
本文介紹了如何診斷和調(diào)優(yōu)，才能輕松與數(shù)據(jù)庫(kù)“timeout”說(shuō)再見(jiàn)？，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

作者介紹

許昌永,高級(jí)DBA,微軟SQLServerMVP,十年以上SQLServer使用經(jīng)驗(yàn).曾就職于騰訊公司,從事了六年游戲行業(yè)SQLServer數(shù)據(jù)庫(kù)開(kāi)發(fā)和管理.目前就職于跨境電商DX.COM三年多,負(fù)責(zé)公司SQLServer和MongoDB的數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)、高可用部署、運(yùn)維管理和性能優(yōu)化等工作.翻譯出版了書(shū)籍《PowerShellV3——SQLServer2012數(shù)據(jù)庫(kù)自動(dòng)化運(yùn)維權(quán)威指南》,文末將有許老師的新書(shū)贈(zèng)送,不容錯(cuò)過(guò)~

一、超時(shí)分析

下面是用戶訪問(wèn)一個(gè)Web站點(diǎn)的常見(jiàn)錯(cuò)誤：

詳細(xì)錯(cuò)誤描述如下：

以上輸出非常清晰地描述了,對(duì)于這個(gè)操作的超時(shí)時(shí)間結(jié)束,而實(shí)際上工作并沒(méi)有完成.

我們常常會(huì)發(fā)現(xiàn)“timeout”錯(cuò)誤,那么具體是在哪個(gè)訪問(wèn)階段、受哪個(gè)設(shè)置影響報(bào)出來(lái)的呢?下面,我們通過(guò)典型的Web應(yīng)用架構(gòu)來(lái)分析下超時(shí)問(wèn)題.

結(jié)合上面的訪問(wèn)關(guān)系圖,可以看到：我們從數(shù)據(jù)庫(kù)實(shí)例的角度出發(fā),它會(huì)收到來(lái)自Web端的訪問(wèn)、用戶的直接訪問(wèn),它也可能同時(shí)訪問(wèn)其他數(shù)據(jù)庫(kù)實(shí)例.那么,連接就分為傳入連接(Incomingconnection)訪問(wèn)和傳出連接(Outgoingconnection)訪問(wèn).那我們就根據(jù)不同階段的訪問(wèn)來(lái)分類超時(shí)問(wèn)題.

傳入連接：Web端訪問(wèn)超時(shí)

首先,針對(duì)傳入連接,我們來(lái)看看Web端的訪問(wèn)超時(shí)：有ASP.NET請(qǐng)求超時(shí)、WebService請(qǐng)求超時(shí)、IIS請(qǐng)求超時(shí)、數(shù)據(jù)庫(kù)連接超時(shí)和查詢超時(shí).

ASP.NET請(qǐng)求超時(shí)

ASP.NET頁(yè)面的運(yùn)行超時(shí)時(shí)間可以在多個(gè)地方設(shè)置.

來(lái)自MSDN的解釋：

httpRuntime是配置asp.Nethttp運(yùn)行時(shí)設(shè)置,以確定如何處理對(duì)asp.Net應(yīng)用程序的請(qǐng)求.

executionTimeout：表示允許執(zhí)行請(qǐng)求的最大時(shí)間限制,單位為秒.默認(rèn)值為90秒.

maxRequestLength：指示ASP.Net支持的最大文件上載大小.該限制可用于防止因用戶將大量文件傳遞到該服務(wù)器而導(dǎo)致的拒絕服務(wù)攻擊.指定的大小以KB為單位.默認(rèn)值為4096KB(4MB).

1、全局超時(shí)時(shí)間

服務(wù)器上如果有多個(gè)網(wǎng)站,希望統(tǒng)一設(shè)置一下超時(shí)時(shí)間,則需要設(shè)置Machine.config文件中的ExecutionTimeout屬性值.Machine.config文件位于%SystemRoot%\Microsoft.NET\Framework\%VersionNumber%\CONFIG\目錄中.

例如：

<httpRuntimeexecutionTimeout=”90″maxRequestLength=”4096″

useFullyQualifiedRedirectUrl=”false”minFreeThreads=”8″

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

2、單個(gè)站點(diǎn)超時(shí)時(shí)間

Web.config配置文件中設(shè)置http請(qǐng)求運(yùn)行時(shí)間：

<system.web>

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

</system.web>

這里設(shè)置的為720秒,前面的屬性maxRequestLength一般用于用戶上傳文件限制大小!默認(rèn)一般為4096KB(4MB).

3、單個(gè)頁(yè)面請(qǐng)求超時(shí)時(shí)間

對(duì)于單個(gè)頁(yè)面,可以使用Server.ScriptTimeout來(lái)設(shè)定超時(shí).

Server.ScriptTimeout=120;

注意：如果在Web.config里設(shè)置了debug屬性,例如：

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

此時(shí),ScriptTimeout會(huì)被忽略.

WebService請(qǐng)求超時(shí)

擴(kuò)大代理類的超時(shí)限制,默認(rèn)是90秒,即在調(diào)用方法前指定超時(shí)時(shí)間.

YourWebServiceyws=newYourWebService();

yws.Timeout=1200000;//20分鐘,單位是毫秒

如果將Timeout屬性設(shè)置為T(mén)imeout.Infinite,則指示該請(qǐng)求無(wú)超時(shí).即使XMLWebservices客戶端可以將Timeout屬性設(shè)置為無(wú)超時(shí),Web服務(wù)器仍可以在服務(wù)器端使請(qǐng)求超時(shí).

IIS請(qǐng)求超時(shí)

在IISManager中,選中Sites,點(diǎn)擊右側(cè)的WebsiteDefaults,在Limits屬性列表中,設(shè)置連接超時(shí)時(shí)間ConnectionTime-out(seconds).默認(rèn)值為120秒.

連接超時(shí)有助于減少由空閑連接消耗的處理資源損失.啟用連接超時(shí)時(shí),IIS會(huì)在連接級(jí)別執(zhí)行以下類型的連接超時(shí)：客戶端已向服務(wù)器發(fā)送了數(shù)據(jù),現(xiàn)處于空閑狀態(tài)造成的連接超時(shí).

已建立了與服務(wù)器的連接,但客戶端未發(fā)送數(shù)據(jù)時(shí)造成的服務(wù)器偵聽(tīng)超時(shí).響應(yīng)超時(shí)(基于可配置的最小字節(jié)數(shù)/秒的值).請(qǐng)求超時(shí),它禁止客戶端向服務(wù)器發(fā)送不合理的慢速請(qǐng)求(例如,1比特/秒).

數(shù)據(jù)庫(kù)連接超時(shí)

在.NET的SqlConnection類,有ConnectionTimeout屬性,獲取終止嘗試并生成錯(cuò)誤之前在嘗試建立連接時(shí)所等待的時(shí)間.等待連接打開(kāi)所需的時(shí)間(以秒為單位).默認(rèn)值為15秒.在這個(gè)時(shí)間內(nèi),如果連接沒(méi)有建立,我們將會(huì)看到這個(gè)錯(cuò)誤.值為0表示無(wú)限制.

主要通過(guò)連接字符串中的ConnectTimeout來(lái)進(jìn)行控制,如下：

<connectionStrings>

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

</connectionStrings>

數(shù)據(jù)庫(kù)查詢超時(shí)

在.NET的SqlCommand類,有CommandTimeout屬性,獲取或設(shè)置在終止嘗試執(zhí)行命令并生成錯(cuò)誤之前的等待時(shí)間.等待命令執(zhí)行所需的時(shí)間(以秒為單位).默認(rèn)值為30秒.如果請(qǐng)求正在運(yùn)行,并且沒(méi)有在超時(shí)時(shí)間內(nèi)完成,那么我們將看到這個(gè)錯(cuò)誤.值為0表示無(wú)限制.

主要是通過(guò)SqlCommand.CommandTimeout來(lái)進(jìn)行控制.如下：

SqlCommandcommand=newSqlCommand(queryString,connection);

//Settingcommandtimeoutto1second

command.CommandTimeout=1;

我們另外再介紹一種超時(shí),在.NET的SqlBulkCopy類,有BulkCopyTimeout屬性,超時(shí)之前操作完成所允許的秒數(shù).如果操作超時(shí),事務(wù)便不會(huì)提交,而且所有已復(fù)制的行都會(huì)從目標(biāo)表中移除.使用SqlBulkCopy批量加載數(shù)據(jù)時(shí)的默認(rèn)超時(shí)設(shè)置為30秒.

每次對(duì)數(shù)據(jù)庫(kù)連接時(shí),我們有時(shí)候會(huì)碰到連接超時(shí)或者命令超時(shí),這兩個(gè)超時(shí)是不一樣的.以ADO.NET為例,當(dāng)客戶端和服務(wù)器端連接時(shí),碰到的超時(shí)情況主要有下面幾種：

• 當(dāng)從連接池獲取一個(gè)連接時(shí),碰到超時(shí).
• 當(dāng)建立一個(gè)全新連接(而不是從連接池獲取)時(shí),碰到超時(shí).
• 當(dāng)發(fā)送一個(gè)命令(command)到SQLServer時(shí),超時(shí).
• 當(dāng)發(fā)送命令(連接字符串帶有“contextconnection=true”屬性)到SQLServer時(shí),超時(shí).
• 當(dāng)不是顯示的發(fā)送一個(gè)命令(implicitly)到SQLServer時(shí),碰到超時(shí).
• 當(dāng)執(zhí)行異步命令時(shí),(BeginExecute)碰到超時(shí).
• 當(dāng)從服務(wù)器端,獲取行時(shí),碰到超時(shí).
• 當(dāng)用Bulkcopy方式,上傳數(shù)據(jù)時(shí),碰到超時(shí).

這些超時(shí)主要是通過(guò)連接字符串中的ConnectTimeout和SqlCommand.CommandTimeout來(lái)進(jìn)行控制.前面兩種是登錄超時(shí)由ConnectionTimeout來(lái)決定什么時(shí)候超時(shí),后面幾種是命令超時(shí)由CommandTimeout來(lái)決定什么時(shí)候超時(shí).

特別注意：“超時(shí)時(shí)間已到.在操作完成之前超時(shí)時(shí)間已過(guò)或服務(wù)器未響應(yīng)”.類似這種錯(cuò)誤,一般是SqlCommand.CommandTimeout或者SqlBulkCopy.BulkCopyTimeout的時(shí)間超時(shí),而不是SqlConnection.ConnectionTimeout.

傳入連接：SSMS訪問(wèn)超時(shí)

接著,針對(duì)傳入連接,我們來(lái)看看用戶通過(guò)SQLServerManagementStudio即SSMS訪問(wèn)數(shù)據(jù)庫(kù)時(shí)的超時(shí)設(shè)置.

這里,我們可以設(shè)置SSMS工具的連接和查詢超時(shí)時(shí)間.連接超時(shí)的默認(rèn)值為15秒.而查詢超時(shí)的默認(rèn)值為0,表示查詢會(huì)一直運(yùn)行直到完成.

傳出連接：數(shù)據(jù)庫(kù)跨實(shí)例遠(yuǎn)程訪問(wèn)超時(shí)

最后,針對(duì)傳出連接,我們來(lái)看看數(shù)據(jù)庫(kù)跨實(shí)例遠(yuǎn)程訪問(wèn)的超時(shí)設(shè)置.

數(shù)據(jù)庫(kù)從一個(gè)實(shí)例訪問(wèn)到另一個(gè)實(shí)例,可以通過(guò)以下方式查看到.

查看配置選項(xiàng)的設(shè)置：

遠(yuǎn)程登錄超時(shí)

遠(yuǎn)程登錄超時(shí)選項(xiàng)指定了,從登錄遠(yuǎn)程服務(wù)器失敗返回前等待的秒數(shù).例如,如果你嘗試登錄到一個(gè)遠(yuǎn)程服務(wù)器,而服務(wù)器宕機(jī)了,遠(yuǎn)程登錄超時(shí)幫助你在你的機(jī)器停止嘗試登錄前,不用無(wú)限等待下去.這個(gè)選項(xiàng)的默認(rèn)值為10秒.值為0表示無(wú)限等待.

在SQLServer2008中,這個(gè)選項(xiàng)的默認(rèn)值為20秒.

遠(yuǎn)程登錄超時(shí)選項(xiàng)影響了異構(gòu)查詢的OLEDB提供者產(chǎn)生的連接.

這個(gè)設(shè)置不用重啟服務(wù)立即生效.

SQLServer2014的遠(yuǎn)程登錄超時(shí)時(shí)間默認(rèn)為10秒.下面的腳本可以修改該值：

EXECsp_configure’remotelogintimeout’,35;

GO

RECONFIGURE;

GO

遠(yuǎn)程查詢超時(shí)

遠(yuǎn)程查詢超時(shí)選項(xiàng)指定了,在SQLServer超時(shí)前一個(gè)遠(yuǎn)程操作花費(fèi)了多少秒.默認(rèn)值為600秒,允許10分鐘的等待.這個(gè)值應(yīng)用于數(shù)據(jù)庫(kù)引擎發(fā)起的作為遠(yuǎn)程查詢的傳出連接.這個(gè)值對(duì)于數(shù)據(jù)庫(kù)引擎收到的查詢無(wú)效.為了禁止超時(shí),可以設(shè)置為為0.那么查詢將會(huì)一直等待直到取消.

對(duì)于異構(gòu)查詢,遠(yuǎn)程查詢超時(shí)指定了,在查詢超時(shí)前,一個(gè)遠(yuǎn)程提供者應(yīng)該等待結(jié)果的秒數(shù).(使用DBPROP_COMMANDTIMEOUT行集屬性在命令對(duì)象初始化).如果被遠(yuǎn)程提供者支持,這個(gè)值也被用戶設(shè)置DBPROP_COMMANDTIMEOUT.在指定的數(shù)秒后,這將導(dǎo)致任何其他的操作超時(shí).

對(duì)于遠(yuǎn)程存儲(chǔ)過(guò)程,遠(yuǎn)程查詢超時(shí)指定的秒數(shù)為,在遠(yuǎn)程存儲(chǔ)過(guò)程超時(shí)之前,在發(fā)送一個(gè)遠(yuǎn)程EXEC語(yǔ)句之后花費(fèi)的時(shí)間.

這個(gè)設(shè)置不用重啟服務(wù)立即生效.

SQLServer2014的遠(yuǎn)程查詢超時(shí)為10分鐘,可以通過(guò)以下腳本修改該值：

EXECsp_configure’remotequerytimeout’,0;

GO

RECONFIGURE;

GO

遠(yuǎn)程服務(wù)器和鏈接服務(wù)器的對(duì)應(yīng)選項(xiàng)

在配置遠(yuǎn)程訪問(wèn)的時(shí)候,可以設(shè)置鏈接服務(wù)器的超時(shí)選項(xiàng),也分連接超時(shí)和查詢超時(shí).

設(shè)置語(yǔ)法如下：

sp_serveroption[@server=]’server’

,[@optname=]’option_name’

,[@optvalue=]’option_value’;

 

二、數(shù)據(jù)庫(kù)超時(shí)診斷和調(diào)優(yōu)

傳入連接：連接超時(shí)

對(duì)于連接超時(shí),首先可以查看ConnectivityRingBuffer中的LoginTimers類型錯(cuò)誤來(lái)分析,如果想獲得更詳盡的信息,再通過(guò)抓包工具networkmonitor.

SQLServer2008中包含一個(gè)新功能,旨在幫助解決特別棘手的連接問(wèn)題.

這個(gè)新功能是ConnectivityRingBuffer,它可以捕捉每一個(gè)由服務(wù)器發(fā)起的連接關(guān)閉記錄(server-initiatedconnectionclosure),包括每一個(gè)session或登錄失敗事件.為了進(jìn)行有效的故障排除,RingBuffer會(huì)嘗試提供客戶端的故障和服務(wù)器的關(guān)閉動(dòng)作之間的關(guān)系信息.只要服務(wù)器在線,最高1K的RingBuffer就會(huì)被保存,1000條記錄后,Buffer開(kāi)始循環(huán)覆蓋,即從最老的記錄開(kāi)始覆蓋.

ConnectivityRingBuffer的記錄是能夠使用DMV查詢的：

SELECTCAST(recordASXML)FROMsys.dm_os_ring_buffers

WHEREring_buffer_type=’RING_BUFFER_CONNECTIVITY’

首先我們從連接的RingBuffer數(shù)據(jù)返回的XML來(lái)入手.執(zhí)行上面的語(yǔ)句,得到下面的結(jié)果：

點(diǎn)擊XML的超鏈接,打開(kāi)文件內(nèi)容看到更可讀的內(nèi)容,包括一條基本的Ring Buffer連接超時(shí)記錄.

可以看到在XML文檔中有許多相當(dāng)有用的信息.像SniConsumerError,State和RemoteHost這些.

特別注意的是,RecordType節(jié)點(diǎn),對(duì)于我們上面的截圖來(lái)看標(biāo)識(shí)為“LoginTimers”,說(shuō)明是連接超時(shí)信息.為了識(shí)別這種類型的連接RingBuffer,我們可以查詢SniConsumerError代碼號(hào),準(zhǔn)確定位是什么錯(cuò)誤導(dǎo)致的.

RecordType包含那些值?

Error–連接錯(cuò)誤

LoginTimers–連接超時(shí)

ConnectionClose–殺掉進(jìn)程

可以通過(guò)如下腳本,將XML數(shù)據(jù)轉(zhuǎn)化為可讀信息：

執(zhí)行上面的查詢后,將得到下面的可讀結(jié)果.在這個(gè)查詢中,我們關(guān)聯(lián)Ring Buffer數(shù)據(jù)和sys.messages視圖去抓取Error id的文本.通過(guò)這個(gè)信息我們可以跟蹤到精確的導(dǎo)致Error:Login失敗的信息.

傳入連接：查詢超時(shí)

對(duì)于查詢超時(shí),針對(duì)SQL Server 2012以下的版本,使用Profiler的TSQL_Duration模板的基礎(chǔ)上,添加“Errors and Warnings”下的“Attention”,根據(jù)捕獲到的Attention結(jié)合上下文去查找相應(yīng)的語(yǔ)句；對(duì)于SQL Server 2012及以上版本,直接使用擴(kuò)展事件監(jiān)控sqlserver.attention事件,直接輸出sql_text.

以下為XE腳本：

調(diào)優(yōu)建議

對(duì)于連接耗時(shí),當(dāng)然務(wù)必要找到具體原因,是網(wǎng)絡(luò)問(wèn)題還是驗(yàn)證問(wèn)題；對(duì)于查詢超時(shí),多為語(yǔ)句性能問(wèn)題導(dǎo)致,如阻塞、未使用合理的索引、輸出數(shù)據(jù)量太大等原因.對(duì)于臨時(shí)解決問(wèn)題,可以在連接配置里、或在程序里的語(yǔ)句級(jí)參數(shù)屬性調(diào)大配置值.應(yīng)及時(shí)找出問(wèn)題的根源并解決.

傳入連接：數(shù)據(jù)庫(kù)跨實(shí)例遠(yuǎn)程訪問(wèn)超時(shí)

鏈接服務(wù)器遠(yuǎn)程訪問(wèn)導(dǎo)致的連接超時(shí)和查詢超時(shí),我們可以在目標(biāo)數(shù)據(jù)庫(kù)服務(wù)器上來(lái)使用以上方法來(lái)監(jiān)控和分析.

調(diào)優(yōu)建議

當(dāng)使用鏈接服務(wù)器(LinkedServers)時(shí),最昂貴的代價(jià)就是網(wǎng)絡(luò)帶寬間大量數(shù)據(jù)的傳輸.在正確的服務(wù)器書(shū)寫(xiě)正確的代碼是非常重要的,因?yàn)槊恳粋€(gè)錯(cuò)誤都會(huì)導(dǎo)致在網(wǎng)絡(luò)帶寬上付出非常昂貴的代價(jià).

盡量避免使用鏈接服務(wù)器向遠(yuǎn)程推送數(shù)據(jù),而是使用LinkedServer.DatabaseName.dbo.TableName為源從遠(yuǎn)程拉取數(shù)據(jù).

跨服務(wù)器查詢時(shí),為了在兩臺(tái)服務(wù)器之間的數(shù)據(jù)集之間執(zhí)行JOIN操作,SQLServer需要將數(shù)據(jù)從一臺(tái)服務(wù)器傳送到另外一臺(tái)服務(wù)器.如果傳送的數(shù)據(jù)是一個(gè)非常大的表,這個(gè)過(guò)程可能會(huì)非常痛苦.通常來(lái)說(shuō),數(shù)據(jù)會(huì)從遠(yuǎn)程服務(wù)器傳送到本地服務(wù)器.為了防止大量數(shù)據(jù)在服務(wù)器之間大傳送,你可以通過(guò)在查詢條件中過(guò)濾數(shù)據(jù),通過(guò)一個(gè)遠(yuǎn)程存儲(chǔ)過(guò)程只取回相關(guān)數(shù)據(jù)來(lái)達(dá)到目的,萬(wàn)一你需要使用INNERJOIN關(guān)聯(lián)兩個(gè)不同服務(wù)器之間的數(shù)據(jù)集,而且本地表的數(shù)據(jù)量遠(yuǎn)小于遠(yuǎn)程服務(wù)器的那個(gè)表.你可以使用REMOTEJOINHINT,這樣就會(huì)將數(shù)據(jù)從本地服務(wù)器將數(shù)據(jù)傳送到遠(yuǎn)程服務(wù)器,從而提高性能.

正如JOIN操作,UNIION不同服務(wù)器之間的兩個(gè)數(shù)據(jù)集必定導(dǎo)致從遠(yuǎn)程服務(wù)器傳送數(shù)據(jù)到本地服務(wù)器.即使你執(zhí)行遠(yuǎn)程查詢合并(UNION)同一個(gè)遠(yuǎn)程服務(wù)器的兩個(gè)數(shù)據(jù)集,還是會(huì)先將兩個(gè)數(shù)據(jù)集傳送到本地服務(wù)器,然后UNION兩個(gè)數(shù)據(jù)集,可以通過(guò)遠(yuǎn)程存儲(chǔ)過(guò)程,函數(shù)或視圖先UNION數(shù)據(jù)庫(kù)來(lái)阻止這個(gè).

避免書(shū)寫(xiě)太復(fù)雜的查詢語(yǔ)句.優(yōu)化器不能總是能明白你需要做什么,尤其是你的SQL語(yǔ)句中使用了鏈接服務(wù)器時(shí),保持SQL腳本簡(jiǎn)單.

當(dāng)數(shù)據(jù)庫(kù)位于同一個(gè)實(shí)例時(shí)不要使用鏈接服務(wù)器.而是直接使用跨庫(kù)訪問(wèn)Database.dbo.TableName來(lái)訪問(wèn).

鏈接服務(wù)器的濫用可能會(huì)導(dǎo)致數(shù)據(jù)庫(kù)出現(xiàn)很多ASYNC_NETWORK_IO等待事件.你可以通過(guò)發(fā)布-訂閱或者作業(yè)將數(shù)據(jù)集(表)數(shù)據(jù)先同步到本地服務(wù)器,然后將SQL腳本中的鏈接服務(wù)器去掉,這樣對(duì)SQL查詢性能有非常大的提升,尤其是查詢比較頻繁或數(shù)據(jù)量大的SQL語(yǔ)句.

對(duì)于鏈接服務(wù)器的對(duì)象調(diào)用,盡量使用同義詞,這樣簡(jiǎn)化了管理.

文章出處：DBAplus社群(訂閱號(hào)ID：dbaplus)

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/4395.html