How to use the SubscriptionStreams parameter of Distribution Agent to test for improved disk subsystem throughput in SQL Server 2005

Article translations Article translations
Article ID: 956600 - View products that this article applies to.
Expand all | Collapse all

INTRODUCTION

This article describes how to use the SubscriptionStreams parameter of Distribution Agent to test for improved disk subsystem throughput in Microsoft SQL Server 2005.

MORE INFORMATION

When the disk subsystem for the subscriber is the bottleneck, you may improve performance by configuring Distribution Agent to use the SubscriptionStreams parameter. We highly recommend that the disk subsystem performs at those levels that are recommended for SQL Server 2005. You may notice performance improvements by using the SubscriptionStreams parameter. If there is an improvement, the improvement may be nominal. It is difficult to determine what kind of performance improvement each disk subsystem on the market will provide by using the SubscriptionStreams parameter. Therefore, we recommend that you prepare a test environment that simulates the production environment. You can test scenarios that use the SubscriptionStreams parameter by using various configuration values and a scenario that does not use the SubscriptionStreams parameter.

For more information about guidelines for disk subsystem performance for SQL Server 2005, visit the following Microsoft TechNet Web site:
http://technet.microsoft.com/en-us/library/cc966540.aspx
We recommend that you perform load testing against the publication and against the subscription to determine the performance improvements that you can obtain by using the SubscriptionStreams parameter.

You should perform the performance baseline testing to understand the expected throughput of the disk subsystem. Before you perform each test, apply many changes to create load at the publisher. When you are creating load, make sure that Distribution Agent does not run. When the replication has sufficient latency, run Distribution Agent to test the performance for the following configurations:
  • Do not use the SubscriptionStreams parameter.
  • Set the value of the SubscriptionStreams parameter to be equal to the number of processors on the server. For example, if the server has 8 processors, set the value of the SubscriptionStreams parameter to 8.
  • Specify different values for the SubscriptionStreams parameter to obtain the optimal configuration.
When you perform the test, you can monitor the following performance counters of Distribution Agent:
  • Dist: Delivered Cmds/sec
  • Dist: Delivery Latency

REFERENCES

For more information about how to configure and troubleshoot the SubscriptionStreams parameter, click the following article number to view the article in the Microsoft Knowledge Base:
953199 How to configure and troubleshoot the SubscriptionStreams parameter of the Distribution Agent in SQL Server 2005

Properties

Article ID: 956600 - Last Review: August 14, 2008 - Revision: 1.1
APPLIES TO
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Keywords: 
kbexpertiseadvanced kbhowto kbinfo kbsql2005repl KB956600

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com