【SqlServer】使用IO比较高的语句

select top 100 (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_phys_reads,
       Execution_count,
       statement_start_offset as stmt_start_offset,
       statement_end_offset as stmt_end_offset,
       substring(sql_text.text,
                 (statement_start_offset / 2),
                 case
                   when (statement_end_offset - statement_start_offset) / 2 <= 0 then
                    64000
                   else
                    (statement_end_offset - statement_start_offset) / 2
                 end) as exec_statement,
       sql_text.text,
       plan_text.*
  from sys.dm_exec_query_stats
 cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
 cross apply sys.dm_exec_query_plan(plan_handle) as plan_text
 WHERE DB_NAME(t.dbid) = 'abce'
 order by (total_logical_reads + total_logical_writes) / Execution_count Desc

　　

其它

平均物理读次数最多的SQL语句：

select top 50 *, (s.total_physical_reads / s.execution_count) as avephysicalreads from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text(s.sql_handle)
order by avephysicalreads desc

平均逻辑读次数最多的SQL语句：

select top 50 *, (s.total_logical_reads / s.execution_count) as avglogicalreads from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text(s.sql_handle)
order by avglogicalreads desc

　　

平均逻辑写次数最多的SQL语句：

select top 50 *, (s.total_logical_writes / s.execution_count) as avglogicalwrites from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text(s.sql_handle)
order by avglogicalwrites desc

 

返回执行的线程所遇到的所有等待的相关信息

select * from sys.dm_os_wait_stats

　　

返回正在等待某些资源的任务的等待队列的有关信息

select * from sys.dm_os_waiting_tasks