Here is an example of how to use the ExtendedXEReader Collector Type.
-- 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 <= 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 <= 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