快速定位MSSQL占用CPU高的sql方法

在使用数据库的过程，经常会出现下图的情况，数据库的CPU或者内存占用高，这里以CPU来举例，如何定位是哪些语句导致CPU占用高

方法一：

1. 登录数据库管理，右键选择【活动和监视器】，打开【最近耗费大量资源的查询】下面根据CPU时间排序，挑选时间最长的一条sql语句测试，如下图

2. 选择一行SQL语句，右键显示【编辑查询文件】：可以展开详细的sql，如下图：

【显示执行计划】：可以看到详细的执行过程、开销情况，以及建议优化的方案，可以选中最大开销节点，右键选择【缺少索引详细信息】进行优化，如下图：

方法二：

1. 可以通过sql语句查询占用情况，效果如下图，这里是根据CPU的执行进行排序，查询top20

SELECT --TOP 20 

total_worker_time / 1000 AS [自编译以来执行所用的CPU时间总量(ms)],

       total_elapsed_time/1000 as [完成执行此计划所用的总时间],

       total_elapsed_time / execution_count/1000 as [平均完成执行此计划所用时间],

       execution_count  as [上次编译以来所执行的次数],   

       creation_time as [编译计划的时间],

       deqs.total_worker_time / deqs.execution_count / 1000 AS [平均使用CPU时间(ms)],

       last_execution_time AS [上次开始执行计划的时间],

       total_physical_reads [编译后在执行期间所执行的物理读取总次数],

       total_logical_reads/execution_count [平均逻辑读次数],

       min_worker_time /1000 AS [单次执行期间所用的最小CPU时间(ms)],

       max_worker_time / 1000 AS [单次执行期间所用的最大 CPU 时间(ms)],

       SUBSTRING(dest.text, deqs.statement_start_offset / 2 + 1,         

       (CASE

         WHEN deqs.statement_end_offset = -1 THEN

          DATALENGTH(dest.text)         

         ELSE deqs.statement_end_offset

       END - deqs.statement_start_offset

       ) / 2 + 1) AS [执行SQL],

       dest.text as [完整SQL],

       db_name(dest.dbid) as [数据库名称],

       object_name(dest.objectid, dest.dbid) as [对象名称]

       ,deqs.plan_handle [查询所属的已编译计划]

  FROM sys.dm_exec_query_stats deqs WITH(NOLOCK)

 CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

--WHERE CONVERT(VARCHAR(16),last_execution_time,120)>'2020-04-24 16:30' AND CONVERT(VARCHAR(16),last_execution_time,120)>'2020-04-24 16:42'

WHERE (max_worker_time / 1000)>100

 --平均使用CPU时间降序

 ORDER BY

 last_execution_time desc

 ,

 (deqs.total_worker_time / deqs.execution_count / 1000) DESC

-- SELECT request_session_id spid, OBJECT_NAME( resource_associated_entity_id )

--tableName FROM sys.dm_tran_locks WHERE resource_type = 'OBJECT'

--WITH    CTE_SID ( BSID, SID, sql_handle )

--          AS ( SELECT   blocking_session_id ,

--                        session_id ,

--                        sql_handle

--               FROM     sys.dm_exec_requests

--               WHERE    blocking_session_id <> 0

--               UNION ALL

--               SELECT   A.blocking_session_id ,

--                        A.session_id ,

--                        A.sql_handle

--               FROM     sys.dm_exec_requests A

--                        JOIN CTE_SID B ON A.SESSION_ID = B.BSID

--             )

--    SELECT  C.BSID ,

--            C.SID ,

--            S.login_name ,

--            S.host_name ,

--            S.status ,

--            S.cpu_time ,

--            S.memory_usage ,

--            S.last_request_start_time ,

--            S.last_request_end_time ,

--            S.logical_reads ,

--            S.row_count ,

--            q.text

--    FROM    CTE_SID C

--            JOIN sys.dm_exec_sessions S ON C.sid = s.session_id

--            CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q

--    ORDER BY sid

 --------------------------------------------------------------------------------------------

--SELECT  s2.dbid ,

--         DB_NAME(s2.dbid) AS [数据库名] ,

--         --s1.sql_handle ,

--         ( SELECT TOP 1

--                     SUBSTRING(s2.text, statement_start_offset / 2 + 1,

--                               ( ( CASE WHEN statement_end_offset = -1

--                                        THEN ( LEN(CONVERT(NVARCHAR(MAX), s2.text))

--                                               * 2 )

--                                        ELSE statement_end_offset

--                                   END ) - statement_start_offset ) / 2 + 1)

--         ) AS [语句] ,

--         execution_count AS [执行次数] ,

--         last_execution_time AS [上次开始执行计划的时间] ,

--         total_worker_time AS [自编译以来执行所用的 CPU 时间总量（微秒）] ,

--         last_worker_time AS [上次执行计划所用的 CPU 时间（微秒）] ,

--         min_worker_time AS [单次执行期间曾占用的最小 CPU 时间（微秒）] ,

--         max_worker_time AS [单次执行期间曾占用的最大 CPU 时间（微秒）] ,

--         total_logical_reads AS [总逻辑读] ,

--         last_logical_reads AS [上次逻辑读] ,

--         min_logical_reads AS [最少逻辑读] ,

--         max_logical_reads AS [最大逻辑读] ,

--         total_logical_writes AS [总逻辑写] ,

--         last_logical_writes AS [上次逻辑写] ,

--         min_logical_writes AS [最小逻辑写] ,

--         max_logical_writes AS [最大逻辑写]

-- FROM    sys.dm_exec_query_stats AS s1

--         CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

-- WHERE   s2.objectid IS NULL

-- ORDER BY last_worker_time DESC

 --------------------------------------------------------------------------------------------

 -- SELECT  ss.SUM_execution_count ,

 --        t.text ,

 --        ss.SUM_total_elapsed_time ,

 --        ss.sum_total_worker_time ,

 --        ss.sum_total_logical_reads ,

 --        ss.sum_total_logical_writes

 --FROM    ( SELECT    S.plan_handle ,

 --                    SUM(s.execution_count) SUM_Execution_count ,

 --                    SUM(s.total_elapsed_time) SUM_total_elapsed_time ,

 --                    SUM(s.total_worker_time) SUM_total_worker_time ,

 --                    SUM(s.total_logical_reads) SUM_total_logical_reads ,

 --                    SUM(s.total_logical_writes) SUM_total_logical_writes

 --          FROM      sys.dm_exec_query_stats s

 --          GROUP BY  S.plan_handle

 --        ) AS ss

 --        CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t

 --ORDER BY sum_total_logical_reads DESC

--select request_session_id 锁表进程, OBJECT_NAME(resource_associated_entity_id) 被锁表名 

--from sys.dm_tran_locks where resource_type = 'OBJECT';

--declare @spid  int

--Set @spid  = 57 --锁表进程

--declare @sql varchar(1000)

--set @sql='kill '+cast(@spid  as varchar)

--exec(@sql)