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.

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


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,

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook