info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



T-SQL subtract current row value from previous row value
by BF (Principal Consultant; Architecture; Engineering)
2015-12-23







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))