Microsoft Knowledge Base Email Alertz
All KB/RSS Links  |  Hot Kb!  |  New Kb!  |  Scott Cate  |  FAQ / KB  |  Web Hosting  |  Unsubscribe  

Microsoft Advisory Services Engagement Scenario - Analysis SQL Transactional Replication High Latency

Search KbAlertz

Advanced Search

Receive Microsoft Knowledge Base articles by E-Mail?

Every night we scan the Microsoft Knowledge Base. If technologies you're interested in are updated, we'll send you an e-mail. You only get one e-mail a day, and only when new articles are added.
Click here to create a
FREE account
Already have an account?
[Click here to Login]











Microsoft Knowledge Base Article

This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks
Article ID: 982895 - Last Review: October 5, 2011 - Revision: 7.0

Analysis SQL Transactional Replication High Latency

View products that this article applies to.
Microsoft Professional Advisory Services is a support option that provides short-term, proactive, consultative support beyond break-fix product maintenance needs. This includes working with the same technician for assistance with issues like product migration, code review, or new program development and is a remote, phone-based support option. This service is typically used for shorter engagements, and is designed for developers and IT professionals who do not require the traditional onsite consulting or sustained account management services that are available from other Microsoft support options.

For additional information on Microsoft Advisory Services, including on how to engage, refer to this Microsoft web page:

http://support.microsoft.com/gp/AdvisoryService (http://support.microsoft.com/gp/AdvisoryService)
Back to the top

Microsoft Advisory Services Engagement Scenario - Analysis SQL Transactional Replication High Latency

Customer is experiencing High Latency moving data to a subscriber using Transactional Replication

Customer has SQL 2005/2008 Transactions or Peer-2-Peer Replication running. They observed a delay in the time it takes a transaction to move from the Publisher to a Subscriber. The problem could be a spike or a sustained latency.

The customer has not yet collected Windows/SQL performance data but requests Microsoft assistance analyzing the root cause for the observed latency.
Back to the top

Microsoft Advisory Services Engagement

Statement of Work for this Scenario:

Analyzing SQL Transactional Latency by first determining if the problem exists with the LogReader or the Distributor. Then further analyze the workload to determine if the problem exists in the Agent’s Reader or Writer thread. Work includes collecting Replication configuration data, enabling Agent logging, collecting Windows Perfmon, SQL Profiler data and SQL DMVs.

Included with Scope
  • Provide description of various Transactional Replication Agent parameters which can be used to Tune Agent performance
  • Collecting Replication Publication Scripts
  • Collecting Replication Publisher Metadata
  • Collecting Replication Distributor Metadata
  • Collecting Replication Subscriber Metadata
  • Restarting Agent to collect LogReader and Distributor verbose log
  • Restarting Agents with HistoryVerboseLevel 2 to provide additional Agent performance details in the Distribution database Agent History Tables
  • Collecting PSSDIAG/Perfmon data during observed problemto track workload load Replication Stored Procedures.
  • Collecting DMV execution stats on Replication stored procedures to determine IO and Duration.
  • Generation of TracerTokens to focus and divide the investigation into LogReader vs. Distributor
  • Analysis of the LogReader Reader/Writer threads:
    • Review LogReader/Distributor History to understand workload and delivery rates
    • Review PSSDIAG Reads/Write/CPU/Duration on RPC:Completed for Key Replication stored procedures. Checking for high IO queries and overall data volumes.
Out of Scope
  • Provide Recommendation as to specific Agent Parameters values to improve resolve latency.
  • Walkthrough of SQLIO to test disk subsystem throughput
  • Recommendation on how to reconfigure IO subsystem to improve disk read/write performance
Back to the top

Self-Help resources for this scenario


Below is a list of self-help resources for this scenario. Microsoft Support Engineers may also use these resources during an Advisory Services engagement.

First Steps in Troubleshooting Replication
http://blogs.msdn.com/repltalk/archive/2010/02/04/first-steps-in-troubleshooting-replication.aspx (http://blogs.msdn.com/repltalk/archive/2010/02/04/first-steps-in-troubleshooting-replication.aspx)

Replication Metadata Collection Scripts
http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=ReplTalk&ReleaseId=4032 (ProjectReleases.aspx?ProjectName=ReplTalk&ReleaseId=4032)
KB article 312292 How to enable replication agents for logging to output files in SQL Server
http://blogs.msdn.com/repltalk/archive/2010/03/04/kb-article-312292-how-to-enable-replication-agents-for-logging-to-output-files-in-sql-server.aspx (http://blogs.msdn.com/repltalk/archive/2010/03/04/kb-article-312292-how-to-enable-replication-agents-for-logging-to-output-files-in-sql-server.aspx)
Divide and Conquer Transactional Replication using Tracer Tokens
http://blogs.msdn.com/repltalk/archive/2010/03/11/divide-and-conquer-transactional-replication-using-tracer-tokens.aspx (http://blogs.msdn.com/repltalk/archive/2010/03/11/divide-and-conquer-transactional-replication-using-tracer-tokens.aspx)
Transactional Replication Conversations
http://blogs.msdn.com/repltalk/archive/2010/02/21/transactional-replication-conversations.aspx (http://blogs.msdn.com/repltalk/archive/2010/02/21/transactional-replication-conversations.aspx) (http://technet.microsoft.com/en-us/library/cc732035(WS.10).aspx) (http://technet.microsoft.com/en-us/library/cc732035(WS.10).aspx)
Enhancing Transactional Replication Performance
http://msdn.microsoft.com/en-us/library/ms151762(SQL.90).aspx (http://msdn.microsoft.com/en-us/library/ms151762(SQL.90).aspx)
Replication team blog
http://blogs.msdn.com/ReplTalk (http://blogs.msdn.com/ReplTalk) (http://blogs.msdn.com/clustering) (http://blogs.msdn.com/clustering)


Back to the top
APPLIES TO
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2008 Standard Edition for Small Business
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
Back to the top
Keywords: 
kbproadvisory kbinfo kbsurveynew kbtshoot kbnomt kbprosql kbproperformance KB982895
Back to the top
        

Community Feedback System

Very often, it takes hours to solve a problem. Very often, you've looked high and low, and have tried a lot of solutions. When you finally found it, chances are, it was because someone else helped you. Here's your chance to give back. Use our community feedback tool to let others know what worked for you and what didn't.

Please also understand that the community feedback system is not warranted to be correct, it's simply a system that we've built to let people try and help each other. If something in a feedback response doesn't make sense to you, or you're not comfortable making changes that the feedback talks about (like registry edits), please consult a professional.

Thank you for using kbAlertz.com Feedback System.

-- Scott Cate

Copyright © 2002-2007 KBALERTZ.COM.  All Rights Reserved. Terms / Privacy. Discount ASP.NET Hosting