Identify excessive memory grants

When an execution plan has to sort, or to build a hash table in memory, the query optimizer has to work out how much memory to reserve for those operations.

Take this query as an example.

SELECT *
FROM Contact.Contact c
ORDER BY c.LastName, c.FirstName;

SQL Server has to reserve enough memory to hold the whole table — SELECT * asks for everything — and perform the sort.

The estimate of how much memory to reserve depends on the cardinality estimate and on the size of the data. A bad estimate therefore has two possible causes:

• a bad cardinality estimate;
• badly sized variable-length types.

Excessive grants

If you systematically declare your varchar columns with a maximum length that is far too large, varchar(max) being the extreme case, you force the optimizer to ask for too much memory.

The memory that was reserved, and the memory actually used, both appear in the actual execution plan.

You can trace how that memory is used with the sqlserver.query_memory_grant_usage event, using the script on my GitHub.

Pay particular attention to the usage_percent event field.

Spills to tempdb

An under-sized grant causes sorts or hashes to spill to disk, in tempdb.