Extended Events - Pro SQL Server Internals - page 556

The key Extended Events data management views that can be used to examine event sessions and data include
the following:

The sys.dm_xe_sessions view provides information about active event sessions. It shows
the configuration parameters of the sessions and execution statistics, such as the number
of dropped events or the amount of time that event collection contributed to blocking if the
NO_EVENT_LOSS option was used.
The sys.dm_xe_session_targets view returns information about targets. One of the key
columns of the view is event_data . Some targets, for example ring_buffer or histogram ,
expose collected event data in this column. For other targets, for example event_file , the
event_data column contains metadata information, such as the file name and session statistics.
The sys.dm_xe_sessions_object_columns expose configuration values for objects bound
to the session. You can use this view to obtain the configuration properties for the targets,
for example, the event file path.

■ You can find more information about the extended events dMvs at: http://technet.microsoft.com/en-us/
library/bb677293.aspx .

Note

Now let's look at how to access data collected in different targets.

Working with the ring_buffer Target
Ring_buffer event data is exposed through the event_data column in the sys.dm_xe_session_targets view.
Listing 28-13 shows how to parse data collected by the TempDB Spill event session, which we defined in Listing 28-11.

Listing 28-13. Examining ring_buffer target data
;with TargetData(Data)
as
(
select convert(xml,st.target_data) as Data
from sys.dm_xe_sessions s join sys.dm_xe_session_targets st on
s.address = st.event_session_address
vwhere s.name = 'TempDB Spills' and st.target_name = 'ring_buffer'

)
,EventInfo([Event Time],[Event],SPID,[SQL],PlanHandle)
as
(
select
t.e.value('@timestamp','datetime') as [Event Time]
,t.e.value('@name','sysname') as [Event]
,t.e.value('(action[@name="session_id"]/value)[1]','smallint')
as [SPID]
,t.e.value('(action[@name="sql_text"]/value)[1]','nvarchar(max)')
as [SQL]
,t.e.value('xs:hexBinary((action[@name="plan_handle"]/value)[1])'
,'varbinary(64)') as [PlanHandle]