An upgrade checklist can be complete while database optimizer settings lag behind. Comparing each database with the native compatibility level shows which ones still use older optimizer rules. That list is a review queue, with Query Store evidence behind every proposed change.
Separate the Engine From the Database Level
SQL Server's engine version and a database's compatibility level answer different questions. The engine version identifies the installed software. The database level controls selected language and query-optimizer behaviors. Upgrading an instance preserves older levels in many databases instead of silently changing every workload's planning rules.
I check this distinction early after an upgrade. An instance can run current software while an important database still uses older behavior. That is a valid transition state when the team has a testing plan. It becomes a problem when everyone assumes the upgrade completed both decisions.
For SQL Server 2012 and later, the installed major version multiplied by ten gives the native level used in this inventory. The arithmetic is specific to these versions, not a universal historical version formula. The following query reports the engine version alongside the calculated target. Azure SQL requires a separate supported-level interpretation.
SELECT SERVERPROPERTY('ProductVersion') AS EngineVersion,
CONVERT(int, SERVERPROPERTY('ProductMajorVersion')) * 10 AS NativeLevel;List Databases Below the Native Compatibility Level
A useful inventory includes the current level, target level, creation date, and database state. The creation date helps explain why databases differ, but it does not prove when a database was upgraded or when its level changed. Treat it as context rather than an upgrade audit trail.
The next query leaves the four system databases out. The generated statement is text only. Returning an ALTER DATABASE command does not execute it. QUOTENAME protects database names containing spaces or closing brackets. Keep that protection when copying the query into a central inventory script.
Check the output against the applications that own each database. Is the lower level deliberate, or did nobody schedule the next step? Add an owner and review date outside this query. A list of commands without an accountable decision just moves the uncertainty into a spreadsheet. The database level is not a competition to see who reaches the largest number first.
DECLARE @NativeLevel int = CONVERT(int, SERVERPROPERTY('ProductMajorVersion')) * 10;
SELECT name, compatibility_level, create_date, state_desc,
N'ALTER DATABASE ' + QUOTENAME(name)
+ N' SET COMPATIBILITY_LEVEL = ' + CONVERT(nvarchar(3), @NativeLevel)
+ N';' AS ReviewStatement
FROM sys.databases
WHERE database_id > 4
AND compatibility_level < @NativeLevel
ORDER BY compatibility_level, name;Put Query Store Ahead of the Change
Query Store records query text, plans, and runtime statistics across time. That gives you a baseline before changing optimizer behavior and a way to find regressions afterward. Enable and configure it before the test window, with enough storage and retention to cover representative activity.
I want normal workload evidence before making a level change. One successful query in SSMS does not represent a database's daily work. Include reporting, scheduled processing, parameter extremes, and less frequent tasks that matter to the business. A quiet afternoon baseline misses the overnight workload completely.
Inspect Query Store's actual state rather than assuming its requested state took effect. READ_ONLY or storage pressure prevents the capture you expect. In the target database, the next query shows those conditions. This is a database-local check, so run it for each database under review and retain the output with the baseline period.
SELECT desired_state_desc, actual_state_desc, readonly_reason,
current_storage_size_mb, max_storage_size_mb,
query_capture_mode_desc
FROM sys.database_query_store_options;
Test a Native Compatibility Level Change With Real Inputs
Raising the native compatibility level enables newer behavior that changes plan selection. A query that improves with one parameter can regress with another. Compare representative requests, not just the easiest test values. Include skewed values, empty results, large results, and the transactions around the query.
Restore a suitable copy to a test instance running the destination engine. Reproduce the relevant settings and data distribution. Then change the database level in that copy and replay the workload using the application's normal execution pattern. Parameters, session settings, and concurrency affect what SQL Server compiles.
Record plan changes and runtime evidence from your own tests. Duration alone hides increased CPU or logical reads. Query Store averages also need their execution counts and observation windows. A short window dominated by one unusual execution is weak evidence. Keep successful and regressed queries visible together so the decision reflects the whole workload.
Prepare a Response to Regressions
Before changing production, agree on the response when a critical query regresses. Query Store plan forcing provides a targeted option when an appropriate previous plan is available and can be forced successfully. Check forcing status and failure reasons. A requested force is not proof that the optimizer used it.
Returning to the previous compatibility level is a broader response. It restores the older level's behavior for the database, but it does not undo application deployments or schema changes performed during the same window. Separate those changes when possible so troubleshooting remains clear.
Changing the level triggers recompilation effects for relevant plans. Expect a transition period and watch compilation pressure as well as execution behavior. Avoid clearing the entire instance plan cache as an extra ritual. That affects databases outside the change. Define success criteria and rollback responsibility before the maintenance window begins, while everyone still has time to think clearly.
Close the Review With Captured Evidence
After the change, check sys.databases again and confirm the chosen level. Then compare Query Store windows with similar workload conditions. Keep watching long enough to cover the jobs and reports included in the original review. An immediately successful smoke test is only the first checkpoint.
Document databases that remain below the target with a concrete reason. That reason can include an identified regression, an unsupported application assumption, or a test that has not yet completed. Record the next action instead of letting the exception become permanent through silence.
A native compatibility level inventory is most useful when repeated after upgrades and restores. Newly restored databases bring their previous level with them. Put the check into your operational review, pair it with Query Store readiness, and make each change an evidence-backed database decision rather than an automatic instance-wide sweep.
Related reading on this blog: Raising a Database Compatibility Level Safely and Compatibility Level 170: What SQL Server 2025 Turns On.
An engine upgrade is not a compatibility review, it is the start of one.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
