Home Microsoft SQL Server DigiMailing Contact
    Keyword



sys.dm_os_ring_buffers

 

This is a view in the mssqlsystemresource database. Significant amount of diagnostic memory information can be obtained from the sys.dm_os_ring_buffers ring buffers DMV. Each ring buffer keeps a record of the last number of notifications of a certain kind. This DMV tell's you how many memory is consummed by a connection. Detailed information on specific ring buffers is provided next.

RING_BUFFER_RESOURCE_MONITOR

You can use information from resource monitor notifications to identify memory state changes. Internally, SQL Server has a framework that monitors different memory pressures. When the memory state changes, the resource monitor task generates a notification. This notification is used internally by the components to adjust their memory usage according to the memory state and it is exposed to the user through sys.dm_os_ring_buffers DMV as in the following code.

select record from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

A record may look like this:

Note: Some parts of the code snippet presented in the following table have been displayed in multiple lines only for better readability. These should be entered in a single line.

<Record id="1701" type="RING_BUFFER_RESOURCE_MONITOR" time="149740267">     <ResourceMonitor>         <Notification>RESOURCE_MEMPHYSICAL_LOW<         /Notification>         <Indicators>2</Indicators>         <NodeId>0</NodeId>     </ResourceMonitor>     <MemoryNode id="0">         <ReservedMemory>1646380</ReservedMemory>         <CommittedMemory>432388</CommittedMemory>         <SharedMemory>0</SharedMemory>         <AWEMemory>0</AWEMemory>         <SinglePagesMemory>26592</SinglePagesMemory>         <MultiplePagesMemory>17128</MultiplePagesMemory>         <CachedMemory>17624</CachedMemory>     </MemoryNode>     <MemoryRecord>         <MemoryUtilization>50</MemoryUtilization>         <TotalPhysicalMemory>3833132</TotalPhysicalMemory>         <AvailablePhysicalMemory>3240228<         /AvailablePhysicalMemory>         <TotalPageFile>5732340</TotalPageFile>         <AvailablePageFile>5057100</AvailablePageFile>         <TotalVirtualAddressSpace>2097024<         /TotalVirtualAddressSpace>         <AvailableVirtualAddressSpace>336760 </AvailableVirtualAddressSpace>         <AvailableExtendedVirtualAddressSpace>0            </AvailableExtendedVirtualAddressSpace>     </MemoryRecord> </Record>

From this record, you can deduce that the server received a low physical memory notification. You can also see the amounts of memory in kilobytes. You can query this information by using the XML capabilities of SQL Server, for example in the following code.

Note: Some parts of the code snippet presented in the following table have been displayed in multiple lines only for better readability. These should be entered in a single line.

select     x.value('(//Notification)[1]', 'varchar(max)') as [Type],     x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],     x.value('(//AvailablePhysicalMemory)[1]', 'int')     as [Avail Phys Mem, Kb],     x.value('(//AvailableVirtualAddressSpace)[1]', 'int')     as [Avail VAS, Kb] from     (select cast(record as xml)      from sys.dm_os_ring_buffers      where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')      as R(x) order by     [Time Stamp] desc

Upon receiving a memory low notification, the buffer pool recalculates its target. Note that the target count stays within the limits specified by the min server memory and max server memory options. If the new committed target for the buffer pool is lower than the currently committed buffers, the buffer pool starts shrinking until external physical memory pressure is removed. Note that SQL Server 2000 did not react to physical memory pressure when running with AWE enabled.

RING_BUFFER_OOM

This ring buffer will contain records indicating server out-of-memory conditions as in the following code example.

select record from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_OOM'

A record may look like this:

<Record id="7301" type="RING_BUFFER_OOM" time="345640123">     <OOM>         <Action>FAIL_VIRTUAL_COMMIT</Action>         <Resources>4096</Resources>     </OOM>

This record tells which operation has failed (commit, reserve, or page allocation) and the amount of memory requested.

RING_BUFFER_MEMORY_BROKER and Internal Memory Pressure

As internal memory pressure is detected, low memory notification is turned on for components that use the buffer pool as the source of memory allocations. Turning on low memory notification allows reclaiming the pages from caches and other components using them.

Internal memory pressure can also be triggered by adjusting the max server memory option or when the percentage of the stolen pages from the buffer pool exceeds 80%.

Internal memory pressure notifications (‘Shrink’) can be observed by querying memory broker ring buffer as in the following code example.

select     x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],     x.value('(//Notification)[1]', 'varchar(100)')     as [Last Notification] from     (select cast(record as xml)      from sys.dm_os_ring_buffers      where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER')      as R(x) order by     [Time Stamp] desc

RING_BUFFER_BUFFER_POOL

This ring buffer will contain records indicating severe buffer pool failures, including buffer pool out of memory conditions.

select record from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'

A record may look like this:

<Record id="1234" type="RING_BUFFER_BUFFER_POOL" time="345640123">     < BufferPoolFailure id="FAIL_OOM">         <CommittedCount>84344 </CommittedCount>         <CommittedTarget>84350 </CommittedTarget >         <FreeCount>20</FreeCount>         <HashedCount>20345</HashedCount>         <StolenCount>64001 </StolenCount>     <ReservedCount>64001 </ReservedCount>     </ BufferPoolFailure >

This record will tell what failure (FAIL_OOM, FAIL_MAP, FAIL_RESERVE_ADJUST, FAIL_LAZYWRITER_NO_BUFFERS) and the buffer pool status at the time. 

 

 




Syntax
CREATE VIEW sys.dm_os_ring_buffers AS
	SELECT *
	FROM OpenRowset(TABLE SYSRINGBUFFERS)

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash