Chapter 10: Intelligent Query Processing

This chapter covers 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. Originally, SQL Server 2017 introduced three of those features under the name of adaptive query processing, and several features have been added to the latest two releases, up to SQL Server 2022. One of those features, memory grant feedback, has been improved with every release.

In general, you can make these features available by enabling the applicable database compatibility level for the database, which could be either 140, 150, or 160 for SQL Server 2017, SQL Server 2019, and SQL Server 2022, respectively. However, as of SQL Server 2022, some of the new features require the query store to be enabled, for example, to persist the information of queries and plans. Every intelligent query processing feature can be disabled if you found a...