Top Procedures By Total Logical Writes

Logical writes relate to both memory and disk I/O pressure

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);