Memory Grant Feedback: Persistence and Percentile Grant

October 27, 2022

Product
SQL

Part of the SQL Server 2022 blog series.

Memory grant feedback (MGF) is an existing feature in SQL Server, with two critical improvements available in SQL Server 2022: feedback persistence, and percentile grant feedback. These two features enhance the benefits of memory grant feedback as it already existed in SQL Server—allowing for less re-learning of appropriate grants and preventing fluctuating grant requirements from blocking the benefit of memory grant feedback.

Memory grant feedback

This existing feature aims to prevent costly spills or wasteful memory allocation of queries by remembering the memory usage of previous executions of the query and adjusting the grant based on previous data. This helps to prevent spills for underestimates, and to increase throughput of a workload by trimming overly large memory grants to a size that better fits a query.

Memory grant persistence

In prior versions of SQL Server, memory grant feedback data was stored only in the query plan cache. Thus, whenever the plan was evicted from cache, or in the case of failover/server restart, the system would have to re-learn the memory grant feedback from scratch. It seems prudent to store relevant information in the query store. This way it could be retrieved at any time from disk, with no concern for performance degradation after cache eviction or server restart. Because query store is on by default in SQL Server 2022, this feature is an obvious win for improving the overall effectiveness of memory grant feedback.

Percentile grant feedback

Prior to SQL Server 2022, memory grant feedback was only determining the current grant adjustment based on the single most recent execution of the query. However, in some cases—especially those where there is a cached plan for a stored procedure in which different parameters cause vastly different result set sizes (and thus vastly different memory requirements)—this can trigger a severe anti-pattern of alternating request sizes and always-wrong memory grant adjustments. This pattern is shown in the image below:

In this example, the first query execution needs 800MB and is given 800MB. On the second execution, the query requires only 5MB but is given 800MB. MGF will realize that this was a massive over-grant and adjust the subsequent execution grant to 5MB—but we are back the original parameter for which 800MB is needed! When MGF only looks at the single prior execution, this undesirable pattern can occur. Before SQL Server 2022, MGF will detect this scenario and disable itself—noticing that it is not helping to improve the workload.

SQL Server 2022 introduces percentile grant—a way of looking at more than just the single prior execution of a query. With percentile grant, we can look back to the history of executions and adjust memory grants based on a larger set of data points. We always err toward providing more memory to avoid spills, as spills are typically substantially more impactful to the customer than hits to throughput from an oversize grant. Thus, in this parameter-sensitive scenario, we might end up with a pattern of grants and executions that looks more like the following:

In this example, you can see that the first execution required a very low memory grant, but the second execution required a much higher grant, which it was not given due to the first execution. The third execution requires a smaller grant size, but percentile grant (blue line) gives it a grant higher than the last required grant, and the algorithm that we had previously (orange line) would grant only the amount needed in the prior execution. Over time, the blue line fine-tunes itself to the upper limit of the memory grants required by the query, whereas the prior grant algorithm finds itself out of phase with the grant required.

Taken together, these two requirements to memory grant feedback make the feature more reliable and more robust. By persisting the feedback, it becomes robust to failovers, restarts, and cache evictions. By using the new percentile grant algorithm, we are able to respond much more effectively to queries with a widely vacillating grant requirement—allowing the feature to continue to bring benefit to the customer even in pathologically bad scenarios. These two improvements to memory grant feedback are just one of the many intelligent query processing improvements in SQL Server 2022.

Learn More