A suite of adaptive query processing improvements introduced in SQL Server 2017 and later, as well as Azure SQL Database, that automatically enhance query performance without requiring changes to queries or indexes.
- Compatibility level: Intelligent Query Processing (IQP) features are enabled by setting the database compatibility level to 140 (SQL Server 2017) or higher, allowing existing queries to benefit automatically.
- Key features: IQP includes Adaptive Joins, which dynamically choose between Nested Loop and Hash Join operations at runtime; Memory Grant Feedback, which adjusts memory allocations based on actual query usage; and Batch Mode on Rowstore, which brings columnar batch processing benefits to traditional row-based tables.
- SQL Server 2022 enhancements: SQL Server 2022 introduces Parameter Sensitive Plan Optimization, which detects parameter-sensitive queries and generates multiple execution plans optimized for different parameter value ranges, reducing the impact of parameter sniffing.
- Complementary optimization: IQP enhances query performance automatically but does not replace traditional DBA responsibilities such as index tuning and query optimization. It is designed to improve a specific class of workloads that are difficult to optimize statically.
- Deployment best practice: DBAs should validate IQP features in non-production environments before increasing database compatibility levels in production, as certain workloads may require query hints to override specific IQP behaviors.
- Related terms: Query Store, Execution Plan, Parameter Sniffing, Automatic Tuning, Columnstore Index.
