sqlserver的日常检查脚本归结

核心提示：goprint '*'print ' 'print ' 'print ' 'print

go

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

print '                             '

print '                             '

print '                             '

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

print '8.临时数据库使用情况              '

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

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

use master

go

select ltrim(sum(user_object_reserved_page_count)*8) as user_objects_kb,

   ltrim(sum(internal_object_reserved_page_count)*8) as internal_objects_kb,

   ltrim(sum(version_store_reserved_page_count)*8) as version_store_kb,

   ltrim(sum(unallocated_extent_page_count)*8) as freespace_kb

from sys.dm_db_file_space_usage

where database_id = 2

go

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

print '                             '

print '                             '

print '                             '

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

print '9.查CPU瓶颈                    '

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

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

use master

go

Select ltrim(scheduler_id) as scheduler_id,

ltrim(current_tasks_count) as current_tasks_count ,

ltrim(runnable_tasks_count) as current_tasks_count

from sys.dm_os_schedulers where scheduler_id<255 

go

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

print '                             '

print '                             '

print '                             '

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

print '10.当前被缓存的消耗CPU资源最多的批处理或者过程'

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

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

use master

go

Select top 50 ltrim(sum(total_worker_time)) as total_cpu_time,

ltrim(sum(execution_count)) as total_execution_count, 

ltrim(count(*)) as number_of_statements,plan_handle