create procedure sys.sp_replmonitorrefreshagentdata
as
begin
set nocount on
declare @retcode int
,@agent_id int
,@agent_id2 int
,@publisher_id int
,@xact_seqno varbinary(16)
,@logreader_latency int
,@publisher_db sysname
,@publication sysname
-- security check
-- Has to be executed from distribution database
if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
begin
raiserror(21482, 16, -1, 'sp_replmonitorrefreshagentdata', 'distribution')
return (1)
end
-- logreader specific
if exists (select agent_id from #tmp_replmonitorrefresh where agent_type = 2)
begin
-- worst_latency in seconds
-- best_latency in seconds
-- avg_latency in seconds
update #tmp_replmonitorrefresh
set worst_latency = latencyview.worst/1000
,best_latency = latencyview.best/1000
,avg_latency = latencyview.average/1000
from
(
select agent_id
,max(delivery_latency) as worst
,min(delivery_latency) as best
,cast(avg(cast(delivery_latency as bigint)) as int) as average
from dbo.MSlogreader_history with (nolock)
where runstatus = 2
group by agent_id
) as latencyview
where #tmp_replmonitorrefresh.agent_id = latencyview.agent_id
and agent_type = 2
-- cur_latency in seconds
update #tmp_replmonitorrefresh
set cur_latency = h.delivery_latency/1000
from
(
dbo.MSlogreader_history as h with (nolock)
join
(
select agent_id, max(timestamp) as maxtimestamp
from dbo.MSlogreader_history with (nolock)
where runstatus = 2
and delivered_commands > 0
group by agent_id
) as latest
on h.agent_id = latest.agent_id
and h.timestamp = latest.maxtimestamp
)
where #tmp_replmonitorrefresh.agent_id = h.agent_id
and agent_type = 2
-- agentstoptime
update #tmp_replmonitorrefresh
set agentstoptime = h.time
from
(
dbo.MSlogreader_history as h with (nolock)
join
(
select agent_id, max(timestamp) as maxtimestamp
from dbo.MSlogreader_history with (nolock)
where (runstatus = 2 or runstatus > 4)
group by agent_id
) as latest
on h.agent_id = latest.agent_id
and h.timestamp = latest.maxtimestamp
)
where #tmp_replmonitorrefresh.agent_id = h.agent_id
and agent_type = 2
end
-- Distribution agent specific
if exists (select agent_id from #tmp_replmonitorrefresh where (agent_type & 3) = 3)
begin
-- retention (same for all logbased and snapshot publications)
-- Use the max_retention value for this distribution db
update #tmp_replmonitorrefresh
set retention = dbs.max_distretention
from msdb.dbo.MSdistributiondbs as dbs
where dbs.name = distdb
and (agent_type & 3) = 3
-- worst_latency in seconds
-- best_latency in seconds
-- avg_latency in seconds
update #tmp_replmonitorrefresh
set worst_latency = latencyview.worst/1000
,best_latency = latencyview.best/1000
,avg_latency = latencyview.average/1000
from
(
select agent_id
,max(delivery_latency) as worst
,min(delivery_latency) as best
,cast(avg(cast(delivery_latency as bigint)) as int) as average
from dbo.MSdistribution_history with (nolock)
where runstatus = 2
group by agent_id
) as latencyview
where #tmp_replmonitorrefresh.agent_id = latencyview.agent_id
and (agent_type & 3) = 3
-- last_distsync (this essentially records the last time
-- some activity happened on the history of the agent)
update #tmp_replmonitorrefresh
set last_distsync = h.time
from
(
dbo.MSdistribution_history as h with (nolock)
join
(
select agent_id, max(timestamp) as maxtimestamp
from dbo.MSdistribution_history with (nolock)
where runstatus in (2,3,4)
group by agent_id
) as latest
on h.agent_id = latest.agent_id
and h.timestamp = latest.maxtimestamp
)
where #tmp_replmonitorrefresh.agent_id = h.agent_id
and (agent_type & 3) = 3
-- agentstoptime
update #tmp_replmonitorrefresh
set agentstoptime = h.time
from
(
dbo.MSdistribution_history as h with (nolock)
join
(
select agent_id, max(timestamp) as maxtimestamp
from dbo.MSdistribution_history with (nolock)
where (runstatus = 2 or runstatus > 4)
group by agent_id
) as latest
on h.agent_id = latest.agent_id
and h.timestamp = latest.maxtimestamp
)
where #tmp_replmonitorrefresh.agent_id = h.agent_id
and (agent_type & 3) = 3
-- cur_latency
-- this value is the ("time of sub commit" - "distribution entry time"/1000
-- basically this is the distrib agent latency in seconds (pre-computed by add hist)
-- if we are looking at a idle (runstatus = 4) entry then it means there are
-- no pending messages and we cannot reliably compute cur_latency. We will set it
-- to zero. This will resolve the cases when some high latency processing happens
-- and then the latency never goes down if there is no further activity (example:
-- Initial Snapshot processing followed by no other activity should not spike the
-- latency for a unduly long time)
update #tmp_replmonitorrefresh
set cur_latency = case when (distlatency.runstatus = 4) then 0 else distlatency.latency/1000 end
from
(
(
select agent_id, max(xact_seqno) as maxseqno, max(time) as maxtime
from dbo.MSdistribution_history with (nolock)
where runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-idle
group by agent_id
) as xactview
join
(
select agent_id, isnull(delivery_latency, 0) as latency, xact_seqno, time, runstatus
from dbo.MSdistribution_history with (nolock)
where runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-idle
) as distlatency
on xactview.agent_id = distlatency.agent_id
and xactview.maxseqno = distlatency.xact_seqno
and xactview.maxtime = distlatency.time
)
where #tmp_replmonitorrefresh.agent_id = distlatency.agent_id
and (agent_type & 3) = 3
-- compute the logreader latency for logbased publications
declare #hcrefreshmonitor cursor local fast_forward for
select publisher_srvid, publisher_db, publication, agent_id
from #tmp_replmonitorrefresh
where publication_type = 0
and (agent_type & 3) = 3
open #hcrefreshmonitor
fetch #hcrefreshmonitor into @publisher_id, @publisher_db, @publication, @agent_id
while (@@fetch_status != -1)
begin
-- initialize
select @agent_id2 = NULL
,@xact_seqno = NULL
,@logreader_latency = NULL
select top 1 @xact_seqno = xact_seqno
from dbo.MSdistribution_history with (nolock)
where agent_id = @agent_id
and runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-idle
order by xact_seqno desc, time desc
-- here we are attempting to find the last transaction in logreader history
-- that is closest to the last transaction delivered to the subscriber.
-- do logreader latency computation only for logbased publications
-- In the event we find a an idle history log, we will set logreader latency to 0
select @agent_id2 = max(id)
from dbo.MSlogreader_agents with (nolock)
where publisher_id = @publisher_id
and publisher_db = @publisher_db
if (@agent_id2 is null)
begin
raiserror('could not find agent entry', 16,1)
return (1)
end
select top 1 @logreader_latency = case when (runstatus = 4) then 0 else delivery_latency/1000 end
from MSlogreader_history with (nolock)
where agent_id = @agent_id2
and xact_seqno >= @xact_seqno
and runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-idle
order by xact_seqno asc, time desc
if @logreader_latency is NULL
begin
-- if we did not have a tran in front then we will use the
-- previous one note the difference in order by clause and range
select top 1 @logreader_latency = case when (runstatus = 4) then 0 else delivery_latency/1000 end
from MSlogreader_history with (nolock)
where agent_id = @agent_id2
and xact_seqno < @xact_seqno
and runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-idle
order by xact_seqno desc, time desc
end
-- add the latency
if @logreader_latency is not NULL
begin
update #tmp_replmonitorrefresh
set cur_latency = cur_latency + @logreader_latency
where agent_id = @agent_id
end
-- fetch next agent
fetch #hcrefreshmonitor into @publisher_id, @publisher_db, @publication, @agent_id
end
close #hcrefreshmonitor
deallocate #hcrefreshmonitor
end
-- Queuereader agent specific
if exists (select agent_id from #tmp_replmonitorrefresh where agent_type = 9)
begin
-- @agentstoptime datetime output
update #tmp_replmonitorrefresh
set agentstoptime = h.time
from
(
dbo.MSqreader_history as h with (nolock)
join
(
select agent_id, max(timestamp) as maxtimestamp
from dbo.MSqreader_history with (nolock)
where (runstatus = 2 or runstatus > 4)
group by agent_id
) as latest
on h.agent_id = latest.agent_id
and h.timestamp = latest.maxtimestamp
)
where #tmp_replmonitorrefresh.agent_id = h.agent_id
and agent_type = 9
end
-- Merge agent specific
if exists (select agent_id from #tmp_replmonitorrefresh where (agent_type & 4) = 4)
begin
update #tmp_replmonitorrefresh
set mergelatestsessionconnectiontype = h.connection_type
,mergelatestsessionrunduration = h.duration
,mergelatestsessionrunspeed=h.delivery_rate
,isagentrunningnow=case when (h.runstatus in (1, 3, 4, 5)) then 1 else 0 end
,last_distsync = h.start_time
from dbo.MSmerge_sessions as h with (nolock), #tmp_replmonitorrefresh
where session_id =
(
select top 1 ms2.session_id
from dbo.MSmerge_sessions ms2 with (nolock)
where ms2.agent_id = h.agent_id
order by ms2.session_id desc
)
and #tmp_replmonitorrefresh.agent_id = h.agent_id
and ((agent_type & 4) = 4)
-- runspeed
update #tmp_replmonitorrefresh
set mergePerformance=
case when (rates.avg_mergerunspeed is not null and rates.avg_mergerunspeed != 0)
then CAST((mergelatestsessionrunspeed*100)/rates.avg_mergerunspeed as int)
else NULL end
from
(
(
select agent_id, connection_type, delivery_rate
from dbo.MSmerge_sessions as ms1 with (nolock)
where (upload_inserts + upload_deletes + upload_updates + download_inserts
+ download_deletes + download_updates) >= 50
and delivery_rate is not null
and session_id = (select top 1 ms2.session_id from dbo.MSmerge_sessions ms2 with (nolock)
where ms1.agent_id = ms2.agent_id
and ms1.connection_type = ms2.connection_type
order by ms2.session_id desc)
) as latest
join
(
select connection_type
,isnull(avg(delivery_rate),0) as avg_mergerunspeed
from dbo.MSmerge_sessions with (nolock)
where delivery_rate is not null
and (upload_inserts + upload_deletes + upload_updates + download_inserts
+ download_deletes + download_updates) >= 50
group by connection_type
having count(*) >= 5 -- compare with min 5 sessions of same conn type and each having
-- replicated at least 50 rows.
) as rates
on latest.connection_type = rates.connection_type
)
where #tmp_replmonitorrefresh.agent_id = latest.agent_id
and mergelatestsessionconnectiontype = latest.connection_type
and ((agent_type & 4) = 4)
end
-- all done
return 0
end
