異常處理流程（不相容SQLServer2005）

starive發表於2015-03-25
SQLServer
以下 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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章