Solution:
Create Extended Events on Replication Subscriber:
DROP EVENT SESSION [Replica RPC Completed] ON SERVER
GO
--Replication Subscriber - RPC Completed
Create Event Session [Replica RPC Completed] On Server
Add Event sqlserver.rpc_completed(
Action(sqlserver.database_name, sqlserver.nt_username, sqlserver.session_id, sqlserver.username)
--I like to think of these actions as highly efficient internal functions. I am talking about the functions like DB_NAME(), DB_ID(), @@SERVERNAME.
--Think of how quickly these execute within SQL Server. Not only are they quick, but they, in essence, attach additional data to the payload (your query results).
Where
([sqlserver].[database_name]=N'Sales')
AND
(
[sqlserver].[like_i_sql_unicode_string]([statement],N'%sp_MSdel_%') --
OR [sqlserver].[like_i_sql_unicode_string]([statement],N'%sp_MSins_%')
OR [sqlserver].[like_i_sql_unicode_string]([statement],N'%sp_MSupd_%'))
)
ADD TARGET package0.ring_buffer
(SET max_events_limit = (1000), max_memory = (5120)) --5MB
WITH
(
MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = ON, STARTUP_STATE = ON
);
--max_memory The maximum amount of memory in kilobytes (KB) to use. Existing events are dropped based on the limit that is first reached: max_event_limit or max_memory.
--max_event_limit The maximum number of events kept in the ring_buffer. Existing events are dropped based on the limit that is first reached: max_event_limit or max_memory. Default = 1000.
Go
Alter Event Session [Replica RPC completed] On Server State = Start
Go
Query Event Data:
SELECT
Ring_Buffer_Total_Event_Count, --Total Events since the Event Session Started
Event_Node_Count, --Total Events currently in the Ring Buffer
Ring_Buffer_Total_Event_Count - event_node_count AS Events_Not_in_XML
FROM
( SELECT
target_data.value('(RingBufferTarget/@eventCount)[1]', 'int') AS Ring_Buffer_Total_Event_Count,
target_data.value('count(RingBufferTarget/event)', 'int') as event_node_count
FROM
(SELECT CAST(target_data AS XML) AS target_data
FROM sys.dm_xe_sessions as s
INNER JOIN sys.dm_xe_session_targets AS st ON s.address = st.event_session_address
WHERE s.name = N'Replica RPC Completed'
AND st.target_name = N'ring_buffer') AS n ) AS t;
SELECT
target_data.value('(RingBufferTarget/@memoryUsed)[1]', 'int') AS Buffer_Memory_Used_Bytes,
ROUND(target_data.value('(RingBufferTarget/@memoryUsed)[1]', 'int')/1024., 1) AS Buffer_Memory_Used_KB,
ROUND(target_data.value('(RingBufferTarget/@memoryUsed)[1]', 'int')/1024/1024., 1) AS Buffer_Memory_Used_MB,
DATALENGTH(target_data) AS xml_length_bytes,
ROUND(DATALENGTH(target_data)/1024., 1) AS xml_length_kb,
ROUND(DATALENGTH(target_data)/1024./1024,1) AS xml_length_MB
FROM (
SELECT CAST(target_data AS XML) AS target_data
FROM sys.dm_xe_sessions as s
INNER JOIN sys.dm_xe_session_targets AS st ON s.address = st.event_session_address
WHERE s.name = N'Replica RPC Completed'
AND st.target_name = N'ring_buffer') as tab(target_data)
IF OBJECT_ID('tempdb..#capture_repl') IS NOT NULL DROP TABLE #capture_repl
GO
SELECT CAST(target_data as xml) AS targetdata
INTO #capture_repl
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xes ON xes.address = xet.event_session_address
WHERE xes.name = 'Replica RPC Completed' AND xet.target_name = 'ring_buffer';
SELECT
'Ring_Buffer' as 'Target',
xed.event_data.value('(@timestamp)[1]', 'datetime2') AS [Timestamp],
xed.event_data.value('(action[@name="database_name"]/value)[1]', 'varchar(100)') AS [Database Name],
xed.event_data.value('(data[@name="object_name"]/value)[1]', 'varchar(100)') AS [Object Name],
xed.event_data.value('(data[@name="cpu_time"]/value)[1]', 'int') AS [Cpu_Time_µs],
xed.event_data.value('(data[@name="duration"]/value)[1]', 'int') AS [Duration_µs],
xed.event_data.value('(data[@name="physical_reads"]/value)[1]', 'int') AS [Physical Reads],
xed.event_data.value('(data[@name="logical_reads"]/value)[1]', 'int') AS [Logical Reads],
xed.event_data.value('(data[@name="writes"]/value)[1]', 'int') AS [Writes],
xed.event_data.value('(action[@name="username"]/value)[1]', 'varchar(100)') AS [Username],
xed.event_data.value('(action[@name="nt_username"]/value)[1]', 'varchar(100)') AS [NT_Username],
xed.event_data.value('(data[@name="statement"]/value)[1]', 'varchar(250)') AS [Statement]
FROM #capture_repl
CROSS APPLY targetdata.nodes('//RingBufferTarget/event') AS xed (event_data)
Order By 1 ASC;
/*
SELECT name, target_name, CAST(xet.target_data AS xml)
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe ON xe.address = xet.event_session_address
WHERE xe.name = 'Replica RPC Completed' and target_name = 'ring_buffer';
*/
/* Replication Snapshot Logging
Create Event Session [Replica Bulk Insert] on Server
Add Event sqlserver.databases_bulk_copy_rows (
Action(sqlserver.database_name, sqlserver.nt_username,
Sqlserver.session_id, sqlserver.sql_text)
Where ([sqlserver].[database_name] = N'ReplDestination')),
Add Event sqlserver.databases_bulk_insert_rows(
Action(sqlserver.database_name, sqlserver.nt_username, sqlserver.session_id,
sqlserver.sql_text)
Where ([sqlserver].[database_name] = N'AdventureWorks_Replica'))
Add Target package0.event_file(Set filename= N'Replica Bulk Insert')
Go
*/
--Select * from sys.dm_xe_object_columns where name like 'cpu_time%' order by [name] asc, [object_name] asc
/*
--List all Actions
SELECT xp.name AS PackageName
, xo.name AS ActionName
, xo.description AS ObjDescription
, xo.capabilities_desc
FROM sys.dm_xe_packages AS xp
INNER JOIN sys.dm_xe_objects AS xo
ON xp.guid = xo.package_guid
WHERE ( xp.capabilities IS NULL
OR xp.capabilities & 1 = 0
)
AND ( xo.capabilities IS NULL
OR xo.capabilities & 1 = 0
)
AND xo.object_type = 'action'
AND xp.name = 'sqlserver'
ORDER BY ActionName, PackageName;
*/
|
|
|
|
|
|