Enhancing Performance

You can enhance the general performance for all types of replication in your application and on your network by:

Set a Minimum Amount of Memory Allocated to SQL Server

By default, SQL Server changes its memory requirements dynamically based on available system resources. To avoid low memory availability during replication activities, use the min server memory option to set the minimum available memory. If the server is a remote Distributor or a combined Publisher and Distributor, you must assign it at least 16 MB of memory. For more information, see Server Memory Options.

Configure Windows NT Memory Management

On Microsoft Windows NT® Server and Windows NT Server Enterprise Edition, make sure that the Maximize throughput for network applications option is selected.

  1. On the Start menu, point to Settings, click Control Panel, and then double-click Network.
  2. On the Services tab, in the Network Services box, select Server, and then click Properties.
  3. Select Maximize throughput for Network applications.
Use a Separate Disk Drive for Each Published Database Log and Distribution Database Log

You can decrease the time it takes to write transactions by storing the log files on a disk drive different than the one used to store the database. You can mirror that drive (RAID-1) if you require fault tolerance. Use RAID 0 or 0+1 (depending on your need for fault tolerance) for other database files. For more information, see RAID Levels and SQL Server.

Use Multiprocessor Computers

SQL Server replication agents can take advantage of additional processors on the server. If you are running at high CPU usage, consider installing a faster CPU or multiple CPUs (symmetric multiprocessing).

Set a Fixed Size for the Distribution Database

When configuring the distribution database initially, do not configure it to expand automatically. Instead, set a fixed size for the distribution database.

Publish Only the Amount of Data Required

Because replication is easy to set up, there is a tendency to publish more data than is actually required. This can consume additional resources within the distribution databases and snapshot files, and can lower the throughput for required data. Avoid publishing unnecessary tables and consider updating publications less frequently.

Run the Snapshot Agent Only When Necessary and at Off-peak Times

The Snapshot Agent bulk copies data from the published table on the Publisher to a file in the snapshot folder on the Distributor. Run the Snapshot Agent as infrequently as possible or at off-peak times so that users are not interrupted by the locks that the agent puts on the published table and so the network is not burdened by the additional copying.

Reduce the Distribution Frequency When Replicating to Numerous Subscribers

A single Distributor can distribute transactions to a larger number of Subscribers if the Distribution and Merge Agents associated with each Subscriber are scheduled to run less frequently. For additional performance gains, stagger the initial times of Distribution Agents so they do not all attempt to start simultaneously.

Enable Pull or Anonymous Subscriptions Whenever Practical

The Distribution Agent executes on the Distributor for push subscriptions, and on Subscribers for pull or anonymous subscriptions. Enabling pull or anonymous subscriptions increases Distributor performance by moving Distribution Agent processing from the Distributor to Subscribers. Anonymous subscriptions, which are especially useful for Internet applications, do not require that information about the Subscriber be stored in the distribution database at the Distributor. This reduces the resource demands on the Publisher because it does not have to maintain information about anonymous Subscribers.

Anonymous subscriptions are a special category of pull subscriptions. In regular pull subscriptions, the Distribution Agent runs at the Subscriber (thereby reducing the resource demands on the Distributor), but still stores information at the Publisher.

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.