Memory grant related diagnostics

Back in March I blogged about Addressing large memory grant requests from optimized Nested Loops. To further enhance discoverability of memory grant related issues, in SQL Server 2016 we released a new xEvent (query_memory_grant_usage). This fires at the end of query processing for all queries with a memory grant over 5MB, and provides insight into potential memory grant inaccuracies.

Also in SQL Server 2014 SP2, we now included a new memory grant warning in showplan. This will also release on SQL Server 2016 in an upcoming update.

Purpose of this warning is to help identify potentially detrimental performance scenarios related to memory grants. As such, this is a warning that is only available at execution time, so an actual execution plan.

Three conditions can trigger this warning to show up in showplan:

1. Excessive Grant: when max used memory is too small compared to the granted memory. This scenario can cause blocking and less efficient usage when large grants exist and a fraction of that memory was used.
2. Used More Than Granted: when the max used memory exceeds the granted memory. This scenario can cause OOM conditions on the server.
3. Grant Increase: when the dynamic grant starts to increase too much, based on the ratio between the max used memory and initial request memory. This scenario can cause server instability and unpredictable workload performance.

Below is a simple example of an excessive grant warning:

Pedro Lopes (@sqlpto) – Senior Program Manager

• Anonymous
  November 01, 2016
  Hello Mr. Lopes, I am looking at the version 1.3 and 1.5 showplan XSD. But I can't find the "MemoryGrantWarning" mentioned in the "Update to add a memory grant warning to the Showplan XML in SQL Server 2014 SP2". Did it really release with the 2014 SP2? If so, could you please provide example SQL to show it in Management Studio?
  • Anonymous
    November 01, 2016
    Hello Lai,The grant warning did indeed ship in SQL Server 2014 SP2 and will be in SQL Server 2016 in an upcoming update. Assuming you have SQL Server 2014 installed in the default C drive, look in the C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd file.
    • Anonymous
      November 01, 2016
      The comment has been removed