Sunday, May 3, 2020

Top Procedures By Total Logical Reads

 Top  Procedures By Total Logical Reads

Are your SQL Server stored procedures running slower than a snail in molasses? Pinpointing the culprits can be a challenge, but with the right query, you can quickly identify those resource-hungry procedures that are slowing down your database. This post provides a powerful SQL script to help you analyze your stored procedure performance by focusing on logical reads, execution counts, and even potential missing indexes.

The following SQL query is designed to help you proactively monitor and optimize your SQL Server stored procedures. It provides insights into their performance, allowing you to prioritize your optimization efforts effectively.

SELECT TOP (25) p.name AS [SP Name]
 ,qs.total_logical_reads AS [TotalLogicalReads]
 ,qs.total_logical_reads / qs.execution_count AS [AvgLogicalReads]
 ,qs.execution_count
 ,ISNULL(qs.execution_count / DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]
 ,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 DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_logical_reads DESC
OPTION (RECOMPILE);

Understanding the Query's Output:

  • SP Name: The name of your stored procedure.
  • TotalLogicalReads: The total number of logical reads performed by the stored procedure since it was last cached. This is a key indicator of I/O activity.
  • AvgLogicalReads: The average logical reads per execution, helping you understand the typical I/O cost of a single run.
  • Execution_Count: How many times the stored procedure has been executed.
  • Calls/Minute: Provides an idea of how frequently the procedure is being called.
  • total_elapsed_time & avg_elapsed_time: Total and average execution time, crucial for identifying slow-running procedures.
  • Has Missing Index: A very important flag! If this is '1', it means the SQL Server query optimizer identified a missing index that could significantly improve performance for this procedure.
  • Last Execution Time & Plan Cached Time: Helpful timestamps for understanding the recency of activity and when the execution plan was last put into cache.

By using this script, you can easily identify your top 25 stored procedures based on total logical reads, which often correlate directly with performance bottlenecks. Pay close attention to procedures with a high "Has Missing Index" flag, as these represent immediate optimization opportunities.

Remember to always test any changes in a development environment before deploying them to production. Happy optimizing!

at

Popular Posts