Treat a database compatibility-level increase as a measured application change. Establish a representative baseline at the existing level, confirm Query Store is collecting the required evidence, then compare correctness and workload performance after the proposed change. An engine upgrade and adoption of new optimiser behaviour are related decisions, but they are not the same setting.1, 2
This article uses SQL Server 2022 and a possible move from compatibility level 150 to 160 as its reference case. It supplies planning and read-only inspection, not a production change command. No compatibility setting, query plan or application workload was changed or tested for it.
Understand what the setting controls
Database compatibility level controls documented Transact-SQL and query-optimisation behaviours for that database. It does not turn a newer SQL Server engine into an older engine, restore removed components or provide backward compatibility for every feature on the instance.2
SQL Server 2022 supports level 160 as well as several earlier levels listed in Microsoft's reference. Choose a level supported by the actual destination version and by the application supplier. Do not infer that the newest level is automatically acceptable for an application whose vendor certification has a narrower scope.2
Microsoft's recommended Query Store upgrade workflow separates the engine upgrade from a later compatibility-level change, with a baseline collected at the earlier level before exposing the workload to newer optimiser behaviour.1 This reduces one source of ambiguity, but it is not a guarantee that nothing else about performance can change during an engine or infrastructure upgrade.
Record the engine build, database compatibility, relevant database-scoped settings and application release. These values belong in the same change record. Otherwise, a reviewer cannot tell which part of the environment produced the baseline.
Define the workload you need to protect
Start with business operations rather than only the most expensive SQL statements. Include frequent interactive requests, large reports, scheduled jobs, imports and infrequent critical processing. A month-end operation may be absent from an ordinary weekday sample even though its failure would be material.
For each operation, identify a correctness check, an agreed response or completion limit and representative data/parameter classes. Record concurrency and workload volume. A fast single-user test does not establish acceptable behaviour during the overlap between reporting and transaction processing.
Distinguish broad coverage from claimed completeness. A baseline collected over one business cycle can be useful, but it must state which seasonal or exceptional workflows were not observed. The owner can then decide whether to simulate them in an authorised test environment or retain them as an explicit acceptance risk.
Also identify application features that depend on behaviour controlled by compatibility. Performance is only one gate. A query that becomes faster but produces an unacceptable result is a failed change.
Inspect the current level and collection state
The following read-only statement is adapted from the compatibility reference. It returns the current database's setting without modifying it:
SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();
Use the intended database and an account authorised to inspect its metadata. Changing compatibility requires ALTER permission on the database; that does not mean the diagnostic reader should receive change authority.2
Inspect Query Store's actual and desired state, capture mode, retention and size usage. Its options view distinguishes a desired read-write setting from an actual read-only condition. For SQL Server 2022, the documented inspection permission is VIEW DATABASE PERFORMANCE STATE or an applicable higher permission.3
If capture is disabled, incomplete or already at its capacity limit, resolve that as a separately scoped preparatory change. Do not clear existing history simply to make the dashboard green. Preserve useful evidence and choose a retention/capture policy that can cover the planned comparison.
Record the timestamp from which the baseline becomes trustworthy. Newly enabled collection cannot tell you how a query behaved before it started capturing. A default setting for newly created databases does not establish the state of an upgraded database.
Build a baseline that supports a decision
Use Query Store to retain query identities, plans, execution counts and relevant resource measures. Microsoft's workflow includes both overall database consumption and individual top-consuming queries in a before-and-after assessment.1
Keep successful and aborted executions distinguishable, and compare equivalent intervals. Record aggregate workload volume as well as per-execution cost so that a quieter period is not presented as a performance improvement. Keep application observations beside the SQL results because the business response includes more than database execution.
Select a small set of explicit gates. For example: the critical order workflow produces the expected records; the reporting job completes inside its agreed window; the important query families do not exceed agreed resource or latency limits; and error/retry counts do not rise. The values belong to the service owner and measured baseline, not a generic article.
Save current plan-forcing and scoped-configuration decisions. An already forced plan can influence what the comparison demonstrates. Without that record, a team may believe it tested unconstrained new behaviour when it actually retained a mitigation from an older incident.
Stage the change and separate causes
Prepare a disposable environment with a representative data shape and workload. Keep test transactions and integrations isolated from customer effects. This article does not provide a replay tool or authorise running one.
Avoid combining the compatibility change with unrelated schema, memory, parallelism and application changes unless the release genuinely requires them. Where changes must be combined, record that performance attribution will be less precise and expand the test design accordingly.
Microsoft documents that stored procedures are recompiled after a compatibility change.2 Plan for the transition rather than comparing only a convenient warmed execution. Retain both initial and steady-state observations where they matter to the application.
For production approval, the change record should identify the exact database, current and proposed levels, supporting source/version, acceptance owner, observation interval and return condition. State what will remain uncertain until a later workload executes. A command completing successfully is the beginning of observation, not the acceptance result.
Handle regressions at the appropriate scope
If a small number of queries regress, compare their plans and workload context. Query Store plan forcing can be considered for a verified previously acceptable plan; Microsoft's upgrade workflow includes that option.1 It requires its own evidence and follow-up, rather than automatic forcing of every old plan.
Check representative parameter ranges. The previous plan may be good for the original complaint but poor for another workload shape. Verify that the force is effective and that the application improves; record any forcing failure instead of treating the requested state as the observed outcome.
If regressions are widespread or the required workload cannot be accepted, consider returning to the prior supported compatibility setting through the agreed change process. Microsoft includes that decision in the upgrade workflow when plan correction is insufficient.1
A compatibility return is not an engine downgrade. It also does not reverse application deployments, schema changes or business writes performed during the window. Confirm that any newly adopted feature remains valid under the proposed return setting, and preserve the independent backup/recovery route for failures outside the setting's scope.
Close with a bounded acceptance record
Report the baseline and changed intervals, workload coverage, query exceptions, correctness results and outstanding risks. Preserve the exact settings and plans used. Assign an owner and review date to temporary forcing or other mitigation so it does not silently become permanent.
If a critical monthly job was not exercised, say that it remains unverified. If the test environment had a different data distribution, explain how that limits the inference. Source documentation describes the supported mechanism; it does not prove this application is ready.
This prospective workflow differs from diagnosing a query that is already slow. Its purpose is to make adoption reviewable before the change and to define what evidence would justify keeping or returning from it.
Learning notes
- Engine version, compatibility level and scoped settings are separate facts.
- A baseline must cover business workload and correctness, not only average query speed.
- Returning a compatibility setting does not undo other changes or completed transactions.
Acceptance checklist
- [ ] Engine version, compatibility level and relevant database settings are recorded separately.
- [ ] A retained baseline covers representative business operations and query behaviour.
- [ ] Approved comparison includes correctness, latency, failures and parameter-sensitive workloads.
- [ ] The return plan identifies changes and business effects that a compatibility-level reversal cannot undo.
References
- Query Store Usage Scenarios - SQL Server | Microsoft Learn. Accessed 2026-11-01.
- ALTER DATABASE Compatibility Level (Transact-SQL) - SQL Server | Microsoft Learn. Accessed 2026-11-01.
- sys.database_query_store_options (Transact-SQL) - SQL Server | Microsoft Learn. Accessed 2026-11-01.