Memory grant feedback
The memory grant feedback feature was introduced with SQL Server 2017 and has been improved and enhanced in the following two releases. The original release was memory grant feedback for batch mode, and its 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.
Let’s start by explaining why this feature is needed. We need to remember that SQL Server uses the buffer pool to store the pages read from disk, and this memory buffer pool uses most of the memory available to a SQL Server instance. However, a memory grant is additional memory that is allocated to a query, and it is only required for operations such as sorting or hashing. This additional memory is only required for the duration of the query. Sorting and hashing operations were covered in Chapter 4, The Execution Engine...
