[MS SQL]利用Extended Events找出逾時過時(Timeout Expired)錯誤的SQL Command

在金控銀行內提供即時金融服務以及個人消費資訊的簡訊平台,必須同時兼具大量運算處理的效能以及即時快速的通訊能力,但資料庫必須保留數年的資料以供查詢。故事是這樣發生的,使用者向銀行資訊中心反應有時候查詢跨月份的彙總報表時,會出現資料庫查詢逾時的錯誤,但大部分時候查詢報表又可以正常出現….

簡訊系統管理員請求SQL DBA組同仁協助進行效能調校,但要找出會發生逾時錯誤的SQL語法又不是這麼容易,原因一是這個彙總報表本就為了提升效能改寫成使用多個SQL語法進行查詢,再於後端AP彙整計算,所以直接查程式碼也不能確認是哪一個語法會逾時;

原因二是偶爾才會查詢逾時(一個月出現兩三次),沒有辦法用SQL Profiler做長時間的錄製,會影響到SQL Server的執行效能;

原因三是所謂資料庫逾時過時(Timeout Expired)的錯誤,其實是後端AP在對SQL Server執行SQL Command時,受到資料庫連線字串的Timeout參數的限制(預設為30秒),當執行時間超過Timeout時間時,AP會主動取消此SQL Command的執行,並丟出Timeout Expired的錯誤,但問題是就算使用SQL Profiler去收集Timeout Expired的錯誤,Attention事件並不直接提供TextData,也就是SQL語法的欄位(見下圖)。這意味著你必須利用Attention事件與其他事件結合起來,才有辦法找出到底是哪個SQL語法發生了Timeout Expired。

SQL Server 2008後推出了Extended Events(擴充事件)的功能

還好SQL Server 2008後推出了Extended Events(擴充事件)的功能,以較不影響SQL Server效能的方式去監控事件。下面的步驟會說明如何設定Extended Events來監控Timeout Expired的事件,DBA同仁可以快速有效的完成:

1.展開SQL Server→管理→擴充事件→工作階段→右鍵→新增工作階段

展開SQL Server,新增工作階段

2.一般→輸入工作階段名稱,這邊因為一開始推測發生Deadlock的原因,所以取名lock

新增工作階段,取名lock

3.事件→輸入事件名稱或選取清單內的事件→「>」→成為選取的事件。因為我們想要監控逾時過時的事件,所以選擇attention,也因為懷疑原因跟deadlock有關,順便把deadlock相關事件加進來,最後右上角有個設定按鈕可以做進階設定

將事件中的輸入事件名稱或選取清單內的事件新增成為選取的事件

4.不同的事件會記錄專屬此事件的欄位資料,如果還想要多紀錄一些其他資訊供參考,就要額外勾選全域欄位,我們的目的是要抓到SQL Command,所以記得勾選sql_text(收集SQL文字)

額外勾選全域欄位

5.SQL Server上有多個資料庫,可以利用篩選→database_id來過濾監控的資料庫

利用篩選database_id來過濾監控的資料庫

6.這邊可以看到attention事件,預設只會紀錄duration與request_id兩個欄位

attention事件,預設只會紀錄duration與request_id兩個欄位

7.資料存放區→選擇event_file→加入

資料存放區,選擇event_file→加入

8.最後進階依照預設設定做確認,即設定好了一個監控的工作階段。記得選取此工作階段→右鍵→啟動工作階段,才會真正開始監控事件

依照預設設定做確認

9.當使用者再度反應發生Timeout Expired時,我們可依其提供的發生時間,回去追查到問題的Sql Command

回去追查到問題的Sql Command

接下來是加碼介紹Extended Events可以怎麼分析其結果:

10.預設上方Grid只會出現Event名稱與事件發生時間,下方詳細資料則是勾選的全域欄位與事件欄位,我們可以在上方Grid標題列→右鍵→選擇資料行,將詳細資料的欄位也加到上方Grid區(例如cllient_app_name)

預設上方Grid只會出現Event名稱與事件發生時間

11.在client_app_name欄位的標題列區→右鍵→依此資料行群組

在client_app_name欄位的標題列區,按右鍵並選擇依此資料行群組

12.出現群組後的結果,其中.Net SqlClient Data Provider群組展開,會列出所有讓AP發生Timeout Expired的語法(根本可以當作Client端AP健康檢查的工具了 ????);Microsoft SQL Server Management Studio -查詢群組展開,則會列出所有透過SSMS下Command,又取消執行的語法

出現群組後的結果,其中.Net SqlClient Data Provider群組展開

展開群組後:

展開群組後

13.工具列除了前面提到的那些功能外,還有彙總和篩選功能可以操作

工具列除了前面提到的那些功能外,還有彙總和篩選功能可以操作

最後,找到會發生Timeout Expired的SQL語法,只是解決問題的第一步,要怎麼讓該語法不再Timeout,又是另一個可以講的故事了。


發表迴響

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料