sys.dm_xe_session_event_actions – Day 63 – One DMV a Day

Hello Geeks and welcome to the Day 63 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.

Today I will be covering the last DMV in extended events category. Sys.dm_xe_session_event_actions returns one row each for an action bound to an event. I have talked about what action is in sys.dm_xe_objects. Actions are the ones which are executed when an event is fired.

Sys.dm_xe_session_event_actions provides the list of actions for each event in the session. Let us create the below session. This has two action objects for the event wait_info.

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'SSG_WaitSession_OneDMVaDay')
    DROP EVENT SESSION SSG_WaitSession_OneDMVaDay ON SERVER
GO

CREATE EVENT SESSION SSG_WaitSession_OneDMVaDay ON SERVER
ADD EVENT [sqlos].[wait_info]
(
    ACTION ([package0].[callstack], [sqlos].[scheduler_id])
    WHERE [wait_type] = 67 -- PAGEIOLATCH_UP only
),
ADD EVENT sqlserver.sp_statement_completed 
(
	 SET collect_object_name = (1), 
	 collect_statement = (0)
),
ADD EVENT sqlserver.sql_batch_completed

ADD TARGET [package0].[asynchronous_bucketizer]
(
    SET filtering_event_name = N'sqlos.wait_info',
    source_type = 1,
    source = N'package0.callstack'
)
WITH
(
    MAX_MEMORY = 50 MB,
    MAX_DISPATCH_LATENCY = 5 SECONDS)
GO

ALTER EVENT SESSION SSG_WaitSession_OneDMVaDay ON SERVER STATE = START;
GO

Now let us query sys.dm_xe_session_event_actions.

SELECT xs.name,
	 xsea.action_name,
	 xsea.event_name
FROM sys.dm_xe_session_event_actions xsea
INNER JOIN sys.dm_xe_sessions xs
ON xsea.event_session_address = xs.address
WHERE xs.name = 'SSG_WaitSession_OneDMVaDay'

sys.dm_xe_session_event_actions

It is quite simple and evident from the output of sys.dm_xe_session_event_actions that it shows the actions associated with the event. So now you know how to use sys.dm_xe_session_event_actions.

This brings to the end of extended events DMVs. Tomorrow I will be covering another 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 *