sys.dm_xe_session_events – Day 62 – One DMV a Day

Hello Geeks and welcome to the Day 62 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 sys.dm_xe_session_events. All the session related DMVs help you see what is the current active sessions’ configuration and details. We have seen sys.dm_xe_sessions giving session details. Sys.dm_xe_session_object_columns gives configurable column values. Sys.dm_xe_session_targets gives session target details.

Sys.dm_xe_session_events gives what events when fired are captured in this session. To setup a session I will use similar script I used in past posts. I will be adding a predicate to single event and you will know why.

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])
    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_events.

SELECT xs.name,
	 xse.event_name,
	 xse.event_predicate
FROM sys.dm_xe_session_events xse
INNER JOIN sys.dm_xe_sessions xs
ON xse.event_session_address = xs.address
WHERE xs.name = 'SSG_WaitSession_OneDMVaDay'

sys.dm_xe_session_events output

From the output of sys.dm_xe_session_events you can see the filtered events in the session. The event_predicate column tells if there is any predicate on the event. In the above case we had filtered the event on PAGEIOLATCH_UP. This is in the XML format and see my predicate below.

sys.dm_xe_session_events XML

So now you know how to use sys.dm_xe_session_events to check events and filters on events of a session. 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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

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 *