Memory Grant Internals Part 5

This part is the continuation of the series to the post Memory Grant Internals part 4

In this final part of the series, lets talk about how we balance these memory grants. If lot of your queries are requesting for more memory than they required, then that causes concurrency issues. Memory grants are allocated to every query even before they get executed based on the cardinality estimates and memory consuming iterators. Once the query memory is allocated to a query, that memory is only released once the query execution completes even if the query actually uses a part of the allocated query. If more memory is requested by the queries than they need, then we are wasting the memory. What happens if the queries only receive less memory grants than they need, then we there is a performance impact on the running queries.

These issues occurs due to some problems. If your estimates are inaccurate, if the plans that got generated are non-optimal in scenarios where you have skew in your data and creating a plan compiled based on your initial parameter value. For example, if you execute the stored procedure for the first time using a parameter value giving you 100 rows, a plan gets generated and with a next parameter gives you 2 million rows, we still use the same execution plan which will be non-optimal for these parameters having large number of rows. This is also known as parameter sniffing. Sometimes, we have “out of model” situation meaning anything we put into a query plan that the query optimizer isn’t handle to set up properly. We need to make sure we are out of “out of model scenarios” to get good estimates. Sometimes, rewriting of the queries will be helpful as well.

There are few ways where we can fix these problems.

  1. Updating the statistics. Even you have the default auto update statistics set to ON, maintaining the statistics regularly using the maintenance plan
  2. Recompiling the query. Recompiling will help keep away the parameter sniffing issues.
  3. Try to avoid the multi statement TDF’s. From SQL Server 2017, Adaptive query processing is introduced where the estimates for these TDF’s are lined up with the actual number of rows but that is not the case for the older versions of SQL Server.
  4. You can use the OPTION(OPTIMIZE FOR) to influence the optimizer.
  5. Check for the variable length Data types. For example, we have a column with varchar(4000) data type but all your values are just using a single character. The estimate is going to be for the average row size is going to be half. Varchar and other variable length data type columns estimate only 50% of the defined size.
  6. Use the resource Governor. If you have many high grant and low cost queries waiting in the queue, you can categorize them into a workload group by creating a pool for just for these types of queries. If you need concurrency, limiting the grant percentage and the setting the timeout options for resource pool helps.

Lets see with the examples.

  1. Low memory grant even the stats were updated. Run the below query and see the actual execution plan along with the extended event live data to see if it spills to disk.
select top(1000) * from
select top (600000)
* from DBO.bigTransactionHistory
)as s
order by ActualCost desc

The memory grant was 55MB which is very less than the maximum grant. There is a spill warning in the event information as well. Why did the spill happen when we have enough memory? Statistics were up to date but may be the estimated row size might differ with the actual and that is the reason it had to spill to disk. In this case, the optimizer was not able to get the good plan for this query.

2. Multi statement table valued function. In the second example, lets see how many rows multi statement variables get estimated. First lets see for the sql server 2012 database compatibility.

SELECT tst.CustomerID, COUNT(*) 
FROM  Sales.Customer c 
INNER JOIN dbo.TEST_MTVF('1/1/2014') tst on c.CustomerID = tst.CustomerID
INNER JOIN Production.Product prod on tst.ProductID = prod.ProductID
GROUP BY tst.CustomerID

When you observe the actual execution plan and hover the mouse over to the rows passing from table to nested loop iterator. The estimated number of rows is 1 but the actual number of rows are 37339 which is a lot. For SQL Server 2012 and below, the estimates to the MTLF is always 1 no matter how many actual number of rows were there.

Now, lets place the database in the sql server 2016 model and see the estimates. Run the same query again and check the actual execution plan to see the estimates. When you observe below, the estimates were showing 100 rows. For SQL Server database compatibility of both 130 (SQL Server 2016) and 120 (SQL Server 2014) estimates for MTVF always show 100 rows no matter how many actual rows might be.

Lets see for the latest versions 2019. Run the same query and check the estimates for MTVF. Now the estimates are exactly lined up with the actual number of rows. This is the same for SQL Server 2017 database compatibility mode (140) That’s because in 2017, Adaptive query processing is introduced which will optimize the execution plan in a better way and get the correct estimates for MTVF’s.

3. Variable length Data types. Lets run the below query which have a varchar datatype and see how the estimates work here:

select *, CONVERT(VARCHAR(8000), 'a') as new
into #B
from bigProduct

select * from #B 
) AS n (l)
ORDER BY name, l

While running the above query, if you query select * from sys.dm_exec_query_memory_grants, you will see the granted memory is 183MB but used memory is only 19MB. Rest all the memory which is granted is wasted. Based on the varchar(8000), memory grant has been allocated but when in actual only some memory is being used to process the query. Due to this, all the other queries running concurrently have to wait for this query memory to be released. Once the memory has been granted, memory doesn’t be released whether the query uses that memory or not.

4. Using TOP with (optimize for) option. In this example, lets run the below query and see the event information. we can use a variable and use the optimize for option like below. There is no sort spilling warning.

declare @C int=100000
select top(1000) * from
(select top (@C)
from sales.SalesOrderHeaderEnlarged
) as a
order by
TotalDue desc
option (MAXDOP 1, OPTIMIZE FOR (@C=150000))

We can use Optimize for option in many ways where we can limit the grant by mentioning less number of rows in optimize for option to get lower amount of grant where as if the memory grant is needed more than optimize for bigger value than we mentioned in the variable, like the see in the above example.

5. We can also change the resource semaphore options request_max_memory_grant_percent to the needed percentage. The default is 25%. We can place the limit on how much percentage the workload group can take. In below example, We can set the limit by altering the workload group with 15% like below:

Alter Workload Group [default] with
Alter Resource Governor RECONFIGURE


In this final part of the series, we have learned how to balance these memory grants with examples. This is our final part for the Memory Grant Internals Series. Too much or Too little memory grants is bad. Balancing the memory is important. During these parts of the series, we started the learning path with memory consuming iterators, how these iterators work and share the memory between them, what is a resource governor, parts of the resource governor, how the spills to disk happens with these iterators, how to identify these spills and finally how to avoid them.

Hope you liked the series and learned from it. If you have any ideas about the topics we can cover in coming blog posts, please do share with me. I would love to learn and share the knowledge while I learn along with you.

Thank you so much for all your support!

Thank you for reading!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s