Service Broker -测量消息在sys.transmission_queue中保留的时间

Question

有没有什么办法我可以测量消息在sys.transmission_queue中停留的时间，例如给定时间段的平均值？

Answer 1

更多的活动来拯救我们！首先，定义以下会话：

CREATE EVENT SESSION [broker_transmission_queue] ON SERVER 
ADD EVENT sqlserver.broker_dialog_transmission_queue_dequeue,
ADD EVENT sqlserver.broker_dialog_transmission_queue_enqueue
ADD TARGET package0.ring_buffer
GO

让它在service broker工作负载运行时运行一段时间。然后，运行以下查询：

WITH e AS (
    SELECT e.query('.') AS event
    FROM (
        select CAST(dxst.target_data AS XML) AS target_data
        FROM sys.dm_xe_session_targets AS dxst
        JOIN sys.dm_xe_sessions AS dxs
            ON dxst.event_session_address = dxs.address
        WHERE dxs.name = 'broker_transmission_queue'
            AND dxst.target_name = 'ring_buffer'
    ) AS t
    CROSS APPLY t.target_data.nodes('/RingBufferTarget/event') AS tgt(e)
), parsed AS (
    SELECT event.value('(event/data[@name="dialog_id"])[1]', 'uniqueidentifier') AS dialog_id, 
    event.value('(event/data[@name="message_sequence_number"])[1]', 'bigint') AS message_sequence_number,
    event.value('(event/@timestamp)[1]', 'datetime2') AS timestamp,
    event.value('(event/@name)[1]', 'sysname') AS event_name
    FROM e
)
SELECT b.timestamp, e.timestamp, DATEDIFF(MILLISECOND, b.timestamp, e.timestamp )
FROM parsed AS b
JOIN parsed AS e
    ON e.dialog_id = b.dialog_id
    AND e.message_sequence_number = b.message_sequence_number
    AND b.event_name = 'broker_dialog_transmission_queue_enqueue'
    AND e.event_name = 'broker_dialog_transmission_queue_dequeue'

本质上，我们在入队和出队事件之间匹配(conversation_handle，sequence_number)元组。一旦你有了匹配，你就可以做任何你想要的数学运算(计数，平均值，等等)。