Memory Pressure in SQL Server

Servers with memory pressure are a nuisance – be they badly specified or misconfigured. Either way, they’re not performing properly. But the question is how often and how long are they affected?

How to Identify For Memory Pressure

Query from Jonathan Kehayias article on SQL Skills to view the ring buffers to see what the memory pressure events are:

SELECT 
    EventTime,
    record.value('(/Record/ResourceMonitor/Notification)[1]', 'varchar(max)') as [Type],
    record.value('(/Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [Avail Phys Mem, Kb],
    record.value('(/Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [Avail VAS, Kb]
FROM (
    SELECT
        DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks / ms_ticks ))) - [timestamp])/1000), GETDATE()) AS EventTime,
        CONVERT (xml, record) AS record
    FROM sys.dm_os_ring_buffers
    CROSS JOIN sys.dm_os_sys_info
    WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS tab
ORDER BY EventTime DESC

That gives us output on this particular server:
SQLSkills Memory Pressure Query Results

Yeah, doesn’t look healthy. There’s a lot of results there. Counting the memory pressure events is easy, but the question I have is – how long do these events run? How long are we waiting for SQL Server / Windows resolves these memory pressure events?

In theory, that would be easy to calculate – stick a ROW_NUMBER on the output of the above query, and join with itself on the next event. (As we’re only interested in the event time and the type, we’ll discard the rest of the fields; and we’re only interested in the last 24 hours, hence the additional filter in the “OrderedBuffers” CTE):

WITH    MemBuffers
          AS ( SELECT   EventTime ,
                        record.value('(/Record/ResourceMonitor/Notification)[1]',
                                     'varchar(max)') AS [Type]
               FROM     ( SELECT    DATEADD(ss,
                                            ( -1 * ( ( cpu_ticks
                                                       / CONVERT (FLOAT, ( cpu_ticks
                                                              / ms_ticks )) )
                                                    - [timestamp] ) / 1000 ),
                                            GETDATE()) AS EventTime ,
                                    CONVERT (XML, record) AS record
                          FROM      sys.dm_os_ring_buffers
                                    CROSS JOIN sys.dm_os_sys_info
                          WHERE     ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
                        ) AS tab
             ),
        OrderedBuffers
          AS ( SELECT   EventTime ,
                        Type ,
                        ROW_NUMBER() OVER ( ORDER BY MemBuffers.EventTime DESC ) AS RowNum
               FROM     MemBuffers
               WHERE    EventTime > DATEADD(DAY, -1, GETDATE())
                        AND Type IN ( 'RESOURCE_MEMPHYSICAL_LOW',
                                      'RESOURCE_MEM_STEADY' )
               UNION
               SELECT DISTINCT
                        GETDATE() ,
                        'Header' ,
                        0
               FROM     MemBuffers
             )
    SELECT  SUM(CONVERT(INT, ABS(CONVERT(FLOAT, ob1.EventTime - ob.EventTime)
                                 * 24 * 60 * 60))) AS SecondsPressure
    FROM    OrderedBuffers ob
            LEFT JOIN OrderedBuffers ob1 ON ob.RowNum = ob1.RowNum + 1
    WHERE   ob.Type = 'RESOURCE_MEMPHYSICAL_LOW';

Seems legit. Let’s run it:
MemoryPressure02

Oh, that doesn’t look so good. 31k seconds is, what, 8.5 hours of dealing with these events in a day?

I absent-mindedly re-ran the query:

MemoryPressure03

What now? A different result? A radically different result? I mean, it’s a better result but the fact that the number has changed and by such a huge amount means that there’s something obviously very wrong with my thinking. What could it be?

Time to get down and dirty with the actual contents of the Ring Buffer to see what’s actually there. Just a quick adjustment to the original query to return the whole XML field:

WITH    MemBuffers
          AS ( SELECT   EventTime ,
                        record.value('(/Record/ResourceMonitor/Notification)[1]',
                                     'varchar(max)') AS [Type] ,
                                         record
               FROM     ( SELECT    DATEADD(ss,
                                            ( -1 * ( ( cpu_ticks
                                                       / CONVERT (FLOAT, ( cpu_ticks
                                                              / ms_ticks )) )
                                                     - [timestamp] ) / 1000 ),
                                            GETDATE()) AS EventTime ,
                                    CONVERT (XML, record) AS record
                          FROM      sys.dm_os_ring_buffers
                                    CROSS JOIN sys.dm_os_sys_info
                          WHERE     ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
                        ) AS tab
             )
    SELECT  *
    FROM    MemBuffers
    WHERE   Type = 'RESOURCE_MEMPHYSICAL_LOW'
            AND EventTime > DATEADD(DAY, -1, GETDATE());

And now we can see the XML:

MemoryPressure04

That looks helpful – an ID field in the XML. Let’s try ordering and joining on that:

WITH    MemBuffers
          AS ( SELECT   EventTime ,
                        record.value('(/Record/ResourceMonitor/Notification)[1]',
                                     'varchar(max)') AS [Type] ,
                        record.value('(/Record/@id)[1]', 'int') AS RecordID
               FROM     ( SELECT    DATEADD(ss,
                                            ( -1 * ( ( cpu_ticks
                                                       / CONVERT (FLOAT, ( cpu_ticks
                                                              / ms_ticks )) )
                                                     - [timestamp] ) / 1000 ),
                                            GETDATE()) AS EventTime ,
                                    CONVERT (XML, record) AS record
                          FROM      sys.dm_os_ring_buffers
                                    CROSS JOIN sys.dm_os_sys_info
                          WHERE     ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
                        ) AS tab
             ),
        OrderedBuffers
          AS ( SELECT   EventTime ,
                        Type ,
                        RecordID ,
                        ROW_NUMBER() OVER ( ORDER BY MemBuffers.RecordID DESC, MemBuffers.EventTime DESC ) AS RowNum
               FROM     MemBuffers
               WHERE    EventTime > DATEADD(DAY, -1, GETDATE())
                        AND Type IN ( 'RESOURCE_MEMPHYSICAL_LOW',
                                      'RESOURCE_MEM_STEADY' )
               UNION
               SELECT DISTINCT
                        GETDATE() ,
                        'Header' ,
                        0 ,
                        0
               FROM     MemBuffers
             )
    SELECT  * ,
            CONVERT(INT, ABS(CONVERT(FLOAT, ob1.EventTime - ob.EventTime) * 24
                             * 60 * 60)) AS SecondsPressure
    FROM    OrderedBuffers ob
            LEFT JOIN OrderedBuffers ob1 ON ob.RowNum = ob1.RowNum + 1
    WHERE   ob.Type = 'RESOURCE_MEMPHYSICAL_LOW';

If you’ve been paying attention, you’ll have noticed that I’ve done the rownumbering in reverse order, and added a dummy (RowNum 0) field at the top of the list – this is to make sure that, if the most recent record is a RESOURCE_MEMPHYSICAL_LOW record, that we can get results that include that value.

This all looks OK in theory. But we’re still getting stupidly high values for the SecondsPressure field, and wait – what’s this? Multiple ring buffer records with the same ID?

MemoryPressure05

Something else isn’t right. Time to properly look at the XML.

Re-run the previous query, and you’ll notice that the XML is underlined – click on it in SSMS, and it’ll open up in a new window, neatly formatted and readable by humans. If you compare and contrast a couple of records with the same ID, you’ll notice that they have different MemoryNodeIDs – and we haven’t taken that into account.

MemoryPressure06

So, let’s extract this field as well, and use it as part of the join.

WITH    MemBuffers
              AS ( SELECT   EventTime ,
                            record.value('(/Record/ResourceMonitor/Notification)[1]',
                                         'varchar(max)') AS [Type] ,
                            record.value('(/Record/@id)[1]', 'int') AS RecordID ,
                            record.value('(/Record/MemoryNode/@id)[1]', 'int') AS MemoryNodeID
                   FROM     ( SELECT    DATEADD(ss,
                                                ( -1 * ( ( cpu_ticks
                                                           / CONVERT (FLOAT, ( cpu_ticks
                                                              / ms_ticks )) )
                                                         - [timestamp] )
                                                  / 1000 ), GETDATE()) AS EventTime ,
                                        CONVERT (XML, record) AS record
                              FROM      sys.dm_os_ring_buffers
                                        CROSS JOIN sys.dm_os_sys_info
                              WHERE     ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
                            ) AS tab
                 ),
           OrderedBuffers
              AS ( SELECT   EventTime ,
                            Type ,
                            RecordID ,
                            MemoryNodeID ,
                            ROW_NUMBER() OVER ( ORDER BY MemoryNodeID, MemBuffers.RecordID DESC, MemBuffers.EventTime DESC ) AS RowNum
                   FROM     MemBuffers
                   WHERE    EventTime > DATEADD(DAY, -1, GETDATE())
                            AND Type IN ( 'RESOURCE_MEMPHYSICAL_LOW',
                                          'RESOURCE_MEM_STEADY' )
                   UNION
                   SELECT DISTINCT
                            GETDATE() ,
                            'Header' ,
                            0 ,
                            MemoryNodeID ,
                            0
                   FROM     MemBuffers
                 )
        SELECT  * ,
                CONVERT(INT, ABS(CONVERT(FLOAT, ob1.EventTime - ob.EventTime)
                                 * 24 * 60 * 60)) AS SecondsPressure
        FROM    OrderedBuffers ob
                LEFT JOIN OrderedBuffers ob1 ON ob.RowNum = ob1.RowNum + 1
                                                AND ob.MemoryNodeID = ob1.MemoryNodeID
        WHERE   ob.Type = 'RESOURCE_MEMPHYSICAL_LOW';

At last! The results appear to make some sense!

MemoryPressure07

So now all we have to do is SUM the last column and return that.

WITH    MemBuffers
          AS ( SELECT   EventTime ,
                        record.value('(/Record/ResourceMonitor/Notification)[1]',
                                     'varchar(max)') AS [Type] ,
                        record.value('(/Record/@id)[1]', 'int') AS RecordID ,
                        record.value('(/Record/MemoryNode/@id)[1]', 'int') AS MemoryNodeID
               FROM     ( SELECT    DATEADD(ss,
                                            ( -1 * ( ( cpu_ticks
                                                       / CONVERT (FLOAT, ( cpu_ticks
                                                              / ms_ticks )) )
                                                     - [timestamp] ) / 1000 ),
                                            GETDATE()) AS EventTime ,
                                    CONVERT (XML, record) AS record
                          FROM      sys.dm_os_ring_buffers
                                    CROSS JOIN sys.dm_os_sys_info
                          WHERE     ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
                        ) AS tab
             ),
        OrderedBuffers
          AS ( SELECT   EventTime ,
                        Type ,
                        RecordID ,
                        MemoryNodeID ,
                        ROW_NUMBER() OVER ( ORDER BY MemoryNodeID, MemBuffers.RecordID DESC, MemBuffers.EventTime DESC ) AS RowNum
               FROM     MemBuffers
               WHERE    EventTime > DATEADD(DAY, -1, GETDATE())
                        AND Type IN ( 'RESOURCE_MEMPHYSICAL_LOW',
                                      'RESOURCE_MEM_STEADY' )
               UNION
               SELECT DISTINCT
                        GETDATE() ,
                        'Header' ,
                        0 ,
                        MemoryNodeID ,
                        0
               FROM     MemBuffers
             )
    SELECT  SUM(CONVERT(INT, ABS(CONVERT(FLOAT, ob1.EventTime - ob.EventTime)
                                 * 24 * 60 * 60))) AS SecondsPressure
    FROM    OrderedBuffers ob
            LEFT JOIN OrderedBuffers ob1 ON ob.RowNum = ob1.RowNum + 1
                                            AND ob.MemoryNodeID = ob1.MemoryNodeID
    WHERE   ob.Type = 'RESOURCE_MEMPHYSICAL_LOW';

And finally, we have a number that’s (a) consistent, and (b) not unbelievable. Not good, but not unbelievable. After all, I’ve picked this server to evaluate because I know it’s not healthy…

MemoryPressure08

5.5 minutes of memory pressure on one server in the last 24h. That can’t be good. But at least it’s a number that we can show and explain.

…and if I had Redgate’s SQLMonitor running at this place, I would put that query (with some editing) into my custom metrics and use it to baseling to see if memory issues were resolved when I changed SQL Server’s Memory settings.

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged , , , , . Bookmark the permalink.

2 Responses to Memory Pressure in SQL Server

  1. Pingback: Memory Pressure – Curated SQL

  2. Pingback: Setting SQL Server Maximum Memory | The Lone DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s