当前位置：首页 > 数据库 > MSSQL教程 > 异常处理流程（不兼容SQLServer2005） > 正文

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

来源：这里教程网 时间：2026-03-02 10:37:35 作者：
以下 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



编辑推荐：

相关推荐

最新软件资讯

scroll lock键在哪，有什么作用
scroll lock键在哪，有什么作用

热文推荐

天极热推