April 2019 – Welcome to our SQL World!

In this article, we will look closely at the new query memory grant options that are available min_grant_percent and max_grant_percent in SQL Server 2012.

MAX_GRANT_PERCENT

A percentage value that specifies the maximum amount of memory that can be granted to a query. Range is 0.0 to 100.0. Float value is valid.

If the size of this max memory limit is smaller than the required memory to run a query, the required memory is granted to the query.

MIN_GRANT_PERCENT

A percentage value that specifies the minimum amount of memory that should be granted to a query. Range is 0.0 to 100.0. Float value is valid.

Note: These two new query memory grant options aren’t available for index creation or rebuild.

Demo

Let’s run the following SELECT statement with the Actual Execution Plan enabled:

SELECT * FROM dbo.TAT_TIME
WHERE WIP_STATUS = 'B'
ORDER BY TK_EMPL_UNO

This query returned around 15 million records 15,287,989.

Next, go to the execution plan and right click and select “Show Execution Plan XML …” It will open the execution plan as XML. Let’s focus on the memory grant information in the current execution plan.

<MemoryGrantInfo DesiredMemory="9451520" RequestedMemory="9451520" GrantWaitTime="0" GrantedMemory="9451520"

As you can notice here the desired memory is 9.45 GB and the granted memory is 9.45 GB.

Let’s use the max_grant_percent query hint and execute the same query now.

SELECT * FROM dbo.TAT_TIME
WHERE WIP_STATUS = 'B'
ORDER BY TK_EMPL_UNO OPTION(max_grant_percent = 50)

We have specified to grant only 50 percent of the maximum amount of memory to the query.

Let’s see the memory grant information for this query in the current execution plan.

<MemoryGrantInfo DesiredMemory="9451520" RequestedMemory="5422824" GrantWaitTime="0" GrantedMemory="5422824"

As you can notice here the desired memory is 9.45 GB but the granted memory is 5.42 GB only.

So the query still desires about 9.45 GB of memory, but since we applied max_grant_percent query hint, the query receives 50% of the maximum memory grant.