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.

SELECT TOP 10
 (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,
 execution_count,
 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,
 plan_handle
 FROM sys.dm_exec_query_stats
 ORDER BY (total_logical_reads + total_logical_writes) DESC

 

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s