This month Wendy Pastrick is the host for T-SQL Tuesday #78. This month we have to “learn something new and blog about it.” There is likely to be a lot of coverage on the new features in SQL Server 2016, so I have decided to cover a new feature that was added to SQL Server 2012 in Service Pack 3, the minimum memory grant query hint.

This is one of a pair of query hints that can manage the memory grants of individual queries. The maximum memory grant hint allows you to limit the amount of memory an individual query can use. If the hint specifies a value lower than the amount of memory required to execute the query, the query will request the required amount. This is like a granular resource governor. For the rest of this post I will concentrate on the minimum memory grant query hint.

When the query optimiser builds an execution plan for a query, it calculates how much memory is required for the query to execute. This calculation is based on the estimated number of rows (used in sort or hash joins) and the average size of these rows. The optimiser calculates the least amount of memory required to perform the operation (required memory) and the amount of memory required to perform the whole operation in memory (desired memory). Each time the query is executed it requests a memory grant from the server. The query will perform much better if it’s granted its desired memory or more. If at run time the query has to perform sorts or hash operations on more than the estimated number of rows, it will spill to tempdb. Spilling to tempdb can be orders of magnitude worse for performance as your data has to be written to disk causing more IO.

Let’s setup a test.

IF (OBJECT_ID('tempdb.dbo.#MemGrantTest') IS NOT NULL)
  DROP TABLE #MemGrantTest
CREATE TABLE #MemGrantTest
(
  ID         INT,
  FirstName  NVARCHAR(MAX),
  LastName   NVARCHAR(MAX)
)
;WITH cte_Names
AS
(
  SELECT  ID,
          FirstName,
          LastName
  FROM(VALUES(1, 'Test', 'Test'),
          (2, 'Test', 'Test'),
          (3, 'Test', 'Test'),
          (4, 'Test', 'Test'),
          (5, 'Test', 'Test'),
          (6, 'Test', 'Test'),
          (7, 'Test', 'Test'),
          (8, 'Test', 'Test'),
          (9, 'Test', 'Test'),
          (10, 'Test', 'Test')) AS v(ID, FirstName, LastName)
)
INSERT      #MemGrantTest
SELECT      n1.ID,
            n1.FirstName,
            n1.LastName
FROM       cte_Names n1
CROSS JOIN cte_Names n2
CROSS JOIN cte_Names n3
CROSS JOIN cte_Names n4;

I’ll now empty the statistics on our temp table with the following statement:

UPDATE STATISTICS #MemGrantTest WITH ROWCOUNT = 0;

This will simulate out of date statistics. SQL Server will think there are 0 rows in our test table. This will produce row estimates of 1 (1 being the minimum).

SELECT    FirstName,
          LastName,
          COUNT(*)
FROM      #MemGrantTest
GROUP BY  FirstName,
          LastName

The query above gives the execution plan below on my SQL Server 2012 SP3 server with Max Memory set to 4GB.

The Sort operator has spilled to tempdb (indicated by the warning symbol). It did this because the out of date statistics told the optimiser that there were no rows in the table. Based on this, an execution plan was generated with a small memory grant. At run-time the query soon ran out of memory when more than 1 row appeared from the table. As more and more rows built up the query ran out of granted memory and had no choice but to start writing data to tempdb.

Adding the following query hint to the query forces the optimiser to grant as much memory as possible.

OPTION(min_grant_percent = 100)

This means we now have enough memory at run-time to perform the whole sort in memory.

Using the new compare execution plan functionality in SSMS 2016 we can see the difference this query hint made.

You’ll notice that desired memory for the first query was 544 but it was granted almost double that at 1024. This is because of the instance level “Minimum memory per query” setting.

In this case, the solution would be to update the statistics. This way the optimiser would have a much better idea of how many rows it would need to store in memory for the sort operator. This is only an example but sort operators can spill to tempdb in the real world world when the statistics are stale, the cardinality estimate is poor because of the way the query is written or the query is selecting from a table variable (table variables do not have statistics objects).

So if updating statistics doesn’t fix it then you’re probably looking at altering the query. If altering code in production requires source control, a change request and various levels of approval (as it should) then you’re not going to be able to solve any tempdb spills quickly. One option is to put the request to change the code in and in the mean time add the hint. It’s likely that the addition of a query hint to the code will be seen as a code change too and therefore require the processes above. In this case, a plan guide used to inject the hint into the query may just get through as it’s not a code change. This would be used as a stop gap in an emergency while the true fix makes it’s way through approval.

If you are using these hints in production, let me know why and what it was they fixed.

The post Minimum Memory Grant Query Hint appeared first on The Database Avenger.