Transactional Replication – Understanding Latency - PowerPoint Presentation

Slide1

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 Slide2

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.

Abhay ChaudharySlide3

About SQLServerGeeks.com

One of the fastest growing SQL Server communitiesBlogs, 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

admin@SQLServerGeeks.com

or visit Join Us section on the websiteSlide4

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 latencyReasons for Dist Agent-Reader thread latencyReasons for Dist Agent-Writer thread latencyReferences

AgendaSlide5

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 .

LatencySlide6

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 .

Slide7

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 ?

How much is the latency ? Slide8

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 ?

Few questions you need to answerSlide9

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 1Slide10

Stop the Distributor agent and check the job history .

Finding threads involved in latency –option 2Slide11

Stop the Distributor agent and check the job history .

Finding threads involved in latency –option 2 ……Slide12

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 . likelyReasons for Log Reader -Reader thread latencySlide13

Most common reasons are :

Blocking on MSRepl_Transactions and MSRepl_Commands

tables.

High IO\CPU load on the Dist server

.

likelyNo \less maintenance on MSRepl_Transactions and MSRepl_Commands tables. Very likely

Storage Latency (Slow writes)

Network Latency

likely

Reasons for Log Reader –Writer thread latencySlide14

Most common reasons are :

Large Batch of transactions Very likely

Lack of maintenance (Fragmentation and stale stats

)

Very likely

Keeping the publication snapshots likelyLong execution of cleanup job causing latency  likelySlow Storage (Read latency )

Reasons for Dist Agent -Reader thread latencySlide15

Most common reasons are :

Resource consuming queries running already Very likely

Lack of maintenance

Very likely

Network issues

Storage issues Too many indexes Very likelyBlocking Very likely

High CPU

Very likely

Reasons for Dist Agent –Writer thread latencySlide16

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 Overviewhttp://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

ReferencesSlide17

Q & ASlide18

Connect with Abhay Chaudhary

https://www.facebook.com/1978abhaySlide19

Resolving Latency issues

What’s nextSlide20

Be a member -

www.SQLServerGeeks.comwww.FaceBook.com/SQLServerGeeks

Twitter @

SQLServerGeeks

Presentation & Scripts uploaded on

www.SQLServerGeeks.com/filesContinue your learning…Slide21

Thank you



for suggestions, please email at

admin@SQLServerGeeks.com