Top Procedures By Total Logical Writes
Understanding "logical writes" is crucial for database performance, as they directly impact both memory and disk I/O. Essentially, logical writes refer to how many pages are written to the buffer cache in memory. High logical writes can indicate a number of things, including inefficient queries that touch too many data pages, or issues with index usage. Monitoring these can help you pinpoint queries that are putting undue pressure on your system's resources.
SELECT TOP (25) p.name AS [SP Name] ,qs.total_logical_writes AS [TotalLogicalWrites] ,qs.total_logical_writes / qs.execution_count AS [AvgLogicalWrites] ,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 qs.total_logical_writes > 0 AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0 ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE);
The SQL query above helps identify the top 25 stored procedures by their total logical writes within your current database. This can be a valuable tool for pinpointing potential performance bottlenecks and optimizing your SQL Server environment. Remember that while this query is powerful, it's always a good idea to analyze the query plan and other metrics to get a complete picture of your query's performance.
