Start here
Nothing applies until you raise compatibility
Most new features ship switched off inside your old compatibility level. Raising it is the first and largest change you make.
| Release | Level | What the level unlocks |
|---|---|---|
| 2016 | 130 | Query Store, database-scoped configuration, cardinality estimation 120 |
| 2017 | 140 | Adaptive joins, interleaved execution, batch-mode memory grant feedback |
| 2019 | 150 | Scalar UDF inlining, batch mode on rowstore, table variable deferred compilation |
| 2022 | 160 | Parameter sensitive plans, cardinality feedback, percentile memory grants |
| 2025 | 170 | Optional parameter plans, optimized locking, preview features |
Check every database, then raise it
select name, compatibility_level from sys.databases; lists what you actually have. alter database YourDb set compatibility_level = 170; moves one database forward.
A database created on 2025 starts at 170, and tempdb runs at 170. A database you restore or upgrade in place keeps whatever level it arrived with, usually 120 or 130.
Every version
Five settings every database needs first
Almost every adaptive feature in the last five releases reads from these. Set them once, before measuring anything.
| Setting | Command | Because |
|---|---|---|
| Query Store | alter database YourDb set query_store = on (operation_mode = read_write); | Cardinality, DOP and memory-grant feedback and OPPO all read it. Off means a silent no-op. |
| Capture mode | alter database YourDb set query_store (query_capture_mode = auto); | ALL is the default on 2016 and 2017. AUTO from 2019, CUSTOM from 2019 with your own thresholds. |
| Row versioning | alter database YourDb set read_committed_snapshot on; | Lock after qualification needs it. Ledger needs snapshot isolation too. |
| ADR | alter database YourDb set accelerated_database_recovery = on; | Off by default on-premises from 2019 on. Instant rollback and constant log truncation. |
| Server | max degree of parallelism, cost threshold for parallelism | DOP feedback lowers from your MAXDOP. It cannot invent a sensible starting value. |
2016 and 2017
The era when the switches appeared
2016 invented the per-database switch itself. 2017 made most of its new query processing free, with one expensive exception.
2016: optimizer hotfixes are still off today
alter database scoped configuration set query_optimizer_hotfixes = on; is trace flag 4199 as a setting. It defaults to off on every release including 2025, so post-RTM optimizer fixes never reach you unless you ask.
2017: adaptive query processing needs no switch
Adaptive joins, interleaved execution and batch-mode memory grant feedback arrive automatically at compatibility 140. Nothing to enable, nothing to buy.
2017: automatic plan correction is the one to flip
alter database YourDb set automatic_tuning (force_last_good_plan = on); needs Query Store on and read-write. It reverts a regression by itself.
2017 also turned clr strict security on by default. Unsigned CLR assemblies that loaded fine on 2016 stop loading until you sign them or lower the setting.
2019 and 2022
Mostly automatic, with four exceptions
Raise the level and the query processing arrives on its own. What does not is recovery, tempdb contention, and one feedback feature 2022 kept off.
| Release | Feature | Switch | Default |
|---|---|---|---|
| 2019 | UDF inlining, batch rowstore, table variable compilation | Compatibility 150 | On |
| 2019 | Accelerated database recovery | alter database YourDb set accelerated_database_recovery = on; | Off |
| 2019 | Memory-optimized tempdb metadata | alter server configuration set memory_optimized tempdb_metadata = on; then restart | Off |
| 2019 | Ad hoc plan cache bloat | set optimize_for_ad_hoc_workloads = on per database | Off |
| 2022 | Parameter sensitive plans, cardinality feedback | Compatibility 160 | On |
| 2022 | Percentile and persistent memory grants | Compatibility 140 or higher | On |
| 2022 | Degree of parallelism feedback | set dop_feedback = on per database | Off |
| 2022 | Ledger | allow_snapshot_isolation on, then ledger on the database | Off |
Microsoft recommends memory-optimized tempdb metadata only when you have measured temporary object metadata contention. It needs a restart and it has limitations.
Readable truncation errors are free at 150 and above. Below that you still need trace flag 460 to see the value that overflowed.
2025
2025 does not switch these on for you
Seven changes. The first three are per-database, the last four are server options that want a RECONFIGURE.
| Feature | Command | Note |
|---|---|---|
| Optimized locking | alter database YourDb set optimized_locking = on; | Off on boxed 2025, always on in Azure. Pair with RCSI for lock after qualification. |
| Optimized sp_executesql | alter database scoped configuration set optimized_sp_executesql = on; | The fix for compilation storms. One compile, the rest wait and reuse it. |
| Preview features | alter database scoped configuration set preview_features = on; | Vector index, change event streaming, fuzzy matching, half-precision vectors. |
| External REST calls | sp_configure 'external rest endpoint enabled', 1; | Required before sp_invoke_external_rest_endpoint will run. |
| External AI runtimes | sp_configure 'external AI runtimes enabled', 1; | Required before create external model will run. |
| Server-scoped credentials | sp_configure 'allow server scoped db credentials', 1; | Lets a credential use the managed identity. Azure Arc scenario. |
| ZSTD backup compression | sp_configure 'backup compression algorithm', 3; | Value 3 exists only on 2025. 2022 rejects ALGORITHM = ZSTD outright. |
PREVIEW_FEATURES gates the features Microsoft has not made generally available. Not for production, whatever the workload.
2025
Already on, so stop hunting for a switch
These were opt-in or absent in 2022. On 2025 they are simply the behaviour, provided the compatibility level is there.
- DOP feedback is on by default. It was off in 2022, and it is the switch most often still scripted by hand.
- Optional parameter plan optimization is on at compatibility 170, for the
col = @p or @p is nullpattern. - Cardinality feedback for expressions is on, at compatibility 160 and above.
- Temporary statistics on readable secondaries are created and updated by default, so a promoted replica is not cold.
- Optimized plan forcing is on, which cuts compile cost for queries you force through Query Store.
- Tempdb space resource governance and ADR inside tempdb have no switch at all.
Standard edition also gains Resource Governor here, with 32 cores and a 256 GB buffer pool. That changes sizing more than any query hint.
Do not
Leave these alone, and check one script
2025 added ten server options. Seven are engine-internal limits meant for a support call, not a tuning pass.
Engine internals
Availability group commit time, max UCS send boxcars, ADR cleaner lock timeout, SLOG memory quota, max lock manager cache memory, tiered memory. Three of them are static, so a careless change waits for the next restart to surprise you.
One removal that breaks deployment scripts
DW_COMPATIBILITY_LEVEL is a syntax error on 2025. Any pipeline that scripts out every scoped configuration stops at that line.
REST endpoints, AI runtimes and server-scoped credentials all let code inside the database reach outside the server. Put them in the hardening baseline and audit them before a developer asks.