Extended events – max dispatch latency

MAX_DISPATCH_LATENCY as extended event session property – Specifies the amount of time that events are buffered in memory before being dispatched to event session targets. States in MSDN see link https://bit.ly/33He9Zt. The default for this value for this option is 30 seconds, and when you set it to 0 buffers are flushed to targets when they are full, or session is closed.

Let’s make a test, how we can check this behaviour. Here you can check in buffer_processed_count attribute how many times were buffer flushed to target. Let’s go to prove this behaviour.

Summary:

Let’s start extended events session and configure it for with max_dispatch_latency 1 seconds, I will use Ring Buffer as the target
There will be running script for 30 seconds and each second there will be raised user message to the system
With above mentioned configuration we will find 30 in buffer_processed_count because buffer is flushed each 1 second so it will be called 30 times
Clear session, create it again with changed configuration for max_dispatch_latency 30 seconds
Now you should see 1 in buffer_processed_count buffer is flushed each 30 seconds, our script runs for 30 seconds, there it comes with 1
Buffer is flushed to target when user calls

Query to get buffer_processed_count bellow

SELECT s.buffer_processed_count,s.name,s.total_regular_buffers,s.regular_buffer_size,s.total_large_buffers,s.large_buffer_size,s.dropped_event_count,s.dropped_buffer_count,ss.largest_event_dropped_size
FROM sys.dm_xe_sessions
AS s;

Script bellow lasts 30 seconds and each second there is raised user message.

 DECLARE @Message VARCHAR(100)  
DECLARE @i INT = 0
DECLARE @sql VARCHAR(30)=''
WHILE @i <30
BEGIn
    SET @sql= 'SELECT ' + CAST (@i AS VARCHAR(20))
    SET @Message='Hello '+ CAST (@i AS VARCHAR(20) )+' '+CONVERT(VARCHAR(20),GETDATE(),120)
    RAISERROR( @Message,1,1);
    EXEC (@sql)
    WAITFOR DELAY '00:00:01'
   SET @i=@i+1
END

Let’s start session.

CREATE EVENT SESSION TestSession ON SERVER ADD EVENT sqlserver.error_reported
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id,sqlserver.username)
WHERE sqlserver.database_id = 21 AND [error_number]=50000
)
ADD TARGET package0.ring_buffer (SET max_memory = 4096 ) WITH (max_dispatch_latency =1 seconds)
ALTER EVENT SESSION TestSession ON SERVER STATE = START

Here you can see that target was accessed 30 times.

Clean the session

IF EXISTS (SELECT NULL FROM sys.server_event_sessions WHERE name = 'TestSession')
BEGIN
DROP EVENT SESSION TestSession ON SERVER;
END

Run it again with changed attribute max_dispatch_latency =30 seconds. Bellow you can see that target was accessed only 1 time.

Beware of that the counter changes in cases you access targets manually with script bellow.

IF OBJECT_ID('tempdb.dbo.#LogData') IS NOT NULL
DROP TABLE #LogData;
SELECT CAST(LocksAcquired.TargetXml AS XML) AS XMLData
INTO #LogData 
FROM (SELECT CAST(t.target_data AS XML) AS TargetXml 
FROM sys.dm_xe_session_targets AS t
JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address
WHERE s.name='TestSession'
) AS LocksAcquired;
SELECT T.XMLData.value('(action[@name="sql_text"])[1]', 'varchar(1000)' ) AS [SqlText],
       T.XMLData.value('(@timestamp)[1]', 'datetime' )AS [OccurredDtTm],
       T.XMLData.value('(data[@name="message"])[1]','varchar(60)' ) AS [Message]
FROM #LogData CROSS APPLY XMLData.nodes('/RingBufferTarget/event[@name="error_reported"]') AS T(XMLData);

Leave a Reply Cancel reply