After moving a fleet to SQL Server 2025, how would you decide when to raise each database's compatibility level, and what role should Query Store play?
answer
- the engine upgrade and the optimizer upgrade differ
- restored databases keep their old level
- baseline first, then raise
- regressed queries, forcing, rollback
basics
~20 sUpgrade the engine but leave each database at its old compatibility level, enable Query Store and baseline a business cycle, then raise levels in risk order, forcing known good plans for regressions and keeping a rollback path.
solid answer
~40 sA restored or in-place-upgraded database keeps its compatibility level, and optimizer changes are tied to that level, so the engine upgrade and the optimizer upgrade are separate decisions. I would make sure Query Store is on for each database, because only new databases get it by default since SQL Server 2022, and let it baseline a full business cycle at the old level. Then I raise databases to 170 in order of risk and business tolerance, watch regressed queries in Query Store, and fix regressions by forcing the previous plan with `sys.sp_query_store_force_plan` or, on Enterprise edition, `FORCE_LAST_GOOD_PLAN` automatic plan correction. Narrower escape hatches are a `QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n` hint or `LEGACY_CARDINALITY_ESTIMATION`; the last resort is reverting the level. The standing cost is Query Store storage and a growing list of forced plans someone must review.
code
sql · 9 linesSELECT name, compatibility_level, is_query_store_on
FROM sys.databases;
SELECT actual_state_desc, readonly_reason,
current_storage_size_mb, max_storage_size_mb,
query_capture_mode_desc
FROM sys.database_query_store_options;
ALTER DATABASE Sales SET COMPATIBILITY_LEVEL = 170;go deeper
Recall that each SQL Server database has a compatibility level separate from the engine version.
Explain which features depend on the compatibility level and what Query Store records about queries and plans.
Walk through baselining, raising the level, finding regressed queries and forcing a good plan, including how forcing can fail.
Own the rollout order, baseline length, forcing policy, Query Store sizing and the review process for forced plans across the fleet.
## Two upgrades, not one Moving a database to SQL Server 2025 changes the **engine**. Whether its queries get the 2025 **optimizer** is decided separately, by the database's compatibility level: - New databases get the instance's default level, **170** on SQL Server 2025, unless `model` has a lower one. - A database that is restored, attached or upgraded in place **keeps its existing level**, if that level is still supported. - Optimizer changes are tied to the level, so plans do not change at the moment of upgrade. They change when someone runs `ALTER DATABASE ... SET COMPATIBILITY_LEVEL = 170`. Several features depend on the level: the dynamic statistics threshold needs 130, Parameter Sensitive Plan optimization needs 160, and 2025's optional parameter plan optimization and PSP for data-modification statements need 170. Staying on an old level is a documented, valid choice for an application that needs none of that; it is also a choice to forgo those improvements. ## The decision a lead owns There is no single right schedule. The questions to settle per database or per tier: 1. **Is the gain worth the risk?** A reporting database that suffers from parameter-sensitive plans has more to gain from 160 or 170 than a small, stable configuration database. 2. **How long is the baseline?** Query Store needs to see the workload's real cycle (month-end, batch windows) at the old level, or a regression has nothing to be compared against. 3. **What order?** Usually low-risk databases first, so the team learns the regression pattern before touching the critical ones. 4. **Who may force plans, and who reviews them?** Forcing is quick and reversible, but every forced plan is a decision that outlives the incident. 5. **Automatic or manual correction?** Automatic plan correction (`FORCE_LAST_GOOD_PLAN`) forces the last known good plan when it detects a plan-choice regression and reverts actions that do not help. It is an Enterprise-edition feature and depends on Query Store. ## The workflow the documentation recommends 1. Upgrade the engine **without** changing the compatibility level. 2. Enable Query Store. Since SQL Server 2022 it is on by default only for **newly created** databases; restored and in-place-upgraded databases keep whatever Query Store settings they had. 3. Let Query Store capture queries, plans and runtime statistics at the old level long enough to establish a stable baseline. 4. Raise the compatibility level. 5. Use Query Store's regressed-queries view or the `sys.query_store_*` catalog views to find statements whose duration or CPU got worse, and force the previous good plan for those. 6. If plans fail to force or performance is still not acceptable, revert the compatibility level. ## Escape hatches narrower than a rollback | Tool | Scope | When it fits | | --- | --- | --- | | `sys.sp_query_store_force_plan` | One query, one plan | A specific plan-choice regression with a known good plan | | `sys.sp_query_store_set_hints` (2022 and later) | One query | You want a hint such as `OPTION (RECOMPILE)` without a code change | | `USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n')` | One query | One statement needs the older optimizer behaviour | | `LEGACY_CARDINALITY_ESTIMATION = ON` | Whole database | Regressions trace to the cardinality estimator across many queries | | Revert `COMPATIBILITY_LEVEL` | Whole database | Nothing narrower works | ## Running Query Store as a standing cost - **Storage.** `MAX_STORAGE_SIZE_MB` defaults to 1,000 MB from SQL Server 2019. The limit is checked when data is flushed, by default every 900 seconds. If it is exceeded, Query Store switches to **read-only** and stops capturing until cleanup frees space, which is exactly when you lose visibility. Watch `actual_state_desc` and `readonly_reason` in `sys.database_query_store_options`. - **Capture policy.** `QUERY_CAPTURE_MODE = AUTO` skips insignificant ad hoc queries; `CUSTOM` sets explicit thresholds for workloads with heavy ad hoc traffic. - **Forced-plan debt.** A forced plan can stop forcing silently after a schema change: the optimizer then compiles normally and `sys.query_store_plan` counts `force_failure_count` with `last_force_failure_reason_desc` (for example `NO_INDEX`). A periodic review of `is_forced_plan = 1` rows, with an owner, keeps forcing from becoming a pile of stale pins that also block later optimizer improvements. ## Where it lands A reasonable fleet policy: every production database runs Query Store with sized storage and alerting on read-only state; engine upgrades never change the level on the same day; each database moves to 170 after a baseline cycle, in risk order; regressions are fixed per query first and per database only as a last resort; and forced plans have an owner and a review date.
- A week after raising a database to 170, one critical query regressed and forcing its old plan fails. What are your options short of reverting the whole database?Read `last_force_failure_reason_desc` first; `NO_INDEX` means a schema change broke the plan, and restoring the index may let forcing succeed. Otherwise attach `USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160')` to that query, through a Query Store hint if the code cannot change. If many queries regress for the same cardinality reason, `LEGACY_CARDINALITY_ESTIMATION` is wider but still short of a level rollback.
- Why not just raise every database to 170 during the upgrade window and fix whatever breaks?Without a baseline at the old level, Query Store cannot show which plans regressed or offer a previous good plan to force, so every complaint becomes a fresh investigation. Changing engine and optimizer at once also doubles the suspects. Separating them keeps each change attributable and keeps forcing available as a fast fix.
- Assumes upgrading the engine to 2025 moves databases to compatibility level 170.
- Thinks Query Store is on for every database after upgrading to SQL Server 2022.
- Raises the compatibility level before Query Store has captured a baseline.
- Treats forced plans as permanent fixes that never need review.
- Assumes automatic plan correction is available in every edition.
Assumes SQL Server 2025, whose default compatibility level is 170; Query Store is on by default for new databases since SQL Server 2022.