《如何診斷和調優，才能輕松與數據庫“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.fzlkiss.com/jiaocheng/4395.html