sqlserver的日常检查脚本归结

核心提示：print '---'print '16.4找出过多编译/重新编译的 DMV 查询'print '---'select * from sys.dm_exec_query_optimizer_infowhere counter = 'optimizati

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

print '16.4找出过多编译/重新编译的 DMV 查询'

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

select * from sys.dm_exec_query_optimizer_info

where 

      counter = 'optimizations'

      or counter = 'elapsed time'

go

print '                             '

print '                             '

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

print '16.5显示已重新编译的前 25 个存储过程'

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

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

go

print '                             '

print '                             '

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

print '16.6哪个查询占用了最多的 CPU 累计使用率'

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

SELECT 

    ltrim(highest_cpu_queries.plan_handle) as plan_handle, 

    ltrim(highest_cpu_queries.total_worker_time) as total_worker_time,

    q.dbid,

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

go

print '                             '

print '                             '