Monitoring disk I/O intensive queries in SQL Server involves identifying queries that are reading or writing a significant amount of data to the disk. This is crucial for performance tuning and optimizing resource usage. The following script helps you identify such queries by leveraging the Dynamic Management Views (DMVs) sys.dm_exec_query_stats, sys.dm_exec_requests, sys.dm_exec_sessions, and sys.dm_exec_sql_text. These DMVs provide information about the execution of queries, including CPU time, I/O statistics, and the actual SQL text of the queries.

SQL Script to Identify Disk I/O Intensive Queries

;WITH AggregatedIOStats AS (
    SELECT
        qs.execution_count,
        qs.total_logical_reads, qs.total_logical_writes,
        qs.total_logical_reads + qs.total_logical_writes AS total_io,
        qs.total_elapsed_time,
        qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
        qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
        qs.total_logical_writes / qs.execution_count AS avg_logical_writes,
        qs.total_io / qs.execution_count AS avg_io,
        qs.plan_handle
    FROM sys.dm_exec_query_stats qs
)
SELECT TOP 10
    t.text AS QueryText,
    s.execution_count,
    s.total_logical_reads, s.total_logical_writes, s.total_io,
    s.avg_elapsed_time,
    s.avg_logical_reads, s.avg_logical_writes, s.avg_io,
    qp.query_plan
FROM AggregatedIOStats s
CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS qp
ORDER BY s.total_io DESC;

Understanding the Script

• CTE AggregatedIOStats: This Common Table Expression (CTE) aggregates I/O statistics for each query, including execution count, total and average logical reads and writes, and total and average I/O.

• total_logical_reads and total_logical_writes: Represent the total number of pages read from and written to the disk, respectively.

• execution_count: Indicates how many times the query has been executed.

• avg_elapsed_time: Shows the average time each execution of the query took.

• CROSS APPLY sys.dm_exec_sql_text: Retrieves the SQL text of the query associated with each plan handle.

• CROSS APPLY sys.dm_exec_query_plan: Retrieves the query execution plan for the plan handle, which can be useful for further analysis and optimization.

• ORDER BY s.total_io DESC: Orders the results by the total I/O, highlighting the most disk I/O intensive queries at the top of the list.

Usage Notes

• Permissions: Accessing DMVs requires appropriate permissions. Ensure you have the necessary permissions or consult with your database administrator.

• Performance: Running this script on a busy production server might impact performance. Consider executing it during off-peak hours or on a representative test environment.

• Context: High I/O does not necessarily mean a query is inefficient. Some operations naturally require more I/O. Use this script as a starting point for investigation rather than a definitive indicator of problems.

By identifying and analyzing disk I/O intensive queries, you can focus your optimization efforts on the most impactful areas, such as improving indexing strategies, query redesign, or schema modifications to enhance the overall performance of your SQL Server.