📐 SQL Server Performance: Solving Memory Grant Overestimation - the limitations of Memory Grant Feedback and the power of SQL Tuning
In an earlier blog SQL Server Performance: Troubleshooting Memory Grant Overestimation using SQL Diagnostic Manager and SQL Server execution plan (mydbadventure.blogspot.com), I introduced how SQL Server miscalculated the required memory for processing a complex query, leading to memory contention on the server and impacting other concurrent queries. In this blog, we will explore and evaluate different approaches to resolve the issue ✨. Are you excited to follow along with me? If so, let’s dive in!!! 🤗
Introduction
When troubleshooting a SQL Server system issue, I found it effective to first identify the problem, and then weigh pros and cons, and exceptions of different solutions. For example, in this case, some solutions might not work, others work but take more time, but one solution stands out as the best solution. Don’t be easily fooled by any single approach 😉. Let’s focus on understanding the problem and researching different ways to solve it.
As I did a lot of research on this, here are the potential solutions for the issue:
- Memory grant feedback
- Upgrade to SQL Server 2022 to use the Persistence Mode Memory Grant feedback
- Query Tuning
Memory Grant Feedback
Microsoft introduced a powerful improvement with regard to memory grants. Simply put, SQL Server attempts to remember the memory needs of a prior executions, and based on this historical data, adjusts the memory grant for subsequent executions (Memory grant feedback - SQL Server | Microsoft Learn).
The version of SQL Server I was using had memory grant feedback enabled, so it should have been adjusting the memory grants as needed. However, it became clear that the query’s memory grant issues were persisting.
What was happening with the Memory Grant Feedback then? Why wasn’t it adjusting the memory grant effectively as expected?
According to Microsoft, feedback will be lost if the query plan is removed from cache (Memory grant feedback - SQL Server | Microsoft Learn). This means that if the execution plan is evicted daily, SQL Server had to rebuild the feedback from scratch each time and thus there would be times it allocated too much memory to execute the query. This could explain our issue.
Additionally, Microsoft states that if a query has unstable memory requirements (e.g. parameter-sensitive), Memory Grant Feedback will keep adjusting the memory grant. After several runs and adjustments with no settlement, the feedback may disable itself.
I decided to analyze the query's historical executions throughout the day. All executions of the query were retrieving data for the last year using the condition >=DATEADD(YEAR, -1, CAST(CONVERT(YEAR, -1, CAST(CONVERT(varchar, getdate(), 101) as datetime)). In theory, the amout of data processes are the same, and thus the memory consumption should either remains consistent or decreased to a stabled level due to the feedback. However, the chart below shows that the memory consumption dropped at around 12pm after about 10 runs with excessive memory, only to spike back up to inefficient levels afterwards. As I have said, there are two possible explanations:
- The feedback lost because the execution plan was removed from the cache due to reasons like query inactivity, memory pressure, statistics change causing plan recompilation. We have limited control over any of these events.
- The memory grant feedback disabled itself due to unstable memory requirement.
I established a test to explain it by running the query and recording the memory grant feedback status after several runs. Keep in mind, I was conducting this test in a testing environment with much less resource available, so the server also granted the memory differently from what have been shown. During the experiment, the feedback fluctuated between granting 400MB and 2GB. This inconsistency may have caused the feedback to disable itself due to unsuccessful adjustments towards a stable state.
| IsMemoryGrantFeedbackAdjusted | Memory Grant |
|---|---|
| No First execution | 5GB |
| Yes Adjusting | 400MB |
| Yes Adjusting | 2GB |
| Yes Adjusting | 400MB |
| Yes Adjusting | 2GB |
| Yes Adjusting | 2GB |
| Yes Adjusting | 4MB |
| Yes Adjusting | 2.5GB |
| Yes Adjusting | 600MB |
This fluctuating memory grant pattern was problematic. The memory allocation never stabilized. Next, let’s move on the new feature introduced in SQL Server 2022.
Upgrade to SQL Server 2022
SQL Server 2022 version introduces a feature called Persistence Mode Memory Grant feedback (Memory grant feedback - SQL Server | Microsoft Learn). This feature addresses the cache eviction limitation by storing grant information with other query data in Query Store. However, at the time of troubleshooting, I needed an immediate fix so upgrading SQL Server wasn’t an option.
Additionally, if the memory grant feedback disabled itself due to unstable memory requirement, Persistence Mode Memory Grant feedback would not solve the issue anyway.
*Side note: any database engine upgrade requires appropriate planning and testing to ensure smooth transition.
Query Tuning
As a last resort, I turned to the execution plan and began tuning the query.
I had two objectives while tuning:
- 1. Improve the estimation of row size and memory grant
- 2. Reduce the number of logical read.
First, I examined where the discrepancy came from and noticed a missing index, which caused the query to spend 2 minutes on an inefficient Index Spool operation. I added the missing index and query duration dropped from 2 minutes to 30s, the number of logical reads went down from 177 million reads to 30 million reads. (When reviewing the query plan, I analyzed it from right to left, following the operation order to identify where the miscalculation began and address it from there).
However, that didn’t resolve the row misestimation issue. The miscalculation now occured during the index seek operation (the right most operation in the query) and then affected the operation 2 and others down the line.
After pinpointing where the miscalculation starts, which is a subquery, I pulled the subquery out of the main query and stored the intermediate result in a temporary table. Previously, I was using a view, but I had to adjust my approach. I implemented a stored procedure that included multiple smaller queries and used temporary tables to hold the intermediate results. Looking at the statistics for the left-most operations of the three smaller queries, they aren’t too bad - at least for Queries 1 and 3.
Memory grant feedback also kicked in for queries 2 and 3, and the memory grant stabilized after 3 executions. SQL Server granted 1.3 GB of memory for executing the store procedure, a significant reduction from the original query’s 8-16 GB.
Logical reads dropped from 177 million to 9 million, and CPU time decreased from 3 minutes 15 seconds to just 45 seconds.
Key takeaways
Two main key takeaways from my experience that you could consider:
- Plan for regular SQL Server cumulative updates. These updates include the latest bug fixes and features that are essential for your system. Allocating enough time and effort to this planning ensures your server stays current and optimized.
- Write queries that are easy to understand. This allows the optimizer to evaluate more efficient access paths and accurately estimate the resources needed to run the query. As we saw in my case, despite SQL Server using features like Memory Grant Feedback, the original complex query couldn’t stabilize its memory grant after multiple executions. Queries with many joins and inaccurate row size estimations signal the need for a rewrite. Breaking the query into smaller parts and storing intermediate results in table variables or temporary tables can significantly improve performance.
Comments
Post a Comment