Memory grants pending root cause

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  September 26, 2024 at 9:18 pm

  #4462850

  Recently I got sql alerts from one of QA servers, every 2 or 3 hours.

  DESCRIPTION:   The SQL Server performance counter 'Memory Grants Pending' (instance 'N/A') of object 'Memory Manager' is now above the threshold of 0.00 (the current value is 1.00).COMMENT:        Ideally there should be no processes waiting for memory and this should = 0. More memory is needed if this value is >=1.

   This is a QA server not many people using it. The application runs fine. and this alert pops up every 2 to 3 hours, I cannot find anything abnormal in sql logs, windows logs. No specific jobs running at that time either.

  How can I find the bottom cause of this alert?

  Thanks

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  September 27, 2024 at 9:28 am

  #4462988

  check "sql-server-diagnostic-information-queries-detailed-day-17" Query #40!

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  September 27, 2024 at 6:05 pm

  #4463173

  Thanks, I know this query,

  But when I run it , it returns 0.

  How can I keep track of it , and what query to use to find out what caused this value >1

  Thanks

  • This reply was modified 1 month, 3 weeks ago by  sqlfriend.
  • This reply was modified 1 month, 3 weeks ago by  sqlfriend.