sys.dm_xe_session_targets – Day 61 – One DMV a Day

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

sys.dm_xe_session_targets output

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.

sys.dm_xe_session_targets XML

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

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published. Required fields are marked *