神刀安全网

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:

Memory Pressure in SQL Server

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:

Memory Pressure in SQL Server

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:

Memory Pressure in SQL Server

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:

Memory Pressure in SQL Server

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?

Memory Pressure in SQL Server

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.

Memory Pressure in SQL Server

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!

Memory Pressure in SQL Server

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…

Memory Pressure in SQL Server

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 .

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Memory Pressure in SQL Server

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮