[SQL][Troubleshooting]利用 DMV 和 SQL Agent 自動刪除一些造成其他作業被 Blocking 的連線

[SQL][Troubleshooting]利用 DMV 和 SQL Agent 自動刪除一些造成其他作業被 Blocking 的連線

這幾天在幫一些客戶查一些資料庫在使用上的問題時,本來想說透過一些 SSMS 上面 Instance 報表 「Activity - All Blocking Transactions」來查看,但用起來不是順手,剛好有同事再詢問到底要怎麼來查看 Locking 的問題,由於他們都是工程背景出身的人員,因此希望用比較簡單的方式來查看問題。

 

而要了解這個問題的時候,首先我們要先知道幾個關於查看執行狀況的 DMV

image

 

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 裡面選擇「新增作業」

image

 

在這個 JOB 中我們增加一個處理步驟,用來執行剛剛所寫好的預儲程序,執行的時候我指定砍掉造成別人被 Blocking 超過 5 分鐘的 Session

image

 

由於避免砍掉一些長時間的批次作業或者是相關的統計報表執行,因此在排程上設定只有星期一到星期五,上午 9:00 到下午 6:00 之間每 3 分鐘檢查一次。

image

 

設定完成之後,就可以利用一些語法故意產生 Blocking 測試一下囉。

 

當然這個指令目前內容比較陽春一點,其實如果你更熟悉相關指令的用法之後,可以再做更多的變化,像是增加判斷一些例外的作業或者是狀態,還是當資料超過多少的時候發通知給相關人員,還是要從 Log 資料表內所記錄的 SQL 語法,看是否因為相關的 Index 沒有建立造成執行時間過長而影響到別人等,都是可以再做強化的地方。如果各位朋友覺得還有甚麼需要調整的地方,也可以留言給我,我來試試看是否可以有機會來做個改善囉。