RESOURCE SEMAPHORE, QUERY MEMORY and PERFORMANCE

Memory has always been my favorite topic when comes to troubleshooting performance in SQL Server :)

I have been contributing and posting few articles on Buffer and Data pages in cache and how it works and all that! But this time I would like to share what exactly query memory is
and how and where to look at, when query performance is suffering!

Let me try to explain in easy language, what happens when query enters the door of SQL Server RDBMS:

1. SQL Server receives query

2. SQL Server creates compiled plan before it creates execution plan

3. SQL server when create compiled plan evaluate the need of two memory parameters

·       Required memory( Min memory need to run sort and hash operations)

·       Additional memory(need to store rows in memory)

4. Server calculates how much memory query need. Formula is
(Required memory * degree of parallelism) + Additional memory

5. If above needed memory exceeds the query memory limit then server reduce the memory to fit the need

6. Now this memory which is able to fit a.k.a requested memory need is fulfilled by RESOURCE SEMAPHORE

7. If RESOURCE_SEMAPHORE can not satisfy this memory need then query is put under RESOURCE_SEMAPHORE wait type

8. And cycle goes on..

If you see high RESOURCE_SEMAPHORE wait types, it is an indicator that query need more requested memory.

This opens up the case to optimize the query!

Memory, Memory and Memory

It’s not been long when I was troubleshooting sql server memory related performance issue and it came back again for another environment

I thought why not write it down something about memory, which can help you and me remember clearly about memory related troubleshooting :) :)

 To easily understand this, I am trying to put notes in bullet points.

First two important questions you should ask yourself are:

·        Is this external memory pressure? In simple terms is it Physical RAM A.KA. Windows OS memory pressure?

·        Is this internal memory pressure a.k.a pressure on VAS allocated to SQL Server by windows OS?

Two answer this, it is important to determine where the pressure is coming from?

Next question comes:

·        How quickly I can determine that? and using what tool?

 Answer is using sys.dm_os_ring_buffers:)

 ·        What exactly this DMO is?

This DMO tells us about if any alerts are fired or recorded within the system

If you select result set from this DMO, you will get lot of ring buffers. The one and only which can point to memory pressure is called "RING_BUFFER_RESOURCE_MONITOR"

If you look at the xml record for RING_BUFFER_RESOURCE_MONITOR, keep an eye on node called "<Notification>"

This node will give you insight on the reason for low memory pressure.

 So before we start looking at performance counters like buffer cache hit ratio, page life expectancy, target server memory etc etc, why not query sys.dm_os_ring_buffers and see what it has to tell about any recording of memory!