Two databases on one SQL Server can choose different plans for the same query shape. Intelligent query processing follows database compatibility and individual feature switches, so the server version alone does not tell you what is active. Check the database first, then the plan.
Intelligent Query Processing Starts at the Database Level
Compatibility level selects a family of query processing behaviors for each database. It is independent of the engine version, within the levels the engine supports. A database restored or upgraded to a newer instance can keep its older level. I check this before comparing plans between test and production. Otherwise the conversation starts with hardware and ends with a setting nobody read.
SELECT name, compatibility_level
FROM sys.databases
WHERE state_desc = N'ONLINE'
ORDER BY name;The result is an inventory, not proof that every eligible feature was used. A query must have a matching shape, and some features need Query Store or repeated executions. A low level can keep an improvement out of consideration. A high level does not force every query into a new plan shape. Record the level beside each workload comparison.
Map the Main Feature Waves
At level 140, SQL Server 2017 introduced interleaved execution for eligible multi-statement table-valued functions, batch mode memory grant feedback, and batch mode adaptive joins. At level 150, SQL Server 2019 added table variable deferred compilation, scalar UDF inlining, row mode memory grant feedback, and batch mode on rowstore. At level 160, SQL Server 2022 added parameter sensitive plan optimization, cardinality estimation feedback, and degree of parallelism feedback. At level 170, SQL Server 2025 added optional parameter plan optimization for eligible optional filters.
That map is a starting point. Some improvements also require a particular engine version, Query Store state, and an applicable query. Percentile and persisted memory grant feedback have their own requirements and do not fit a one-line compatibility rule. Read the feature's requirements before promising a result. What query shape are you trying to improve, and what evidence would identify that feature in its plan?
Inspect the Intelligent Query Processing Switches
Database scoped configurations can override an eligible feature without lowering the whole compatibility level. This matters after an emergency workaround. A switch left off years later can explain why two otherwise similar databases behave differently. Query the names and values in the database under investigation. The list varies by engine version, so discover what exists rather than hard-coding a query that assumes every newer setting is present.
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name LIKE N'%FEEDBACK%'
OR name LIKE N'%DEFERRED%'
OR name LIKE N'%INTERLEAVED%'
OR name LIKE N'%BATCH_MODE%'
OR name LIKE N'%PARAMETER%'
OR name LIKE N'%UDF%'
ORDER BY name;A value of 1 means the feature is allowed, not that a particular statement used it. Some older versions express an override with a name beginning DISABLE, while newer versions offer a positive switch. Read the exact setting name before interpreting the value. I save this output with the database compatibility level and Query Store state when investigating an upgrade.
Check Query Store Requirements
Several feedback features need Query Store enabled and in READ_WRITE mode to persist learning. A feature that was active in one environment can appear inconsistent in another if Query Store is read-only or full. Check the actual state rather than assuming that the desired state took effect. A database can request READ_WRITE and operate in READ_ONLY for a documented reason.
SELECT actual_state_desc, desired_state_desc,
current_storage_size_mb, max_storage_size_mb,
readonly_reason
FROM sys.database_query_store_options;Do not turn on every IQP option in response to one slow query. Fix the evidence trail first. Check whether the query is captured, whether a plan changed, and whether the feature has the history it needs. A learning feature without usable history has very little to learn. That sounds obvious until an upgrade checklist becomes a list of switches with no workload test.
Find Intelligent Query Processing Evidence in the Plan
Capture the actual execution plan for the representative statement, with the same parameters and database context as the application. For batch mode, inspect operator Execution Mode. For memory grant feedback, inspect the MemoryGrantInfo properties after repeated executions; the first execution cannot reflect feedback from a previous run. For table variable deferred compilation, compare the table variable estimate with the rows present when the statement was first compiled. For interleaved execution, inspect plan properties around the multi-statement function and its cardinality.
SET STATISTICS XML ON;
SELECT COUNT_BIG(*) AS object_columns
FROM sys.objects AS o
JOIN sys.columns AS c ON c.object_id = o.object_id;
SET STATISTICS XML OFF;This query shows how to obtain XML plan output, not a guarantee that an IQP feature will activate. Use a statement that matches the feature you are testing. An actual plan can show runtime counters that an estimated plan cannot. Some feedback features need several executions and a stable cached plan, so capture more than the first run.
A feature can also be skipped because the optimizer finds no useful candidate. For example, batch mode on rowstore is aimed at analytical shapes, not every small lookup. A row mode plan at level 150 is not automatically evidence that its switch is off. Look at the input row counts and operator work. For a parameter sensitive plan, inspect dispatcher and variant plans in Query Store rather than expecting one plan to explain every parameter value. Preserve the application call pattern during the test.
Change One Lever and Recheck
A compatibility-level change affects more than one feature. Test a representative workload in a nonproduction copy, record baseline Query Store plans, and have a rollback procedure. A database scoped switch can narrow a workaround, but it still needs a measured reason. When a feature helps one query and hurts another, a query-level hint can be more precise than disabling it across the database.
I prefer a small comparison sheet: engine version, database level, scoped switches, Query Store state, query text, plan ID, and runtime numbers. That set turns an upgrade debate into a test. If the plans differ, inspect the first meaningful estimate or operator choice before declaring the newer behavior good or bad. The server does not owe every query the same plan merely because they share an instance.
Related reading on this blog: Batch Mode in RowStore: Performance Comparison and SQL SERVER 2019: Disabling Scalar UDF Inlining.
An IQP feature is not active by version alone, it is active when the plan shows its effect.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
