SQL SERVER – SSMS: Memory Consumption Report

The next in line in this series of reports is the Memory Consumption Report from SQL Server Management Studio. This is a goldmine of a report in my humble opinion and lesser respected. When I used to be consulted or land into performance tuning exercises for customers in the past, there is one question that gets repeated and echoed every now and then – “My SQL Server is eating away my RAM and it is not releasing it back even in non-peak hours”. I always am smiling when this question comes up. SQL Server or for that matter any database system is highly memory oriented processes. If they had taken for some reason, then they are not going to release it because they assume at a later point in time they will require it again. So instead of depending on the OS to allocate, they go with the assumption of grabbing and never releasing even when it is not required in the interim.

Memory Consumption

Now that brings to the point the fact what is my SQL Server using this memory for? Well, if you search the internet you will be amazed by the plethora of scripts and it is overwhelming how people have killed this subject to death. But this hidden gem inside SQL Server Management Studio is never talked about. So in this blog post, let me take a tour of what this report contains and how one should read the sections.

This report can be launched by going to Server Node in SQL Server Management Studio (SSMS) right click > Reports > Standard Reports > Memory Consumption.

The report has multiple sections which we would discuss one by one.

Memory Related Counters

These three values can give us a rough indication of memory pressure on SQL Server Instance. These three values are retrieved from SQL Server Memory counters.

SELECT  OBJECT_NAME
,counter_name
,CONVERT(VARCHAR(10),cntr_value) AS cntr_value
FROM sys.dm_os_performance_counters
WHERE ((OBJECT_NAME LIKE '%Manager%')
AND(counter_name = 'Memory Grants Pending'
OR counter_name='Memory Grants Outstanding'
OR counter_name = 'Page life expectancy'))

As per perfmon counters help, “Memory Grants Outstanding” shows counter shows the current number of processes that have successfully acquired a workspace memory grantgrant, whereas “Memory Grants Pending” counter shows the current number of processes waiting for a workspace memory grant. Page life expectancy is defined as “Number of seconds a page will stay in the buffer pool without references”

Top Memory Consuming Components

This section of the report shows various memory consumers (called clerks) in a pie chart based on the amount of memory consumed by each one of them. In most of the situations, SQLBUFFERPOOL would be the biggest consumer of the memory. This output is taken from sys. dm_os_memory_clerks DMV, which is one of the key DMV in monitoring SQL Server memory performance.  We can use sys.dm_os_memory_clerks to identify where exactly SQL’s memory is being consumed.

Buffer Pages Distribution (# Pages)

This particular section of the report shows the state of buffer pages. Behind the scenes it uses DBCC MEMORYSTATUS to get the distribution of buffer in various states. Buffer Distribution can be one of the following as: ‘Stolen’, ‘Free’, ‘Cached’, ‘Dirty’, ‘Kept’, ‘I/O’, ‘Latched’ or ‘Other’. Interestingly, if we run the DBCC MEMORYSTATUS, we may not see all these states. This is because memory status output format has been constantly changing SQL 2000 (KB 271624) and SQL 2005 (KB 907877).

Memory Changes Over Time (Last 7 Days)

This section of the report shows data from default trace. One of the event which is captured by default trace is “Server Memory Change” (Event id 81). Behind the scene, this section reads default trace, looks for event ID 81 and adds a filter (datediff(dd,StartTime,getdate()) < 7) to display last 7 days records. My laptop doesn’t have much load that why we don’t see any memory change. Another reason, as quoted in the text, of no data could be that default trace are disabled.

I am sure in your production or active development boxes these values are not going to be zero for sure.

Memory Usage By Components

At the bottom, there is a table which shows the memory for each component.  This is also taken from the same DMV, which is used in “Top Memory Consuming Components”. The graph earlier shows top 5% consumers by name and the rest would be shown as others. It’s important to note that in SQL 2014, it would always show MEMORYCLERK_XTP which is used by In-Memory OLTP engine (even if it’s not a top consumer).

Here is the little description of various columns:

To understand reserve and committed, I always quote this. Imagine that you need to fly to Mumbai on a certain date and you book a flight ticket. This is called reservation. There’s nothing there yet, but nobody else can claim that seat either. If you release your reservation the place can be given to someone else. Committing is actually grabbing the physical seat on the day of travel.

Hope this gives you a fair idea about various pieces of memory consumers. As I mentioned before, this is one of those hidden gem reports that never gets seen. One can learn and know about a current running system and who are using SQL Server Memory from this report easily.

I would be curious to know if in any of your systems if there is any other component apart from BufferPool or SOSNode as the top memory consumers?

Reference: Pinal Dave (https://blog.sqlauthority.com)