SQL Server IO Bottleneck Monitoring

Identifying those queries that are using the most disk IO is the first step to understanding your database performance and potential bottlenecks. You can run this DMV query to identify this information, even if you aren’t experiencing any issues. Documenting normal performance will be extremely helpful when it does come time to troubleshoot an issue. The output of this query lists the top 10 queries (the SQL text and the query plan handle) that have generated the most IO across all executions, since the last time the SQL Services were reset or the server was rebooted.

 (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_physical_reads,
 statement_start_offset as stmt_start_offset,
 (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
   (CASE WHEN statement_end_offset = -1
         THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
         ELSE statement_end_offset
    END - statement_start_offset)/2)
    FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
 FROM sys.dm_exec_query_stats
 ORDER BY (total_logical_reads + total_logical_writes) DESC



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.