SQL Server Compatibility Level Upgrades: The Regression That Hid Behind the Version Bump

The migration itself was flawless. We moved the finance database from SQL Server 2014 to a shiny 2019 instance over a Saturday, restored, ran the smoke tests, and flipped the compatibility level to 150 with the upgrade — because the runbook said the new features live at the new level, which is true, and because nothing in testing had warned us, which was the actual finding. Monday and Tuesday were fine. On Wednesday the month-end reconciliation suite — three queries that joined ledger tables through a view with a decade of sediment in it — ran for nine hours instead of thirteen minutes. The hardware was faster, the statistics were freshly updated, and the plans were unrecognizable: the new cardinality estimator had looked at our skewed, multi-join, mildly-terrible view and produced row estimates off by three orders of magnitude, and the optimizer had dutifully built plans for a different universe. Setting the database back to compatibility level 110 took thirty seconds and ended the incident. Getting to 150 safely took another quarter, and that second project is the one worth writing down.

Compatibility level is the most underestimated dial in a SQL Server upgrade, because it is a single number that gates an entire query processor generation, and because the standard advice — flip it and see — measures your blast radius in production. This is what the level actually controls, why the estimator is usually the casualty, and the Query Store methodology that turns the flip into a non-event. Applies to upgrades landing on SQL Server 2016 through 2022.

What does the compatibility level actually gate?

Three categories, and only one of them causes the regressions. First, T-SQL surface behavior: a handful of syntax and semantics differences between levels, mostly ancient history by now. Second, feature availability: the modern query processing features — adaptive joins and batch mode on rowstore at 150, scalar UDF inlining at 150, intelligent query processing additions at 160 — require the corresponding level, which is the carrot for flipping at all. Third, and this is the whole risk budget: the cardinality estimator version. At compatibility level 120 and above you get the estimator Microsoft rewrote in 2014, with different assumptions about join independence, containment, and ascending keys; at 140 and 150 the estimator and the optimizer’s plan-selection heuristics shift again under you. A query whose plan was shaped by the legacy estimator’s assumptions — usually shaped over years of people adding indexes and hints until the old estimator produced something good — can land on the new estimator and get a plan that is theoretically defensible and practically catastrophic. Nothing is broken; the engine is doing exactly what the level tells it to do. That is what makes it treacherous: the regression is correct behavior you did not rehearse. The check that tells you what you are standing on is one line:

SELECT name, compatibility_level
FROM sys.databases
ORDER BY compatibility_level, name;

-- per-query override exists when you cannot flip the database:
-- ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
-- or the query hint: OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

Why does the estimator change hit old workloads hardest?

Because old workloads carry old assumptions embedded in their schema and code, and the new estimator’s more-correct assumptions interact with them badly. The concrete cases from our finance migration: the join independence assumption — the new estimator assumes join predicates are uncorrelated where the legacy one assumed containment — turned our ledger-to-calendar-to-region joins into row estimates a thousand times low, and every downstream operator sized its memory and chose its join strategy from that fiction. Table-valued functions, multi-statement ones especially, got a fixed one-row guess under the legacy estimator and a different fixed guess under the new one; either guess is wrong, but they are wrong differently, and plans tuned around one break under the other. And the ascending-key problem — statistics that never see the newest values on an ever-growing key — is handled by different heuristics at different levels, which is why the same query on the same data can change plan the moment the level changes. None of this is an argument against the new estimator, which is better on the median query and measurably better on schemas built this decade; it is an argument that the median query is not the one that pages you. The workload’s worst ten queries decide whether your upgrade is a success, and they are exactly the queries most likely to have co-evolved with the old estimator’s quirks. My statistics and cardinality notes cover the estimation mechanics themselves; this piece is about the change management around them.

What is the Query Store methodology for flipping safely?

The procedure that made our second attempt boring has five steps and one hard rule: the level does not change until Query Store has a baseline. Step one, on the new instance, leave the database at the old compatibility level — it keeps its old estimator, and the upgrade itself goes live without a query processor change, which is the single most important de-risking in the whole process. Step two, enable Query Store and let it capture at least one full business cycle — for us, a month, because the month-end suite was the casualty last time and regressions that only appear monthly need a month of baseline. Step three, flip the compatibility level and immediately run the regression report, the workflow I detailed in the Query Store regression hunting notes, comparing post-flip runtime against the captured baseline. Step four, force the baseline plans for the regressors — the mechanics are in the plan forcing notes — which restores the old performance at the new level, instantly and per query, instead of rolling back the whole database for three bad apples. Step five, fix the forced queries properly on your own schedule and unforce them one by one:

-- capture baseline at the OLD level, then after the flip find regressions
SELECT q.query_id, qt.query_sql_text,
       rs_old.avg_duration AS avg_dur_old, rs_new.avg_duration AS avg_dur_new
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs_new ON rs_new.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i_new
  ON i_new.runtime_stats_interval_id = rs_new.runtime_stats_interval_id
WHERE i_new.start_time > @flip_time
ORDER BY rs_new.avg_duration DESC;

-- pin the last good plan for each regressor
EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;

Our second flip produced eleven regressions out of thousands of queries. Nine were forced and fixed within two sprints — most needed nothing more than a statistics refresh with FULLSCAN and one needed a rewritten view. Two turned out to be latent bugs the new estimator merely exposed, which is the part nobody tells you: some regressions are the estimator being right about a query that was accidentally fast before.

What honest gains come with the new level?

Enough that staying at the old level forever is its own risk, and I say that as someone whose incident report started this article. Batch mode on rowstore at 150 gave our scan-heavy reports a genuine two-to-four-times improvement once the regressors were pinned. Adaptive joins rescued a class of queries whose best plan legitimately varied by parameter. Scalar UDF inlining turned one vendor function from a per-row curse into set-based code with zero application change. The intelligent query processing additions at 160 — parameter sensitive plan optimization, memory grant feedback — are the first features I would call structural fixes for problems I have been working around for a decade. The reason to upgrade the level is real; the reason to upgrade it with a baseline, a forcing safety net, and a fix-forward queue is everything above. Flip it like a database change, not like a checkbox, and it stops being the scariest line in the migration runbook.

Watching version and level drift with MonPG when SQL Server support lands

For MonPG product capabilities and setup information, see the SQL Server monitoring page. Use the diagnostics in this article to identify the measurements and operational checks your deployment needs.

Related documentation

• SQL Server: Query Store