Using the XEReader Collector type

Here is an example of how to use the ExtendedXEReader Collector Type.
The parameters found in the XML schema are described below:

-- Enable editing advanced configuration options
EXEC sp_configure 'advanced', 1
RECONFIGURE 
GO

-- Set the blocked process threshold
EXEC sp_configure 'blocked process threshold (s)', 15
RECONFIGURE 
GO


USE msdb;
GO
IF EXISTS (
	SELECT 1
	FROM msdb.dbo.syscollector_collection_sets
	WHERE name = N'Blocked Process Reports'
)
EXEC dbo.sp_syscollector_delete_collection_set 
    @name = N'Blocked Process Reports';
   

-----------------------------------------------------------------------------
/*
    ======
    Create Collection Set
    ======
*/
-- Create the collection set
RAISERROR ('Creating collection set "Blocked Process Reports"...',0, 1) WITH NOWAIT;
DECLARE @schedule_uid UNIQUEIDENTIFIER;
SELECT @schedule_uid = (select schedule_uid from sysschedules_localserver_view where name=N'CollectorSchedule_Every_5min');
DECLARE @collection_set_id INT;
EXEC dbo.sp_syscollector_create_collection_set 
    @name = N'Blocked Process Reports', 
    @schedule_uid = @schedule_uid,  -- 5 minutes
    @collection_mode = 0,           -- cached
    @days_until_expiration = 30,    -- retain stats for 30 days
    @description = N'Collects Blocked Process Reports using Extended Events', 
    @collection_set_id = @collection_set_id output, 
    @collection_set_uid = '9FD45270-2A37-47ED-BE6E-DEA413095420';




-- Create a collection item to capture blocked processes
DECLARE @parameters xml;
DECLARE @collection_item_id INT;
SELECT @parameters = convert(xml, N'
<ns:ExtendedXEReaderCollector xmlns:ns="DataCollectorType">
    <Session>
        <Name>blocked_processes</Name>
        <OutputTable>blocked_processes_table</OutputTable>
		<Definition>
		CREATE EVENT SESSION [blocked_processes] ON SERVER ADD EVENT sqlserver.blocked_process_report
		WITH (
			MAX_MEMORY = 2048 KB
			,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
			,MAX_DISPATCH_LATENCY = 30 SECONDS
			,MAX_EVENT_SIZE = 0 KB
			,MEMORY_PARTITION_MODE = NONE
			,TRACK_CAUSALITY = OFF
			,STARTUP_STATE = ON
			)
		</Definition>
		<Filter>duration &lt;= 30000000</Filter>
		<ColumnsList>blocked_process</ColumnsList>
    </Session>
	<Alert Enabled="true" WriteToERRORLOG="true" WriteToWindowsLog="false">
		<Sender>MailProfile</Sender>
		<Recipient>DbAdmins@mycompany.com</Recipient>
		<Subject>Blocked process detected</Subject>
		<Importance>High</Importance>
		<ColumnsList>blocked_process</ColumnsList>
		<Filter>duration &lt;= 30000000</Filter>
		<Mode>Atomic</Mode>
        <Delay>60</Delay>
	</Alert>
</ns:ExtendedXEReaderCollector>
');

RAISERROR ('Creating collection item "Blocked Process Reports"...',0, 1) WITH NOWAIT;
EXEC dbo.sp_syscollector_create_collection_item
    @collection_set_id = @collection_set_id,
    @collector_type_uid = N'57AFAFB4-D4BE-4E62-9C6A-D2F2EA5FC5E9', -- Collector Type
    @name = 'Test Item',
    @frequency = 60, 
    @parameters = @parameters,
    @collection_item_id = @collection_item_id OUTPUT;

GO 

The created collection set will resemble this:

XEReaderCollectionSet.png

The email messages received will look like this one:

email.png

Known limitations