SQL Server ring buffers (ring buffer)--loop buffering in AlwaysOn applications

SQL Server ring buffers (ring buffer)--loop buffering in AlwaysOn applications

You can get some diagnostic AlwaysOn information from the SQL Server ring buffer, or dynamically manage views from sys.dm_os_ring_buffers. The ring buffer is created when SQL Server starts and records alarms within the SQL Server System for internal diagnostics. They are not supported, but you can still get useful information from them. The following query can get all the event records from an AlwaysOn ring buffer.

SELECT * from Sys.dm_os_ring_buffers WHERE ring_buffer_type like '%hadr% '

To better manage your data, filter data by date and ring buffer type. The following query gets the records in today's specific ring buffer.

DECLARE @runtime Datetimeset @runtime = GETDATE () SELECT CONVERT (varchar (), @runtime, 121) as Data_collection_runtime,d Ateadd (MS,-1 * (inf.ms_ticks-ring.[ Timestamp]), GETDATE ()) as ring_buffer_record_time,ring. [Timestamp] As Record_timestamp, inf.ms_ticks as Cur_timestamp, Ring.*from sys.dm_os_ring_buffers ringcross JOIN sys.dm_os_sys_info INF where ring_buffer_type= ' <RING_BUFFER_TYPE> '

The record column in each row contains diagnostic information in XML format. The XML data distinguishes between different ring buffer types. For each ring buffer type, you can refer to the following. To make the XML data more readable, you need to modify the XML elements required by the T-SQL query extraction. For example, the following query gets events from the Ring_buffer_hadrdbmgr_api ring buffer type and formats the XML data into separate table columns.

with hadr (Ts, type, record)  as (SELECT timestamp  as ts, ring_buffer_type as type, cast (Record as xml)  AS  recordfrom sys.dm_os_ring_buffers where ring_buffer_type =  ' RING_BUFFER_HADRDBMGR_API ') Selectts,type,record.value (' (./record/@id) [1] ', ' bigint ')  as [record id],record.value (' (./ record/@time) [1] ', ' bigint ')  as [time],record.value (' (./record/hadrdbmgrapi/dbid) [1] ',  ' bigint ')  as [dbid],record.value (' (/RECORD/HADRDBMGRAPI/API) [1] ',  ' varchar ')  AS [API], Record.value (' (/record/hadrdbmgrapi/action) [1] ',  ' varchar (+) ')  as [action],record.value (' (/ Record/hadrdbmgrapi/role) [1] ',  ' int ')  as [role],record.value (' (/record/stack) [1] ',  ' varchar (+) ')  as [call stack]from hadrorder by record.value (' (./Record/@time) [1 ] ', ' bigint ')  descgo 

AlwaysOn Ring Buffer Type

There are 4 types of ring buffers in the sys.dm_os_ring_buffers:

ring_buffer_hadrdbmgr_api– records the state changes that have occurred or are occurring. Focus on the value of objecttype when you are concerned about changes in state.

Ring_buffer_hadrdbmgr_state– records internal methods and function calls for AlwaysOn activities. You can see, for example, suspend, resume, or role changes, including all entrances and exits.

Ring_buffer_hadrdbmgr_commit

Ring_buffer_hadr_transport_state

This article is from the "Dripping Stone Wear" blog, please be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1583956

SQL Server ring buffers (ring buffer)--loop buffering in AlwaysOn applications