Summary

In this chapter, we covered the most important intelligent query processing features of all three releases up to SQL Server 2022. Of course, new to SQL Server 2022 is that some of the features, such as memory grant feedback, cardinality estimation feedback, and the degree of parallelism feedback, now 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.

The first release of intelligent query processing, in SQL Server 2017, introduced three of those features under the name of adaptive query processing, and usually, you can make these features available by enabling the applicable database compatibility level of the database. Similarly, any intelligent query processing feature can be disabled if you find a performance regression, either at the database level, by changing the database compatibility level, or at the query level by using...