异常处理流程（不兼容SQLServer2005）

以下 SQL兼容 SQLServer2008R2， 但是不兼容SQLServer2005

1. --查询所有正在运行中的SQL,按照cpu耗时倒序排列
   
2. select top 10 (select top 1 text from sys.dm_exec_sql_text(dm_c.most_recent_sql_handle)) as sqltext
   
3. ,dm_s.cpu_time
   
4. ,dm_c.session_id
   
5. ,*
   
6. from Sys.dm_exec_connections as dm_c
   
7. join Sys.dm_exec_sessions as dm_s on(dm_c.session_id = dm_s.session_id)
   
8. where 1=1 --and dm_s.login_name = 'TCScenery'
   
9. and dm_s.status = 'running'
   
10. order by dm_s.cpu_time desc

1. --查看sysprocesses中锁及产生锁的session的SQL信息
   
2. WITH ProcessCTE(blocked) AS
   
3. (
   
4. SELECT blocked FROM sys.sysprocesses WHERE blocked>0
   
5. )
   
6. SELECT distinct a.*
   
7.   FROM (
   
8.    SELECT TEXT,AA.* FROM sys.sysprocesses AA
   
9.     CROSS APPLY sys.dm_exec_sql_text(AA.sql_handle)
   
10.        ) a
    
11.   JOIN ProcessCTE bucte WITH(NOLOCK)
    
12.     ON bucte.blocked=a.spid
    
13.  ORDER BY a.blocked

1. --总耗CPU最多的前个SQL:
   
2. SELECT TOP 20
   
3.     total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
   
4.     qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
   
5.     last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
   
6.     SUBSTRING(qt.text,qs.statement_start_offset/2+1,
   
7.         (CASE WHEN qs.statement_end_offset = -1
   
8.         THEN DATALENGTH(qt.text)
   
9.         ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
   
10.     AS [使用CPU的语法], qt.text [完整语法],
    
11.     qt.dbid, dbname=db_name(qt.dbid),
    
12.     qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
    
13. FROM sys.dm_exec_query_stats qs WITH(nolock)
    
14. CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
    
15. WHERE execution_count>1
    
16. ORDER BY total_worker_time DESC

1. --平均耗CPU最多的前个SQL:
   
2. SELECT TOP 20
   
3.     total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
   
4.     qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
   
5.     last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],
   
6.     max_worker_time /1000 AS [最大执行时间(ms)],
   
7.     SUBSTRING(qt.text,qs.statement_start_offset/2+1,
   
8.         (CASE WHEN qs.statement_end_offset = -1
   
9.         THEN DATALENGTH(qt.text)
   
10.         ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
    
11.     AS [使用CPU的语法], qt.text [完整语法],
    
12.     qt.dbid, dbname=db_name(qt.dbid),
    
13.     qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
    
14. FROM sys.dm_exec_query_stats qs WITH(nolock)
    
15. CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
    
16. WHERE execution_count>1
    
17. ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26435490/viewspace-1472724/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26435490/viewspace-1472724/