监控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执行状态的方法0

监控SQL执行状态的方法1

补充说明

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

加载余下内容▼

相关文章:

;