Intelligent query processing—a family of features whose purpose is to improve the performance of existing workloads with no application changes or minimal implementation effort. I will cover the new features of adaptive query plans in SQL server 2022: Memory grant feedback and Adaptive join.
Memory grant feedback feature was introduced with SQL Server 2017 and has been improved and enhanced in the following two releases. Original release was memory grant feedback for batch mode. Row mode version was introduced in SQL Server 2019. This feature helped to adjust memory grant sizes for both batch and row mode operators. SQL Server 2022 improves on this feature by adding persistence and percentile capabilities.
New to SQL Server 2022 is that some of the features, such as memory grant feedback, require the query store to persist the information of queries and plans. Memory grant feedback had the limitation that the feedback was lost when plans were evicted or SQL Server was restarted.
Choosing an incorrect join algorithm during query optimization due to cardinality estimation errors could seriously impact the performance of your query. Main purpose of an adaptive join is to leave the decision of which physical join to utilize until the execution time. Although memory grant feedback started with batch mode and was later enhanced to include row mode, adaptive join remains a batch mode-only feature.
This is a maximum demo session with minimum theory!