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.


  • 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)  
WHILE @i <30
    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

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)

Here you can see that target was accessed 30 times.

Buffer processed count
Buffer processed count

Clean the session 

IF EXISTS (SELECT NULL FROM sys.server_event_sessions WHERE name = 'TestSession')

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

Buffer processed count
Buffer processed count

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

IF OBJECT_ID('tempdb.dbo.#LogData') IS NOT NULL
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);

Partitioned views

In this post I would like to show you how to create simple local horizontal partitioned view.  The big advantage of this technique is to split huge table into smaller parts you can work with.  There must be fulfilled some prerequisites you can find here on MSDN in detail. In this post we will try just to access data from created partitioned view. Here are important prerequisites, must be fulfilled for creating portioned view in short summary. More detail you can find in MSDN  https://bit.ly/2WTrQmO in view sectio..

  • Tables in partitioned view are joined with UNION ALL operator
  • The same table cannot be repeated multiple times
  • Columns of each table has to be in same ordinal position
  • Partitioned column has to be part of primary key
  • Partitioned column has to be constrained based on partitioning conditions (for example date period)
  • All Columns of table (partition) has to be obtained in select of partitioned view

Script to create 3 tables partitions with booking_date partition column.

CREATE TABLE [dbo].[Partition__2019_10_01__2019_11_01]
(id INT NOT NULL , booking_date DATE NOT NULL, data SYSNAME) 
ALTER TABLE [dbo].[Partition__2019_10_01__2019_11_01] WITH CHECK ADD CONSTRAINT [CH__Partition1] CHECK (([booking_date]>='2019-10-01' AND [booking_date]<'2019-11-01'))
ALTER TABLE [dbo].[Partition__2019_10_01__2019_11_01] ADD PRIMARY KEY CLUSTERED (    [id]         ASC,   [booking_date] ASC)
CREATE TABLE [dbo].[Partition__2019_11_01__2019_12_01] (id INT NOT NULL, booking_date DATE NOT NULL,data SYSNAME)
ALTER TABLE [dbo].[Partition__2019_11_01__2019_12_01] WITH CHECK ADD CONSTRAINT [CH__Partition2] CHECK (([booking_date]>='2019-11-01' AND [booking_date]<'2019-12-01'))
ALTER TABLE [dbo].[Partition__2019_11_01__2019_12_01] ADD PRIMARY KEY CLUSTERED 
(   [id]         ASC,  [booking_date] ASC
CREATE TABLE [dbo].[Partition__2019_12_01__2020_01_01](id INT NOT NULL, booking_date DATE NOT NULL, data SYSNAME)
ALTER TABLE [dbo].[Partition__2019_12_01__2020_01_01] WITH CHECK ADD  CONSTRAINT [CH__Partition3] CHECK (([booking_date]>='2019-12-01' AND [booking_date]<'2020-01-01'))
ALTER TABLE [dbo].[Partition__2019_12_01__2020_01_01] ADD PRIMARY KEY CLUSTERED (    [id]         ASC,    [booking_date] ASC)
CREATE VIEW [dbo].[PartitionedView] 
SELECT * FROM [dbo].[Partition__2019_10_01__2019_11_01]
SELECT * FROM [dbo].[Partition__2019_11_01__2019_12_01]
SELECT * FROM [dbo].[Partition__2019_12_01__2020_01_01]

Let’s put some data to all these partitions. Since your partitioned view is designed for data modification. I will mention prerequisites in next post.

INSERT INTO [dbo].[PartitionedView]
SELECT 1 ,'2019-11-05','TestData1'
INSERT INTO [dbo].[PartitionedView]
SELECT 2 ,'2019-11-06','TestData2'
INSERT INTO [dbo].[PartitionedView]
SELECT 3 ,'2019-11-07','TestData2'
INSERT INTO [dbo].[PartitionedView]
SELECT 4 ,'2019-11-08','TestData2'
INSERT INTO [dbo].[PartitionedView]
SELECT 5 ,'2019-12-05','TestData3'

Here is execution plan if you select data from one month (partition). You can see that we touched only partition, the predicate corresponds to the check constraint of the selected partition. Be aware that it is important to have partition column in predicate of query to work only with requested partition.

SELECT * FROM [dbo].[PartitionedView] WHERE booking_date >= '2019-11-01' AND booking_date <'2019-12-01'
Execution plan - Partitioned view
Execution plan – Partitioned view

Let’s suppose that we would like to get concrete record based on id attribute in our case. Using following query, we get data using index seek but we touched each partitioning partitioned view. Because we didn’t use partition column, used in check constraint of partition.

SELECT * FROM [dbo].[PartitionedView] WHERE id = 4
Execution plan - Partitioned view
Execution plan – Partitioned view

To get requested record effectively let’s change above mentioned query to:

SELECT * FROM [dbo].[PartitionedView] WHERE booking_date >= '2019-11-01' AND booking_date <'2019-12-01' AND id=4
Execution plan - Partitioned view
Execution plan – Partitioned view

Next time I would like to share some other knowledges regarding partitioned views. STAY TUNED!



I would like to start some posts series on my blog regarding techniques connected with high data volume. One of these techniques is partitioning. There are lots of sources describing partitioning approach. I will concentrate on practical usage in my posts. I will start this theme with partitioning views, since they are available in earlier versions of SQL server standard editions and they help to better illustrate principles of partitioning.

Partitioning helps when you have huge table with lots of historical data. Even when this table has designed indexes in good shape, large table has large indexes which has impact on seek operations and reading is not so fast.  It could cause other problems too, for example with data modification or data maintenance. Full-scan of very large statistic created with the index could be very time consuming. 

Splitting such a large objects to smaller partitions will help with all above mentioned aspects.  Even better, each partition could be part of filegroup on separate disk storage which leads to better I/O operations when data could be read in parallel. It will brings other advantages when you dont want to touch historical data or use them just for reading – you can bring them to filegroups and change them to read-only state.

There are few approaches how partitioning can be designed. I would like to describe them in several posts. In short:

from point of view of partitioned direction.

  • Horizontal partitioning – data are partitioned based on attribute/attributes defined in table. For example, data partitioned by month in separated tables. Each partition has the same structure – columns.
  • Vertical partitioning – there is defined attribute which connects more tables with fewer columns. Structure of each partition can be different. For example, let’s have transaction info, one table partition stores about customers another one regarding region. Each partition has unique key transaction_id creating relations between such a partitions.

from point of view of partition data distribution

  • Equally distributed – for example partitions based on monthly period connected to date dimension
  • Floating partitions – partition with data for last tree months. Older data are moved to archive partition with historical data

From point of view of data source

  • Partitions created in the same database
  • Partitions created across databases on the same server
  • Distributed partitions created across servers

There are more perspectives how we could look on partitoining. Lets go trough some sammples and implementation styles to get better overview of these basic terms.

Next post will be concentrated on partitioned views so stay tuned.