监控 SQL Server 的运行状况

　　SUM(qs.total_worker_time) AS total_cpu_time,

　　SUM(qs.execution_count) AS total_execution_count,

　　COUNT(*) AS number_of_statements,

　　qs.sql_handle --,

　　--MIN(statement_start_offset) AS statement_start_offset,

　　--MAX(statement_end_offset) AS statement_end_offset

　　FROM

　　sys.dm_exec_query_stats AS qs

　　GROUP BY qs.sql_handle

　　ORDER BY SUM(qs.total_worker_time) DESC) AS stats

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

　　下面的查询显示 CPU 平均占用率最高的前 50 个 SQL 语句。

　　view plaincopy to clipboardprint?

　　SELECT TOP 50

　　total_worker_time/execution_count AS [Avg CPU Time],

　　(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *

　　FROM sys.dm_exec_query_stats

　　ORDER BY [Avg CPU Time] DESC

　　SELECT TOP 50

　　total_worker_time/execution_count AS [Avg CPU Time],

文章来源于领测软件测试网 https://www.ltesting.net/