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

作者介紹

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

一、超時(shí)分析

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

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

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

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

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

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

首先,針對傳入連接,我們來看看Web端的訪問超時(shí)：有ASP.NET請求超時(shí)、WebService請求超時(shí)、IIS請求超時(shí)、數(shù)據(jù)庫連接超時(shí)和查詢超時(shí).

ASP.NET請求超時(shí)

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

來自MSDN的解釋：

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

executionTimeout：表示允許執(zhí)行請求的最大時(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請求運(yùn)行時(shí)間：

<system.web>

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

</system.web>

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

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

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

Server.ScriptTimeout=120;

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

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

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

WebService請求超時(shí)

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

YourWebServiceyws=newYourWebService();

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

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

IIS請求超時(shí)

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

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

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

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

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

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

<connectionStrings>

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

</connectionStrings>

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

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

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

SqlCommandcommand=newSqlCommand(queryString,connection);

//Settingcommandtimeoutto1second

command.CommandTimeout=1;

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

每次對數(shù)據(jù)庫連接時(shí),我們有時(shí)候會碰到連接超時(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í)主要是通過連接字符串中的ConnectTimeout和SqlCommand.CommandTimeout來進(jìn)行控制.前面兩種是登錄超時(shí)由ConnectionTimeout來決定什么時(shí)候超時(shí),后面幾種是命令超時(shí)由CommandTimeout來決定什么時(shí)候超時(shí).

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

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

接著,針對傳入連接,我們來看看用戶通過SQLServerManagementStudio即SSMS訪問數(shù)據(jù)庫時(shí)的超時(shí)設(shè)置.

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

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

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

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

查看配置選項(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ī)器停止嘗試登錄前,不用無限等待下去.這個(gè)選項(xiàng)的默認(rèn)值為10秒.值為0表示無限等待.

在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ù)庫引擎發(fā)起的作為遠(yuǎn)程查詢的傳出連接.這個(gè)值對于數(shù)據(jù)庫引擎收到的查詢無效.為了禁止超時(shí),可以設(shè)置為為0.那么查詢將會一直等待直到取消.

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

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

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

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

EXECsp_configure’remotequerytimeout’,0;

GO

RECONFIGURE;

GO

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

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

設(shè)置語法如下：

sp_serveroption[@server=]’server’

,[@optname=]’option_name’

,[@optvalue=]’option_value’;

 

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

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

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

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

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

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

SELECTCAST(recordASXML)FROMsys.dm_os_ring_buffers

WHEREring_buffer_type=’RING_BUFFER_CONNECTIVITY’

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

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

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

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

RecordType包含那些值?

Error–連接錯(cuò)誤

LoginTimers–連接超時(shí)

ConnectionClose–殺掉進(jìn)程

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

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

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

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

以下為XE腳本：

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

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

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

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

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

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

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

跨服務(wù)器查詢時(shí),為了在兩臺服務(wù)器之間的數(shù)據(jù)集之間執(zhí)行JOIN操作,SQLServer需要將數(shù)據(jù)從一臺服務(wù)器傳送到另外一臺服務(wù)器.如果傳送的數(shù)據(jù)是一個(gè)非常大的表,這個(gè)過程可能會非常痛苦.通常來說,數(shù)據(jù)會從遠(yuǎn)程服務(wù)器傳送到本地服務(wù)器.為了防止大量數(shù)據(jù)在服務(wù)器之間大傳送,你可以通過在查詢條件中過濾數(shù)據(jù),通過一個(gè)遠(yuǎn)程存儲過程只取回相關(guān)數(shù)據(jù)來達(dá)到目的,萬一你需要使用INNERJOIN關(guān)聯(lián)兩個(gè)不同服務(wù)器之間的數(shù)據(jù)集,而且本地表的數(shù)據(jù)量遠(yuǎn)小于遠(yuǎn)程服務(wù)器的那個(gè)表.你可以使用REMOTEJOINHINT,這樣就會將數(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ù)集,還是會先將兩個(gè)數(shù)據(jù)集傳送到本地服務(wù)器,然后UNION兩個(gè)數(shù)據(jù)集,可以通過遠(yuǎn)程存儲過程,函數(shù)或視圖先UNION數(shù)據(jù)庫來阻止這個(gè).

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

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

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

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

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

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