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

12 March,2015 by Jack Vamvas

 

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Find high IO read and write SQL Queries and display query plan


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer