SQL Server 2022 Expert

Key Features

Parameter Sensitive Plan (PSP) Optimization

PSP addresses parameter sniffing by creating multiple plan variants for a single parameterized query, each optimized for different parameter value ranges.

-- Verify PSP is active (requires compat level 160)
-- PSP plans show as plan_type = 2 in Query Store
SELECT q.query_id, p.plan_id, p.query_plan_hash,
       qt.query_sql_text
FROM sys.query_store_plan p
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE p.plan_type = 2;  -- Dispatcher plan

-- PSP creates a dispatcher plan that routes to variant plans
-- based on runtime parameter values and cardinality range boundaries

-- Disable for a specific query if problematic:
SELECT ... OPTION (USE HINT ('DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION'));

How it works:

1. Optimizer detects a query with skewed data distribution (parameter sniffing risk)
2. Creates a dispatcher plan with cardinality range boundaries (low/medium/high)
3. At runtime, sniffed parameter value maps to a range, and the corresponding variant plan executes
4. Each variant is independently optimized for its cardinality range

Limitations:

• Only works with equality predicates on a single column
• Column must have a statistics histogram with sufficient skew
• Does not work with multi-column parameter sniffing scenarios
• Maximum 3 plan variants per query

DOP (Degree of Parallelism) Feedback

Automatically adjusts the degree of parallelism for individual queries based on observed performance:

-- DOP feedback requires Query Store enabled and compat level 160
-- Monitor DOP adjustments:
SELECT q.query_id, qt.query_sql_text,
       p.plan_id, rs.avg_duration, rs.avg_cpu_time
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE p.has_compile_time_dop_feedback = 1;

If a parallel query consistently uses less parallelism than granted, DOP feedback reduces it. If a query benefits from more parallelism, it can increase it (up to MAXDOP).

Cardinality Estimation (CE) Feedback

The optimizer adjusts cardinality estimates based on actual vs. estimated row counts from previous executions:

-- CE feedback adjusts model assumptions for specific queries
-- Look for CEFeedback hints in execution plans
-- Monitor via Query Store and plan attributes

CE feedback can correct for:

• Correlation assumptions between columns
• Join containment assumptions
• Base table cardinality estimation errors

Optimized Plan Forcing

When Query Store forces a plan, the optimizer now stores the optimization replay script. Forced plan recompilation is significantly faster because it replays specific optimization steps instead of full re-optimization.

Query Store Hints

Apply query hints without modifying application code:

-- Force MAXDOP 2 and RECOMPILE for a specific query
EXEC sp_query_store_set_hints @query_id = 42,
    @query_hints = N'OPTION (MAXDOP 2, RECOMPILE)';

-- Clear hints
EXEC sp_query_store_clear_hints @query_id = 42;

-- View active hints
SELECT query_hint_id, query_id, query_hint_text,
       last_query_hint_failure_reason_desc
FROM sys.query_store_query_hints;

Supported hints: MAXDOP, RECOMPILE, OPTIMIZE FOR, FORCE ORDER, USE HINT, TABLE HINT, and more.

Ledger Tables

Tamper-evident tables that use blockchain-inspired hash chains to detect unauthorized changes:

-- Updatable ledger table
CREATE TABLE dbo.AccountBalance (
    AccountID INT PRIMARY KEY,
    Balance DECIMAL(18,2),
    LastModified DATETIME2
) WITH (SYSTEM_VERSIONING = ON, LEDGER = ON);

-- Append-only ledger table (no updates/deletes)
CREATE TABLE dbo.AuditLog (
    EventID INT IDENTITY PRIMARY KEY,
    EventType NVARCHAR(50),
    Details NVARCHAR(MAX)
) WITH (LEDGER = ON (APPEND_ONLY = ON));

-- Verify ledger integrity
EXEC sp_verify_database_ledger;

-- View ledger history
SELECT * FROM sys.database_ledger_transactions;

Contained Availability Groups

Contained AGs include instance-level objects (logins, SQL Agent jobs, linked servers) within the AG, so they failover automatically:

-- Create a contained AG
CREATE AVAILABILITY GROUP [ContainedAG]
WITH (CONTAINED)
FOR DATABASE [MyDB]
REPLICA ON
    N'Node1' WITH (ENDPOINT_URL = 'TCP://Node1:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT),
    N'Node2' WITH (ENDPOINT_URL = 'TCP://Node2:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

Benefits:

• Logins created in the contained AG replicate to all replicas
• SQL Agent jobs replicate
• No more "login doesn't exist on secondary" after failover

S3-Compatible Object Storage

Back up to and restore from S3-compatible storage:

-- Create credential for S3
CREATE CREDENTIAL [s3://mybucket.s3.amazonaws.com/backups]
WITH IDENTITY = 'S3 Access Key',
SECRET = 'aws_access_key:aws_secret_key';

-- Backup to S3
BACKUP DATABASE [MyDB]
TO URL = 's3://mybucket.s3.amazonaws.com/backups/MyDB.bak'
WITH COMPRESSION, CHECKSUM;

Works with AWS S3, MinIO, and other S3-compatible storage providers.

New T-SQL Functions

-- GREATEST / LEAST (replaces complex CASE expressions)
SELECT GREATEST(col1, col2, col3) AS max_val,
       LEAST(col1, col2, col3) AS min_val
FROM dbo.MyTable;

-- DATETRUNC (truncate date to specified precision)
SELECT DATETRUNC(MONTH, GETDATE()) AS first_of_month;
SELECT DATETRUNC(HOUR, GETDATE()) AS start_of_hour;

-- GENERATE_SERIES (number table generator)
SELECT value FROM GENERATE_SERIES(1, 100);
SELECT value FROM GENERATE_SERIES(1, 100, 5);  -- step by 5

-- STRING_SPLIT with ordinal (preserves position)
SELECT value, ordinal
FROM STRING_SPLIT('a,b,c,d', ',', 1)
ORDER BY ordinal;

-- IS DISTINCT FROM (NULL-safe comparison)
SELECT * FROM dbo.MyTable
WHERE col1 IS DISTINCT FROM col2;
-- Equivalent to: WHERE col1 <> col2 OR (col1 IS NULL AND col2 IS NOT NULL) OR ...

-- WINDOW clause (reusable window definitions)
SELECT col1,
       SUM(amount) OVER w AS running_total,
       AVG(amount) OVER w AS running_avg
FROM dbo.MyTable
WINDOW w AS (PARTITION BY category ORDER BY date_col ROWS UNBOUNDED PRECEDING);

XML Compression

Compress XML data stored in XML columns:

ALTER TABLE dbo.MyTable REBUILD WITH (XML_COMPRESSION = ON);
-- Or per index/partition

Typical compression ratios: 50-80% for XML data.

Resumable ADD CONSTRAINT

Add primary key and unique constraints with pause/resume capability:

ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable PRIMARY KEY (ID) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 120);

Version Boundaries

• This agent covers SQL Server 2022 (compat level 160) specifically
• Features NOT available in 2022 (introduced in 2025):
  • Native vector data type and DiskANN vector indexes
  • Regular expression functions (REGEXP_LIKE, REGEXP_REPLACE, etc.)
  • Native JSON data type and JSON index
  • Optimized locking (TID locking, lock after qualification)
  • sp_invoke_external_rest_endpoint (call REST APIs from T-SQL)
  • Change Event Streaming
  • Fabric mirroring
  • SSRS replaced by Power BI Report Server

Common Pitfalls

1. PSP creating too many plan variants -- Monitor Query Store for dispatcher plans. If PSP creates worse plans than parameter sniffing, disable per-query with the USE HINT.
2. Query Store hints conflicting with plan forcing -- If you both force a plan and apply hints, the hint takes precedence and may prevent the forced plan from being used.
3. Contained AG complexity -- Contained AGs add a system database (contained_ag_name_master, contained_ag_name_msdb). Understand the dual-master/dual-msdb model.
4. Ledger table overhead -- Ledger tables create additional history tables and require digest management. Plan for storage growth.
5. GENERATE_SERIES memory -- Large series (millions) consume memory. Use with reasonable bounds.
6. CE feedback instability -- CE feedback adjustments may not stabilize for queries with highly variable data. Monitor for plan flapping.
7. Stretch Database deprecated -- Stretch Database is deprecated in 2022. Do not use for new projects. Migrate existing Stretch Database solutions.

Migration from SQL Server 2019

When upgrading from SQL Server 2019 (compat level 150) to 2022 (compat level 160):

1. Enable Query Store at compat 150 -- Capture baseline performance
2. Upgrade engine -- Keep compat level at 150 initially
3. Change compat level to 160 -- Unlocks PSP optimization, DOP feedback, CE feedback
4. Monitor IQP v2 features:
   • Check for PSP dispatcher plans in Query Store
   • Monitor DOP feedback adjustments
   • Watch for CE feedback plan changes
5. Evaluate new features:
   • Query Store hints for parameter sniffing workarounds (replace plan guides)
   • Contained AG if you have login/job sync issues during failover
   • Ledger tables for compliance/audit requirements
   • S3 backup for cloud-native backup strategies
6. Replace deprecated workarounds:
   • Replace FOR XML PATH string concatenation with STRING_AGG (already available in 2017)
   • Replace CASE-based min/max with GREATEST/LEAST
   • Replace number tables with GENERATE_SERIES

Known Behavioral Changes at Compat 160

• PSP optimization may create multiple plans for queries that previously had one
• DOP feedback may reduce parallelism for over-parallelized queries
• CE feedback adjusts cardinality estimates based on history
• DATETRUNC, GREATEST, LEAST, GENERATE_SERIES functions available
• STRING_SPLIT gains ordinal parameter
• IS DISTINCT FROM operator available
• WINDOW clause for reusable window definitions

Reference Files

Load these for deep knowledge:

• ../references/architecture.md -- Storage engine, buffer pool, query processing
• ../references/diagnostics.md -- Wait stats, DMVs, Query Store usage, Extended Events
• ../references/best-practices.md -- Instance configuration, backup strategy, security