sp_BlitzQueryStore - Missing results related to PSPO executions

[sqljared]

Version of the script
SELECT @Version = '8.15', @VersionDate = '20230613';

What is the current behavior?
If sp_BlitzQueryStore is executed with the @StoredProcName parameter, the results will not include any executed queries where Parameter Sensitive Procedure Optimization is used.

• Any parent queries are excluded because their plans (the dispatch plans) do not execute. The query on line 1104 includes query_store_runtime_stats, so the query will filter out at that time.
• Variant queries have an object_id of 0 like ad hoc queries and won't be included by the filter.

Here is an example of the behavior from sp_BlitzQueryStore in a local example:

For comparison, here is a query I ran to see all queries for this same procedure and any variant plans associated:
WITH qpsp AS ( SELECT ISNULL(qv.query_id,qsq.query_id) AS query_id, ISNULL(qvp.plan_id,qsp.plan_id) AS plan_id, ISNULL(qvp.query_plan,qsp.query_plan) AS query_plan, ISNULL(qv.query_text_id,qsq.query_text_id) AS query_text_id, ISNULL(qvp.plan_type_desc,qsp.plan_type_desc) AS plan_type_desc FROM sys.query_store_query qsq INNER JOIN sys.query_store_plan qsp ON qsp.query_id = qsq.query_id LEFT JOIN sys.query_store_query_variant vr ON vr.parent_query_id = qsq.query_id LEFT JOIN sys.query_store_query qv ON qv.query_id = vr.query_variant_query_id LEFT JOIN sys.query_store_plan qvp ON qvp.query_id = qv.query_id WHERE qsq.object_id = OBJECT_ID('Sales.GetOrders') ) SELECT qpsp.query_id, qpsp.plan_id, qpsp.plan_type_desc, CAST(qpsp.query_plan as XML), qt.query_sql_text, rsi.end_time, (rs.avg_duration * rs.count_executions) as total_duration, rs.avg_duration, rs.count_executions, rs.avg_cpu_time, rs.avg_logical_io_reads, rs.avg_rowcount FROM qpsp INNER JOIN sys.query_store_query_text qt ON qt.query_text_id = qpsp.query_text_id LEFT JOIN sys.query_store_runtime_stats rs ON rs.plan_id = qpsp.plan_id LEFT JOIN sys.query_store_runtime_stats_interval rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id WHERE rs.last_execution_time BETWEEN '20230701' AND '20230724';

The results show several execution plans for the same query and text, before plan_id 550 was created with three variants.

If the current behavior is a bug, please provide the steps to reproduce.
Any setup for a procedure that results in PSPO being used to generate and execute variant plans will suffice.

What is the expected behavior?
When we filter on the @StoredProcName, we should include the variant queries even though the query_store_query.object_id for those queries is 0.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
SQL Server 2022, if the compatibility level is >=160 and the database scoped configuration PARAMETER_SENSITIVE_PLAN_OPTIMIZATION is not disabled.
OS is irrelevant.

[BrentOzar]

@sqljared did you plan to submit a pull request in the changes to get it to work? It sounds like you're the most qualified person to make the changes and gauge whether or not it succeeded.

[sqljared]

@BrentOzar
I can, it's just going to be a lot of change. Looking at this again, I'd need to branch logic for how the key temp tables are populated (like #grouped_interval and #working_plans) if PSPO is turned on and we filter on StoredProcName.
It seems like everything else populates from them, so this might not be as huge a change as I thought at first.

[sqljared]

Came up with a different approach, and it wasn't nearly as many lines as I first expected.
I've created a PR with two commits that resolve the two issues above in my limited testing.
Let me know if I need to do anything different with the fork or PR.

[BrentOzar]

Thanks for the pull request! Looks good, works beautifully. Merging into the dev branch, will be in the next release with credit to you in the release notes.