sys.dm_xe_sessions – Day 59 – One DMV a Day

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

Yesterday while discussing about sys.dm_xe_map_values I have used the below script to show where to use it. Today I will use the same session to talk about sys.dm_xe_sessions. This DMV lists out the current active extended events sessions.

So let us create a session to collect the events when it sees PAGEIOLATCH_SH wait type. This will be easy for me to capture on a low memory laptop.

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] = 66 -- PAGEIOLATCH_SH only
)
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

After starting this session let us see sys.dm_xe_sessions and understand the data this DMV provides.

SELECT name,
	 pending_buffers AS PendingBuf,
	 total_regular_buffers AS regBufCnt,
	 regular_buffer_size AS regBufSize,
	 total_large_buffers AS largeBufCnt,
	 total_buffer_size AS largeBufSize,
	 buffer_policy_desc AS bufPolicy,
	 flag_desc AS flag,
	 dropped_event_count AS dropEventCnt,
	 dropped_buffer_count AS dropBufCnt,
	 blocked_event_fire_time AS blockedTime,
	 largest_event_dropped_size AS largeEventDropSize
FROM sys.dm_xe_sessions

sys.dm_xe_sessions

Pending_buffers – The number of buffers pending on the processing.
Total_regular_buffers – Regular buffers are used by most events. This gives the total number of regular buffers. This is controlled based on regular_buffer_size and MEMORY_PARTITION_MODE.
Regular_buffer_size – The size of the regular buffer in bytes. This can be controlled by MAX_MEMORY option in CREATE EVENT SESSION. Default is 4 MB.
Total_large_buffers – Few events use large buffers which is allocated at event session start. This is based on large_buffer_size
Large_buffer_size – The size of large buffer. This can be controlled by MAX_EVENT_SIZE option.
Total_buffer_size – The total buffer size used to store event data for the session.
Buffer_policy_desc – Specifies how the buffer is handled when it is full.
Flag_desc – Description of flags set on the session.
Dropped_event_count – Events dropped when buffers are full. Value is 0 if the policy is “do not drop events” or “drop full buffer”.
Dropped_buffer_count – Buffers dropped when buffers are full. Value is 0 if the policy is “do not drop events” or “drop event”.
Blocked_event_fire_count – This is the time the event firing is blocked when the buffer is full. Value is 0 if the policy is “drop event” or “drop buffer”.
Largest_event_dropped_size – This is the largest event that did not fit in the buffer. This will help you in deciding the MAX_EVENT_SIZE.

sys.dm_xe_sessions exposes these details. There is lot to more in extended events with each individual cases. The profiling knows no bounds. 🙂 So explore using these DMVs when using extended events.

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 *