It’s 3AM and I’m curious if Query Store plan forcing works on queries that use the RECOMPILE hint (aka OPTION(RECOMPILE) ).
So might as well make a blog post out of it.
Small spoiler: as Kevin Feasel mentioned, the title ignores Betteridge’s law of headlines.
Environment
I’m using the 180GB StackOverflow database, on SQL Server 2022 (RTM-CU20) (KB5059390) – 16.0.4205.1.
First, start with a clean query store.
| 1 2 3 4 | USE [master] GO ALTER DATABASE [StackOverflow] SET QUERY_STORE CLEAR; GO |
Initial test run
Next, I run a small yet poor performing query against the 180GB StackOverflow database.
| 1 2 3 4 5 6 7 8 9 | DECLARE @Score INT = 100; SELECT top 10 DisplayName, Score, COUNT(*) AS CommentsCount FROM Comments AS C INNER JOIN Users AS U ON C.UserId = U.Id WHERE C.Score = @Score GROUP BY U.DisplayName, C.Score OPTION( RECOMPILE); |
The execution plan looks as terrible as expected.
Forcing an execution plan
I can force the plan either from the one of the Query Store reports (“Top resource consuming queries” in this case):
Or via T-SQL:
First, I use this query to the relevant information:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT txt.query_text_id, txt.query_sql_text, pl.plan_id, pl.is_forced_plan, pl.force_failure_count, pl.last_force_failure_reason_desc, qry.count_compiles, qry.initial_compile_start_time, qry.last_compile_start_time, qry.last_execution_time FROM sys.query_store_plan AS pl INNER JOIN sys.query_store_query AS qry ON pl.query_id = qry.query_id INNER JOIN sys.query_store_query_text AS txt ON qry.query_text_id = txt.query_text_id WHERE txt.query_sql_text LIKE N'%SELECT top 10 DisplayName%'; |
And then I execute sp_query_store_force_plan and pass the relevant query_id and plan_id.
| 1 | EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 1; |
Rerun initial query
I rerun the query 3 more times with random values for @Score and check what’s happening.
Notice that the plan is marked as forced, the compile count has increased by the number of new executions, and there are no plan force failures.
Judging by the count_compiles and last_compile_start_time columns, it looks like SQL Server does somewhat respect the RECOMPILE hint, as in it does compile a new plan with every run, but goes with the forced plan anyway.
The GUI states pretty much the same thing.
At this point it looks like the Query Store can successfully force the plan even if the query uses the RECOMPILE hint.
What if I add that recommended index?
I create the following index on the Comments table.
| 1 2 3 4 5 6 7 | USE [StackOverflow] GO CREATE NONCLUSTERED INDEX [ix_comments_score] ON [dbo].[Comments] ([Score]) INCLUDE ([UserId]) WITH (MAXDOP=0, ONLINE=OFF); GO |
And then execute the query again.
In the actual execution plan I can already see changes that indicate this is a new plan.
Even though the plan shape has not changed (there’s still a clustered index scan on Comments), the cardinality estimates are perfect.
The OptimizerStatsUsage node of the XML execution plan shows where those good cardinality estimates come from.
Notice the statistics object tied to the newly created index is being used, leading to those good cardinality estimates.
Both the GUI and the query confirm that this is a new plan with a new plan ID and it is not forced (although I doubt this).
This is interesting.
Although a new plan is generated, it does seem like SQL Server is doing its best to avoid that newly added index. Using a plan with the same shape as the forced one that leads to the same 42 seconds execution time.
Let’s try with a more selective value for @Score.
For reference, there are 48 comments with a score of 100, which is pretty selective for a table with 66432641 records.
This run, I’ll use @Score = 387 which only has 1 record. You can’t get more selective than this
| 1 2 3 4 5 6 7 8 9 | DECLARE @Score INT = 387; SELECT top 10 DisplayName, Score, COUNT(*) AS CommentsCount FROM Comments AS C INNER JOIN Users AS U ON C.UserId = U.Id WHERE C.Score = @Score GROUP BY U.DisplayName, C.Score OPTION( RECOMPILE); |
Still great cardinality estimates with crappy plan shape and long execution time.
But checking the query store reveals something interesting.
This run used the plan with id 1 and it shows up as forced.
I did 3 more runs with different values for @Score, and they all use the same plan shape, with the 40-42 seconds execution time.
But the plan forcing seems to be inconsistent when checking the Query Store information.
In the first two of these runs it generated new plan IDs and didn’t mark the plans as forced, in the third run it reused plan ID 1 and marked it as forced.
At this point I think SQL Server is actually forcing the plan, or at least the same plan shape, but the RECOMPILE + index combination might lead to some scenario where the Query Store can’t accurately track plan forcing.
Taking into account the fact that the optimizer uses the stats on the new index for cardinality estimations, I’m suspecting that variations in the number of existing records for a given value of @Score sometimes trigger a new plan generation to factor in the changes in cardinality estimates.
What happens if I unforce the plan?
I run sp_query_store_unforce_plan.
| 1 | EXEC sp_query_store_unforce_plan @query_id = 1, @plan_id = 1; |
And go back to the original @Score = 100 version of the query.
| 1 2 3 4 5 6 7 8 9 | DECLARE @Score INT = 100; SELECT top 10 DisplayName, Score, COUNT(*) AS CommentsCount FROM Comments AS C INNER JOIN Users AS U ON C.UserId = U.Id WHERE C.Score = @Score GROUP BY U.DisplayName, C.Score OPTION( RECOMPILE); |
Notice the nice index seek on Comments and the sub-second execution time.
The new plan can also be seen in the Query Store.
The “red dot” plans are the previously forced ones, the “orange dot” plans are the ones that showed up as not forced, but had the exact same plan shape as the forced plan.
And the “green dot” plan is the new plan that uses the nonclustered index.
What if I force again the inefficient plan?
I run the following commands again to force the original plan (plan ID 1).
| 1 | EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 1; |
And then re-execute the original query.
This time, the optimizer ignores the forced plan completely and uses the new efficient plan that leverages the new index.
It does show the previous runs as having the plan with ID 1 forced again, but the new run uses the same plan generated right after I’ve unforced plan ID 1.
Update: Forcing the old plan works if the plan cache is cleared before forcing it.
Erin Stellato chimed in on my LinkedIn post and:
- Confirmed that the using plan with a new ID but the same shape (a “morally equivalent” plan) as the forced plan is expected behavior.
- Forcing the original plan should work if the plan cache is flushed after the plan that uses the index is generated, but before the old plan is forced
So I went through the following steps:
- Unforce the original plan.
- Rerun the query just to make sure the index is being used (you don’t need to do this, I was just double-checking)
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;- Force the original plan again.
- Rerun the query
And it does indeed use the old plan this time.
Note that you shouldn’t be running those DBCC commands on a prod system unless you’re ok with flushing your entire plan cache and buffer.
“But have you asked ChatGPT?”
Lately I’ve been seeing an uptick in two things:
- factually incorrect AI slop being posted everywhere
- people confidently telling me stuff like “Copilot can easily write that script for you” and then Copilot makes the worst assumptions possible about system objects, data types, and doesn’t seem to care about overall performance.
But yes, I did ask ChatGPT the following question:
In SQL Server, does query store plan forcing work on queries that have the recompile hint or does recompile override the plan forcing?
Spolier: that URL cited by ChatGPT does not exist – https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store
And also Mistral’s Le Chat.
Notice how both answered variations of “Query Store plan forcing doesn’t work with RECOMPILE”.
Confidently wrong.
Imagine unironically paying for this in hopes of getting factually correct technical information, but instead you get hallucinated MS Learn URLs and confidently incorrect info.
And then I wonder where people get silly and obviously wrong ideas like CTEs store results in memory or in tempdb.
Conclusion
Query Store forcing still works and can force a plan for queries using the RECOMPILE hint.
It also has some weird behavior when new indexes are added in conjunction with recompile, which leads to a new execution plan showing up as not forced.
But which is an exact copy of the original (forced) plan with just better cardinality estimates.
The reason for the better cardinality estimates is that the optimizer uses the statistics on the new index for cardinality estimates, yet uses the same overall plan shape that’s forced in Query Store.
If you’re interested in reading another performance related post, you might want to check out this post about finding long values faster.
Or, if weird behavior is more your jam, check out this post about truncating NVARCHAR(MAX) variables.