Database Reference
In-Depth Information
as
(
select
Data.value('/event[1]/@timestamp','datetime') as [Event Time]
,Data.value('/event[1]/@name','sysname') as [Event]
,Data.value('(/event[1]/action[@name="session_id"]/value)[1]'
,'smallint') as [SPID]
,Data.value('(/event[1]/action[@name="sql_text"]/value)[1]'
,'nvarchar(max)') as [SQL]
,Data.value(
'xs:hexBinary((/event[1]/action[@name="plan_handle"]/value)[1])'
,'varbinary(64)') as [PlanHandle]
,File_Name
,File_Offset
from
TargetData
)
select
ei.[Event Time], ei.File_Name, ei.File_Offset
,ei.[Event], ei.SPID, ei.SQL, qp.Query_Plan
from
EventInfo ei
outer apply sys.dm_exec_query_plan(ei.PlanHandle) qp
For active sessions, you can obtain the path to the target file from the
sys.dm_xe_session_object_columns
view. However, this path does not include rollover information, which SQL Server appends to the file name when it is
created. You need to transform it by adding a wildcard to the path. Listing 28-15 shows how you can do this with SQL
Server 2012/2014.
Listing 28-15.
Reading the path to the event_file target file in SQL Server 2012/2014
declare
@dataFile nvarchar(260)
-- Get path to event data file
select
@dataFile =
left(column_value,len(column_value ) -
charindex('.',reverse(column_value))) + '*.' +
right(column_value, charindex('.',reverse(column_value))-1)
from
sys.dm_xe_session_object_columns oc join sys.dm_xe_sessions s on
oc.event_session_address = s.address
where
s.name = 'TempDB Spills' and
oc.object_name = 'event_file' and
oc.column_name = 'filename';
You can use a similar approach to obtain the path to the metadata file in SQL Server 2008/2008R2. The path,
however, could be NULL in the
sys.dm_xe_session_object_columns
view if you did not specify it as a parameter of
the target. SQL Server would use the same file name as the event file, replacing the extension to
xem
if this is the case.
