Check Your Server Version First

Everything in this module depends on which version and compatibility level you are actually running. Several of the most useful features arrived in specific releases and some are gated behind the database compatibility level rather than the engine version — so a SQL Server 2022 instance hosting a database still at compatibility level 110 gets almost none of them.

"We're on 2022" is frequently reported and frequently wrong: it is easy to confuse the SSMS version, the version the vendor says is supported, and the version actually installed. Check before planning anything.

The version check

SELECT  @@VERSION                                     AS full_version,
        SERVERPROPERTY('ProductVersion')              AS product_version,
        SERVERPROPERTY('ProductMajorVersion')         AS major_version,
        SERVERPROPERTY('ProductLevel')                AS product_level,   -- RTM / SP1 / CU
        SERVERPROPERTY('ProductUpdateLevel')          AS cu_level,
        SERVERPROPERTY('Edition')                     AS edition,
        SERVERPROPERTY('EngineEdition')               AS engine_edition,  -- 3 = Enterprise, 2 = Standard, 5 = Azure SQL DB
        SERVERPROPERTY('IsHadrEnabled')               AS availability_groups_enabled;

Major version numbers:

| ProductMajorVersion | Release | |---|---| | 11 | SQL Server 2012 | | 12 | SQL Server 2014 | | 13 | SQL Server 2016 | | 14 | SQL Server 2017 | | 15 | SQL Server 2019 | | 16 | SQL Server 2022 |

The compatibility level check — equally important

SELECT  name,
        compatibility_level,
        is_query_store_on,
        is_read_committed_snapshot_on,
        snapshot_isolation_state_desc,
        is_auto_create_stats_on,
        is_auto_update_stats_on,
        is_parameterization_forced
FROM sys.databases
WHERE name = DB_NAME();     -- or list all: remove the WHERE clause

Compatibility level 160 = 2022, 150 = 2019, 140 = 2017, 130 = 2016, 120 = 2014, 110 = 2012.

A database restored from an old server keeps its old compatibility level. It is entirely normal to find a 2022 instance running a database at level 110 because it was migrated from 2012 and nobody raised it. That database gets the old cardinality estimator, the old statistics auto-update threshold, and none of the intelligent query processing features.

Feature availability, plainly

| Feature | Needs engine | Needs compat level | |---|---|---| | Query Store | 2016+ | any (enable per database) | | New cardinality estimator | 2014+ | 120+ | | Dynamic statistics update threshold | 2016+ | 130+ | | Adaptive joins, memory grant feedback (batch mode) | 2017+ | 140+ | | Interleaved execution for MSTVFs | 2017+ | 140+ | | Scalar UDF inlining | 2019+ | 150+ | | Table variable deferred compilation | 2019+ | 150+ | | Row-mode memory grant feedback | 2019+ | 150+ | | Batch mode on rowstore | 2019+ | 150+ | | Query Store hints | 2022 (and 2019 with CU) | any with Query Store on | | Parameter Sensitive Plan optimisation | 2022 | 160 | | Memory grant feedback persistence | 2022 | 160 | | Cardinality estimation feedback | 2022 | 160 | | Degree of parallelism (DOP) feedback | 2022 | 160 | | Query Store on by default for new databases | 2022 | n/a |

Two practical readings of that table:

If you are on 2022 at compatibility level 160, most of this module's later lessons apply automatically and your job is to verify they are working.
If you are on 2022 at a lower compatibility level, the highest-leverage single change available may be raising it. That is a real change with real risk — it swaps the cardinality estimator and can move plans in both directions — so it is done deliberately, with Query Store recording before and after, not casually.

Raising compatibility level safely

The safe sequence, since Query Store gives you a rollback story:

-- 1. Confirm Query Store is capturing (Module 1) and let it run for a representative week.

-- 2. Raise the level
ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 160;

-- 3. If specific queries regress, force their previous good plan rather than reverting everything:
--    EXEC sp_query_store_force_plan @query_id = ..., @plan_id = ...;

-- 4. Full rollback if needed
-- ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 110;

There is also a middle option: raise the compatibility level for the new features but keep the old cardinality estimator, which is usually the component responsible for regressions.

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

While you are here: the database-scoped configuration

Several tuning-relevant settings live here rather than at instance level. Worth reading once:

SELECT configuration_id, name, value, value_for_secondary, is_value_default
FROM sys.database_scoped_configurations
ORDER BY name;

Names worth noticing: MAXDOP, LEGACY_CARDINALITY_ESTIMATION, PARAMETER_SNIFFING, QUERY_OPTIMIZER_HOTFIXES, LAST_QUERY_PLAN_STATS, and the DW_COMPATIBILITY_LEVEL. If PARAMETER_SNIFFING is OFF on your database, someone has already been down this road — that setting disables sniffing for every query, which is a very blunt instrument and worth understanding before you change anything else.

In practice

Check SERVERPROPERTY('ProductMajorVersion') and the database's compatibility level first, because those two decide which tools you have. A 2022 instance hosting a database still at level 110 gets the legacy cardinality estimator and none of the intelligent query processing features, and that is common after a migration.