sp_BlitzCache - Performance issues due to duplicate sys.dm_exec_query_plan_stats calls #3791
Description
Version of the script
Version = '8.28', VersionDate = '20251124';
What is the current behavior?
The procedure runs slowly with certain sort orders ('avg cpu' for example) due to the existence of a redundant sys.dm_exec_query_plan_stats CROSS APPLY. FNGETQUERYPLANSTATS is being executed for almost all rows in sys.dm_exec_query_stats before Top N filtering is applied.
Another FNGETQUERYPLANSTATS Table Valued Function operator exists in the plan with more reasonable times as it is only running once for each row after the Top N has been applied:
The procedure is adding this to the body variable twice:
This can be seen in the output of Debug = 1:
FROM (SELECT TOP (@Top) x.*, xpa.*,
CAST((CASE WHEN DATEDIFF(mi, creation_time, GETDATE()) > 0 AND execution_count > 1
THEN DATEDIFF(mi, creation_time, GETDATE())
ELSE NULL END) as MONEY) as age_minutes,
CAST((CASE WHEN DATEDIFF(mi, creation_time, last_execution_time) > 0 AND execution_count > 1
THEN DATEDIFF(mi, creation_time, last_execution_time)
ELSE Null END) as MONEY) as age_minutes_lifetime
FROM sys.dm_exec_query_stats x
CROSS APPLY (SELECT * FROM sys.dm_exec_plan_attributes(x.plan_handle) AS ixpa
WHERE ixpa.attribute = 'dbid') AS xpa
CROSS APPLY sys.dm_exec_query_plan_stats(x.plan_handle) AS deqps -- HERE
WHERE 1 = 1
AND CAST(xpa.value AS INT) NOT IN (SELECT database_id FROM #ReadableDBs)
AND COALESCE(LOWER(DB_NAME(CAST(xpa.value AS INT))), '') NOT IN ('master', 'model', 'msdb', 'tempdb', '32767', 'dbmaintenance', 'dbadmin', 'dbatools') AND COALESCE(DB_NAME(CAST(xpa.value AS INT)), '') NOT IN (SELECT name FROM sys.databases WHERE is_distributor = 1)
ORDER BY total_worker_time / execution_count DESC
) AS qs
CROSS JOIN(SELECT SUM(execution_count) AS t_TotalExecs,
SUM(CAST(total_elapsed_time AS BIGINT) / 1000.0) AS t_TotalElapsed,
SUM(CAST(total_worker_time AS BIGINT) / 1000.0) AS t_TotalWorker,
SUM(CAST(total_logical_reads AS DECIMAL(30))) AS t_TotalReads,
SUM(CAST(total_logical_writes AS DECIMAL(30))) AS t_TotalWrites
FROM sys.dm_exec_query_stats) AS t
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY sys.dm_exec_query_plan_stats(qs.plan_handle) AS deqps -- AND HERE
WHERE 1 = 1
AND pa.attribute = 'dbid'
AND (total_worker_time / execution_count) > 0
ORDER BY total_worker_time / execution_count DESC OPTION (RECOMPILE)
The selected QueryPlan column is only making use of the CROSS APPLY on sys.dm_exec_query_plan_stats(qs.plan_handle):
CASE WHEN DATALENGTH(COALESCE(deqps.query_plan,'')) > DATALENGTH(COALESCE(qp.query_plan,'')) THEN deqps.query_plan ELSE qp.query_plan END AS QueryPlan,
If the current behavior is a bug, please provide the steps to reproduce.
Run the procedure with Debug = 1 to confirm the CROSS APPLY is occurring twice.
What is the expected behavior?
The CROSS APPLY on sys.dm_exec_query_plan_stats(x.plan_handle) should likely be removed to improve performance.
Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
I'm running on SQL Server 2022 CU 20. It appears this code was committed in 2019, so I assume it has been an issue since then.
Further performance details
The fix speeds up procedure execution time for calculated sort orders (avg, executions per minute, etc). I've tested default parameters (other than sort orders) on multiple servers:
~30k rows in sys.dm_exec_query_stats
avg cpu - brings the duration down from ~1 min 30 sec to ~15 sec
xpm - brings the duration down from ~1 min 20 sec to ~5 sec
~65k rows in sys.dm_exec_query_stats
avg cpu - brings the duration down from ~13 min to ~40 sec
xpm - brings the duration down from ~12 min 40 sec min to ~20 sec