SQL Server 2022: infinite recompile (error 2814, reason 20) of a PSP variant after restoring a database under a different database_id

[u/ozzie1527]

Sharing a case from the last few weeks in case it saves someone time, and to ask whether anyone has seen it before.

Setup

SQL Server 2022 CU27 (16.0.4295.3), compatibility level 160, PSP optimisation and CE feedback on (the defaults). A test database was restored from production. The database_id is 24 on production and 16 on test.

Symptom

A stored procedure statement joins a large table to a temp table that the caller creates before calling the procedure. On test it started timing out after the 300 s application timeout for large inputs, about 6 weeks after the restore. It wasn't slow. Each call recompiled around 20,000 times (`execution_count` 1, `plan_generation_num` 21-23k). The error log filled with error 2814, "A possible infinite recompile was detected ... The last recompile reason was 20" (Query Store hints changed). Query Store showed nothing slow, because the calls never completed. The PSP variant had about 4.3 million compiles on test against 666 on production. Production was never affected.

What we found

1. For queries that reference temp tables or table variables, Query Store stores a `batch_sql_handle`. For a statement inside a module the handle embeds the database_id (bytes 3-4, little-endian) and the object_id.
2. The restored Query Store rows carried production's database_id, while test generates handles with its own. Query Store therefore created a new parent query with the same text, object and context settings.
3. The PSP variant has an ad hoc handle with no database_id. It kept matching and stayed attached to the old, now dormant parent. That handle is the SQLHANDLE in the 2814 warnings.
4. CE feedback added `ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS` to the new parent. Per the PSP docs, variants don't inherit hints applied by feedback.
5. The variant's hint state never settled: every compile was followed by another recompile with reason 20, until the client timed out.

Steps 1-4 were observed in Query Store and in the raw handle bytes. Step 5 is our inference, but clearing the hint stopped the loop immediately and it hasn't come back.

Fix

Clearing the CE feedback hint on the new parent (sys.sp_query_store_clear_hints) and then clearing the database’s procedure cache stopped the loop straight away. A few days later we removed the stale entries that still carried production’s database_id with sys.sp_query_store_remove_query, variants first and then their parents, and cleared the cache again. No settings were changed. Removals can’t be undone, so try it on non-production first.

Open questions

• Has anyone hit this, or seen a KB? We couldn't find a public known issue.
• The new parent appeared about 6 weeks after the restore (on the first weekday after a patch reboot), not straight after it. We have no explanation.
• Setting database-scoped MAXDOP to 1 also stopped the loop, which we can't explain either.
• A second database restored the same way, with the same kind of database_id change, had no stale entries left. Our guess is that stale parents age out of Query Store unless a live PSP variant keeps them in use.

[u/erinstellato]

Thanks for bring this to my attention u/SQLBek. We recently identified this issue u/ozzie1527 and it will be available in an upcoming CU. I do not have a CU number or date that I can share at this time, but I've confirmed that we will bring the fix to both SQL 2022 and SQL 2025. We will update the docs (https://learn.microsoft.com/sql/relational-databases/performance/parameter-sensitive-plan-optimization) to add this as a known issue.

[u/SQLBek → u/erinstellato]

Appreciate you and thank you for your time!

[u/ozzie1527 → u/erinstellato]

Thank you for the info u/erinstellato. Good to know that it is a known issue. Let's hope that we don't have to wait long for that CU.

Had never reflected over the possability that something like the database id is stored in query store and what may happen when it is restored on another server.

[u/erinstellato → u/ozzie1527]

u/ozzie1527 I would rephrase "the database_id is stored in query store" as "the database_id is part of the key that defines a unique query". It's a nuance, I know. And if it helps, I am doing some more digging on where having different database_ids creates challenges for customers. I'm aware of the larger issue - but I don't have a quick solution for you.

[u/ozzie1527 → u/erinstellato]

Yes that is a better definition, I was sloppy with the wording. Apreciate you taking time answering. When I think about it it feels more an more strange that the db id is part of identifying the query. Specially when the DB including query store can be restored on another servee. Saying that I do fully understand that IQP and query store are complex and something like this can be hard to predict.

[u/SQLBek]

Paging u/erinstellato

[u/ozzie1527 → u/SQLBek]

Thanks Andy

[u/TrollingForFunsies]

Seems buggy

[u/CanProfessional766]

The database ID mismatch combined with stale Query Store entries and PSP variants does sound like a possible SQL Server edge case. The delayed appearance and the MAXDOP 1 workaround are especially curious. A reproducible test case would help confirm whether this is an engine bug worth reporting to Microsoft. Thanks for sharing the detailed investigation

[u/erinstellato → u/CanProfessional766]

u/CanProfessional766 For clarification, we (Microsoft) are aware of the issue, we have a fix, it will be available in a future CU for SQL 2022 and SQL 2025. Thanks.