Transcript Latency_Tran_Repl.ppt

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
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.
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, Parikshit Savjani, Abhay Chaudhary,
Raunak Jhawar, Ahmed Osama, Amit Karkhanis,
Vasudev Menon, Ritesh Medhe, Rakesh Mishra, Piyush
Bajaj, Rahul Sharma, Satnam Singh, Bhagwan Singh
and more …..
If you want to contribute, email us at
[email protected] or visit Join Us section
on the website
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
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 .
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 .
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 ?
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 ?
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
Finding threads involved in latency –option 2
Stop the Distributor agent and check the job history .
Finding threads involved in latency –option 2 ……
Stop the Distributor agent and check the job history .
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
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
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 )
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
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
Connect with Abhay Chaudhary
https://www.facebook.com/1978abhay
What’s next
Resolving Latency issues
Continue your learning…
Be a member - www.SQLServerGeeks.com
www.FaceBook.com/SQLServerGeeks
Twitter @SQLServerGeeks
Presentation & Scripts uploaded on
www.SQLServerGeeks.com/files
Thank you 
for suggestions, please email at [email protected]