[SQL][Troubleshooting]利用 DMV 和 SQL Agent 自動刪除一些造成其他作業被 Blocking 的連線
這幾天在幫一些客戶查一些資料庫在使用上的問題時，本來想說透過一些 SSMS 上面 Instance 報表 「Activity - All Blocking Transactions」來查看，但用起來不是順手，剛好有同事再詢問到底要怎麼來查看 Locking 的問題，由於他們都是工程背景出身的人員，因此希望用比較簡單的方式來查看問題。
而要了解這個問題的時候，首先我們要先知道幾個關於查看執行狀況的 DMV
sys.dm_exec_connections http://msdn.microsoft.com/zh-tw/library/ms181509.aspx
sys.dm_exec_sessions http://msdn.microsoft.com/zh-tw/library/ms176013.aspx
sys.dm_exec_requests http://msdn.microsoft.com/zh-tw/library/ms177648.aspx
其中要特別注意 sys.dm_exec_connections 裡面主要放的都是一些連線的資訊，因此可以透過這個 DMV 查到由哪個連線是何時、何地、何人透過甚麼樣的通訊協定來連接 SQL Server。而 sys.dm_exec_sessions 這個 DMV 主要放的是更細的資料，像是連接到哪個資料庫、連線的 SET 屬性值等更細的資訊，而資料筆數會比 sys.dm_exec_connections 多，其中 session_id 小於 50 的都是屬於系統所使用。而這兩個主要就看我們要取得哪些連線資訊，再來決定要使用哪個 DMV
而如果當我們要查看目前 session 在處理哪個指令的時候，一般情況可以使用兩個方式。第一種是利用 sys.dm_exec_requests 這個 DMV 下面的 sql_handle 欄位，可以取得 session 正在執行中尚未執行完畢的資料，因此如果命令執行完畢在等待後面的指令時，則這個時候就該 session 在 dm_exec_requests 內就沒有資料了。第二種是取 sys.dm_exec_connections 下的 most_recent_sql_handle 這個欄位，而這個欄位主要是取得目前這個 session 最後一次所送出的指令，因此如果要抓之前是送出甚麼指令的時候，則可以使用該欄位。但這兩者使用的時候要注意，因此取到的是一個 sql_handle 值，因此要透過 dm_exec_sql_text 這個來取得真正的 SQL Script。
了解基本指令之後，那我們就可以想辦法用指令來查看是否有 Locking 的狀況發生
WITH RootBlocking AS
(
SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests
WHERE blocking_session_id > 50
AND blocking_session_id not In
( SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 50 )
)
SELECT ses.session_id,ses.host_name,ses.program_name,ses.login_name,ses.status, ses.last_request_end_time,
ct1.text sql_text,ct2.text recent_sql_text
FROM RootBlocking rot
INNER JOIN sys.dm_exec_connections con ON rot.blocking_session_id = con.session_id
INNER JOIN sys.dm_exec_sessions ses ON rot.blocking_session_id = ses.session_id
LEFT JOIN sys.dm_exec_requests req ON rot.blocking_session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) ct1
OUTER APPLY sys.dm_exec_sql_text(con.most_recent_sql_handle) ct2
在上述這個語法當中，前半段我利用 CTE 的方式，來找到引發 Locking 的始作俑者，因此我利用 sys.dm_exec_requests 這個 DMV 來找到所有 blocking 別人的 session，而這些 session 如果自己也是被別人 blocking 的話那就不要了，這樣當我們有 Session 53 被 Session 55 給鎖住，而 Session 55 又被 Session 57 給鎖住的話，則我們就只要去找 Session 57 就好了。而後半段則是當我們取得主要的禍首之後，利用前面所介紹的那些 DMV，來取得相關資料，讓我們可以比較清楚到底是甚麼程式在甚麼時間，送出甚麼樣的指令才造成 Locking 的狀況。
當基本指令出來之後，那麼我們就再做一些加工，當有某個 Session 造成別人被 Locking 超過一定的時間之後，我們就把它先記錄到 Log 表之後再把它給 Kill 掉
CREATE PROCEDURE CHECK_LONG_TIME_BLOCKING @minutes INT
AS
BEGIN
DECLARE @list TABLE( session_id INT )
IF OBJECT_ID( 'BLOCKINGLOG') IS NULL
SELECT ses.session_id,ses.host_name,ses.program_name,ses.login_name,ses.status, ses.last_request_end_time,
CAST( '' AS nvarchar(max)) sql_text,CAST( '' AS nvarchar(max)) recent_sql_text
INTO BLOCKINGLOG FROM sys.dm_exec_sessions ses WHERE 0=1;
WITH RootBlocking AS
(
SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
AND blocking_session_id not In ( SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0 )
)
INSERT INTO BLOCKINGLOG
OUTPUT inserted.session_id INTO @list
SELECT ses.session_id,ses.host_name,ses.program_name,ses.login_name,ses.status, ses.last_request_end_time,
ct1.text sql_text,ct2.text recent_sql_text
FROM RootBlocking rot
INNER JOIN sys.dm_exec_connections con ON rot.blocking_session_id = con.session_id
INNER JOIN sys.dm_exec_sessions ses ON rot.blocking_session_id = ses.session_id
LEFT JOIN sys.dm_exec_requests req ON rot.blocking_session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) ct1
OUTER APPLY sys.dm_exec_sql_text(con.most_recent_sql_handle) ct2
WHERE DATEDIFF( MI, ses.last_request_end_time, GETDATE() ) >= @minutes
ORDER BY ses.last_request_end_time
DECLARE @session_id INT;
SELECT TOP 1 @session_id=session_id FROM @list
WHILE @session_id > 50
BEGIN
EXEC ( 'KILL '+@session_id )
DELETE @list WHERE session_id = @session_id
SET @session_id = 0
SELECT TOP 1 @session_id=session_id FROM @list
END
END
在這個 Stored Procedure 中，前半段我們主要是去建立一個 BLOCKINGLOG 的表格，用來紀錄被我們所砍掉的 Session 資訊，中間一部份就是之前所說用來查 Locking 狀況的指令，其中我們用到一點小技巧，使用 OUTPUT 將所記錄的 session 編號，放到一個 @list 的表格變數內。而最後一個部分則是利用這個 @list 內所記錄的 session 編號抓去斬立決。
而科技始終來自於人性，當有辦法判斷 Locking 的狀況，也將相關指令封裝成為 Stored Procedure 之後，同事還是不滿足，還希望能將這個部分自動去處理，實在是有點貪心。於是好人做到底，再教他使用 SQL Agent 來建立一個 JOB 定時處理
首先在 SQL Agent 裡面選擇「新增作業」
在這個 JOB 中我們增加一個處理步驟，用來執行剛剛所寫好的預儲程序，執行的時候我指定砍掉造成別人被 Blocking 超過 5 分鐘的 Session
由於避免砍掉一些長時間的批次作業或者是相關的統計報表執行，因此在排程上設定只有星期一到星期五，上午 9:00 到下午 6:00 之間每 3 分鐘檢查一次。
設定完成之後，就可以利用一些語法故意產生 Blocking 測試一下囉。
當然這個指令目前內容比較陽春一點，其實如果你更熟悉相關指令的用法之後，可以再做更多的變化，像是增加判斷一些例外的作業或者是狀態，還是當資料超過多少的時候發通知給相關人員，還是要從 Log 資料表內所記錄的 SQL 語法，看是否因為相關的 Index 沒有建立造成執行時間過長而影響到別人等，都是可以再做強化的地方。如果各位朋友覺得還有甚麼需要調整的地方，也可以留言給我，我來試試看是否可以有機會來做個改善囉。