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 the future of monitoring the SQL Server. For many years solutions that used SQL Profiler Traces or DMV were not always accurate and fast. DMV queries could be speedy. However, it is impossible to get statistics only per session from DMV. And that is why I want to show you how to use Extended Events Sessions and filter them only for the particular session ID. Of course, you can add filtering per database ID, but I think a session ID is better for that purpose. You can imagine that you have 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. Remember that collecting statistics from large session track XML in memory could be very slow, so consider splitting 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 😉