How to track what triggered memory grant pending

Question

I have a test SQL server, it is lightly used. Recently I got some sql alerts a couple of times daily random time, the alert is like below, I cannot find what caused this, how can I track it and find the root cause?

And when I got the alert, I ran dmv query to check, but it returns 0. So no way to catch it.

 

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.

Answer 1

The root cause is likely to be that you are using a monitoring system which is overly zealous.

OK, true, ideally there should not be any memory grants pending at all, but if there is for a few seconds and you are not seeing any problems, should you bother?

The reason this would happen is that there are two queries scheduled for execution at the same time, and both needs a considerable memory grant for their execution. Memory grant is the memory allocated for sort or hashing operations. The required memory is something the optimizer computes and is based on estimates and thus may be wrong.

Action would be to inspect the queries (or the same query being executed by different processes) or get more memory in the machine.

What does "SELECT @@version" report? How much RAM do you have?

Answer 2

Hi @AnneW

How about using Resource Governor to monitor the workload groups and the memory limits.

See this doc: Resource Governor.

Besides, refer to this doc for more details regarding Troubleshoot slow performance or low memory issues caused by memory grants in SQL Server.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".