sqlserver的日常检查脚本归结

核心提示：from sys.dm_exec_query_stats cross apply sys.dm_exec_query_plan(plan_handle) p cross apply sys.dm_exec_sql_text(plan_handle) as qorder by (total_logic

from sys.dm_exec_query_stats

      cross apply sys.dm_exec_query_plan(plan_handle) p

      cross apply sys.dm_exec_sql_text(plan_handle) as q

order by (total_logical_reads + total_logical_writes)/execution_count Desc

go

print '                             '

print '                             '

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

print '17.7查找哪些批处理/请求生成的 I/O 最多'

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

select top 5 

    ltrim(total_logical_reads/execution_count) as avg_logical_reads,

    ltrim(total_logical_writes/execution_count) as avg_logical_writes,

    ltrim(total_physical_reads/execution_count) as avg_phys_reads,

     ltrim(Execution_count) as Execution_count, 

    ltrim(statement_start_offset) as stmt_start_offset, 

    sql_handle, 

    plan_handle

from sys.dm_exec_query_stats  

order by  (total_logical_reads + total_logical_writes) Desc

go

print '                             '

print '                             '

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

print '18.阻塞'

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

print '                             '

print '                             '

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

print '18.1 确定阻塞的会话'

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

use master

go

select blocking_session_id, wait_duration_ms, session_id from 

sys.dm_os_waiting_tasks

where blocking_session_id is not null

go

print '                             '

print '                             '

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

print '18.2 SQL 等待分析和前 10 个等待的资源'

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

select top 10 ltrim(wait_type) as wait_type, ltrim(waiting_tasks_count) as waiting_tasks_count,