Introduction
Replication has been part of SQL Server since the mid-1990s, and despite new features like Change Data Capture (CDC) and Change Tracking, it remains one of the most reliable ways to move data in real time. Transactional replication in particular powers everything from reporting environments and data warehouses to mission-critical distributed systems.
But one question keeps surfacing in production: “Why is replication so slow?” The culprit almost always comes down to how the Log Reader Agent and Distribution Agent are configured. Understanding how they work — and tuning them properly — is key to cutting latency and keeping replication fast and stable.
In this guide, we’ll explore how transactional replication works under the hood, how to tune the agents that drive it, and the best practices for SQL Server 2022. We’ll also walk through a hands-on workshop for those new to replication tuning.
A Brief History of Transactional Replication
SQL Server replication started as a way to copy data between servers for scale and reporting. Early versions were limited and slow, but over time the architecture evolved into a robust three-part system:
- Log Reader Agent – Scans the transaction log of the publisher and pushes committed transactions into the distribution database.
- Distribution Agent – Delivers those commands from the distributor to the subscriber(s).
- Snapshot Agent – Creates the initial schema and data snapshot when replication is first set up.
For transactional replication, the Log Reader and Distribution Agent are where most of the performance challenges — and opportunities — live.
How Transactional Replication Works
Before tuning, it’s worth understanding the flow:
- A transaction is committed in the publisher database.
- The Log Reader Agent scans the transaction log and writes the changes to
MSrepl_transactionsandMSrepl_commandsin the distribution database. - The Distribution Agent reads those commands and applies them to the subscriber.
If either agent falls behind, replication latency spikes. That’s why tuning them is so important.
Step 1 – Optimize the Log Reader Agent
The Log Reader is responsible for getting changes out of the transaction log quickly. If it’s slow, nothing else matters. You can view its status in Replication Monitor under the “Log Reader Agent” tab or by querying:
USE distribution;
SELECT * FROM mslogreader_agents;
SELECT TOP 20 * FROM mslogreader_history ORDER BY time DESC;Recommended Settings
Open the Log Reader Agent profile (Replication Monitor → Agent Profiles) and adjust these key parameters:
| Parameter | Default | Recommended | Purpose |
|---|---|---|---|
| PollingInterval | 5 sec | 1–2 sec | How often the log reader scans the transaction log. Lower is faster. |
| ReadBatchSize | 500 | 5,000–20,000 | Number of log records read per scan. Larger batches reduce overhead. |
| LogScanThreshold | 500,000 | 1,000,000+ | Records scanned before commit. Higher = fewer commits, more throughput. |
| MaxCmdsInTran | 0 (unlimited) | 5,000–10,000 | Caps very large transactions so they don’t block replication. |
Example T-SQL to adjust these parameters via SQL Agent job:
EXEC msdb.dbo.sp_update_jobstep
@job_name = 'LOGREADER - <PublisherName>-<DB>-<Publication>',
@step_id = 1,
@command = '-ReadBatchSize 10000 -PollingInterval 2 -LogScanThreshold 1000000 -MaxCmdsInTran 5000';📌 Tip: If sp_MSget_repl_commands is slow, it often means the distribution tables are bloated or missing proper indexes.
Step 2 – Index and Maintain the Distribution Database
The distribution database is a high-traffic environment. Poor indexing here can destroy performance. Make sure these indexes exist:
USE distribution;
GO
-- Core index for joining commands to transactions
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_MSrepl_commands_xact_seqno_command_id')
CREATE NONCLUSTERED INDEX IX_MSrepl_commands_xact_seqno_command_id
ON dbo.MSrepl_commands (xact_seqno, command_id);
-- Helps filter by article and database
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_MSrepl_commands_pubdb_article_xactseq')
CREATE NONCLUSTERED INDEX IX_MSrepl_commands_pubdb_article_xactseq
ON dbo.MSrepl_commands (publisher_database_id, article_id, xact_seqno);
-- Speeds up lookups in transactions table
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_MSrepl_transactions_xact_seqno')
CREATE NONCLUSTERED INDEX IX_MSrepl_transactions_xact_seqno
ON dbo.MSrepl_transactions (xact_seqno);
-- Optional if multiple publishers use the same distributor
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_MSrepl_transactions_pubdb_xactseq')
CREATE NONCLUSTERED INDEX IX_MSrepl_transactions_pubdb_xactseq
ON dbo.MSrepl_transactions (publisher_database_id, xact_seqno);Also, keep statistics fresh:
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN';And confirm the Distribution Cleanup job runs frequently (every 10–15 minutes) and finishes successfully. Stale commands increase scan times.
Step 3 – Tune the Distribution Agent
Once commands are in the distribution database, the Distribution Agent delivers them to subscribers. If this agent is slow, you’ll see high latency even if the Log Reader is fast.
View its status in Replication Monitor under “Distribution Agent.”
Recommended Settings
| Parameter | Default | Recommended | Purpose |
|---|---|---|---|
| CommitBatchSize | 100 | 500–2000 | How many commands per commit. Larger batches = fewer commits. |
| CommitBatchThreshold | 1000 | 5000–20000 | Commands per batch. Bigger batches improve throughput. |
| SubscriptionStreams | 1 | 4–8 | Number of parallel threads for delivery. Great for large subscribers. |
Example:
EXEC msdb.dbo.sp_update_jobstep
@job_name = 'DISTRIBUTION - <Publication>-<Subscriber>',
@step_id = 1,
@command = '-CommitBatchSize 1000 -CommitBatchThreshold 10000 -SubscriptionStreams 4';📌 Watch: If the subscriber struggles with load, keep SubscriptionStreams lower. Too many threads can overwhelm it.
Step 4 – Avoid Giant Transactions
The single biggest replication killer is a massive transaction — millions of rows in one commit. The Log Reader must read the entire thing before sending anything downstream.
✅ Best practice: Break large batch jobs into smaller transactions.
✅ If possible, schedule heavy ETL or schema changes off-peak.
Check for open transactions:
DBCC OPENTRAN('<YourDB>');
EXEC sys.sp_repltrans; -- Shows undistributed transactionsStep 5 – Monitor for Blocking and Waits
Even a well-tuned replication setup can stall if blocking occurs. Use this to check:
SELECT
r.session_id,
r.wait_type,
r.blocking_session_id,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.database_id = DB_ID('distribution')
AND t.text LIKE '%sp_MSget_repl_commands%';If you see blocking, investigate what’s causing it (cleanup jobs, index rebuilds, ad-hoc queries).
Hands-On Workshop: Tuning Replication in a Lab
For new DBAs, here’s a step-by-step workshop you can do in a dev environment:
- Set up transactional replication between two databases.
- Generate data changes with a script that inserts thousands of rows.
- Observe baseline performance:
sp_replmonitorsubscriptionpendingcmds- Log Reader and Distribution Agent metrics in Replication Monitor
- Apply tuning:
- Adjust Log Reader Agent profile parameters.
- Add distribution indexes.
- Tune Distribution Agent parameters.
- Re-run the data load and compare:
- Commands/sec
- Latency
- Log Reader and Distribution Agent durations
- Experiment with large vs. small transactions to see how replication behaves.
This exercise builds real intuition for how each part of replication contributes to overall performance.
Summary
Transactional replication remains one of SQL Server’s most powerful features, but it’s also one of the most misunderstood. Most performance issues trace back to the Log Reader or Distribution Agent — and those are usually solved with a combination of agent tuning, distribution database indexing, cleanup scheduling, and transaction sizing.
SQL Server 2022 hasn’t changed the fundamentals of replication, but modern hardware and workloads demand more thoughtful tuning than ever before. With the steps above, you’ll drastically reduce latency, stabilize replication, and extend the life of your replication topology.
Final Thoughts
Replication tuning is part art, part science. No two environments are identical, and what works on one system might not be ideal for another. Start with the safe recommendations above, watch your metrics, and adjust gradually. Above all, monitor continuously — replication issues rarely appear suddenly; they build up slowly over time.
A well-tuned replication system isn’t just faster — it’s more predictable, easier to troubleshoot, and far less likely to surprise you in the middle of the night.
References
- Microsoft Docs – Replication Agents
- Microsoft Docs – Optimize Transactional Replication Performance
- Redgate – Troubleshooting Transactional Replication
- Brent Ozar – Tuning Transactional Replication
Discover more from SQLYARD
Subscribe to get the latest posts sent to your email.
