SQL Server's Intelligent Query Processing Enhancements

 SQL Server's Intelligent Query Processing (IQP) framework has seen significant improvements in recent versions, particularly in SQL Server 2022 and the 2024 preview. These features collectively aim to improve query performance automatically without requiring code changes.

Key Intelligent Query Processing Enhancements

Parameter Sensitive Plan Optimization (PSP)

• Addresses the "parameter sniffing" problem by automatically maintaining multiple cached execution plans for the same query
• The query optimizer intelligently selects the most appropriate plan based on input parameter values
• Reduces the need for manual query hints or plan guides
• Particularly valuable for queries where different parameter values require drastically different execution strategies

Cardinality Estimation Improvements

• Enhanced statistical modeling for more accurate row count predictions
• Better handling of correlated columns and complex predicates
• Improved histogram analysis for skewed data distributions
• More accurate estimations for expressions and functions

Memory Grant Feedback

• Adaptive memory allocation that learns from previous executions
• Prevents both under-allocation (causing expensive spills to disk) and over-allocation (wasting memory)
• Works for both batch and row mode execution
• Now supports concurrent query executions and persists feedback across server restarts

Batch Mode on Rowstore

• Extends batch processing benefits (traditionally limited to columnstore indexes) to rowstore tables
• Automatically applied to eligible complex queries on traditional tables
• Significant performance boosts for analytical queries without requiring index changes

Degree of Parallelism (DOP) Feedback

• Dynamically adjusts parallelism based on past query execution metrics
• Prevents both under-parallelization (not using available resources) and over-parallelization (wasting resources on coordination)
• Adapts to varying server workloads

Interleaved Execution

• Optimizes plans with multi-statement table-valued functions (MSTVFs)
• Pauses optimization, executes parts of the query to get accurate cardinality, then resumes optimization
• No longer requires explicit recompile hints

Query Store Improvements

• Enhanced performance insights with more granular metrics
• Better plan forcing mechanisms
• Improved query wait statistics for bottleneck identification
• Automatic plan correction capabilities for identifying and fixing regression-prone queries

These improvements work together as part of SQL Server's commitment to "it just works better" query processing, requiring minimal administrator intervention while delivering substantial performance benefits.