Memory Grants
Throughout the phases of query processing there are several memory consumers that the memory broker and the memory manager inside SQL Server try to handle efficiently. From those, compile, cache and memory grant are amongst the most significant.
Compile requires memory to build and search a proper plan out of all candidates, whereas cache represents the memory consumed to store compiled plans in cache for reuse. Lastly and most importantly, the memory grant represents a segment of server memory used to store temporary data while sorting and joining rows. The grant is reserved prior to the query execution and lasts for the query lifetime, its purpose is to improve query reliability under server load. The rationale is, a query with reserved memory is not only less likely to run out of memory while executing, but also to take the entire server memory.
Grants and Estimates
When SQL Server creates a compiled plan it calculates two parameters that will define the amount of requested memory:
- Required memory: The memory required to create internal data structures that will handle sorts and hash joins. It is mandatory that this amount is available in order to the query to be executed. The wait type that is accumulated if memory isn’t available is RESOURCE_SEMAPHORE.
- Additional memory: It’s used to store temporary rows in memory. This value depends intrinsically on the cardinality estimate of expected number of rows and their average size. It’s not required that this amount of memory is available, the data can be stored into disk instead, although depending on the quantity of data this can be as good as shooting yourself in the foot.
After deciding whether a memory grant is necessary or not, calculating the DOP and the memory limit for one query, these parameters are used to calculate the ideal query memory, i.e. the amount of memory the query needs to run properly, based on its cardinality estimate. Finally, SQL Server verifies if the ideal memory exceeds the per-query limit (reducing it if needed) and asks Resource Semaphore to grant the requested memory.
Now, if memory grants are deeply related to cardinality estimate, what happens if the numbers we get are not accurate, for example due to outdated statistics? The answer is… Pain. But as some pains are worse than others, we also might be more or less miserable depending on whether SQL Server overestimates or underestimates the work it thinks it has to do.
When SQL Server overestimates the work to be done in a particular query it will grant more memory than necessary. Although one might think that like money, there is no such thing as too much memory, that will come at a cost. This means we’re stealing memory from a limited source that feeds cache, buffer pool and other queries. In extreme cases this might cause queries to lack required memory, hence not executing. And to make things worse, until now, in row mode, this behavior would just keep repeating itself, execution after execution, until, potentially, the server dies in agony.
On the other hand, if SQL Server underestimates the workload, it may not grant enough memory to keep things out of tempdb. If it was expecting a few rows and ended up having to deal with millions, it will spill data to disk in order to handle the mess and this may have a significant impact on performance. As in overestimates, SQL Server would not learn from its mistakes and data would just continuously be spilled to disk in each execution.
But… Could inadequate memory grants be the root of all evil? Probably not, but if wait types like IO_COMPLETION or RESOURCE_SEMAPHORE start to accumulate that might just be the case.
Doctor, My Memory Hurts
In such circumstances there are tools that can be used to diagnose and troubleshoot these issues. As SQL Server editions went by, the number of available tools also risen.
In SQL Server 2012 Extended Events were added in order to help the diagnosis of these cases: sort_warning, hash_spill_details to improve tempdb spill diagnostics and query_memory_grant_usage to improve memory grant diagnostics. Also, two query memory grant options were made available (MIN_GRANT_PERCENT and MAX_GRANT_PERCENT) that allow to specify the minimum and maximum grant percentage (based on the memory grant that’s specified in the resource governor configuration) on a per query basis.
Later, SQL Server 2016 introduces the almighty Query Store and improves a couple of DMVs and Extended Events.
Lastly, in SQL Server 2017, the feature that is most closely related to the one we’re about to see was released: Batch Mode Adaptive Memory Grant Feedback. In fact, it is, basically, the same feature applied to a different context. If you want to know more about Adaptive Memory Grant Feedback in Batch Mode check Niko’s explanation here.
So, finally we’re in 2019 when SQL brought this feature to Row Mode. Let’s take a walk!
Row Mode Adaptive Memory Grant Feedback
For the purposes of this demo, I’m using a sample of FootballData public stats that I stored in a very simple and small database (no Contoso or AdventureWorks for you today). Also, keep in mind that this is an Enterprise Edition feature.
We’ll start with a lower compatibility level to confirm the behaviour prior to this feature.
ALTER DATABASE [FStats] SET COMPATIBILITY_LEVEL = 140
Next, we’ll create a store procedure to intentionally create an evil memory grant.
CREATE OR ALTER PROCEDURE GetHomeVictoriesByTeam
@Team nvarchar(128)
AS
BEGIN
DECLARE @TeamID int = (SELECT Id FROM Teams WHERE Name = @Team)
SELECT
l.Country,l.Name, m.Date,
h.Name AS HomeTeam, a.Name as AwayTeam,
m.HTHG,m.HTAG,m.HTR,m.FTHG,m.FTAG,m.FTR,
d.Att, d.Ref, d.HS, d.[AS], d.HST, d.AST,
d.HHW, d.AHW, d.HC, d.AC, d.HF, d.AF,
d.HFKC, d.AFKC, d.HO, d.AO, d.HY,
d.AY, d.HR, d.AR, d.Preview
FROM
Matches m (NOLOCK)
INNER HASH JOIN
MatchesDetails d (NOLOCK) ON m.id = d.MatchDetails
INNER JOIN
Teams h (NOLOCK) ON m.HomeTeam = h.id
INNER JOIN
Teams a (NOLOCK) ON m.AwayTeam = a.Id
INNER JOIN
Stadiums s (NOLOCK) ON s.Team = h.Id
INNER JOIN
Leagues l (NOLOCK) ON m.League = l.Id
WHERE
m.hometeam = @TeamID AND m.FTR = 'H'
ORDER BY
l.Name, m.Date, m.FTHG, m.FTAG,
m.HTHG, m.HTAG, m.HTR, m.FTR, d.Preview
END
And execute it as follows.
USE FStats EXEC GetHomeVictoriesByTeam 'Benfica'
When we open the query execution plan, it’s visible a warning indicating an underestimate that resulted on a spill to disk. In order to view the MemoryGrantInfo, right-click on the SELECT, then choose properties.
If we keep executing this statement, we’ll watch SQL Server suffering from anterograde amnesia, just like Dory. The value of DesiredMemory will not change and the query will spill to disk, execution after execution. Now, let’s set the compatibility level to 150 and see if things change.
ALTER DATABASE [FStats] SET COMPATIBILITY_LEVEL = 150 GO EXEC GetHomeVictoriesByTeam 'Benfica'
At first glance everything looks similar, we get that same warning in the query execution plan, but in the MemoryGrantInfo a new field named IsMemoryGrantFeedbackAdjusted is now visible with the value “NoFirstExecution”. That looks promising…
And in fact, when we execute it again, black magic starts to happen. This time SQL Server remembers that the desired memory wasn’t enough to sort the huge amount of Benfica’s home victories (incomprehensible mistake though) and requests more memory. This avoids spilling out to tempdb, as we can verify by the absence of the previous warning. Nevertheless, SQL Server hasn’t yet found the ideal memory grant size and is still trying to get it, as we can see by the value of IsMemoryGrantFeedbackAdjusted – “YesAdjusting“.
After a couple executions, SQL Server is finally able to achieve the ideal memory grant as we can confirm by the value of IsMemoryGrantFeedbackAdjusted – “YesStable“. Awesome! From now on, executing this query is all about stability and happiness. Or is it?!
The premise here is that SQL Server will try to use the result from a previous execution in order to adjust the memory grant in the following. In the previous example that was rather simple, because we’ve used the same parameter across executions. Contrarily, the real world, full of evil users, tends to be more dynamic. So, what will happen if we change parameters between executions? In order to determine that we’ll intercalate the parameters of two executions and create an Extended Event to catch those spills. As Katy Perry said, “don’t be afraid to catch spills, ride Extended Events and chase thrills”.
CREATE EVENT SESSION DontBeAfraidToCatchSpills ON SERVER
ADD EVENT sqlserver.sort_warning(
ACTION(sqlos.task_time,sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text))
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=ON)
GO
--GOTO: Management > Extended Events > Sessions > DontBeAfraidToCatchSpills > Start Session
--GOTO: Management > Extended Events > Sessions > DontBeAfraidToCatchSpills > Watch Live Data
EXEC GetHomeVictoriesByTeam 'Benfica'
EXEC GetHomeVictoriesByTeam 'Villareal'
GO 16
We’ve created an Extend Event using sqlserver.sort_warning because we know that, in this case, only the sort operation will spill to disk. Also note that we’re executing this block 16 times, so 32 executions of the store procedure are being done. The MemoryGrantInfo in the last execution plan will show us that SQL Server is still trying to adjust the memory grant.
Accordingly, the results of the Extended Event show queries spilling all over the place, as we can state by the various threads being displayed. When there isn’t enough memory on SQL Server to execute a sort operation within a query, the sort table will be written to disk and divided into multiple “passes”. Each pass represents an overhead and will increase the required time to complete the operation. The sort_warning_type indicates whether it was executed in single pass or consumed multiple passes. The results show that, in these circumstances, the sort operation was finished within one pass i.e. it was divided into 2 steps.
After 32 executions SQL Server hasn’t yet figured out the ideal memory grant. Perhaps if we try again it’ll succeed. Furthermore, what could possibly go wrong?
EXEC GetHomeVictoriesByTeam 'Benfica'
Well… Actually, things got worse. Turns out that SQL Server will try to adjust the DesiredMemory until the 32nd execution, after that it just gives up on us. Note that the value of IsMemoryGrantFeedbackAdjusted changed to NoFeedbackDisabled. This means that, from now on, SQL Server will use the memory grant of the first execution, which in our case is far from good.
In conclusion, row mode adaptive memory grant is quite awesome, particularly in scenarios where is expectable that the query inputs have no or few variations. Otherwise, things can get unpleasant, as we saw, so it’s wise to be cautious.
