SQLServerPedia

Question continued…I hear people talk about memory pressure and how to deal with it/avoid it, but I need to actually see evidence that it occurred before I start working.

Memory pressure is certainly discussed in performance tuning forums, whitepapers and the like pretty frequently. When tuning SQL Server you’ll deal with virtual (VAS) and physical memory pressure that’s either internal or external. SQL Server 2005 and the new SQLOS implement a complete framework to deal with memory pressure. When diagnosing memory pressure you have to be aware of the Resource Monitor task; Resource Monitor monitors the state of external and internal memory indicators, then broadcasts notifications of noteable conditions to the SQLOS memory clerks. Basically, the proof you’re looking for to see SQL Server dealing with memory pressure lies in the data available from the Resource Monitor task.

When Microsoft introduced the dynamic management views (DMVs) in SQL Server 2005, they provided a huge amount of diagnostic data. One of the undocumented DMV gems is the sys.dm_os_ring_buffers view (which is “intended for internal and debugging purposes” however MSDN and some popular experts’ blogs have enough information on the topic to get you going). A ring buffer is an internal structure that captures various information. In the technical article Troubleshooting Performance Problems in SQL Server 2005 on MSDN, there is some information on the resource manager and ring buffers roughly 1/3 of the way into the document. SQLOS specialists like Slava Oks have also shed light on this view.

Check out the linked articles to gain a deeper understanding, but copy this code snippet onto your SQL Server to get an idea of the information available in this DMV and to see just how frequently the Resource Manager task broadcasts messages to manage your SQL Server’s memory.

SELECT mxml.value('(//Record/@time)[1]','bigint') as NotificationTime
,mxml.value('(//Record/ResourceMonitor/Notification)[1]','nvarchar(36)') as RM_Notification
,mxml.value('(//Record/ResourceMonitor/Indicators)[1]','int') as RM_Indicators
,mxml.value('(//Record/ResourceMonitor/NodeId)[1]','bigint') as RM_NodeID
,mxml.value('(//Record/MemoryNode/@id)[1]','bigint') as MemNode_ID
,mxml.value('(//Record/MemoryNode/ReservedMemory)[1]','bigint')/1024 as MemNode_Reserved (MB)
,mxml.value('(//Record/MemoryNode/CommittedMemory)[1]','bigint')/1024 as MemNode_Committed (MB)
,mxml.value('(//Record/MemoryNode/SharedMemory)[1]','bigint')/1024 as MemNode_Shared (MB)
,mxml.value('(//Record/MemoryNode/AWEMemory)[1]','bigint')/1024 as MemNode_AWE (MB)
,mxml.value('(//Record/MemoryNode/SinglePagesMemory)[1]','bigint')/1024 as MemNode_SinglePages (MB)
,mxml.value('(//Record/MemoryNode/MultiplePagesMemory)[1]','bigint')/1024 as MemNode_MultiPages (MB)
,mxml.value('(//Record/MemoryNode/CachedMemory)[1]','bigint')/1024 as MemNode_Cached (MB)
,mxml.value('(//Record/MemoryRecord/MemoryUtilization)[1]','int')/1024 as Memory_Utilization (MB)
,mxml.value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]','bigint')/1024 as TotalPhysMemory (MB)
,mxml.value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]','bigint')/1024 as AvailPhysMemory (MB)
,mxml.value('(//Record/MemoryRecord/TotalPageFile)[1]','bigint')/1024 as TotalPF (MB)
,mxml.value('(//Record/MemoryRecord/AvailablePageFile)[1]','bigint')/1024 as AvailPF (MB)
,mxml.value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]','bigint')/1024 as TotalVAS (MB)
,mxml.value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]','bigint')/1024 as AvailVAS (MB)
,mxml.value('(//Record/MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]','bigint')/1024 as AvailExtendedVAS (MB)
FROM (SELECT CAST([record] AS XML)
FROM [sys].[dm_os_ring_buffers]
WHERE [ring_buffer_type] = 'RING_BUFFER_RESOURCE_MONITOR') AS R(mxml)
ORDER BY [NotificationTime] DESC

The following are the message types that are available in the sys.dm_os_ring_buffers DMV. See which types interest you and rework the query above to suit your enterprise!

• RING_BUFFER_RESOURCE_MONITOR – memory state changes due to various types of memory pressure
• RING_BUFFER_MEMORY_BROKER – notification to components by memory broker advising them to grow, shrink or stay stable
• RING_BUFFER_SINGLE_PAGE_ALLOCATOR – when the Buffer Pool single page allocator turns on/off internal memory pressure
• RING_BUFFER_OOM – out-of-memory conditions
• RING_BUFFER_BUFFER_POOL – severe buffer pool failures, including buffer pool out-of-memory conditions
• RING_BUFFER_SCHEDULER – scheduler operations
• RING_BUFFER_SCHEDULER_MONITOR – scheduler health
• RING_BUFFER_EXCEPTION – list of exceptions
• RING_BUFFER_CLRAPPDOMAIN – state of AppDomains loaded in the system
• RING_BUFFER_SECURITY_ERROR – (new in SQL Server 2005 SP2) Windows API failure information