Transactional Pull Replication – Perfroamnce Issue with Distribution | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Transactional Pull Replication – Perfroamnce Issue with Distribution

Hi,
I have a pull replication. The distributor and subscriber are on one box.
For the first 2 days, I was getting great perfroamnce in Distributon. After that the cmds/sec has droped to 20cmds/sec to 80 cmds/sec.
Earlier it used to run at 400 cmds/sec.
Currently the log reader works well.
I am seeing the issue with Distribution from distributot to subscriber. Is there any thing that can be done here.
The number of transactions, cmds, etc is nearly the same on a day to day basis.
Let me know your inputs.
Thanks.

Is the distribution database getting bigger? Run the Cleanup agent and see if it clears up some transactions that have already been applied. The distribution agent reads the transactions from the distribution db and applies them on the subscriber. If you look through the tables, there is only one index (or may be two) on those tables. Perhaps cleaning up some old transactions might help. Also, check if your subscriber needs any maintenance.

Questions:1) How did you determine that distributor is bottleneck? How bad is replication latency?
2) What is resource utilization on the subscriber system?3) How much data is being pulled? Is there any baseline number that lead you to conclusion that Distributor is now ill and unable to perform better?
4) Where distributor data files are placed? Shared with user and system data files or separated?
5) While poor performance on distributor database what waittypes do you see on a regular basis in sysprocesses? Suggestions: First, determine the root cause of this problem. Capture Replication perfmon counters during peak periods along with Memory,CPU and disk counters that will help you to ensure that system resources are not bottleneck. Once that is confirmed find out statements (see below) waiting to be replicated to check for poor query performance, verify that indexes on publisher and subscriber tables are similar and subscriber indexes are not fragmented.
1) Run this command to find out the number of commands waiting to go to subscriber;
SELECT AGENT_ID FROM MSDISTRIBUTION_STATUSGROUP BY AGENT_ID HAVING SUM(UNDELIVCMDINDISTDB)>100ORDER BY SUM(UNDELIVCMDINDISTDB) DESC
2) Find out which commands are waiting to be loaded. SELECT TRANSACTION_TIMESTAMPP FROM SUBSCRIBERDATABASENAME..MSREPLICATION_SUBSCRIPTIONS
3) Then run this in Distribution database (Replace value with one returned from above) SP_BROWSEREPLCMDS @xact_seqno_start=’ ‘

If your clean up agent jobs are running on default values/timings just check if there is any blocking caused by them.

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |