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
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
