SQL-Articles
On Premise and Cloud Database Knowledge Base

Measure SQL Server Replication Latency

This script can be used to measure Latency in your Replication Subsystem. This script does not use TRACER TOKENS and there would be no negative impact to the system’s performance.

USE [distribution] 

GO

/****** Object: StoredProcedure [dbo].[usp_latencymonitor] Script Date: 01/20/2010 22:31:24 ******/

SET ANSI_NULLS ON 

GO

SET UOTED_IDENTIFIER ON 

GO

create procedure [dbo].[usp_latencymonitor] 

as

begin

--Written by Sugeshkumar Rajendran Dated 01/20/2010

create table #latency_monitor(pubdb varchar(100),publication varchar(100),subdb varchar(100),distagent varchar(300),distcntrvalue int NULL,logagent varchar(300),logcntrvalue int NULL) 

insert into #latency_monitor(pubdb,publication,subdb,distagent,logagent) 

select a.publisher_db,a.publication,a.subscriber_db,  a.name as 'DistAgent',b.name as 'LogreaderAgent' 

from dbo.MSdistribution_agents a, dbo.MSlogreader_agents b 

where a.publisher_db=b.publisher_db 

--select * from #latency_monitor

declare @distagent varchar(300) 

declare @logagent varchar(300) 

declare @distcntr int 

declare @logcntr int 

DECLARE replmonitor CURSOR FOR 

select a.name as 'DistAgent',b.name as 'LogreaderAgent' 

from dbo.MSdistribution_agents a, dbo.MSlogreader_agents b 

where a.publisher_db=b.publisher_db 

OPEN replmonitor 

FETCH

NEXT FROM replmonitor into @distagent,@logagent 

WHILE @@FETCH_STATUS = 0 

BEGIN

select @distcntr=cntr_value from sys.dm_os_performance_counters 

where 

object_name like '%Replication%' 

and

counter_name like '%Latency%' and ltrim(rtrim(instance_name)) = ltrim(rtrim(@distagent)) 

select @logcntr=cntr_value from sys.dm_os_performance_counters 

where object_name like '%Replication%' 

and counter_name like '%Latency%' and ltrim(rtrim(instance_name)) = ltrim(rtrim(@logagent)) 

update #latency_monitor set logcntrvalue = @logcntr where ltrim(rtrim(logagent)) = ltrim(rtrim(@logagent)) 

update #latency_monitor set distcntrvalue = @distcntr where ltrim(rtrim(distagent)) = ltrim(rtrim(@distagent)) 

FETCH

NEXT FROM replmonitor intO @distagent,@logagent 

END

CLOSE replmonitor 

DEALLOCATE replmonitor 

select * from #latency_monitor 

drop table #latency_monitor 

end

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*