Query Store and Intelligent Query Processing

36.1 Query Store (2016+; ON by default for new databases since 2022)

A "flight data recorder" for queries: plans, runtime stats, and wait stats over time.

SSMS reports: Regressed Queries, Top Resource Consuming, Queries with Forced Plans, High Variation, Overall Resource Consumption.

Query Store on readable secondaries (2022).

Automatic tuning: ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

ALTER DATABASE ShopDB SET QUERY_STORE = ON
(OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO, MAX_STORAGE_SIZE_MB = 1024,
 CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), WAIT_STATS_CAPTURE_MODE = ON);

-- Views
SELECT * FROM sys.query_store_query;
SELECT * FROM sys.query_store_plan;
SELECT * FROM sys.query_store_runtime_stats;
SELECT * FROM sys.query_store_wait_stats;

-- Force a plan
EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;
EXEC sp_query_store_unforce_plan @query_id = 42, @plan_id = 7;

-- Query Store hints (2022): apply hints without changing code
EXEC sp_query_store_set_hints @query_id = 42, @query_hints = N'OPTION (MAXDOP 1)';

36.2 Intelligent Query Processing (IQP) Feature Family

Adaptive joins (batch mode) Interleaved execution for MSTVFs

Memory grant feedback (batch → row mode, percentile, persistence) Batch mode on rowstore Scalar UDF inlining Table variable deferred compilation

Approximate query processing (APPROX_COUNT_DISTINCT, APPROX_PERCENTILE_*) Parameter Sensitive Plan optimization Cardinality estimation feedback Optimized plan forcing

Optional parameter plan optimization (OPPO) CE feedback for expressions

Most features turn on with compatibility level 150 / 160 / 170.