Find high IO read and write SQL Queries and display query plan

During some SQL Server Performance troubleshooting , I needed to identify the SQL queries causing the highest amount of IO read and write.

Using  the list of queries  in this recordset , I can analyse the query plan and identify operations consuming large amounts of IO.  Once identifying large IO consumers , I begin to breakdown the reasons for the slow down.

It could be something simple, such as statistics are out of date , creating an inefficient query plan. Or it could be more involved such a stored procedure not using set based approach.

This is a quick and dirty approach to identify high IO bottlenecks caused by increased workload on the system.  Quite often a deeper analysis is required , where information should be gathered .

Read more on how to troubleshoot IO_COMMPLETION, BACKUPIO with waitstats and perfmon.

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_phys_reads, 
     Execution_count,  
    statement_start_offset as stmt_start_offset,  
	statement_end_offset as stmt_end_offset,  
    sql_handle,  
    plan_handle ,
    qpl.query_plan
from sys.dm_exec_query_stats  
CROSS APPLY sys.dm_exec_query_plan ( plan_handle ) qpl 
order by  
 (total_logical_reads + total_logical_writes) Desc

Read More on finding IO bottlenecks

SQL Server Query Optimizer and Statistics - SQL Server DBA

SQL Server - Find last time STATISTICS updated - update statistics

SQL Performance tuning - Asking the right question