Extended Events - Pro SQL Server Internals - page 559

Listing 28-16 shows how to obtain paths for both event and metadata files in SQL Server 2008/2008R2.
Listing 28-16. Reading the path to asynchronous_file_target target event and metadata files in SQL Server
2008/2008R2
declare
@dataFile nvarchar(512)
,@metaFile nvarchar(512)
-- 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 = 'asynchronous_file_target' and
oc.column_name = 'filename';
-- Get path to metadata file
select
@metaFile =
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 = 'asynchronous_file_target' and
oc.column_name = 'metadatafile';
if @metaFile is null
select @metaFile =
left(@dataFile,len(@dataFile) -
charindex('*',reverse(@dataFile))) + '*.xem';
Working with event_counter and synchronous_event_counter Targets
The synchronous_event_counter (SQL Server 2008/2008R2) and event_counter (SQL Server 2012/2014) targets
allow you to count the number of occurrences of specific events. Both targets provide data in a very simple XML
format, which can be accessed through the event_data column in the sys.dm_xe_session_targets view.
Listing 28-17 creates an event session that counts the number of reads from and writes to tempdb files in
SQL Server 2012/2014. That same code will work in SQL Server 2008/2008R2 if you replace the target name to
synchronous_event_counter .