Database Reference
In-Depth Information
from
TargetData cross apply
TargetData.Data.nodes('/RingBufferTarget/event') as t(e)
)
select
ei.[Event Time], ei.[Event], ei.SPID, ei.SQL, qp.Query_Plan
from
EventInfo ei
outer apply sys.dm_exec_query_plan(ei.PlanHandle) qp
If you forced a
tempdb
spill with the code from Listings 3-8, 3-9, and 3-10 in Chapter 3, you would see results
similar to what is shown in Figure
28-16
.
Figure 28-16.
Examining
ring_buffer
target data
Working with event_file and asynchronous_file_target Targets
The
sys.fn_xe_file_target_read_file
table-valued function allows you to read the content of the
asynchronous_file_target
and
event_file
targets.
Similar to SQL Traces, Extended Events file-based targets can generate multiple rollover files. You can read data
from an individual file by specifying the exact file name in the first parameter of the function,
@path
. Alternatively, you
can read data from all of the files by using
@path
with wildcards.
The SQL Server 2008/2008R2
asynchronous_file_target
creates another file type called a
metadata file
. You
should provide the path to this file as the second parameter of the function,
@mdpath
. Though SQL Server 2012/2014
does not use metadata files, this function still has such a parameter for backwards compatibility reasons. You can use
NULL
instead.
Finally, the third and fourth parameters allow you to specify the starting point to read. The third parameter,
@initial_file_name
, is the first file to read. The fourth parameter,
@initial_offset
, is the starting offset in the file.
This function skips all of the data from the file up to the offset value. Both the file name and offsets are included in the
result set, which allows you to implement the code that reads only the newly collected data.
Listing 28-14 illustrates how you can read data from the
event_file
target generated by a
TempDB Spills
session
in SQL Server 2012.
Listing 28-14.
Reading data from the event_file target
;with TargetData(Data, File_Name, File_Offset)
as
(
select convert(xml,event_data) as Data, file_name, file_offset
from
sys.fn_xe_file_target_read_file
('c:\extevents\TempDB_Spiils*.xel', null, null, null)
)
,EventInfo([Event Time], [Event], SPID, [SQL], PlanHandle
,File_Name, File_Offset)
