sqlserver的日常检查脚本归结

核心提示：ltrim(physical_memory_in_bytes / 1024 / 1024) as physical_memory_mb,ltrim(virtual_memory_in_bytes / 1024 / 1024) as virtual_memory_mb,ltrim(bpool_committed * 8

ltrim(physical_memory_in_bytes / 1024 / 1024) as physical_memory_mb,

ltrim(virtual_memory_in_bytes / 1024 / 1024) as virtual_memory_mb,

ltrim(bpool_committed * 8 / 1024) as bpool_committed_mb,

ltrim(bpool_commit_target * 8 / 1024) as bpool_target_mb,

ltrim(bpool_visible * 8 / 1024) as bpool_visible_mb

from sys.dm_os_sys_info

go

print '                             '

print '                             '

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

print '17.4I/O 瓶颈'

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

select wait_type, ltrim(waiting_tasks_count) as waiting_tasks_count , ltrim(wait_time_ms) as wait_time_ms , 

ltrim(signal_wait_time_ms) as signal_wait_time_ms, ltrim(wait_time_ms / waiting_tasks_count) as avgtime

from sys.dm_os_wait_stats  

where wait_type like 'PAGEIOLATCH%'  and waiting_tasks_count > 0

order by wait_type

go

print '                             '

print '                             '

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

print '17.5查找当前挂起的 I/O 请求'

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

print '正常情况下不返回任何值'

select 

    database_id, 

    file_id, 

    io_stall,

    io_pending_ms_ticks,

    scheduler_address 

from  sys.dm_io_virtual_file_stats(NULL, NULL)t1,

        sys.dm_io_pending_io_requests as t2

where t1.file_handle = t2.io_handle

go

print '                             '

print '                             '

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

print '17.6查看IO相关查询读取次数'

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

select top 5 (total_logical_reads/execution_count) as avg_logical_reads,

                   (total_logical_writes/execution_count) as avg_logical_writes,

           (total_physical_reads/execution_count) as avg_physical_reads,

           Execution_count, statement_start_offset, p.query_plan, q.text