監控 SQL Server 的運行狀況

　　COUNT(*) AS number_of_statements,

　　qs.sql_handle

　　FROM sys.dm_exec_query_stats AS qs

　　GROUP BY qs.sql_handle

　　ORDER BY SUM(qs.total_worker_time) DESC

　　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.sql_handle

　　FROM sys.dm_exec_query_stats AS qs

　　GROUP BY qs.sql_handle

　　ORDER BY SUM(qs.total_worker_time) DESC

　　下面的查詢顯示緩存計劃所占用的 CPU 總使用率(帶 SQL 文本)。

　　view plaincopy to clipboardprint?

　　SELECT

　　total_cpu_time,

　　total_execution_count,

　　number_of_statements,

　　s2.text

　　--(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text

文章來源于領測軟件測試網 http://www.kjueaiud.com/