The MIN_GRANT_PERCENT and MAX_GRANT_PERCENT hints were first introduced in SQL Server 2012 SP3 and are now in SQL Server 2016 RTM (they are still not available in SQL Server 2014). They address the problem of inappropriate memory grant for query execution.

Memory grant is a memory associated with the execution of queries whose execution plan contains operators that need to store temporary row data while sorting and joining rows (Sort, Hash Join, and others). The value for memory grant depends on SQL Server's Estimated Number of Rows that should be processed by memory operators. If the Estimated Number of Rows significantly differs from the actual number, the memory grant is overestimated or underestimated.

To demonstrate an overestimated memory grant, use the following query:

USE WideWorldImporters; 
DECLARE @now DATETIME = GETDATE();
SELECT * FROM dbo.Orders
WHERE orderdate >= @now
ORDER BY amount DESC; 

The query returns no rows. The execution plan is simple too: Clustered Index Scan followed by the Sort operator. The plan is shown in the following screenshot:

You can see that the Query Optimizer has significantly overestimated the number of rows for the Sort operator. Therefore, it is the most expensive part of the execution plan. Actually, the result of the query is an empty set, but SQL Server thinks that 3 million rows from the table will be returned. Since the Sort operator requires memory, this query needs a memory grant. With the mouse over the Select operator, you can see that the memory granted for this query is 263 MB. More details about memory grant are available in the XML representation of the execution plan, as shown in the following screenshot:

For the execution of a single query that returns no rows, 260 MB of memory is granted! If this query was executed by 100 concurrent sessions, almost 26 GB of memory would be granted for its execution.

As mentioned earlier, query hints should be used as a last resort for performance tuning. For demonstration purposes, you will use the MAX_GRANT_PERCENT query hint to limit the amount of memory granted.

The MAX_GRANT_PERCENT query hint defines the maximum memory grant size as a percentage of available memory. It accepts float values between 0.0 and 100.0. Granted memory cannot exceed this limit, but can be lower if the resource governor setting is lower than this. Since you know that you have no rows in the output, you can use a very low value in the query hint:

DECLARE @now DATETIME = GETDATE();
SELECT * FROM dbo.Orders
WHERE orderdate >= @now
ORDER BY amount DESC
OPTION (MAX_GRANT_PERCENT=0.001); 

When you observe the execution plan, shown in the following screenshot, you can see that the plan remains the same, only the memory grant value has dropped to 2 MB:

With the memory grant hint, you cannot change the execution plan, but you can significantly reduce the amount of memory granted.

You can see that the applied query hint saved 2 GB of memory. The query hint MAX_GRANT_PERCENT should be used when:

• You don't know how to trick the optimizer into coming up with the appropriate memory grant
• You want to fix the problem immediately and buy time to search for a final solution

Here, you have used the query hint just for demonstration purposes; the correct way to fix a query is to understand why the estimated and actual number of rows are so discrepant and to then try to rewrite the query. To help the Query Optimizer to make a better estimation, you need to avoid local variables and write directly in the WHERE clause:

SELECT * FROM dbo.Orders
WHERE orderdate >=  GETDATE()
ORDER BY amount DESC;

This generates the execution plan displayed in the following screenshot:

You can see the expected Nested Loop Join operator and a symbolic Memory Grant of 1 MB.