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

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

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

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: