Compatibility-Level Upgrades: A Measured Rollout Plan

Installing a newer SQL Server engine and raising a database compatibility level are related changes, but they are not the same change. Combining them with schema changes, index maintenance, and an application release makes a regression unnecessarily hard to explain. A controlled upgrade creates comparison points so the team can identify which change altered important query behavior.

Establish two separate baselines

First record the source engine build, database compatibility level, database-scoped settings, and important application settings. Restore a representative copy on the target engine using a supported retained compatibility level, then evaluate it before raising that level. A retained level is not a complete emulation of the old engine; infrastructure, servicing fixes, and other engine behavior can still differ.

The following read-only inventory runs in the database being evaluated. The Query Store view requires the relevant database visibility permissions for the installed version. Preserve the result with the test date and workload identifier.

SELECT SERVERPROPERTY('ProductVersion') AS EngineVersion;
SELECT name, compatibility_level
FROM sys.databases WHERE database_id=DB_ID();
SELECT actual_state_desc, desired_state_desc, readonly_reason,
       current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Desired READ_WRITE state does not prove that Query Store is currently recording. Check actual state, storage consumption, and the reason if it is read-only. Capture settings and retention must keep the baseline through the comparison period. A full repository that silently stops recording can make the upgrade appear uneventful precisely when evidence is most needed.

Choose a workload covering important parameter distributions and a complete business cycle. Include the largest tenant, empty results, month-end work, and concurrent requests. A replay of daytime point lookups alone cannot qualify an overnight reporting workload. Keep data distribution, statistics, hardware allocation, and test concurrency comparable or record their differences explicitly.

Change the level as its own experiment

On a SQL Server 2022 test instance, a database retained at level 150 can be evaluated at 160 as a separate step. The commented template below deliberately names a practice database. Confirm support on the actual target engine before adapting it; compatibility numbers cannot enable an engine version that is not installed.

-- Example only: SQL Server 2022, isolated practice database.
-- ALTER DATABASE [CompatibilityPractice]
-- SET COMPATIBILITY_LEVEL = 160;

Record the change time so before and after Query Store intervals are not accidentally mixed. The transition can cause recompilation, so distinguish initial compilation and cache warmup from sustained execution. Do not clear the entire instance plan cache as a routine measurement step: that creates an additional event affecting unrelated databases.

Compare execution counts and resource cost together. A lower average duration can conceal a critical rare query that became much slower, while a higher total CPU may simply reflect more requests. For each important query, compare representative durations, CPU, reads, plans, and observed concurrency. Use application telemetry for user-facing latency distributions instead of assuming aggregated Query Store intervals provide every percentile.

When a regression appears, inspect estimated versus actual rows, join choices, memory grants, and parameter sensitivity. Preserve both plans and test the hypothesis on the same data. A previously captured plan may be a useful targeted mitigation, but verify that forcing succeeds and remains appropriate for varied parameters. A forced plan is an operational decision with an owner and a review date, not a permanent substitute for understanding the regression.

Define acceptance and the real rollback

Write measurable acceptance criteria before the change: critical transaction latency, batch completion deadline, error rate, and resource headroom at the expected load. Name the decision maker and the observation period. If the business cycle includes a monthly job, immediate daytime success is only partial evidence.

Returning to an older supported compatibility level can mitigate some query-processing changes. It does not downgrade the database file format or make a newer-engine backup restorable on an older engine. Engine rollback therefore needs its own tested migration and data-reconciliation plan, especially once the new system has accepted writes.

Also account for application code that starts using features requiring the new level. Reverting only the setting can then break the application contract. Keep that dependency out of the initial trial or coordinate its rollback explicitly. After acceptance, retain the baseline, remove temporary mitigations only through measurement, and continue monitoring the next full business cycle. The deliverable is a documented performance decision, not merely a higher number in sys.databases.

Technical references: Microsoft Learn: Upgrade workflow · Microsoft Learn: Compatibility levels · Microsoft Learn: Query Store state.