sqlskills-logo-2015-white.png

An XEvent a Day (19 of 31) – Using Customizable Fields

Today’s post will be somewhat short, but we’ll look at Customizable Fields on Events in Extended Events and how they are used to collect additional information.  Customizable Fields generally represent information of potential interest that may be expensive to collect, and is therefore made available for collection if specified by the Event Session.  In SQL Server 2008 and 2008 R2, there are 50 Events that have customizable columns in their payload.  In SQL Server Denali CTP1, there are 124 Events that have customizable columns in their payload. The customizable columns and Events that have them can be found with the following query.

SELECT 
    p.name AS package_name,
    o.name AS event_name,
    oc.name AS column_name,
    oc.column_type,
    oc.type_name,
    oc.description
FROM sys.dm_xe_packages p
JOIN sys.dm_xe_objects o
    ON p.guid = o.package_guid
JOIN sys.dm_xe_object_columns oc
    ON o.name = oc.object_name 
        AND o.package_guid = oc.object_package_guid
WHERE ((p.capabilities is null or p.capabilities & 1 = 0)
  AND (o.capabilities is null or o.capabilities & 1 = 0)
  AND (oc.capabilities is null or oc.capabilities & 1 = 0))
  AND o.object_type = 'event'
  AND oc.column_type = 'customizable'

If we look at a specific Event containing a customizable column, in this case the sqlserver.file_read_completed Event, we will see that there is a customizable column as well as a data column for the data collected by the customizable column.

SELECT 
    p.name AS package_name,
    o.name AS event_name,
    oc.column_id,
    oc.name AS column_name,
    oc.column_type,
    oc.type_name,
    oc.description
FROM sys.dm_xe_packages p
JOIN sys.dm_xe_objects o
    ON p.guid = o.package_guid
JOIN sys.dm_xe_object_columns oc
    ON o.name = oc.object_name 
        AND o.package_guid = oc.object_package_guid
WHERE ((p.capabilities is null or p.capabilities & 1 = 0)
  AND (o.capabilities is null or o.capabilities & 1 = 0)
  AND (oc.capabilities is null or oc.capabilities & 1 = 0))
  AND o.object_type = 'event'
  AND o.name = 'file_read_completed'
  AND oc.column_type <> 'readonly'
ORDER BY oc.column_type, oc.column_id

image

In the red box are the customizable columns, and the blue box has the associated data columns to the customizable columns.  The data columns will exist in the Event data from the Event firing, but they will only have a value in the Event data if the customizable column is set to collect the information.

CREATE EVENT SESSION CustomizableColumnDemo
ON SERVER
ADD EVENT sqlserver.file_read_completed
(
    WHERE (database_id = 4)    
)
ADD TARGET package0.ring_buffer
GO
ALTER EVENT SESSION CustomizableColumnDemo
ON SERVER
STATE=START
GO
DBCC DROPCLEANBUFFERS
GO
SELECT TOP 10 * FROM msdb.dbo.backupset
GO

If we query the Target data for the above Event, we’ll see that the path and io_data columns are included in the Event XML, but there is no value in the XML nodes.

SELECT CAST(target_data AS XML) as target_data
FROM sys.dm_xe_sessions AS s    
JOIN sys.dm_xe_session_targets AS t
    ON s.address = t.event_session_address
WHERE s.name = 'CustomizableColumnDemo'
  AND t.target_name = 'ring_buffer'

<event name="file_read_completed" package="sqlserver" id="83" version="1" timestamp="2010-12-20T03:14:20.393Z">
  <data name="mode">
    <type name="file_io_mode" package="sqlserver" />
    <value>0</value>
    <text>Contiguous</text>
  </data>
  <data name="duration">
    <type name="uint64" package="package0" />
    <value>0</value>
    <text />
  </data>
  <data name="file_handle">
    <type name="ulong_ptr" package="package0" />
    <value>0x0000000000000b38</value>
    <text />
  </data>
  <data name="offset">
    <type name="uint64" package="package0" />
    <value>14352384</value>
    <text />
  </data>
  <data name="database_id">
    <type name="uint16" package="package0" />
    <value>4</value>
    <text />
  </data>
  <data name="file_id">
    <type name="uint16" package="package0" />
    <value>1</value>
    <text />
  </data>
  <data name="file_group_id">
    <type name="uint16" package="package0" />
    <value>1</value>
    <text />
  </data>
  <data name="path">
    <type name="unicode_string" package="package0" />
    <value />
    <text />
  </data>
  <data name="io_data">
    <type name="binary_data" package="package0" />
    <value />
    <text />
  </data>
</event>

To set the customizable column to collect the data, in the ADD EVENT section of the CREATE EVENT SESSION or ALTER EVENT SESSION DDL command, the SET option is used to turn data collection on for the column.

DROP EVENT SESSION CustomizableColumnDemo
ON SERVER
GO
CREATE EVENT SESSION CustomizableColumnDemo
ON SERVER
ADD EVENT sqlserver.file_read_completed
(    
    SET collect_path = 1
    WHERE(database_id = 4)
)
ADD TARGET package0.ring_buffer
GO
ALTER EVENT SESSION CustomizableColumnDemo
ON SERVER
STATE=START
GO
DBCC DROPCLEANBUFFERS
GO
SELECT TOP 10 * FROM msdb.dbo.backupset
GO
ALTER EVENT SESSION CustomizableColumnDemo
ON SERVER
DROP EVENT sqlserver.file_read_completed
GO

Notice that the SET option does not use parenthesis, they are not allowed in the DDL definition.  By setting the collect_path customizable column to 1 the Event XML now contains the path to the data file that was read.

-- Query the XML to get the Target Data
SELECT 
    n.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    n.value('(event/@package)[1]', 'varchar(50)') AS package_name,
    DATEADD(hh, 
            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
            n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
    ISNULL(n.value('(event/data[@name="database_id"]/value)[1]', 'int'),
            n.value('(event/action[@name="database_id"]/value)[1]', 'int')) as [database_id],
    n.value('(event/data[@name="mode"]/value)[1]', 'nvarchar(50)') as [mode],
    n.value('(event/data[@name="duration"]/value)[1]', 'bigint') as [duration],
    n.value('(event/data[@name="file_handle"]/value)[1]', 'nvarchar(50)') as [file_handle],
    n.value('(event/data[@name="offset"]/value)[1]', 'int') as [offset],
    n.value('(event/data[@name="file_id"]/value)[1]', 'int') as [file_id],
    n.value('(event/data[@name="path"]/value)[1]', 'nvarchar(250)') as [path],
    n.value('(event/data[@name="id_data"]/value)[1]', 'nvarchar(max)') as [io_data]   
FROM
(    SELECT td.query('.') as n
    FROM 
    (
        SELECT CAST(target_data AS XML) as target_data
        FROM sys.dm_xe_sessions AS s    
        JOIN sys.dm_xe_session_targets AS t
            ON s.address = t.event_session_address
        WHERE s.name = 'CustomizableColumnDemo'
          AND t.target_name = 'ring_buffer'
    ) AS sub
    CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(td)
) as tab
GO

image

The increase in the number of Events with customizable columns in Denali CTP1 is, in my own opinion, a great step in the right direction for Extended Events.  The use of customizable columns to add data into the Event payload extends the flexibility of Extended Events by providing a mechanism to gather additional data related to Events that is specific to the Event and not globally available like Actions.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course