[AlwaysOn Availability Groups]AlwaysOn Ring BuffersAlwaysOn 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

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.

<Recordid="45" type="RING_BUFFER_HADRDBMGR_STATE" time="1723487912">

    <dbId>5</dbId>

    <objectType>HadrDbMgr</objectType>

    <role>2</role>

RING_BUFFER_HADRDBMGR_COMMIT

<Recordid="0" type="RING_BUFFER_HADRDBMGR_COMMIT" time="1723475368">

  <HadrDbMgrCommitPolicy>

    <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>

  </HadrDbMgrCommitPolicy>

RING_BUFFER_HADR_TRANSPORT_STATE

<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>

  </HadrTransportState>

你可以從ring buffer解析資料。你先要使用cast轉化到XML。

   (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]

    本文轉自 Fanr_Zh 部落格園部落格，原文連結：http://www.cnblogs.com/Amaranthus/p/4986900.html，如需轉載請自行聯系原作者