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

Mapping Extended Events To Trace Events

Jason Brimhall shows how Extended Events and server trace events (like Profiler uses) map together: If you have browsed XEvents to any extent you should probably be familiar with at least one map object that Microsoft has given us. That particular object is sys.dm_xe_map_values and I wrote a bit about it here. That object presents some […]

Read More

Valuable Extended Events Sessions

Derik Hammer has a few useful extended events sessions that he’s sharing with us: Extended Events are the all-around smart choice. They take a little bit of time to get used to, however. With thousands of new events and data points, it can be difficult to create an event session in a pinch. That is […]

Read More

Categories

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