Hello Geeks and welcome to the Day 61 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.
We are covering the extended event DMVs since last one week. Today I will be covering sys.dm_xe_session_targets. If you are following this series you would have already used the scripts I used in last two blogs to create an extended events session. If not see sys.dm_xe_sessions and sys.dm_xe_session_object_columns.
Sys.dm_xe_session_targets is a straight forward DMV. This provides the target information used for each session. The output column target_data is an xml. This is because each target has different meta data. So let us see the output for all session targets running on my instance.
SELECT xs.name, xst.target_name, xst.execution_count, xst.execution_duration_ms, xst.target_data FROM sys.dm_xe_session_targets xst INNER JOIN sys.dm_xe_sessions xs ON xst.event_session_address = xs.address
The output shows how many times the target got executed (execution_count) and how much time did it execute (execution_duration_ms). The target_data column has the information of the target. For example the event_file target of system_health session has the file location of my xel file.
Now you know how to get target information from sys.dm_xe_session_targets. Tomorrow I will be covering another extended events related DMV. So stay tuned. Till then
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
