Transactional Replication – Understanding Latency By Abhay Chaudhary

1. Transactional Replication – Understanding Latency By Abhay Chaudhary Database Architect (IBM India Pvt.Ltd.) MCTS\MCITP : SQL Server 2005 , SQL Server 2008, SQL Server 2008 BI ,MCTS: SQL Server 2008 DB Developer , OCP 9i

2. Abhay Chaudhary 9+ years of Database Management experience . Ex- Microsoft PSS (2007-2009). Blogging on SQL Server articles since 2008. Assist SQL Server technical community through various forums.

3. About SQLServerGeeks.com • One of the fastest growing SQL Server communities • Blogs, Articles, Podcasts, In-person events, webcasts • Millions of page views • People behind SQLServerGeeks: Amit Bansal, Sarabpreet Singh, ParikshitSavjani, Abhay Chaudhary, RaunakJhawar, Ahmed Osama, Amit Karkhanis, VasudevMenon, Ritesh Medhe, Rakesh Mishra, Piyush Bajaj, Rahul Sharma, Satnam Singh, Bhagwan Singh and more ….. • If you want to contribute, email us at admin@SQLServerGeeks.com or visit Join Us section on the website

4. Agenda Latency Where is the latency ? How much is the latency ? Few questions you need to answer Finding threads involved in latency –option 1 Finding threads involved in latency –option 2 (prefer) Reasons for Log Reader-Reader thread latency Reasons for Log Reader-writer thread latency Reasons for Dist Agent-Reader thread latency Reasons for Dist Agent-Writer thread latency References

5. Latency • Slowness of transaction delivery by either Log reader agent or Distribution agent or both . • For log reader agent its slowness in picking up the transactions that are marked for replication in Publisher log. • For Distribution agent it’s the slowness in picking up the transactions from the distribution database .

6. Where is the latency ? If all the subscriptions of one publisher are affected then the latency is from publisher to distributor . If a few subscriptions of one publisher are affected then the latency is from distributor to subscriber .

7. How much is the latency ? Query MSLogreader_history and MSDistribution_history system table in Distribution agents. Example : select * from MSlogreader_history where order by time desc select * from MSdistribution_history where order by time desc What's next ?

8. Few questions you need to answer Is the latency in reading the publisher log ? Is the latency in writing the data to the distributor ? Is the latency reading the data from distributor database ? Is the latency in writing the data to the subscriber tables ?

9. Finding threads involved in latency –option 1 Check the latest comments section of MSlogreader_history and MSdistribution_history tables in distribution DB. Log reader Check the latest comments section of MSDistribution_history and MSdistribution_history tables in distribution DB. Distribution agent State 1 = Normal State 2 = Reader Thread waits for Writer Thread State 3 = Writer thread waits for Reader thread

10. Finding threads involved in latency –option 2 Stop the Distributor agent and check the job history .

11. Finding threads involved in latency –option 2 …… Stop the Distributor agent and check the job history .

12. Reasons for Log Reader -Reader thread latency • Most common reasons are : • Big T-log size due to maintenance . Very likely • large Batch(s) of replicated transactions . Very likely • Storage latency (Slow Read IO) • High CPU \IO or load on the Server in due course of time .likely

13. Reasons for Log Reader –Writer thread latency • Most common reasons are : • Blocking on MSRepl_Transactions and MSRepl_Commands tables. • High IO\CPU load on the Dist server .likely • No \less maintenance on MSRepl_Transactions and MSRepl_Commands tables. Very likely • Storage Latency (Slow writes) • Network Latency likely

14. Reasons for Dist Agent -Reader thread latency • Most common reasons are : • Large Batch of transactions Very likely • Lack of maintenance (Fragmentation and stale stats ) Very likely • Keeping the publication snapshots likely • Long execution of cleanup job causing latency  likely • Slow Storage (Read latency )

15. Reasons for Dist Agent –Writer thread latency • Most common reasons are : • Resource consuming queries running already Very likely • Lack of maintenance Very likely • Network issues • Storage issues • Too many indexes Very likely • Blocking Very likely • High CPU Very likely

16. References How Transactional Replication Works http://msdn.microsoft.com/en-us/library/ms151706.aspx Physical Architecture (Replication) http://msdn.microsoft.com/en-us/library/bb500345.aspx Transactional Replication Overview http://msdn.microsoft.com/en-us/library/ms151176.aspx sp_replcmds (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms186983.aspx sp_repldone (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms173775.aspx Solution Design Considerations (Replication) http://msdn.microsoft.com/en-us/library/ms152508.aspx Designing and Implementing (Replication) http://msdn.microsoft.com/en-us/library/ms151847.aspx Replication Agents Overview http://msdn.microsoft.com/en-us/library/ms152501.aspx

17. Q & A

18. Connect with Abhay Chaudhary https://www.facebook.com/1978abhay

19. What’s next Resolving Latency issues

20. Continue your learning… Be a member - www.SQLServerGeeks.com www.FaceBook.com/SQLServerGeeks Twitter @SQLServerGeeks Presentation & Scripts uploaded on www.SQLServerGeeks.com/files

21. Thank you  for suggestions, please email at admin@SQLServerGeeks.com