Sent: Monday, March 09, 2009 6:20 PM
Subject: Ring Buffers in SQL2008

Hi, do you have any documentation/white papers/examples about extended dmv’s or ring buffers in SQL2008?

select distinct ring_buffer_type from sys.dm_os_ring_buffers

==================================

My Reply

==================================

Nothing that I would consider advanced.

• Each can have multiple message types.

• The message types are XML formatted so you can see the various data points.

• The ring buffers are included in the mini-dumps to help in troubleshooting to get a better idea of current and lead-up state.

RING_BUFFER_RESOURCE_MONITOR - Resource Monitor activity like was physical memory pressure signaled or not.
RING_BUFFER_SCHEDULER_MONITOR  - What is the state of the logical schedulers, the health record type is very helpful.
RING_BUFFER_MEMORY_BROKER  - Actions the internal memory broker is taking to balance the memory between caches.
RING_BUFFER_SECURITY_ERROR  - Errors that occur during security operations.  Ex: login failed may have more details about the OS error code and such
RING_BUFFER_SCHEDULER - Actual scheduler activity such as context switching.  You can reconstruct the execution order from these entries.
RING_BUFFER_EXCEPTION - Any exceptions encountered in the server.  SQL uses throw internally for errors so you can see SQL errors as well.
RING_BUFFER_CONNECTIVITY - Core connectivity information - useful in tracking down connection failure information

Reference:

http://blogs.msdn.com/psssql/archive/2008/03/24/how-it-works-sql-server-2005-sp2-security-ring-buffer-ring-buffer-security-error.aspx

http://blogs.msdn.com/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx

Bob Dorr - Principal SQL Server Escalation Engineer