Create a new Extended Events session capturing events data for RPC_COMPLETED and SQL_BATCH_COMPLETED on the RING_BUFFER target.
The below T-SQL code is for use with On-Prem MSSQL. The key difference with On-Prem vs Azure SQL Database is the use of the ON SERVER
clause and the ON DATABASE clause. ON DATABASE clause is for Azure SQL Database.
Solution:
-- Create Event Session
-- Events: sqlserver.rpc_completed and sqlserver.sql_batch_completed
CREATE EVENT SESSION [CaptureDBTraffic] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_batch_completed
ADD TARGET package0.ring_buffer(SET max_memory=(102400))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
-- Alter Event Session & Start it.
ALTER EVENT SESSION [CaptureDBTraffic] ON SERVER STATE=START;
GO
--Confirm Event created (target_data field holds captured data)
select * from sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
ON t.event_session_address = s.address
WHERE s.name = N'CaptureDBTraffic'
AND t.target_name = N'ring_buffer';
--Generate Traffic here (RPC calls, Batch Calls)
-- Wait for Event buffering to Target (~5-10 seconds)
-- Query the Extended Events Captured Data
-- Create XML variable to hold Target Data
DECLARE @target_data XML;
SELECT @target_data = CAST(target_data AS XML)
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
ON t.event_session_address = s.address
WHERE s.name = N'CaptureDBTraffic'
AND t.target_name = N'ring_buffer';
--***Note: May need to replace "=" with "like" above in the ON clause
-- Query XML variable to get Total Event Data Processed
SELECT @target_data.value('(RingBufferTarget/@totalEventsProcessed)[1]', 'int') AS TotalEventsProcessed
-- Query XML variable to get Event Data
SELECT
n.value('(@name)[1]', 'varchar(50)') AS event_name,
n.value('(@package)[1]', 'varchar(50)') AS package_name,
n.value('(@id)[1]', 'int') AS id,
n.value('(@version)[1]', 'int') AS version,
DATEADD(hh,
DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cputime,
n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
n.value('(data[@name="logical_reads"]/value)[1]', 'varchar(15)') as logical_reads,
n.value('(data[@name="writes"]/value)[1]', 'varchar(max)') as writes,
n.value('(data[@name="reads"]/value)[1]', 'varchar(max)') as reads,
n.value('(data[@name="row_count"]/value)[1]', 'varchar(max)') as row_count,
n.value('(data[@name="result"]/value)[1]', 'varchar(max)') as result,
n.value('(data[@name="batch_text"]/value)[1]', 'varchar(max)') as batch_text
FROM @target_data.nodes('RingBufferTarget/event') AS q(n);
GO
-- Alter the Event Session & Stop it.
ALTER EVENT SESSION [CaptureDBTraffic] ON SERVER STATE=STOP;
GO
-- Drop the Event to stop collection
ALTER EVENT SESSION [CaptureDBTraffic] ON SERVER DROP EVENT sqlserver.rpc_completed;
ALTER EVENT SESSION [CaptureDBTraffic] ON SERVER DROP EVENT sqlserver.sql_batch_completed;
GO
-- Drop the Event Session
DROP EVENT SESSION [CaptureDBTraffic] ON SERVER;
GO
|
|
|
|
|
|