Using a LAG function to calculate the SQL Server Transactional Replication Rate of Transactions being applied to a Subscriber DB.
The below example shows original data set that is updated every 5 minutes to indicate the total number of Transactions
remaining to be applied to a Subscriber DB. In this case the Subscriber DB is an Azure SQL Database(as-a-service).
MSDN: LAG (Transact-SQL)
Accesses data from a previous row in the same result set without the use of a self-join. LAG provides access to a row at a
given physical offset that comes before the current row.
Solution:
Create Procedure dbo.spaTRXDistroRate
as
--select * from Ops.dbo.Log_Transactional_Replication_TRX_Distro_to_Sub order by LogID Desc;
select LogID, InsertedDate, TRXDistroToSub,
convert(decimal(18,1),(convert(decimal(18,1),coalesce(lag(TRXDistroToSub,1,0) over (order by LogID), 0) - TRXDistroToSub) /5 /60)) as 'TRX/sec'
from Ops.dbo.Log_Transactional_Replication_TRX_Distro_to_Sub
order by LogID Desc;
Image #1: Original DataSet
Image #2: DataSet using LAG function
Resources:
LAG (Transact-SQL)
Extra:
Query that captures the total number of Transactions remaining to be applied to a Subscriber DB in SQL Server Transactional
Replication configuration.
select 'DB01' as 'Instance', count(*) as 'TRX - Dist - Sub' from distribution.dbo.MSrepl_commands with (nolock) where xact_seqno >
(select transaction_timestamp from [AZURELINKEDSERVER].AZUREDB.dbo.MSreplication_subscriptions with (nolock))
|
|
|
|
|
|