sqlserver的日常检查脚本归结

核心提示：from sys.dm_exec_query_stats qs group by plan_handle order by sum(total_worker_time) desc goprint '*'print ' 'print

from sys.dm_exec_query_stats qs group by 

plan_handle order by sum(total_worker_time) desc 

go

print '*********************************'

print '                             '

print '                             '

print '                             '

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

print '11.查询前100个缓存使用率高、最消耗缓存的sql语句'

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

print '*********************************'

use master

go

select top 100 ltrim(usecounts) as usecounts,

objtype,

ltrim(p.size_in_bytes) as  size_in_bytes ,

sql.text

from sys.dm_exec_cached_plans  p

outer apply

sys.dm_exec_sql_text(p.plan_handle) sql

order by usecounts desc

go

print '*********************************'

print '                             '

print '                             '

print '                             '

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

print '12.解数据库中的缓存情况，包括被使用的次数、缓存类型、占用的内存大小'

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

print '*********************************'

use master

go

SELECT usecounts=ltrim(usecounts), substring(cacheobjtype,1,30) as cacheobjtype, objtype,ltrim(size_in_bytes) as size_in_bytes, plan_handle

FROM sys.dm_exec_cached_plans

go

print '*********************************'

print '                             '

print '                             '

print '                             '

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

print '13.计划缓存总数'

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

print '*********************************'

use master

go

Select ltrim(Count(*)) CNT,

cast(sum(size_in_bytes)/1024/1024 as varchar(100))+' MB' TotalSize

From sys.dm_exec_cached_plans

go

print '*********************************'