Database Reference
In-Depth Information
Listing 28-17.
Creating the session that counts number of reads and writes to/from tempdb files
create event session [FileStats]
on server
add event
sqlserver.file_read_completed
(
where(sqlserver.database_id = 2)
),
add event
sqlserver.file_write_completed
(
where(sqlserver.database_id = 2)
)
add target
package0.event_counter
with
(
event_retention_mode=allow_single_event_loss
,max_dispatch_latency=5 seconds
);
After you start the session, you can examine the data collected with the code shown in Listing 28-18. You should
change the target name to
synchronous_event_counter
in the
TargetData
CTE if you are working with SQL Server
2008/2008R2.
Listing 28-18.
Examining session 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
where s.name = 'FileStats' and st.target_name = 'event_counter'
)
,EventInfo([Event],[Count])
as
(
select
t.e.value('@name','sysname') as [Event]
,t.e.value('@count','bigint') as [Count]
from
TargetData cross apply
TargetData.Data.nodes
('/CounterTarget/Packages/Package[@name="sqlserver"]/Event')
as t(e)
)
select [Event], [Count]
from EventInfo;
