Top Procedures By Total Physical Reads
This post provides a helpful SQL query to identify your most I/O-intensive stored procedures. By analyzing physical and logical reads, you can pinpoint performance bottlenecks and optimize your database.
This query targets the top 25 stored procedures in your current database that have generated physical reads. It provides key metrics like **Total Physical Reads**, **Average Physical Reads per execution**, **Execution Count**, **Total Logical Reads**, and **Total Elapsed Time**. Additionally, it checks for **missing indexes** within the query plan, which can be a significant indicator of performance issues. You'll also see the **Last Execution Time** and **Plan Cached Time** for better context.
Feel free to uncomment the `qp.query_plan` line if you want to inspect the actual query plan for deeper analysis. This script is a great starting point for identifying stored procedures that might be causing high disk I/O and slowing down your database.
SELECT TOP (25) p.name AS [SP Name]
,qs.total_physical_reads AS [TotalPhysicalReads]
,qs.total_physical_reads / qs.execution_count AS [AvgPhysicalReads]
,qs.execution_count
,qs.total_logical_reads
,qs.total_elapsed_time
,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
,CASE WHEN CONVERT(NVARCHAR(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index]
,FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time]
,FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND qs.total_physical_reads > 0
ORDER BY qs.total_physical_reads DESC
,qs.total_logical_reads DESC
OPTION (RECOMPILE);
This query targets the top 25 stored procedures in your current database that have generated physical reads. It provides key metrics like **Total Physical Reads**, **Average Physical Reads per execution**, **Execution Count**, **Total Logical Reads**, and **Total Elapsed Time**. Additionally, it checks for **missing indexes** within the query plan, which can be a significant indicator of performance issues. You'll also see the **Last Execution Time** and **Plan Cached Time** for better context.
Feel free to uncomment the `qp.query_plan` line if you want to inspect the actual query plan for deeper analysis. This script is a great starting point for identifying stored procedures that might be causing high disk I/O and slowing down your database.
