sqlserver的日常检查脚本归结

print '                             '

print '-------------------------------------------------------'

print '16.2查询显示缓存计划所占用的 CPU 总使用率（带 SQL 文本）'

print '-------------------------------------------------------'

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

FROM 

      (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 --,

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

go

print '                             '

print '                             '

print '-------------------------------------------------------'

print '16.3显示 CPU 平均占用率最高的前 50 个 SQL 语句'

print '-------------------------------------------------------'

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

go

print '                             '

print '                             '