异常处理流程（不兼容SQLServer2005）
以下 SQL兼容 SQLServer2008R2， 但是不兼容SQLServer2005
-
--查询所有正在运行中的SQL,按照cpu耗时倒序排列
-
select top 10 (select top 1 text from sys.dm_exec_sql_text(dm_c.most_recent_sql_handle)) as sqltext
-
,dm_s.cpu_time
-
,dm_c.session_id
-
,*
-
from Sys.dm_exec_connections as dm_c
-
join Sys.dm_exec_sessions as dm_s on(dm_c.session_id = dm_s.session_id)
-
where 1=1 --and dm_s.login_name = 'TCScenery'
-
and dm_s.status = 'running'
- order by dm_s.cpu_time desc
-
--查看sysprocesses中锁及产生锁的session的SQL信息
-
WITH ProcessCTE(blocked) AS
-
(
-
SELECT blocked FROM sys.sysprocesses WHERE blocked>0
-
)
-
SELECT distinct a.*
-
FROM (
-
SELECT TEXT,AA.* FROM sys.sysprocesses AA
-
CROSS APPLY sys.dm_exec_sql_text(AA.sql_handle)
-
) a
-
JOIN ProcessCTE bucte WITH(NOLOCK)
-
ON bucte.blocked=a.spid
- ORDER BY a.blocked
-
--总耗CPU最多的前个SQL:
-
SELECT TOP 20
-
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
-
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
-
last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
-
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
-
(CASE WHEN qs.statement_end_offset = -1
-
THEN DATALENGTH(qt.text)
-
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
-
AS [使用CPU的语法], qt.text [完整语法],
-
qt.dbid, dbname=db_name(qt.dbid),
-
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
-
FROM sys.dm_exec_query_stats qs WITH(nolock)
-
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
-
WHERE execution_count>1
- ORDER BY total_worker_time DESC
-
--平均耗CPU最多的前个SQL:
-
SELECT TOP 20
-
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
-
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
-
last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],
-
max_worker_time /1000 AS [最大执行时间(ms)],
-
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
-
(CASE WHEN qs.statement_end_offset = -1
-
THEN DATALENGTH(qt.text)
-
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
-
AS [使用CPU的语法], qt.text [完整语法],
-
qt.dbid, dbname=db_name(qt.dbid),
-
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
-
FROM sys.dm_exec_query_stats qs WITH(nolock)
-
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
-
WHERE execution_count>1
- ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26435490/viewspace-1472724/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
