Tutorial of MS SQL Extended Events in 7 T-SQL Steps

SqlNinja Hi, today I would like to show you how you can use Extended Events Sessions on SQL Server 2008 or above. Extended Event Sessions are future of monitoring of the SQL Server. From many years solutions that used SQL Profiler Traces or DMV were not always accurate and fast. DMV queries could be very fast. However it is impossible to get statistics only per one sessions from DMV. And that is why I want to show you how to use Extended Events Sessions and how to filter them only for particular session ID, of course you can add filtering per database ID but I think session ID is better for that propose. You can imagine that you have got 20 threads load generation tool and you want to for each thread open connection and execute code then collect statistics from that execution. In my opinion that is the only possible way to do that. Keep in mind that collecting statistics from large session track XML in memory could be very slow, so consider to split your work. Thanks for reading. Enjoy!

-- 1. drop extended events session
DECLARE @session_id AS VARCHAR(100) = CAST(@@SPID AS VARCHAR(100))
DECLARE @session_drop AS NVARCHAR(MAX) =
'
IF EXISTS(SELECT * FROM sys.server_event_sessions
  WHERE name=''DbTesterTrackingSession'+@session_id+''')
  DROP EVENT SESSION [DbTesterTrackingSession'+@session_id+'] ON SERVER
'
EXEC(@session_drop)
GO

-- 2. create extended events session
DECLARE @session_id AS VARCHAR(100) = CAST(@@SPID AS VARCHAR(100))
DECLARE @session_create AS NVARCHAR(MAX) =
'
CREATE EVENT SESSION [DbTesterTrackingSession'+@session_id+'] ON SERVER 
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1)
    ACTION(sqlserver.database_id,sqlserver.session_id)
	WHERE sqlserver.session_id = '+@session_id+'),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.database_id,sqlserver.session_id)
	WHERE sqlserver.session_id = '+@session_id+'),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.database_id,sqlserver.session_id)
	WHERE sqlserver.session_id = '+@session_id+')
ADD TARGET package0.ring_buffer(SET max_events_limit=(10000))
WITH (MAX_MEMORY=4096 KB,MAX_EVENT_SIZE=0 KB,STARTUP_STATE=OFF)
'
EXEC (@session_create)
GO

-- 3. start extended events
DECLARE @session_id AS VARCHAR(100) = CAST(@@SPID AS VARCHAR(100))
DECLARE @session_start AS NVARCHAR(MAX) =
'
ALTER EVENT SESSION [DbTesterTrackingSession'+@session_id+']
ON SERVER STATE = START
'
EXEC(@session_start)
GO

-- 4. execute something or invoke any t-sql code
SELECT * FROM SYS.OBJECTS WHERE TYPE = 'S'
SELECT * FROM SYS.OBJECTS WHERE TYPE = 'U'
SELECT * FROM SYS.OBJECTS WHERE TYPE = 'IT'
SELECT * FROM SYS.OBJECTS WHERE TYPE = 'SQ'
GO

-- 5. get statistics from extended events session
DECLARE @session_id AS VARCHAR(100) = CAST(@@SPID AS VARCHAR(100))
DECLARE @session_events AS XML = 
(
SELECT
  CAST(target_data AS XML)
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xes
ON xes.address = xet.event_session_address
WHERE
  xes.name = 'DbTesterTrackingSession' + @session_id
  AND xet.target_name = 'ring_buffer'
)

SELECT
  ISNULL(xed.event_data
  .value('(data[@name="statement"]/value)[1]', 'nvarchar(4000)'),'')
   AS [QUERY],
  ISNULL(xed.event_data
  .value('(data[@name="object_type"]/text)[1]', 'nvarchar(200)'), 'RAW')
   AS [QUERY_T],
  ISNULL(xed.event_data
  .value('(data[@name="object_name"]/value)[1]', 'nvarchar(200)'), '')
   AS [QUERY_P],
  CAST(xed.event_data
  .value('(data[@name="duration"]/value)[1]', 'varchar(40)') AS BIGINT)
   AS [DURATION],
  CAST(xed.event_data
  .value('(data[@name="cpu_time"]/value)[1]', 'varchar(40)') AS BIGINT)
   AS [CPU_TIME],
  CAST(xed.event_data
  .value('(data[@name="logical_reads"]/value)[1]', 'varchar(40)') AS BIGINT)
   AS [LOGICAL_READS],
  CAST(xed.event_data
  .value('(data[@name="physical_reads"]/value)[1]', 'varchar(40)') AS BIGINT)
   AS [PHYSICAL_READS],
  CAST(xed.event_data
  .value('(data[@name="writes"]/value)[1]', 'varchar(40)') AS BIGINT)
   AS [WRITES],
  CAST(xed.event_data
  .value('(data[@name="row_count"]/value)[1]', 'varchar(40)') AS BIGINT)
   AS [ROW_COUNT],
  ISNULL(CAST(xed.event_data
  .value('(data[@name="nest_level"]/value)[1]', 'varchar(40)') AS BIGINT), 0)
   AS [NEST_LEVEL],
  CAST(xed.event_data
  .value('(action[@name="database_id"]/value)[1]', 'varchar(40)') AS INT)
   AS [DATABASE_ID],
  CAST(xed.event_data
  .value('(action[@name="session_id"]/value)[1]', 'varchar(40)') AS INT)
   AS [SESSION_ID] 
FROM @session_events.nodes('//RingBufferTarget/event') AS xed (event_data)
GO

-- 6. stop extended events
DECLARE @session_id AS VARCHAR(100) = CAST(@@SPID AS VARCHAR(100))
DECLARE @session_stop AS NVARCHAR(MAX) =
'
ALTER EVENT SESSION [DbTesterTrackingSession'+@session_id+']
ON SERVER STATE = STOP
'
EXEC(@session_stop)
GO

-- 7. drop extended events session
DECLARE @session_id AS VARCHAR(100) = CAST(@@SPID AS VARCHAR(100))
DECLARE @session_drop AS NVARCHAR(MAX) =
'
DROP EVENT SESSION [DbTesterTrackingSession'+@session_id+']
ON SERVER
'
EXEC(@session_drop)
GO

p ;)

Leave a Reply

Your email address will not be published. Required fields are marked *

*