Monitoring and Troubleshooting with sys.dm_os_ring_buffers

This post describes the care and feeding of the bastard circular queue in SQL Server exposed by the sys.dm_os_ring_buffer dynamic management view (DMV). The information in this ring buffer can give visibility to some important and always up-to-the-minute SQL Server health information:

  • security exceptions not returned to the client process for security reasons
  • exceptions raised at the sql_os level (way down there – think spid 1-50)
  • connections dropped by the server
  • help in identifying and understanding memory pressure
  • system resource utilization
  • CLR Integration scheduler State
  • Extended Events subsystem state

Ostensibly, the events selected for inclusion are what the Microsoft program groups and support engineers decided we don’t know we need to know. We have no control over which events find their way to this queue. Eventually, they did provide the ability to turn the entire feature on or off two service packs after introduction. Occasionally a new event type is added. To my knowledge there are 12 Record Types included for SQL Server 2012. VIEW_SYSTEM_STATE permission is required to see this data.

Known Record Types (with link to the query below for that type):

Ring Buffer Record Type added
RING_BUFFER_CLRAPPDOMAIN
RING_BUFFER_CONNECTIVITY 2008 RTM
RING_BUFFER_EXCEPTION
RING_BUFFER_HOBT_SCHEMAMGR 2012 RTM
RING_BUFFER_MEMORY_BROKER
RING_BUFFER_MEMORY_BROKER_CLERKS
RING_BUFFER_RESOURCE_MONITOR
RING_BUFFER_SCHEDULER
RING_BUFFER_SCHEDULER_MONITOR
RING_BUFFER_SECURITY_ERROR 2005 SP2
RING_BUFFER_XE_BUFFER_STATE
RING_BUFFER_XE_LOG

Other than a support disclaimer on the “SQL Server Operating System Related Dynamic Management Views (Transact-SQL)” jump page, sys.dm_os_ring_buffers is not documented in the Books Online. Several internal Microsoft bloggers have provided timely information around this DMV since it was introduced in SQL Server 2005. While by no means exhaustive, the following are good examples of such resources.

Lots of good resources.

Unfortunately I did not learn much about the RING_BUFFER_HOBT_SCHEMAMGR type in my recent search. It must be new in SQL Server 2012? All I see are operations where the action is ADD with a distribution of events across all databases on the instance. Seems to be more entries in databases where I have been active. I am not sure what to make of RING_BUFFER_HOBT_SCHEMAMGR. The XML is not terribly revealing:

<Record id=11type=RING_BUFFER_HOBT_SCHEMAMGRtime=1961318>
  <operationaction=ADDdbid=3version=144202 />
  <hobtid=0001:0000003Ctype=VISIBLE />
</Record>
 
 

The thing about all ring buffer record types is that each one has at least one underlying XML schema. There are not that many more schema’s than types – most types seem to have only one associated XML schema. Just beware of the oddballs.

When interrogating the ring buffer, T-SQL using SSMS is my preferred interface. The SSMS query tool’s one click access to the XML document from the query results grid output is helpful. It would be even better if Reporting Services was as friendly with XML typed data as the query tool’s results panel.

~~~~~~~~~~~~

The remainder of the post documents some sys.dm_os_ring_buffer queries that may be helpful to quickly determine what is in your ring buffer.

To see a summary of current records for the 12 types found in the queue by record type for SQL Server 2012 RTM:

SELECT known.[ring_buffer_type]
          , ISNULL( COUNT(buffer.[ring_buffer_type] ), 0 ) AS [type_count]
FROM ( SELECT ‘RING_BUFFER_BUFFER_POOL’ AS [ring_buffer_type] — do not see in 2012?
      UNION ALL
        SELECT ‘RING_BUFFER_CLRAPPDOMAIN’
      UNION ALL
       SELECT ‘RING_BUFFER_CONNECTIVITY’   — SQL 2008
      UNION ALL
       SELECT ‘RING_BUFFER_EXCEPTION’
      UNION ALL
       SELECT ‘RING_BUFFER_HOBT_SCHEMAMGR’  — SQL 2012
      UNION ALL
       SELECT ‘RING_BUFFER_MEMORY_BROKER’
      UNION ALL
       SELECT ‘RING_BUFFER_MEMORY_BROKER_CLERKS’
      UNION ALL
       SELECT ‘RING_BUFFER_OOM’                              — do not see in 2012?
      UNION ALL
       SELECT ‘RING_BUFFER_RESOURCE_MONITOR’
      UNION ALL
       SELECT ‘RING_BUFFER_SCHEDULER’
      UNION ALL
       SELECT ‘RING_BUFFER_SCHEDULER_MONITOR’
      UNION ALL
       SELECT ‘RING_BUFFER_SECURITY_ERROR’   — SQL 2005 SP2
      UNION ALL
       SELECT ‘RING_BUFFER_SINGLE_PAGE_ALLOCATOR’            — do not see in 2012?
      UNION ALL
       SELECT ‘RING_BUFFER_XE_BUFFER_STATE’
      UNION ALL
       SELECT ‘RING_BUFFER_XE_LOG’ ) AS known
LEFT JOIN sys.dm_os_ring_buffers buffer
ON buffer.[ring_buffer_type] = known.[ring_buffer_type]
CROSS JOIN sys.dm_os_sys_info info
GROUP BY known.[ring_buffer_type]
 
 
Shredding the XML to render XML elements as columnar data attributes makes it a little easier to understand what is swinging through the ring at the present moment. Note that the SELECT statement in the common table expression can be executed to retrieve the detail rows used to build the aggregate.
  
WITH BufferTypeCte
AS (
           — select & run this query for a list of records in the queue
    SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( ‘@time’, ‘BIGINT’ )
                                     , Buffer.Record.value( ‘@id’, ‘INT’ )
                                     , Data.[Address]
                                     , Data.[Type] ) AS [RowNumber]
          , Data.[Address]
          , Data.[Type]
                 , CASE
              WHEN Data.[Type] = ‘RING_BUFFER_CLRAPPDOMAIN’
                THEN Buffer.Record.value(‘Action[1]’, ‘NVARCHAR(128)’)
              WHEN Data.[Type] = ‘RING_BUFFER_CONNECTIVITY’
                THEN Buffer.Record.value( ‘(ConnectivityTraceRecord/RecordType)[1]’
                                        , ‘NVARCHAR(128)’)
              WHEN Data.[Type] = ‘RING_BUFFER_EXCEPTION’
                THEN Buffer.Record.value( ‘(Exception/Error)[1]’, ‘NVARCHAR(128)’)
              WHEN Data.[Type] = ‘RING_BUFFER_HOBT_SCHEMAMGR’
                THEN DB_NAME( Buffer.Record.value( ‘operation[1]/@dbid’, ‘INT’) )
              WHEN Data.[Type] = ‘RING_BUFFER_MEMORY_BROKER’
                THEN Buffer.Record.value( ‘(MemoryBroker/Broker)[1]’
                                        , ‘NVARCHAR(128)’ )
              WHEN Data.[Type] = ‘RING_BUFFER_MEMORY_BROKER_CLERKS’
                THEN Buffer.Record.value( ‘(MemoryBrokerClerk/Name)[1]’
                                        , ‘NVARCHAR(128)’ )
              WHEN Data.[Type] = ‘RING_BUFFER_RESOURCE_MONITOR’
                THEN Buffer.Record.value( ‘(ResourceMonitor/Notification)[1]’
                                        , ‘NVARCHAR(128)’ )
              WHEN Data.[Type] = ‘RING_BUFFER_SCHEDULER’
                THEN Buffer.Record.value( ‘(Scheduler/Action)[1]’
                                        , ‘NVARCHAR(128)’)
              WHEN Data.[Type] = ‘RING_BUFFER_SCHEDULER_MONITOR’
                THEN ‘heartbeat’
              WHEN Data.[Type] = ‘RING_BUFFER_SECURITY_ERROR’
                THEN FORMATMESSAGE(‘ErrorCode: [%s], SQLErrorCode: [%s]’
                                    , Buffer.Record.value( ‘(Error/ErrorCode)[1]’
                                                         , ‘NVARCHAR(128)’ )
                                    , Buffer.Record.value( ‘(Error/SQLErrorCode)[1]’
                                                         , ‘NVARCHAR(128)’ ) )
              WHEN Data.[Type] = ‘RING_BUFFER_XE_BUFFER_STATE’
                THEN Buffer.Record.value( ‘(XE_BufferStateRecord/NewState)[1]’
                                        , ‘NVARCHAR(128)’ )
              WHEN Data.[Type] = ‘RING_BUFFER_XE_LOG’
                THEN Buffer.Record.value( ‘(XE_LogRecord/@message)[1]’
                                        , ‘NVARCHAR(128)’ )
              ELSE Data.[Type]  — the next new one
              END AS [Element]
                         , DATEADD( SECOND
                    ,( Buffer.Record.value( ‘@time’
                                           , ‘BIGINT’ ) Data.ms_ticks ) / 1000 
                    , GETDATE() ) AS [Time]    
                         , Buffer.Record.value(‘@id’, ‘INT’) AS [Id]
          , Data.EventXML
    FROM (SELECT CAST(b.Record AS XML) AS EventXML
                , b.ring_buffer_address AS [Address]
                , b.ring_buffer_type AS [Type]
                , i.ms_ticks
          FROM sys.dm_os_ring_buffers b
          CROSS JOIN sys.dm_os_sys_info i ) AS Data
    CROSS APPLY EventXML.nodes(‘//Record’) AS Buffer(Record)
    –ORDER BY RowNumber
   )  
SELECT first.[Type]
               , summary.[Element]
               , summary.[count]
               , first.[Time] AS [FirstTime]
               , last.[Time] AS [LastTime]
     , first.EventXML AS [FirstRecord]
     , last.EventXML AS [LastRecord]
FROM (SELECT [Type]
           , [Element]
           , COUNT( RowNumber ) AS [count]
           , MIN( RowNumber ) AS [FirstRow]
           , MAX( RowNumber ) AS [LastRow]
      FROM BufferTypeCTE
      GROUP BY [Type], [Element] ) AS summary
JOIN BufferTypeCTE AS first
ON first.RowNumber = summary.[FirstRow]
JOIN BufferTypeCTE AS last
ON last.RowNumber = summary.[LastRow]
–ORDER BY [FirstTime];
–ORDER BY [LastTime];
ORDER BY [Type], [Element];

~~~~~~~~~~~~~~

And finally, some queries for deeper shredding of specific record types & XML schemas, I’ll spare us the output screen shots. Just try ‘em out if you see that type in your queue using the above.If nothing else you get the XML to help you write the query you want to see.

If I know of any resources for a record type – other than the ones already listed – I will include them near the query for that record type.

RING_BUFFER_CLRAPPDOMAIN

(“It’s 3 AM. Do you know where your appdomain is?”)

WITH CLRAppDomainCte
AS (
           — select & run this query for a list of records in the queue
    SELECT ROW_NUMBER() OVER
( ORDER BY Buffer.Record.value( ‘@time’, ‘BIGINT’ )
                       , Buffer.Record.value( ‘@id’
                                            , ‘INT’ ) ) AS [RowNumber]
         , Data.ring_buffer_type AS [Type]
         , Buffer.Record.value( ‘Action[1]’, ‘NVARCHAR(128)’) AS [Action]
         , Buffer.Record.value( ‘AppDomain[1]/@dbid’
                              , ‘INT’) AS [AppDomainDbId]
         , Buffer.Record.value( ‘AppDomain[1]/@ownerid ‘
                              , ‘INT’) AS [AppDomainOwnerId]
         , Buffer.Record.value( ‘AppDomain[1]/@type’
                              , ‘NVARCHAR(128)’) AS [AppDomainType]
         , Buffer.Record.value( ‘(AppDomain/State)[1]’
                              , ‘NVARCHAR(128)’) AS [AppDomainState]
         , Buffer.Record.value( ‘@time’, ‘BIGINT’ ) AS [time]
         , Buffer.Record.value(‘@id’, ‘INT’) AS [Id]
         , Data.EventXML
    FROM (SELECT CAST(b.Record AS XML) AS EventXML
               , b.ring_buffer_type
          FROM sys.dm_os_ring_buffers AS b
          WHERE ring_buffer_type = ‘RING_BUFFER_CLRAPPDOMAIN’) AS Data
    CROSS APPLY Data.EventXML.nodes(‘//Record’) AS Buffer(Record)
   )  
SELECT first.[Type]
     , summary.[Action]
     , summary.[count]
     , DATEADD( SECOND
              ,( first.[time] info.ms_ticks ) / 1000
              , CURRENT_TIMESTAMP ) AS [FirstTime]
     , DATEADD( SECOND
               ,( last.[time] info.ms_ticks ) / 1000
               , CURRENT_TIMESTAMP ) AS [LastTime]
     , first.EventXML AS [FirstRecord]
     , last.EventXML AS [LastRecord]
FROM (SELECT [Action]
           , COUNT( RowNumber ) AS [count]
           , MIN( RowNumber ) AS [FirstRow]
           , MAX( RowNumber ) AS [LastRow]
      FROM CLRAppDomainCte
      GROUP BY [Action] ) AS summary
JOIN CLRAppDomainCte AS first
ON first.RowNumber = summary.[FirstRow]
JOIN CLRAppDomainCte AS last
ON last.RowNumber = summary.[LastRow]
CROSS JOIN sys.dm_os_sys_info AS info
ORDER BY [Action]; 

RING_BUFFER_CONNECTIVITY

Protocols team post mentioned above talks about identifying domain authentication problems with these records.

First available SQL Server 2008 RTM

WITH ConnectivityCte
AS (
    SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( ‘@time’, ‘BIGINT’ )
                                     , Buffer.Record.value( ‘@id’, ‘INT’ ) ) AS [RowNumber]
         , Data.ring_buffer_type AS [Type]
         , Buffer.Record.value( ‘(ConnectivityTraceRecord/RecordSource)[1]’
                              , ‘NVARCHAR(128)’) as [RecordSource]
         , Buffer.Record.value( ‘(ConnectivityTraceRecord/RecordType)[1]’
                              , ‘NVARCHAR(128)’) as [RecordType]
         , Buffer.Record.value( ‘@time’, ‘BIGINT’ ) AS [time]
         , Buffer.Record.value( ‘@id’, ‘INT’) AS [Id]
         , Data.EventXML
    FROM ( SELECT CAST(Record AS XML) AS EventXML
               , ring_buffer_type 
           FROM sys.dm_os_ring_buffers
           WHERE ring_buffer_type = ‘RING_BUFFER_CONNECTIVITY’ ) AS Data
    CROSS APPLY EventXML.nodes(‘//Record’) AS Buffer(Record)
   )
SELECT first.[Type]
     , summary.[RecordSource]
     , summary.[RecordType]
     , summary.[count]
     , DATEADD( second
               , first.[Time] info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [FirstTime]
     , DATEADD( second
               , last.[Time]  info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [LastTime]
     , first.EventXML AS [FirstRecord]
     , last.EventXML AS [LastRecord]
FROM (SELECT [RecordSource]
           , [RecordType]
           , COUNT(*) AS [count]
           , MIN(RowNumber) AS [FirstRow]
           , MAX(RowNumber) AS [LastRow]
                     FROM ConnectivityCte
      GROUP BY [RecordSource], [RecordType] ) AS summary
JOIN ConnectivityCte AS first
ON first.RowNumber = summary.[FirstRow]
JOIN ConnectivityCte AS last
ON last.RowNumber = summary.[LastRow]
CROSS JOIN sys.dm_os_sys_info AS info
ORDER BY summary.[FirstRow]; 

RING_BUFFER_EXCEPTION

There should be no errors of unknown origin and a plan to eliminate the rest. It pays to dig deep to understand each and every exception shown.

WITH ExceptionCte
AS (
           — select & run this query for a list of records in the queue
    SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( ‘@time’, ‘BIGINT’ )
                                     , Buffer.Record.value( ‘@id’, ‘INT’ ) ) AS [RowNumber]
         , Data.ring_buffer_type AS [Type]
         , Buffer.Record.value(‘(Exception/Error)[1]’, ‘int’) AS [Error]
         , Buffer.Record.value( ‘@time’, ‘BIGINT’ ) AS [time]
         , Buffer.Record.value(‘@id’, ‘int’) AS [Id]
         , Data.EventXML
    FROM (SELECT CAST(Record AS XML) AS EventXML
               , ring_buffer_type 
          FROM sys.dm_os_ring_buffers
          WHERE ring_buffer_type = ‘RING_BUFFER_EXCEPTION’) AS Data
    CROSS APPLY EventXML.nodes(‘//Record’) AS Buffer(Record)
   )
SELECT first.[Type]
     , summary.[Error]
     , CASE WHEN msg.message_id IS NOT NULL 
            THEN msg.text
            ELSE ‘no sys.messages row for Error’
            END AS [External Message Info]
     , summary.[count]
     , DATEADD( second
               , first.[Time] info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [FirstTime]
     , DATEADD( second
               , last.[Time]  info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [LastTime]
     , first.EventXML AS [FirstRecord]
     , last.EventXML AS [LastRecord]
FROM ( SELECT [Error]
            , COUNT(*) AS [count]
            , MIN(RowNumber) AS [FirstRow]
            , MAX(RowNumber) AS [LastRow]
       FROM ExceptionCte
       GROUP BY [Error] ) AS summary
JOIN ExceptionCTE AS first
ON first.RowNumber = summary.[FirstRow]
JOIN ExceptionCTE AS last
ON last.RowNumber = summary.[LastRow]
LEFT JOIN sys.messages msg
ON summary.Error = msg.message_id
AND msg.language_id = 1033
CROSS JOIN sys.dm_os_sys_info AS info
ORDER BY [Error];

RING_BUFFER_HOBT_SCHEMAMGR

WITH HOBTSchemaMgrCte
AS (
           — select & run this query for a list of records in the queue
    SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( ‘@time’, ‘BIGINT’ )
                                     , Buffer.Record.value( ‘@id’, ‘INT’ ) ) AS [RowNumber]
         , Data.ring_buffer_type AS [Type]
         , Buffer.Record.value( ‘operation[1]/@action’ , ‘NVARCHAR(128)’) AS [Action]
         , Buffer.Record.value( ‘operation[1]/@dbid’ , ‘INT’) AS [DbId]
         , Buffer.Record.value( ‘operation[1]/@version’, ‘INT’ ) AS [Version]
         , Buffer.Record.value( ‘@time’, ‘BIGINT’ ) AS [time]
         , Buffer.Record.value( ‘@id’, ‘BIGINT’ ) AS [Id]
         , Data.EventXML
    FROM (SELECT CAST(Record AS XML) AS EventXML
               , ring_buffer_type
          FROM sys.dm_os_ring_buffers
          WHERE ring_buffer_type = ‘RING_BUFFER_HOBT_SCHEMAMGR’) AS Data
    CROSS APPLY EventXML.nodes(‘//Record’) AS Buffer(Record)
   )
SELECT first.[Type]
     , summary.[DbId]
     , DB_NAME(summary.[DbId]) AS DbName
              , summary.[count]
     , DATEADD( second
               , first.[Time] info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [FirstTime]
     , DATEADD( second
               , last.[Time]  info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [LastTime]
     , first.EventXML AS [FirstRecord]
     , last.EventXML AS [LastRecord]
FROM (SELECT [DbId]
           , COUNT(*) AS [count]
           , MIN([RowNumber]) AS [FirstRow]
           , MAX([RowNumber]) AS [LastRow]
       FROM HOBTSchemaMgrCte
       GROUP BY [DbId] ) AS summary
JOIN HOBTSchemaMgrCte AS first
ON first.RowNumber = summary.[FirstRow]
JOIN HOBTSchemaMgrCte AS last
ON last.RowNumber = summary.[LastRow]
CROSS JOIN sys.dm_os_sys_info AS info
ORDER BY DbId;

RING_BUFFER_MEMORY_BROKER

“Actions the internal memory broker is taking to balance the memory between caches.“ – Bob Dorr
http://blogs.msdn.com/b/slavao/archive/2005/11/05/489459.aspx

WITH MemoryBrokerCte
AS (
           — select & run this query for a list of records in the queue
    SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( ‘@time’, ‘BIGINT’ )
                                     , Buffer.Record.value( ‘@id’, ‘INT’ ) ) AS [RowNumber]
         , Data.ring_buffer_type AS [Type]
         , Buffer.Record.value(‘(MemoryBroker/Pool)[1]’, ‘INT’) AS [Pool]
         , Buffer.Record.value(‘(MemoryBroker/Broker)[1]’, ‘NVARCHAR(128)’) AS [Broker]
         , Buffer.Record.value(‘(MemoryBroker/Notification)[1]’, ‘NVARCHAR(128)’) AS [Notification]
         , Buffer.Record.value( ‘@time’, ‘BIGINT’ ) AS [time]
         , Buffer.Record.value(‘@id’, ‘int’) AS [Id]
         , Data.EventXML
    FROM (SELECT CAST(Record AS XML) AS EventXML
                      , ring_buffer_type
          FROM sys.dm_os_ring_buffers
          WHERE ring_buffer_type = ‘RING_BUFFER_MEMORY_BROKER’) AS Data
    CROSS APPLY EventXML.nodes(‘//Record’) AS Buffer(Record)
   )
SELECT first.[Type]
     , summary.[Pool]
     , summary.[Broker]
     , summary.[Notification]
     , summary.[count]
     , DATEADD( second
               , first.[Time] info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [FirstTime]
     , DATEADD( second
               , last.[Time]  info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [LastTime]
     , first.EventXML AS [FirstRecord]
     , last.EventXML AS [LastRecord]
FROM (SELECT [Pool]
           , [Broker]
           , [Notification]
           , COUNT(*) AS [count]
           , MIN(RowNumber) AS [FirstRow]
           , MAX(RowNumber) AS [LastRow]
      FROM MemoryBrokerCte
      GROUP BY [Type]
             , [Pool]
             , [Broker]
             , [Notification] ) AS summary
JOIN MemoryBrokerCte AS first
ON first.RowNumber = summary.[FirstRow]
JOIN MemoryBrokerCte AS last
ON last.RowNumber = summary.[LastRow]
CROSS JOIN sys.dm_os_sys_info AS info
ORDER BY [Type]
       , [Pool]
       , [Broker]
       , [Notification];
  

RING_BUFFER_MEMORY_BROKER_CLERKS

Slava Oks’ posts are the best place to read about memory broker clerks

(see sys.dm_os_memory_clerks in BOL for more about memory clerks.)

WITH MemoryBrokerClerksCte
AS (
           — select & run this query for a list of records in the queue
    SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( ‘@time’, ‘BIGINT’ )
                                     , Buffer.Record.value( ‘@id’, ‘INT’ ) ) AS [RowNumber]
         , Data.ring_buffer_type AS [Type]
         , Buffer.Record.value( ‘(MemoryBrokerClerk/Name)[1]’, ‘NVARCHAR(128)’ ) AS Name
         , Buffer.Record.value( ‘(MemoryBrokerClerk/TotalPages)[1]’, ‘INT’ ) AS TotalPages
         , Buffer.Record.value( ‘@time’, ‘BIGINT’ ) AS [time]
         , Buffer.Record.value( ‘@id’, ‘INT’ ) AS [Id]
         , Data.EventXML
    FROM (SELECT CAST(Record AS XML) AS EventXML
               , ring_buffer_type
          FROM sys.dm_os_ring_buffers
          WHERE ring_buffer_type = ‘RING_BUFFER_MEMORY_BROKER_CLERKS’ ) AS Data
    CROSS APPLY EventXML.nodes(‘//Record’) AS Buffer(Record)
   )
SELECT first.[Type]
     , summary.[Name]
     , summary.[TotalPages]
     , summary.[count]
     , DATEADD( second
               , first.[Time] info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [FirstTime]
     , DATEADD( second
               , last.[Time]  info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [LastTime]
     , first.EventXML AS [FirstRecord]
     , last.EventXML AS [LastRecord]
FROM (SELECT [Name]
           , [TotalPages]
           , COUNT(*) AS [count]
           , MIN(RowNumber) AS [FirstRow]
           , MAX(RowNumber) AS [LastRow]
      FROM MemoryBrokerClerksCte
      GROUP BY [Type]
             , [Name]
             , [TotalPages] ) AS summary
JOIN MemoryBrokerClerksCte AS first
ON first.RowNumber = summary.[FirstRow]
JOIN MemoryBrokerClerksCte AS last
ON last.RowNumber = summary.[LastRow]
CROSS JOIN sys.dm_os_sys_info AS info
ORDER BY [Type], [Name]; 

RING_BUFFER_RESOURCE_MONITOR

(The CSS link above also speaks to the Resource Monitor ring buffer records.)

“allows you to see memory state changes due to various types of memory pressure.” – Christian Lefter

Actions the internal memory broker is taking to balance the memory between caches.“ – Bob Dorr

WITH ResourceMonitorCte
AS (
           — select & run this query for a list of records in the queue
    SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( ‘@time’, ‘BIGINT’ )
                                     , Buffer.Record.value( ‘@id’, ‘INT’ ) ) AS [RowNumber]
         , Data.ring_buffer_type AS [Type]
         , Buffer.Record.value( ‘(ResourceMonitor/Notification)[1]’, ‘NVARCHAR(128)’ ) AS [ResourceNotification]
         , Buffer.Record.value( ‘@time’, ‘BIGINT’ ) AS [time]
         , Buffer.Record.value( ‘@id’, ‘INT’ ) AS [Id]
         , Data.EventXML
    FROM (SELECT CAST(Record AS XML) AS EventXML
               , ring_buffer_type
          FROM sys.dm_os_ring_buffers
          WHERE ring_buffer_type = ‘RING_BUFFER_RESOURCE_MONITOR’) AS Data
    CROSS APPLY EventXML.nodes(‘//Record’) AS Buffer(Record)
   )
SELECT first.[Type]
     , summary.[ResourceNotification]
            , summary.[count]
     , DATEADD( second
               , first.[Time] info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [FirstTime]
     , DATEADD( second
               , last.[Time]  info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [LastTime]
     , first.EventXML AS [FirstRecord]
     , last.EventXML AS [LastRecord]
FROM (SELECT [ResourceNotification]
           , COUNT(*) AS [count]
           , MIN(RowNumber) AS [FirstRow]
           , MAX(RowNumber) AS [LastRow]
      FROM ResourceMonitorCte
      GROUP BY [ResourceNotification] ) AS summary
JOIN ResourceMonitorCte AS first
ON first.RowNumber = summary.[FirstRow]
JOIN ResourceMonitorCte AS last
ON last.RowNumber = summary.[LastRow]
CROSS JOIN sys.dm_os_sys_info AS info
ORDER BY [ResourceNotification]; 

RING_BUFFER_SCHEDULER

(Don’t see many examples of using this data. Instead more people seem to find meaning from the RING_BUFFER_SCHEDULER_MONITOR records)

Actual scheduler activity such as context switching. You can reconstruct the execution order from these entries.“ – Bob Dorr

WITH SchedulerCte
AS (
           — select & run this query for a list of records in the queue
    SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( ‘@time’, ‘BIGINT’ )
                                     , Buffer.Record.value( ‘@id’, ‘INT’ ) ) AS [RowNumber]
         , Data.ring_buffer_type AS [Type]
         , Buffer.Record.value(‘(Scheduler/Action)[1]’, ‘NVARCHAR(128)’) AS [Action]
         , Buffer.Record.value( ‘@time’, ‘BIGINT’ ) AS [time]
         , Buffer.Record.value(‘@id’, ‘BIGINT’) AS [Id]
         , Data.EventXML
    FROM (SELECT CAST(Record AS XML) AS EventXML
               , ring_buffer_type 
          FROM sys.dm_os_ring_buffers
          WHERE ring_buffer_type = ‘RING_BUFFER_SCHEDULER’) AS Data
    CROSS APPLY EventXML.nodes(‘//Record’) AS Buffer(Record)
   )
SELECT first.[Type]
     , summary.[Action]
     , summary.[Count]
     , DATEADD( second
               , first.[Time] info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [FirstTime]
     , DATEADD( second
               , last.[Time]  info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [LastTime]
     , first.EventXML AS [FirstRecord]
     , last.EventXML AS [LastRecord]
FROM ( SELECT [Action]
                   , COUNT(*) AS [Count]
            , MIN(RowNumber) AS [FirstRow]
            , MAX(RowNumber) AS [LastRow]
       FROM SchedulerCte
       GROUP BY [Action] ) AS summary
JOIN SchedulerCte AS first
ON first.RowNumber = summary.[FirstRow]
JOIN SchedulerCte AS last
ON last.RowNumber = summary.[LastRow]
CROSS JOIN sys.dm_os_sys_info AS info
ORDER BY FirstTime;
–ORDER BY LastTime DESC; 

RING_BUFFER_SCHEDULER_MONITOR

“[tell’s us] what is the state of the logical schedulers, the health record type is very helpful.“ – Bob Dorr

WITH SchedulerMonitorCte
AS (
           — select & run this query for a list of records in the queue
    SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( ‘@time’, ‘BIGINT’ )
                                     , Buffer.Record.value( ‘@id’, ‘INT’ ) ) AS [RowNumber]
         , Data.ring_buffer_type AS [Type]
         , Buffer.Record.value(‘(SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’, ‘bigint’) AS [ProcessUtilization]
         , Buffer.Record.value(‘(SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘bigint’) AS [SystemIdle]
         , Buffer.Record.value(‘(SchedulerMonitorEvent/SystemHealth/UserModeTime)[1]’, ‘bigint’) AS [UserModeTime]
         , Buffer.Record.value(‘(SchedulerMonitorEvent/SystemHealth/KernelModeTime)[1]’, ‘bigint’) AS [KernelModeTime]
         , Buffer.Record.value(‘(SchedulerMonitorEvent/SystemHealth/PageFaults)[1]’, ‘bigint’) AS [PageFaults]
         , Buffer.Record.value(‘(SchedulerMonitorEvent/SystemHealth/WorkingSetDelta)[1]’, ‘bigint’) AS [WorkingSetDelta]
         , Buffer.Record.value(‘(SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]’, ‘bigint’) AS [MemoryUtilization]
                            , Buffer.Record.value( ‘@time’, ‘BIGINT’ ) AS [time] 
         , Buffer.Record.value(‘@id’, ‘int’) AS [Id]
         , Data.EventXML
    FROM ( SELECT CAST(Record AS XML) AS EventXML
                , ring_buffer_type
           FROM sys.dm_os_ring_buffers
           WHERE ring_buffer_type = ‘RING_BUFFER_SCHEDULER_MONITOR’) AS Data
    CROSS APPLY EventXML.nodes(‘//Record’) AS Buffer(Record)
   )
SELECT first.[Type]
     , summary.[Count]
     , DATEADD( second
               , first.[Time] info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [FirstTime]
     , DATEADD( second
               , last.[Time]  info.ms_ticks / 1000
               , CURRENT_TIMESTAMP ) AS [LastTime]
     , first.EventXML AS [FirstRecord]
     , last.EventXML AS [LastRecord]
FROM ( SELECT COUNT(RowNumber) AS [count]
            , MIN(RowNumber) AS [FirstRow]
            , MAX(RowNumber) AS [LastRow]
       FROM SchedulerMonitorCte ) AS summary
JOIN SchedulerMonitorCte AS first
ON first.RowNumber = summary.[FirstRow]
JOIN SchedulerMonitorCte AS last
ON last.RowNumber = summary.[LastRow]
CROSS JOIN sys.dm_os_sys_info AS info;

RING_BUFFER_SECURITY_ERROR

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

WITH SecurityErrorCte
AS (
           — select & run this query for a list of records in the queue
    SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( ‘@time’, ‘BIGINT’ )
                                     , Buffer.Record.value( ‘@id’, ‘INT’ ) ) AS [RowNumber]
         , Data.ring_buffer_type AS [Type]
         , Buffer.Record.value( ‘(Error/SPID)[1]’, ‘INT’ ) AS [SessionId]
         , Buffer.Record.value( ‘(Error/ErrorCode)[1]’, ‘NVARCHAR(128)’ ) AS [ErrorCode]
         , Buffer.Record.value( ‘(Error/SQLErrorCode)[1]’, ‘NVARCHAR(128)’ ) AS [SQLErrorCode]
         , Buffer.Record.value( ‘(Error/APIName)[1]’, ‘NVARCHAR(128)’ ) AS [APIName]
         , Buffer.Record.value( ‘(Error/CallingAPIName)[1]’, ‘NVARCHAR(128)’ ) AS [CallingAPIName]
                            , Buffer.Record.value( ‘@time’, ‘BIGINT’ ) AS [time] 
         , Buffer.Record.value(‘@id’, ‘INT’) AS [Id]
         , Data.EventXML   
                     FROM (SELECT CAST(Record AS XML) AS EventXML
                                   , ring_buffer_type
                                  FROM sys.dm_os_ring_buffers
                                  WHERE ring_buffer_type = ‘RING_BUFFER_SECURITY_ERROR’) AS Data
                     CROSS APPLY EventXML.nodes(‘//Record’) AS Buffer(Record)
    )
  SELECT first.[Type]
       , summary.[ErrorCode]
       , summary.[SQLErrorCode]
       , summary.[APIName]
       , summary.[CallingAPIName]
       , summary.[Count]
       , DATEADD( second
                 , first.[Time] info.ms_ticks / 1000
                 , CURRENT_TIMESTAMP ) AS [FirstTime]
       , DATEADD( second
                 , last.[Time]  info.ms_ticks / 1000
                 , CURRENT_TIMESTAMP ) AS [LastTime]
       , first.EventXML AS [FirstRecord]
       , last.EventXML AS [LastRecord]
  FROM ( SELECT [ErrorCode]
              , [SQLErrorCode]
              , [APIName]
              , [CallingAPIName]
              , COUNT(*) AS [count]
              , MIN(RowNumber) AS [FirstRow]
              , MAX(RowNumber) AS [LastRow]
         FROM SecurityErrorCte
         GROUP BY [ErrorCode]
                , [SQLErrorCode]
                , [APIName]
                , [CallingAPIName] ) AS summary
JOIN SecurityErrorCte AS first
ON first.RowNumber = summary.[FirstRow]
JOIN SecurityErrorCte AS last
ON last.RowNumber = summary.[LastRow]
CROSS JOIN sys.dm_os_sys_info AS info
ORDER BY [FirstTime];
–ORDER BY [LastTime] DESC;

RING_BUFFER_XE_BUFFER_STATE

As Extended Events take over for SQL Trace, XE buffer management will become more necessary and this type could become more interesting.

WITH XEBufferStateCte
AS (
           — select & run this query for a list of records in the queue
    SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( ‘@time’, ‘BIGINT’ )
                                     , Buffer.Record.value( ‘@id’, ‘INT’ ) ) AS [RowNumber]
         , Data.ring_buffer_type AS [Type]
         , Buffer.Record.value(‘(XE_BufferStateRecord/NewState)[1]’, ‘varchar(100)’) as [NewState]
         , Buffer.Record.value( ‘@time’, ‘BIGINT’ ) AS [time]
         , Buffer.Record.value(‘@id’, ‘int’) AS [Id]
         , Data.EventXML
    FROM (SELECT CAST(Record AS XML) AS EventXML
               , ring_buffer_type 
          FROM sys.dm_os_ring_buffers
          WHERE ring_buffer_type =
‘RING_BUFFER_XE_BUFFER_STATE’
) AS Data
    CROSS APPLY EventXML.nodes(‘//Record’) AS Buffer(Record)
   )
SELECT first.[Type]
      , summary.[NewState]
      , summary.[Count]
      , DATEADD( second
                , first.[Time] info.ms_ticks / 1000
                , CURRENT_TIMESTAMP ) AS [FirstTime]
      , DATEADD( second
                , last.[Time]  info.ms_ticks / 1000
                , CURRENT_TIMESTAMP ) AS [LastTime]
      , first.EventXML AS [FirstRecord]
      , last.EventXML AS [LastRecord]
FROM (SELECT [NewState]
           , COUNT(*) AS [count]
           , MIN(RowNumber) AS [FirstRow]
           , MAX(RowNumber) AS [LastRow]
      FROM XEBufferStateCte
      GROUP BY [NewState] ) AS summary
JOIN XEBufferStateCte AS first
ON first.RowNumber = summary.[FirstRow]
JOIN XEBufferStateCte AS last
ON last.RowNumber = summary.[LastRow]
CROSS JOIN sys.dm_os_sys_info AS info
–ORDER BY [FirstTime];
ORDER BY [LastTime] DESC;

RING_BUFFER_XE_LOG

WITH XELogCte

AS (
           — select & run this query for a list of records in the queue
    SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( ‘@time’, ‘BIGINT’ )
                                    , Buffer.Record.value( ‘@id’, ‘INT’ ) ) AS [RowNumber]
        , Data.ring_buffer_type AS [Type]
        , Buffer.Record.value(‘(XE_LogRecord/@message)[1]’, ‘varchar(max)’) AS [XE_LogRecord]
        , Buffer.Record.value( ‘@time’, ‘BIGINT’ ) AS [time] 
        , Buffer.Record.value(‘@id’, ‘int’) AS [Id]
        , Data.EventXML
    FROM (SELECT CAST(Record AS XML) AS EventXML
                , ring_buffer_type 
          FROM sys.dm_os_ring_buffers
          WHERE ring_buffer_type = ‘RING_BUFFER_XE_LOG’) AS Data
    CROSS APPLY EventXML.nodes(‘//Record’) AS Buffer(Record)
    )
SELECT first.[Type]
      , summary.[XE_LogRecord]
      , summary.[Count]
      , DATEADD( second
                , first.[Time] info.ms_ticks / 1000
                , CURRENT_TIMESTAMP ) AS [FirstTime]
      , DATEADD( second
                , last.[Time]  info.ms_ticks / 1000
                , CURRENT_TIMESTAMP ) AS [LastTime]
      , first.EventXML AS [FirstRecord]
      , last.EventXML AS [LastRecord]
FROM ( SELECT [XE_LogRecord]
            , COUNT(*) AS [count]
            , MIN(RowNumber) AS [FirstRow]
            , MAX(RowNumber) AS [LastRow]
       FROM XELogCte
       GROUP BY [XE_LogRecord]) AS summary
JOIN XELogCte AS first
ON first.RowNumber = summary.[FirstRow]
JOIN XELogCte AS last
ON last.RowNumber = summary.[LastRow]
CROSS JOIN sys.dm_os_sys_info AS info
–ORDER BY [FirstTime];
ORDER BY [LastTime] DESC;

Advertisements
This entry was posted in Monitoring. Bookmark the permalink.

5 Responses to Monitoring and Troubleshooting with sys.dm_os_ring_buffers

  1. Prakash Heda says:

    awesome, thx for sharing

  2. André says:

    Some queries are presenting the following error (e.g: RING_BUFFER_XE_LOG ):

    Msg 248, Level 16, State 1, Line 1
    The conversion of the nvarchar value ‘2322013059’ overflowed an int column.

    • Bill Wunder says:

      Thanks André, I’d need to see the row from your buffer to be sure but I suspect you are enjoying a bug that jumps right out at me in the common table expressions ordering method now that you mention it.
      Notice that @time is returned from the XQUERY as an INT in the ROW_NUMBER statement but as a BIGINT just below in it’s own column definition? Oops.
      SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( ‘@time’, ‘INT’ )
      , Buffer.Record.value( ‘@id’, ‘INT’ ) ) AS [RowNumber]
      , Data.ring_buffer_type AS [Type]
      , Buffer.Record.value( ‘(ResourceMonitor/Notification)[1]‘, ‘NVARCHAR(128)’ ) AS [ResourceNotification]
      , Buffer.Record.value( ‘@time’, ‘BIGINT’ ) AS [time]

      If you have the time, try changing the ORDER BY to return as a BIGINT and let me know how it goes.

  3. Pingback: Dominus Anulorum – Troubleshooting SQL Server Connectivity Via Ring Buffers | A posteriori

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