如何偵查SQL執行狀態

風移發表於2016-11-08

引出問題

隨著菜鳥一點點的成熟,現在老鳥已經開始慢慢的將一些問題拋給他:“菜鳥,你去研究下如何有效而快捷的偵查SQL執行狀態?”。

“鳥哥,你交代的任務,我分分鐘就去辦。”,菜鳥領了任務趕緊忙碌起來。

分析問題

“其實這個場景經常遇到,比如:我們想知道某個程式的SQL查詢執行到哪一步了?當前執行多久了?查詢是從哪個登入使用者哪一個機器過來的?跑的是什麼業務?甚至執行計劃是什麼樣子?老鳥就是老鳥,總想的比我們遠”。菜鳥一邊尋思著,一邊又馬不停蹄的問G哥,終於功夫不負有心人,總算有了點眉目。

解決問題

利用一系列的常見系統檢視就可以很輕鬆的解決這個問題:

  • sys.dm_exec_requests:這個動態檢視可以拿到所有於執行請求的資訊
  • sys.dm_exec_sql_text:這個動態檢視可以拿到執行的語句和當前執行的語句
  • sys.dm_exec_query_plan:這個動態檢視可以拿到執行計劃

跑在59號程式的測試程式碼

while 1 = 1
begin
    print 11
    waitfor delay `00:00:01`    
end

偵查59號程式的程式碼如下:

use master
GO
SELECT
    req.session_id
    ,req.start_time
    ,[current_time] = getdate()
    ,ms_since_start = DATEDIFF(ms,req.start_time,GETDATE())
    ,req.percent_complete
    ,req.total_elapsed_time
    ,database_name = db.name
    ,req.status
    ,login_name = suser_name(req.user_id)
    ,pro.hostname
    ,pro.program_name
    ,req.command
    ,req.reads
    ,req.logical_reads
    ,req.writes
    ,req.row_count
    ,transaction_isolation_level = 
        case req.transaction_isolation_level
            when 0 then `Unspecified`
            when 1 then `ReadUncomitted`
            when 2 then `ReadCommitted`
            when 3 then `Repeatable`
            when 4 then `Serializable`
            when 5 then `Snapshot`
            else ``
        end
    ,req.open_transaction_count
    ,st.text
    ,stat = SUBSTRING(st.text, 
                (req.statement_start_offset/2)+1,
                (
                    (CASE req.statement_end_offset 
                        WHEN - 1 THEN DATALENGTH(st.text)
                    ELSE req.statement_end_offset
                    END - req.statement_start_offset)/2
                    ) + 1
            )
    ,qp.query_plan
FROM sys.dm_exec_requests as req WITH(NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(req.plan_handle) qp
INNER JOIN sys.databases as db WITH(NOLOCK)
    ON req.database_id = db.database_id
INNER JOIN sys.sysprocesses as pro WITH(NOLOCK)
    ON req.session_id = pro.spid
WHERE session_id = 59

執行結果如下(人為分成兩段):
_1

_2

補充說明

當然我們也可以使用上一篇文章講到的SQL Profiler工具。但是,這種方法有個典型的缺點是對被跟蹤的SQL Server伺服器效能影響較大,不如使用動態檢視來得簡單方便快捷。


相關文章