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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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语句：

?

1 2 3

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语句：

?

1 2 3

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语句：

?

1 2 3

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

 

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

?

1	select * from sys.dm_os_wait_stats

　　

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

?

1	select * from sys.dm_os_waiting_tasks