mssql sql server当前运行中脚本获取方法分享-dba必备脚本1(维护脚本1)


摘要:
下文主要讲述场景:
如果用户反馈系统慢时,DB端的紧急监控方法,
立即采用以下sql脚本在db服务器上捕捉一批sql,
然后将sql下载后,进行相应的分析,得出我们所需的结果。


  select     
    DB_NAME(a.database_id) as [数据库名称],  
    a.cpu_time as [cpu耗时],  
    a.reads as [读耗时],  
    a.writes as [写耗时],  
    a.logical_reads [逻辑读耗时],  
    login_name as [db账户],  
    a.status as [当前语句状态],  
    blocking_session_id as [被**ID阻塞], 
    wait_type as [等待类型],  
    wait_resource as [等待资源],  
    wait_time as [等待时间],    
    c.text as [脚本信息],  
    program_name as [程序名],  
    host_name as [主机名称],   
    start_time as [开始时间],     
    (SELECT  query_plan  FROM  sys.dm_exec_query_plan (a.plan_handle))AS  [查询计划]  
FROM  
    sys.dm_exec_requests a  
    INNER  JOIN  sys.dm_exec_sessions  b  ON a.session_id=b.session_id  
    CROSS  APPLY  sys.dm_exec_sql_text (a.sql_handle)AS  c  
WHERE  
    b.session_id> 50           
    AND  b.session_Id  NOT  IN(@@SPID)  


主要观察点:
1 耗时时间
2 当前语句状态
3 blocking_session_id 是否存在相关数值
———————————————
如果blocking_session_id 值不为0 ,代表此进程被blocking_session_id 进程阻塞,
此时我们应该采用 dbcc inputbuffer(blocking_session_id)获取进程ID的内容,然后查找相关原因,进行相关优化。