Technical Background:
Extended Events - Histogram Target:
The Histogram Target is a target that provides grouping of a specific event within the Event Session. The grouping can be configured on
Data in the payload or on an Action with the event. An Action is basically a way to append additional run-time data to an event and since
they are synchronous on the thread that fires an event, they can have a performance impact.
The Histogram Target can do the following:
- Count occurrences for several items separately.
- Count occurrences of different types of items: Event fields, Actions
Histogram Config Options:
Slots:
A user-specified value indicating the maximum number of groupings to retain. When this value is reached, new events that do not belong to the existing groups are ignored. The 'slots' parameter default is 256. If you assign another value, the value is rounded up to the next power of 2. For example, slots=59 would be rounded up to =64.
Filtering_Event_Name:
A user-specified value that is used to identify a class of events. Only instances of the specified event are bucketed. All other events are ignored. If you specify this value, you must use the format: package_name.event_name, for example 'sqlserver.checkpoint_end'. If you do not specify the filtering_event_name value, source_type must be set to 1 (the default).
Source_Type:
The type of object that the bucket is based on. This value is optional and if not specified has a default value of 1.
- source_type=0 means collect data for event fields.
- source_type=1 means collect data for actions.
Source:
The event field or action name that is used as the data source.
When you specify an event column for source, you must specify a column from the event that is used for the filtering_event_name value.
When you specify an event column for source, you do not have to include the package name in the source value.
When you specify an action name for data source, you must specify the source value by using the format: package_name.action_name.
XQuery:
Transact-SQL supports a subset of the XQuery language that is used for querying the XML data type.
XQuery is used to read data from an XML variable or column. If you need to read the values from an XML column use CROSS APPLY so that
can access more than one record.
Methods: nodes(), value(), query(), exist(), modify()
The Solution:
The high-level process is to create a new Extended Events session, based on sqlserver.lock_deadlock, and then set targets for that
session data for file and Histogram. Note that proper configuration of the Histogram Target specifically is very important to capture required
aggregated data set (buckets). Once the Extended Events session is created & started, we can query directly from the Histogram XML data to see the results.
Create a new Extended Events Session:
CREATE EVENT SESSION [EEDeadlock] ON SERVER
ADD EVENT sqlserver.lock_deadlock(
ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET Filename=N'G:\XEventsDeadLocks\Deadlock.xel'),
ADD TARGET package0.histogram
( SET
slots = 256, --Optional / Adjust based on number of DBs
filtering_event_name = 'sqlserver.lock_deadlock',
source_type = 0, --Collect data for Event fields (not action data)
source = 'database_id') --Grouping Field
WITH (STARTUP_STATE=OFF);
GO
Next, Start Extended Events Session:
ALTER EVENT SESSION [EEDeadlock] ON SERVER STATE = START;
GO
/*
SQL Helper:
Select * from sys.server_event_session_fields where event_session_id = 66184
Select * from sys.server_event_session_fields where event_session_id = 66184
*/
Next, Query the target_data field as XML:
SELECT CAST ([target_data] AS XML) AS target_data
FROM sys.dm_xe_session_targets AS st
INNER JOIN sys.dm_xe_sessions AS s ON s.address = st.event_session_address
WHERE s.name = N'EEDeadlock'
AND st.target_name = N'histogram';
Next, Query the captured Histogram target data using XQuery:
SELECT
DB_NAME(T2.C.value('(value)[1]', 'bigint')) AS [Database], --or DB_NAME(T2.C.query('./value').value('.', 'int')) AS [Database]
T2.C.value('./@count', 'int') AS [Total Deadlocks]
FROM
(
SELECT CAST(t.target_data AS XML) AS target_data--, *
/*
target_date is column with the XML Document in it:
<HistogramTarget truncated="0" buckets="256"><Slot count="5"><value>8</value></Slot><Slot count="1"><value>11</value></Slot><Slot count="1"><value>10</value></Slot></HistogramTarget>
*/
FROM sys.dm_xe_session_targets AS t
INNER JOIN sys.dm_xe_sessions AS s ON t.event_session_address = s.address
WHERE s.name = 'EEDeadlock' AND t.target_name = 'histogram'
) AS T
CROSS APPLY T.target_data.nodes('/HistogramTarget/Slot') AS T2 (C) --target_data is field, T is resulting table, C is resulting column
ORDER BY T2.C.value('./@count', 'int') DESC
Stop Extended Events Session:
ALTER EVENT SESSION [EEDeadlock] ON SERVER STATE = STOP;
GO
Drop Extended Events Session:
DROP EVENT SESSION [EEDeadlock] ON SERVER
That's it! Now you have a fully configured Evended Events session monitoring deadlock events as they occur for every database in the engine.
Resources:
Histogram Target
SQL Server Extended Events Targets
Targets for Extended Events in SQL Server
XQuery Language Reference (SQL Server)
|
|
|
|
|
|