Diagnosing Memory Grant Issues

Pedro Lopes looks at Extended Events around memory grants:

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.

I like that this information also shows up when you view an execution plan using SSMS 2014 SP2.

Related Posts

Saving Extended Event Session Data To A Table

Matthew McGiffen shows how you can take the results of an Extended Events session and insert them into a table: You just need to select a destination database connection and table name and the export starts. Be warned that it doesn’t default to the current database connection. I’ve fallen for that and overwritten the data […]

Read More

Building An Extended Events Session

Aamir Syed gives us a simple example of using the Extended Events UI to create a new session: Many of us have not made the effort to switch from profiler to Extended events.  It’s 2018, if you haven’t found a few hours to learn about this incredibly powerful tool, I urge you to do so […]

Read More

Categories

July 2016
MTWTFSS
« Jun Aug »
 123
45678910
11121314151617
18192021222324
25262728293031