Understanding memory grants is important for ensuring SQL Server queries run efficiently, as they play a pivotal role in allocating temporary storage for data operations like sorting and joining. By analyzing how SQL Server calculates these grants and the impact of new features like Row Mode Memory Grant Feedback, database administrators can significantly enhance system performance. This blog delves into the mechanisms of memory allocation for query execution, the challenges it presents, and the operational benefits of leveraging advanced SQL Server functionalities to optimize resource utilization.
Memory Grants in SQL Server Explained
Memory Grants are critical for SQL Server queries, facilitating temporary storage for data during operations such as sorting and joining, and during the compilation of query plans. SQL Server requests memory from the SQL Server Operating System (SQLOS) based on the estimated needs of a query. This estimation process aims to allocate sufficient memory to prevent any single query from monopolizing server memory resources. Memory grants are categorized into required memory, the minimal amount needed for sorting and joining, and additional memory, allocated based on the estimated row count.
Challenges with Pre-Allocated Memory Grants
Allocating memory grants prior to query execution presents two main issues: overallocation and underallocation of memory. Overallocation results in unused reserved memory, leading to inefficient resource utilization. Underallocation may cause operations to spill to disk, degrading performance. These allocation inaccuracies typically stem from outdated or inaccurate column statistics, leading to improper memory grant estimations by the Query Optimizer.
Introduction of Memory Grant Feedback
SQL Server 2017 introduced Batch Mode Memory Grant Feedback within its Adaptive Query Processing suite, aimed at adjusting memory grant sizes based on actual execution metrics. This adaptive mechanism enhances subsequent query executions by adjusting memory allocations based on prior performance.
Expanding on this, SQL Server 2019 implemented Row Mode Memory Grant Feedback, extending the benefits of adaptive memory allocation to Rowstore data queries. This feature dynamically recalibrates memory grants for queries involving Rowstore data, optimizing resource utilization and improving execution efficiency.
Rowstore Data Definition
Rowstore data, defined as data logically organized in a tabular format with rows and columns and physically stored in a row-wise manner, contrasts with Columnstore formats designed for batch processing. This traditional format includes heaps, clustered indexes, or memory-optimized tables.
With SQL Server 2022 the Intelligent Query Processing family is getting a little larger. Among its additions are a couple of enhancements to memory grant feedback: percentile and persistence mode.
The goal of memory grant feedback is to automatically correct overly large and small grants that can hurt performance. Excessively large grants waste memory, but can also impact run times and concurrency if requests must wait for those large grants. When excessively large grants are detected (memory granted >> memory used) you’ll see a warning in the query plan
At the other extreme are memory grants too small to accommodate the rows in play during query execution. This results in performance-killing sort and hash spills resulting in more physical I/O in tempdb. We also get warnings when this occurs:
When memory grant feedback is available (and enabled) you’ll see something like this in the XML query plans for subsequent executions of the problematic statements:
<MemoryGrantInfo . . . GrantedMemory="420320" MaxUsedMemory="7376" MaxQueryMemory="3254424" LastRequestedMemory="0" IsMemoryGrantFeedbackAdjusted="No: First Execution"/>
<MemoryGrantInfo . . . GrantedMemory="11776" MaxUsedMemory="7384" MaxQueryMemory="3254424" LastRequestedMemory="420320" IsMemoryGrantFeedbackAdjusted="Yes: Adjusting"/>
<MemoryGrantInfo . . . GrantedMemory="11776" MaxUsedMemory="7384" MaxQueryMemory="3254424" LastRequestedMemory="11776" IsMemoryGrantFeedbackAdjusted="Yes: Stable"/>
Grant sizes are decreased until less than 50% of the allocated memory is wasted. In my testing it appears that compensation for spills is about a 10X increase in grant size.
If you happen to have a parameter-sensitive query and the correct grant size is a moving target (constantly shifting up and down), the feedback mechanism recognizes this and disables itself. The new MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT feature addresses this challenge, but we’ll tackle that on another day.
The feedback data is persisted in the cached plans so the corrected grant sizes (larger or smaller) can be used going forward.
But perhaps “persisted” isn’t quite the right word as cached plans only exist in memory. In the event of a server restart or failover, or if a plan is evicted from cache due to memory pressure, the memory grant adjustment is lost and must be reestablished through a few more sub-optimal statement executions.
That’s where the new Intelligent Query Processing feature comes in! When the database scoped configuration setting MEMORY_GRANT_FEEDBACK_PERSISTENCE is enabled by default, the memory grant adjustments are truly persisted in the Query Store! There’s a new (still undocumented) catalog view where you can see the feedback data: sys.query_store_plan_feedback.
Conclusion
The evolution of Memory Grant Feedback in SQL Server, particularly with the advent of Row Mode Memory Grant Feedback, marks a significant enhancement in optimizing memory allocation for query execution. This adaptive feature, by dynamically adjusting memory grants based on actual execution metrics, not only mitigates the challenges of overallocation and underallocation but also contributes to a more efficient utilization of resources. Consequently, database administrators and system engineers are equipped with a more robust toolset to ensure SQL Server’s performance is maximized, reflecting a sophisticated approach to managing the intricate dynamics of memory usage in database operations. The continual advancements in Intelligent Query Processing, including the introduction of persistence mode for memory grant adjustments, further solidify SQL Server’s commitment to delivering high-performing, reliable data management solutions.