Here's the indicators of replication commands taking longer to apply to subscriber:
- check the distribution agent job history. check for
<stats state="2"
Article Level Parameterized Command Stats
*******************************************************************************
2017-12-28 00:01:47.952 <stats state="1" work="4274" idle="0"><reader fetch="12" wait="4274"/><writer write="4274" wait="11"/><sincelaststats elapsedtime="668" work="664" cmds="111" cmdspersec="0.000000"><reader fetch="3" wait="664"/><writer write="664" wait="3"/></sincelaststats><message>Normal events that describe both the reader and writer thread performance.</message></stats>
2017-12-28 00:02:49.529 9 transaction(s) with 9 command(s) were delivered.
2017-12-28 00:03:50.762 10 transaction(s) with 10 command(s) were delivered.
2017-12-28 00:05:00.082 13 transaction(s) with 12 command(s) were delivered.
2017-12-28 00:06:01.771 11 transaction(s) with 12 command(s) were delivered.
2017-12-28 00:06:47.991 <stats state="2" fetch="12" wait="4573" cmds="42123" callstogetreplcmds="9"><sincelaststats elapsedtime="300" fetch="0" wait="299" cmds="42123" cmdspersec="140.000000"/><message>Raised events that occur when an agent's reader thread waits longer than the agent's -messageinterval time. (By default, the time is 60 seconds.) If you notice State 2 events that are recorded for an agent, this indicates that the agent is taking a long time to write changes to the destination.</message></stats>
2017-12-28 00:07:03.974 10 transaction(s) with 9 command(s) were delivered.
2017-12-28 00:08:04.304 11 transaction(s) with 12 command(s) were delivered.
2017-12-28 00:09:15.350 12 transaction(s) with 11 command(s) were delivered.
- Still on distribution agent job history. You can look at your stats on writer and reader thread of distribution agent. (eg.
Time to Apply Cmds (ms) : 4752946
)
************************ STATISTICS SINCE AGENT STARTED ***********************
12-28-2017 00:09:47
Total Run Time (ms) : 4773703 Total Work Time : 4752946
Total Num Trans : 770 Num Trans/Sec : 0.16
Total Num Cmds : 769 Num Cmds/Sec : 0.16
Total Skipped Cmds : 0
Total Idle Time : 0
Writer Thread Stats
Total Number of Retries : 0
Time Spent on Exec : 3211250
Time Spent on Commits (ms): 263488 Commits/Sec : 0.16
Time to Apply Cmds (ms) : 4752946 Cmds/Sec : 0.16
Time Spent on Schema Changes (ms) : 0
Time Cmd Queue Empty (ms) : 12694 Empty Q Waits > 10ms: 451
Total Time Request Blk(ms): 12694
P2P Work Time (ms) : 0 P2P Cmds Skipped : 0
Reader Thread Stats
Calls to Retrieve Cmds : 9
Time to Retrieve Cmds (ms): 4556259 Cmds/Sec : 0.17
Time Cmd Queue Full (ms) : 208913 Full Q Waits > 10ms : 648
So why are the commands are taking longer to apply in subscriber db?
We need to step back a bit and apply basic SQL Server troubleshooting on subscriber db as well. Check for blocking, IO contention, network issue, check for wait stats, triggers, cursors, long running job/transactions, service broker, AGs redo queue, etc. From my experience, the silent killer of replication performance is... triggers. You wouldn't be able to catch it unless you're using profiler or you understand how the data flow works. These could quickly stock up the pending commands to be delivered.
How can I make it do this update immediately?
Options (make sure to stop the distribution agent job before applying any changes):
- Apply the missing command on the subscriber db and let the command flow to subscriber
- Delete the command from distribution db (on
MSrepl_commands
table and make sure you specify xact_seqno
and command_id
). Apply the command manually in subsciber db.
- Skip the missing command. (not recommended)
- Change the
-CommitBatchSize
and -CommitBatchThreshold
parameter to 1:1
just to see if the command flows properly to subscriber db. This is also good for troubleshooting to isolate the issue. Then set it back to default or your desired paramater.
I would highly recommend you to capture a baseline on replication commands stats (Distribution Agent). We can use Perfmon to monitor the following:
- Dist:Delivered Cmds/sec
- Dist:Delivered Trans/sec
- Dist:Delivery Latency
This will give us an idea on how long it will take to apply the commands in subscriber db. The before and after picture of commands stats is important as we can determine how slow is slow or what have changed since last baseline.