Quick Scan Report – Running at older compatibility level

What this check looks for

Databases whose compatibility_level is lower than the level matching the instance’s own version, computed from SERVERPROPERTY('ProductVersion').

Why it matters

Compatibility level is not a compatibility shim around the edges. It selects which query optimizer the database runs on.

The largest single difference is at level 120, SQL Server 2014, where the cardinality estimator was rewritten. The old estimator had been essentially unchanged since SQL Server 7.0, and the new one models correlated predicates, ascending keys and multi-table joins differently. A database left at 110 or below is using an estimator designed in the 1990s on an instance that ships a much better one.

Level by level, what you are leaving unused:

• 130 and above: batch mode execution improvements, and from 2019 batch mode on rowstore, which can transform analytic queries.
• 140: adaptive query processing, including interleaved execution for multi-statement table valued functions and adaptive joins.
• 150: scalar UDF inlining, table variable deferred compilation, and memory grant feedback. Memory grant feedback in particular fixes a problem that has its own check on this report.
• 160: parameter sensitive plan optimization, and cardinality estimation feedback.

Several of those directly address findings elsewhere in this scan. An instance reporting memory grants queuing, on a database at level 130, is declining a feature built to fix it.

And it is usually not deliberate. A database restored from an older instance keeps its level, so the common path is: migrate to a new server, restore the databases, everything works, and every database is quietly still on the old optimizer. The upgrade was paid for and half of it was not taken.

That said, the caution is real. Raising the level changes plans, and a plan change can be a regression. That is why this is Medium rather than High, and why the method below matters more than the statement.

How to confirm it yourself

SELECT d.[name],
       d.[compatibility_level],
       CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(30)),
                 CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(30))) - 1) AS INT) * 10 AS [instance_level],
       d.[is_query_store_on],
       d.[state_desc]
  FROM sys.databases AS d WITH (NOLOCK)
 WHERE d.[database_id] > 4
 ORDER BY d.[compatibility_level];

Whether Query Store is available to make this safe:

SELECT [name], [is_query_store_on] FROM sys.databases WITH (NOLOCK) WHERE [database_id] > 4;

And the database scoped settings, because they interact:

USE [YourDatabase];
GO
SELECT [name], [value], [value_for_secondary]
  FROM sys.database_scoped_configurations WITH (NOLOCK)
 WHERE [name] IN ('LEGACY_CARDINALITY_ESTIMATION', 'PARAMETER_SNIFFING',
                  'QUERY_OPTIMIZER_HOTFIXES', 'MAXDOP');

How to fix it

The statement is trivial. The method is the point.

ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 160;

Do it the safe way, which Microsoft documents and which exists precisely for this:

1. Turn Query Store on and let it collect a representative period, ideally a full business cycle including month end:

ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);

2. Raise the compatibility level.
3. Watch for regressions in Query Store’s Regressed Queries view, or the Plan Regressions report in this product.
4. Force the old plan for anything that got worse, which fixes that query without giving up the level for the whole database:

EXEC sp_query_store_force_plan @query_id = 1234, @plan_id = 5678;

That last step is why raising the level is not the gamble it used to be. Before Query Store the only lever was the whole database; now a single regressed query can be pinned while everything else benefits.

If a handful of queries regress and you cannot address them individually, the intermediate position is to raise the level and keep the old estimator:

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

That takes the newer features while keeping the older estimation model. It is a deliberate compromise rather than a default.

Check with the application vendor first where the database belongs to one. Some certify against a specific level, and that is a real constraint rather than folklore.

How long it takes

About an hour for the change itself. The Query Store observation period before and after is days to weeks, and that is the part that makes it safe.

Related reports

Related checks

Frequently asked questions

Will raising it break my application? Query syntax deprecated at a given level can stop working, which is rare above level 110. The real risk is plan changes, and Query Store plus plan forcing is how that is managed.

Can I go back? Yes. ALTER DATABASE ... SET COMPATIBILITY_LEVEL works in both directions and takes effect immediately. That makes a careful trial genuinely reversible.

Do I have to go straight to the newest? No, and stepping is a reasonable approach on a large jump: move to 130 first, settle, then continue. It doubles the testing and halves the size of each change.

Our vendor requires an older level. Then that is the constraint. Ask them for it in writing and revisit at the next application upgrade, because the cost compounds with every version you stay behind.