如何侦查SQL执行状态

引出问题

随着菜鸟一点点的成熟，现在老鸟已经开始慢慢的将一些问题抛给他：“菜鸟，你去研究下如何有效而快捷的侦查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

执行结果如下（人为分成两段）：

补充说明

当然我们也可以使用上一篇文章讲到的SQL Profiler工具。但是，这种方法有个典型的缺点是对被跟踪的SQL Server服务器性能影响较大，不如使用动态视图来得简单方便快捷。