Intelligent Query Processing (IQP)

 Intelligent Query Processing (IQP) is a suite of features introduced in SQL Server to optimize query performance with minimal changes to existing code. It is designed to automatically improve query performance by adapting to runtime conditions, reducing the need for manual query tuning. IQP is part of the broader Intelligent Database concept and includes several enhancements aimed at improving the performance of queries across various workloads.

Here are the key features of Intelligent Query Processing:

1. Batch Mode on Rowstore (SQL Server 2019)

• Previously, batch mode execution was only available for columnstore indexes. Now, SQL Server 2019 extends batch mode processing to rowstore tables, providing performance improvements for analytical queries on traditional rowstore tables.

2. Table Variable Deferred Compilation (SQL Server 2019)

• In prior versions, table variables were always assumed to have a single row at compile time, leading to suboptimal query plans when they contained more data. SQL Server 2019 defers the compilation of table variables until runtime when actual row counts are known, improving query performance.

3. Approximate Query Processing (SQL Server 2019)

• The APPROX_COUNT_DISTINCT function is introduced to provide faster approximate distinct count calculations for large datasets. This is useful in cases where exact precision is not required but performance is critical, such as in large analytical workloads.

4. Scalar UDF Inlining (SQL Server 2019)

• Scalar user-defined functions (UDFs) often caused performance bottlenecks because they were executed row-by-row. With inlining, SQL Server replaces the UDF calls with the function body, allowing the entire query to be optimized as a single unit and improving performance significantly.

5. Adaptive Joins (SQL Server 2017)

• SQL Server can now switch dynamically between Nested Loops and Hash Joins during query execution based on the actual row count. This helps optimize join performance in cases where cardinality estimates may be inaccurate.

6. Interleaved Execution for Multi-Statement Table-Valued Functions (SQL Server 2017)

• SQL Server can now defer execution of certain parts of the query plan involving multi-statement table-valued functions (MSTVF) until runtime when actual data sizes are known, leading to more accurate query plans and better performance.

7. Memory Grant Feedback (SQL Server 2017)

• SQL Server adjusts the memory allocated for query execution based on feedback from prior executions. If a query requests too much or too little memory, the system learns and adjusts the memory grant for future executions, reducing memory contention and improving performance.

8. Batch Mode Memory Grant Feedback (SQL Server 2019)

• Expanding on memory grant feedback, batch mode execution can now dynamically adjust memory grants, which is especially beneficial for large analytical queries, helping to avoid both under- and over-provisioning of memory.

9. Batch Mode Adaptive Joins (SQL Server 2019)

• Similar to adaptive joins, this feature extends adaptive behavior to batch mode, allowing the system to switch between join strategies at runtime based on data distribution and actual row counts.

10. Cardinality Estimation Feedback (SQL Server 2022)

• Introduced in SQL Server 2022, this feature helps improve the accuracy of query plans by updating cardinality estimates based on feedback from prior executions, optimizing for future runs.

11. Parameter Sensitivity Plan Optimization (SQL Server 2022)

• Parameter sensitivity issues can cause performance problems due to a single query plan being used for different parameter values. This feature addresses the problem by generating and storing multiple plans for a single query based on parameter values, improving query performance and reducing bottlenecks.

12. Degree of Parallelism (DOP) Feedback (SQL Server 2022)

• This feature optimizes the degree of parallelism used by queries, adjusting based on the actual runtime conditions. Over time, SQL Server learns and refines the parallelism strategy, improving the query's performance in future executions.

---

These IQP features help SQL Server adapt to changing workloads and data distribution, reducing manual tuning effort while improving performance across OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) workloads. They contribute to more efficient query execution by learning from actual data and workload behavior.