Raising a Database Compatibility Level Safely

A new SQL Server engine can run a database at its old compatibility level. Raising a compatibility level safely is a separate change that deserves its own baseline and rollback rule. Query Store gives you evidence when a critical plan changes.

Keep Engine Upgrade and Level Change Separate

An engine upgrade changes the SQL Server binaries. A database compatibility level controls a set of database behaviors, including query optimizer behavior tied to that level. Keeping the old level during the initial move can reduce simultaneous changes. It also gives you time to capture a baseline on the new engine before enabling the newer optimizer model.

To raise a compatibility level safely, I prefer a separate ticket for the change. It makes the scope and rollback decision clear. Which database and application workload are in scope? Do not raise every database simply because the engine supports a higher number. Test the supported level for the target engine and the features your application uses.

Record the Current State

Query the current compatibility level for each user database and save the result with the change plan. The first query also shows whether a database is online. An offline database should not be treated as validated merely because it has a recorded level. Identify the connection strings and jobs that reach the database before scheduling the change.

I keep the previous level in the rollback instructions, not in someone’s memory. The exact number matters because the quick reversal uses it. Do not copy a level from another database or an old document. Read the current value from the target instance immediately before the change.

SELECT
    name,
    compatibility_level,
    state_desc
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

Let Query Store Build a Baseline

Enable Query Store in the database if it is not already configured, then confirm its actual state and capture mode. Let it observe a representative business cycle at the existing compatibility level. Include scheduled jobs, reports, and less frequent operations. A baseline consisting only of one quiet morning will miss important paths.

The next query runs in the database being changed and reports Query Store settings. Review read-only conditions and storage limits before relying on it. I check that capture is active and that the team can inspect its reports. Query Store does not help if it stopped collecting before the change.

SELECT
    actual_state_desc,
    desired_state_desc,
    query_capture_mode_desc,
    current_storage_size_mb,
    max_storage_size_mb
FROM sys.database_query_store_options;

Change the Compatibility Level Safely, One Database at a Time

Use ALTER DATABASE with the chosen supported COMPATIBILITY_LEVEL during an approved window. The change can cause query recompilation and plan differences. Make it in a lower environment with representative workload first, then production with monitoring ready. Do not combine it with an index overhaul and a client driver upgrade unless the release truly requires all three.

I watch the application after the change, not just the ALTER statement result. A successful command says the setting changed. It does not say the checkout path, report, and overnight job still meet their performance target. Keep a named owner for each validation path.

Compare Queries, Not Only Averages

Use Query Store to compare important queries and plans before and after the level change. Look for regressions in duration, CPU, logical reads, and execution count using the same workload periods. Averages can hide a slow parameter case, so inspect distributions and the affected plan. Pair database evidence with application response times and errors.

I start with the highest-impact user paths, then review the broader workload. Query Store can identify a query with a plan choice regression and retain a prior plan for investigation. Do not force a plan blindly. Check that it is suitable for the parameter patterns the application uses. The old plan is evidence, not a permanent trophy. There is no medal for forcing it forever.

Fix Regressions or Revert the Compatibility Level Safely

When a query regresses, compare its plans, statistics, indexes, and parameter behavior. A targeted plan force or query change can stabilize it while you investigate. Record why the intervention was chosen and how it will be reviewed. If several critical queries regress or a forcing attempt fails, reverting the database to its prior compatibility level is a direct way to reduce the new optimizer behavior.

I define the reversal trigger before deployment. Under pressure, a clear threshold beats a debate about whether the chart looks bad enough. Keep the original level and the ALTER DATABASE command in the runbook. Reverting the level does not downgrade the engine or erase data changes, so application behavior still needs validation.

Watch the Workload After the Window

A normal business cycle can contain operations absent from a short change window. Keep Query Store and application monitoring active long enough to see scheduled reports, maintenance, and peak usage. Note any plan force or query rewrite made during the observation period. If Query Store enters a read-only state, investigate why before using its lack of new data as proof of stability.

I review the next scheduled batch after a level change. A web screen can be healthy while a nightly procedure slows down. The change record should remain open until the required workload has run. A single green smoke test is useful, but it is not the whole business cycle.

Confirm the Compatibility Level Is Safely in Place

Once important paths meet their targets, save the new compatibility level, Query Store state, interventions, and observed comparisons. Remove temporary forcing when a durable query or index fix replaces it. Update the rollback instructions and application support notes. If the level was reverted, document the specific regressions and a plan to address them before another attempt.

The goal is a measured move, not a race to the newest number. A separate compatibility change lets the team use newer behavior while keeping a practical route back when a plan surprises you. Repeat the comparison after statistics maintenance or a major data change if important plans shift again.

Related reading on this blog: How to Change Database Compatibility Level? and How to Read a SQL Server Build Number.

A compatibility level change is not a version badge, it is a workload change you can measure and reverse.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.