AlwaysOn Ring Buffers
一些AlwaysOn的诊断信息可以从SQL Server ring buffers。或者从sys.dm_os_ring_buffers。ring buffer在SQL Server启动的时候被创建，SQL Server系统为内部诊断记录警告。
通过以下查询获取所有事件记录
SELECT * FROM sys.dm_os_ring_buffers WHERE ring_buffer_type LIKE '%HADR%'
为了让数据更加可控，可以通过日期，ring buffer类型来过滤数据。以下是获取指定ring buffer的查询
DECLARE @runtime datetime
SET @runtime = GETDATE()
SELECT CONVERT (varchar(30), @runtime, 121) as data_collection_runtime,
DATEADD (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 ring
CROSS JOIN sys.dm_os_sys_info inf where ring_buffer_type='<RING_BUFFER_TYPE>'
Record列包含了XML格式的诊断信息。XML数据和ring buffer类型不同。 为了让XML更加可读，你需要客户化TSQL提取想要的XML元素。比如如下语句，获取所有RING_BUFFER_HADRDBMGR_API ring buffer，并且XML数据放入独立的表的列。
WITH hadr(ts, type, record) AS
(
SELECT timestamp AS ts, ring_buffer_type AS type, CAST(record AS XML) AS record
FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_HADRDBMGR_API'
)
SELECT
ts,
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(50)') AS [API],
record.value('(/Record/HadrDbMgrAPI/Action)[1]', 'varchar(50)') AS [Action],
record.value('(/Record/HadrDbMgrAPI/role)[1]', 'int') AS [Role],
record.value('(/Record/Stack)[1]', 'varchar(100)') AS [Call Stack]
FROM hadr
ORDER BY record.value('(./Record/@time)[1]','bigint') DESC
GO
1. AlwaysOn Ring Buffer类型
sys.dm_os_ring_buffers有4种类的ring buffer。
| AlwaysOn Ring Buffer Type |
Description |
| RING_BUFFER_HADRDBMGR_API |
Records state transitions that have taken place or are taking place. When looking at the state transitions pay close attention to the objectType values. XML <Recordid="11" type="RING_BUFFER_HADRDBMGR_STATE" time="860243"> <HadrDbMgrState> <objectType>HadrUsers</objectType> <currentState>HDbMState_Starting</currentState> <proposedState>HDbMState_Started</proposedState> <targetState>HDbMState_Started</targetState> <legalTransition>Y</legalTransition> <role>1</role> </HadrDbMgrState> </Record> |
| RING_BUFFER_HADRDBMGR_STATE |
Records internal method or function calls made by AlwaysOn activity. It can show information such as suspend, resume, or role changes, including both the entry and exit points. XML <Recordid="45" type="RING_BUFFER_HADRDBMGR_STATE" time="1723487912"> <HadrDbMgrState> <dbId>5</dbId> <objectType>HadrDbMgr</objectType> <currentState>HDbMState_Starting</currentState> <proposedState>HDbMState_Started</proposedState> <targetState>HDbMState_Started</targetState> <legalTransition>Y</legalTransition> <role>2</role> </HadrDbMgrState> </Record> |
| RING_BUFFER_HADRDBMGR_COMMIT |
XML <Recordid="0" type="RING_BUFFER_HADRDBMGR_COMMIT" time="1723475368"> <HadrDbMgrCommitPolicy> <dbId>5</dbId> <replicaId>883a18f5-97d5-450f-8f8f-9983a4fa5299</replicaId> <dbHardenPolicy>KillAll</dbHardenPolicy> <dbSyncConfig>0x0</dbSyncConfig> <syncPartnerCount>0</syncPartnerCount> <minSyncPartnerConfig>0</minSyncPartnerConfig> <partnerHardenPolicy>KillAll</partnerHardenPolicy> <partnerSyncConfig>0x0</partnerSyncConfig> <logBlock>0x0000000000000000</logBlock> <leaseExpired>Y</leaseExpired> <partnerChange>N</partnerChange> <role>2</role> </HadrDbMgrCommitPolicy> </Record> |
| RING_BUFFER_HADR_TRANSPORT_STATE |
XML <Recordid="3" type="RING_BUFFER_HADR_TRANSPORT_STATE" time="1723485399"> <HadrTransportState> <agId>08264B79-D10B-412F-B38D-CA07B08E9BD8</agId> <localArId>883A18F5-97D5-450F-8F8F-9983A4FA5299</localArId> <targetArId>628D6349-72DD-4D18-A6E1-1272645660BA</targetArId> <currentState>HadrSession_Configuring</currentState> <targetState>HadrSession_Connected</targetState> <legalTransition>Y</legalTransition> </HadrTransportState> </Record> |
2. 从Ring Buffer 解析XML数据
你可以从ring buffer解析数据。你先要使用cast转化到XML。
WITH hadr(ts, type, record) AS
(SELECT timestamp AS ts, ring_buffer_type AS type, CAST(record AS XML) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_HADRDBMGR_API')
SELECT ts,
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(50)') AS [API],
record.value('(/Record/HadrDbMgrAPI/Action)[1]', 'varchar(50)') AS [Action],
record.value('(/Record/HadrDbMgrAPI/role)[1]', 'int') AS [Role],
record.value('(/Record/Stack)[1]', 'varchar(100)') AS [Call Stack]
FROM hadr
ORDER BY record.value('(./Record/@time)[1]','bigint') DESC
GO
