Create a new Extended Events session capturing events data for RPC_COMPLETED on the RING_BUFFER target.
The below T-SQL code is for use with Azure SQL Database. A key difference with On-Prem MSSQL vs Azure SQL Database
is the use of the ON SERVER and ON DATABASE clauses. ON DATABASE clause is for Azure SQL Database.
Solution:
-- Step 1: Create Event Session
CREATE EVENT SESSION [CaptureRPCCallsAzure] ON DATABASE
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1) --sql_text is blank and thus cannot filter out sp_reset_connection
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.context_info,sqlserver.database_name,sqlserver.request_id,
sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
)
--WHERE ([sqlserver].[username]=N'')
--ADD EVENT sqlserver.module_end --*sql_text is not present and thus cannot filter out sp_reset_connection
ADD TARGET package0.ring_buffer(SET max_memory=(1024))
WITH (MAX_MEMORY=1024 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
-- Step 2: Alter Event Session & Start it & we give it a WAITFOR as not to consume too much Azure Resources.
If NOT Exists (Select Name FROM sys.dm_xe_database_sessions where name = 'CaptureRPCCallsAzure')
Begin
ALTER EVENT SESSION [CaptureRPCCallsAzure] ON DATABASE STATE = START;
End
select * from sys.dm_xe_database_sessions
select * from sys.dm_xe_database_session_events
select * from sys.dm_xe_database_session_targets
WAITFOR DELAY '00:00:05'
-- Step 3: Generate Traffic here (RPC calls)
-- Step 4: Query the Extended Events Captured Data
/*SELECT XML DATA*/
DECLARE @target_data_xml XML;
SELECT CAST(st.target_data AS XML)
FROM
sys.dm_xe_database_sessions AS se
INNER JOIN sys.dm_xe_database_session_targets AS st ON se.address like st.event_session_address
where se.name = 'CaptureRPCCallsAzure'
/*SELECT COLUMN DATA FROM XML*/
DECLARE @target_data XML;
SELECT @target_data = CAST(st.target_data AS XML)
FROM
sys.dm_xe_database_sessions AS se
INNER JOIN sys.dm_xe_database_session_targets AS st ON se.address like st.event_session_address
where se.name = 'CaptureRPCCallsAzure'
SELECT @target_data.value('(RingBufferTarget/@totalEventsProcessed)[1]', 'int') AS TotalEventsProcessed
SELECT --top 100
n.value('(@name)[1]', 'varchar(50)') AS event_name,
n.value('(@package)[1]', 'varchar(50)') AS package_name,
DATEADD(hh,-5, n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
n.value('(action[@name="username"]/value)[1]', 'varchar(max)') as username,
n.value('(data[@name="duration"]/value)[1]', 'int') /1000 as 'duration (ms)',
n.value('(data[@name="logical_reads"]/value)[1]', 'varchar(15)') as logical_reads,
n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
n.value('(data[@name="writes"]/value)[1]', 'varchar(max)') as writes,
n.value('(data[@name="cpu_time"]/value)[1]', 'int') /1000 as 'cputime (ms)',
n.value('(data[@name="row_count"]/value)[1]', 'varchar(max)') as row_count,
n.value('(data[@name="statement"]/value)[1]', 'varchar(max)') as [statement],
n.value('(data[@name="object_name"]/value)[1]', 'varchar(max)') as [object_name],
n.value('(action[@name="client_app_name"]/value)[1]', 'varchar(max)') as client_app_name
FROM @target_data.nodes('RingBufferTarget/event') AS q(n)
WHERE
not n.value('(data[@name="object_name"]/value)[1]', 'varchar(max)') like '%sp_reset_connection%'
ORDER BY n.value('(@timestamp)[1]', 'datetime2') desc
-- Step 5: Alter the Event Session & Stop it & Drop it
If Exists (Select Name FROM sys.dm_xe_database_sessions where name = 'CaptureRPCCallsAzure')
Begin
ALTER EVENT SESSION CaptureRPCCallsAzure ON DATABASE STATE = STOP;
ALTER EVENT SESSION CaptureRPCCallsAzure ON DATABASE DROP TARGET package0.ring_buffer
DROP EVENT SESSION CaptureRPCCallsAzure ON DATABASE
End
GO
Extended events in SQL Database (Azure)
Find the available extended events, actions, and targets: (execute On_Prem vs Azure SQL Database to compare)
SELECT
o.object_type,
p.name AS [package_name],
o.name AS [db_object_name],
o.description AS [db_obj_description]
FROM
sys.dm_xe_objects AS o
INNER JOIN sys.dm_xe_packages AS p ON p.guid = o.package_guid
WHERE
o.object_type in
(
'action', 'event', 'target'
)
ORDER BY
o.object_type,
p.name,
o.name;
|
|
|
|
|
|