监控消耗CPU最多的语句

--1 通用版（适用于2005，2008）
SELECT TOP 20
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
AS [Total Duration (s)]
, CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
AS DECIMAL(28, 2)) AS [% CPU]
, CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
, qs.execution_count
, CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count
AS DECIMAL(28, 2)) AS [Average Duration (s)]
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0
ORDER BY qs.total_elapsed_time DESC


-- SQL CPU 100%问题，启动以来累计使用CPU资源最多的语句（作用同上）
select
    highest_cpu_queries.*,q.dbid,
    q.objectid, q.number, q.encrypted, q.[text]
from
    (select top 50 qs.*
    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


--2 查看正在运行的语句，消耗时长及语句相关信息（适用于2008）
 SELECT TOP (50) start_time = CONVERT(datetime2(0), R.start_time), run_time = datediff(mm,R.start_time, GETDATE()),
         R.session_id, blocking_id = R.blocking_session_id,
         S.[host_name], S.[program_name], S.login_name, database_name = DB_NAME(R.database_id),
         [object_name] = OBJECT_SCHEMA_NAME(T.objectid, R.database_id) + '.' + OBJECT_NAME(T.objectid, R.database_id),
         T.[text], R.command, R.[status], estimated_completion_time = R.estimated_completion_time ,
         wait_time = R.wait_time , R.last_wait_type, cpu_time = R.cpu_time/1000 ,
         total_elapsed_time = R.total_elapsed_time / 1000, R.reads, R.writes, R.logical_reads,
         R.open_transaction_count, R.open_resultset_count, R.percent_complete, R.database_id,
         [object_id] = T.objectid, S.host_process_id, S.client_interface_name, R.[sql_handle], R.plan_handle
    FROM sys.dm_exec_requests R
         CROSS APPLY sys.dm_exec_sql_text(R.[sql_handle]) T
         LEFT JOIN sys.dm_exec_sessions S ON S.session_id = R.session_id
    ORDER BY R.start_time