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