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'

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.

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