Get MSdistribution_history cmdspersec rate:
Ideally, this is logged in a separate table to keep weeks/months of historical records for trend analysis.
Select
t1.time,
t1.agent_id as 'AgentID',
t2.name as 'Agent Name',
comments
from Distribution.dbo.MSdistribution_history t1 with (nolock)
inner join Distribution.dbo.MSdistribution_agents t2 with (nolock)
on t1.agent_id = t2.id
--and t1.agent_id = 21
where [time] > getdate() - 1
and comments like '%cmdspersec%'
order by time desc
Also see: Get cmdspersecrate extracted
Cmdspersec Delivery Rate differences:
Distribution Agent History Recent Logged:
<stats state="2" fetch="1558" wait="93799" cmds="7670" callstogetreplcmds="83679"><sincelaststats elapsedtime="300" fetch="1" wait="298" cmds="7670" cmdspersec="25.000000"/></stats>
cmdspersec = 25 (7670/300)
Distribution Agent History Past Logged:
<stats state="2" fetch="13" wait="286" cmds="764209" callstogetreplcmds="1"><sincelaststats elapsedtime="300" fetch="13" wait="286" cmds="764209" cmdspersec="2547.000000"/></stats>
cmdspersec = 2547 (764209/300)
Commands Per Second delivery rate had dropped from 2547 per second to 25 per second !!! The result is number of undistributed commands may backlog and create a queue especially in a busy OLTP environment.
Solution:
Re-start the Distribution Agent SQL Job for the Agent that has the cmdspersec delivery rate change. We immediately saw the cmdspersec return to 2500 per second and the queue cleared for undistributed commands.
Replication Monitor:
Resources:
TROUBLESHOOTING TRANSACTIONAL REPLICATION LATENCY USING AGENT STATISTICS
|
|
|
|
|
|