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

Finding Who Changed Auto-Tuning Settings On Azure SQL DB

Arun Sirpal shows us the extended event to watch to learn who changed that auto-tuning setting: It is said to be safe, reliable and proven using complex algorithms and built-in intelligence where it can do the following (see this link for more details: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning) CREATE INDEX – identifies indexes that may improve performance of your workload, creates […]

Read More

Learning Why A Plan Was Removed From Cache

Grant Fritchey shows us that there is some limited information to tell us why an execution plan was removed from cache: You’ll note that the second statement in the sequence is “CREATE OR AL…” in the batch_text. That’s me modifying the procedure. The very next event is sp_cache_remove. It shows the remove_method as “Compplan Remove”. […]

Read More

Categories

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