--当前缓存中消耗最多CPU的语句
SELECT TOP 50
        SUM(qs.total_worker_time) AS total_cpu_time ,
        SUM(qs.execution_count) AS total_execution_count ,
        COUNT(*) AS number_of_statements ,
        qs.plan_handle
FROM    sys.dm_exec_query_stats qs
GROUP BY qs.plan_handle
ORDER BY SUM(qs.total_worker_time) DESC

GO

--查找提交大量ad hoc的语句
SELECT  q.query_hash ,
        q.number_of_entries ,
        t.text AS sample_query ,
        p.query_plan AS sample_plan
FROM    ( SELECT TOP 20
                    query_hash ,
                    COUNT(*) AS number_of_entries ,
                    MIN(sql_handle) AS sample_sql_handle ,
                    MIN(plan_handle) AS sample_plan_handle
          FROM      sys.dm_exec_query_stats
          GROUP BY  query_hash
          HAVING    COUNT(*) > 1
          ORDER BY  COUNT(*) DESC
        ) AS q
        CROSS APPLY sys.dm_exec_sql_text(q.sample_sql_handle) AS t
        CROSS APPLY sys.dm_exec_query_plan(q.sample_plan_handle) AS p
go

--前N个重编译的存储过程
SELECT TOP 25
        sql_text.text ,
        sql_handle ,
        plan_generation_num ,
        execution_count ,
        dbid ,
        objectid
FROM    sys.dm_exec_query_stats a
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
WHERE   plan_generation_num > 1
ORDER BY plan_generation_num DESC

--累计消耗CPU最多的查询
SELECT  highest_cpu_queries.plan_handle ,
        highest_cpu_queries.total_worker_time ,
        q.dbid ,
        q.objectid ,
        q.number ,
        q.encrypted ,
        q.[text]
FROM    ( SELECT TOP 50
                    qs.plan_handle ,
                    qs.total_worker_time
          FROM      sys.dm_exec_query_stats qs
          ORDER BY  qs.total_worker_time DESC
        ) AS highest_cpu_queries
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY highest_cpu_queries.total_worker_time DESC 

--是否有正在并行运行的查询
SELECT  r.session_id ,
        r.request_id ,
        MAX(ISNULL(exec_context_id, 0)) AS number_of_workers ,
        r.sql_handle ,
        r.statement_start_offset ,
        r.statement_end_offset ,
        r.plan_handle
FROM    sys.dm_exec_requests r
        JOIN sys.dm_os_tasks t ON r.session_id = t.session_id
        JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE   s.is_user_process = 0x1
GROUP BY r.session_id ,
        r.request_id ,
        r.sql_handle ,
        r.plan_handle ,
        r.statement_start_offset ,
        r.statement_end_offset
HAVING  MAX(ISNULL(exec_context_id, 0)) > 0