You can enhance the performance of merge replication in your application and on your network by:
When you use a filter to create an article, create an index on each of the columns that is used in the filter’s WHERE clause. Without an index, Microsoft® SQL Server™ has to read each row in the table to determine whether the row should be included in the article (that is, in the horizontal partition of the table). With an index, SQL Server can quickly locate which rows should be included. The fastest processing takes place if SQL Server can fully resolve the WHERE clause of the filter from just the index.
Indexing all the columns used in JOIN filters is also important. Each time the Merge Agent runs, it searches the base table to determine which rows in the base table and which rows in related tables are included in the article. Creating an index on the JOIN columns saves SQL Server from having to read each row in the table every time the Merge Agent runs.
Merge replication requires that each published table have a ROWGUIDCOL column. If a ROWGUIDCOL column does not exist in the table before the Snapshot Agent creates the initial snapshot files, the agent must first add and populate the ROWGUIDCOL column. You can reduce the processing time for the initial snapshot by creating a ROWGUIDCOL column before publishing the table. You should also create an index on the ROWGUIDCOL column.
By default, the Merge Agent processes 100 generations in each batch uploaded and downloaded between the Publisher and Subscriber. If you make frequent updates to a single table and update a large number of rows in a single transaction, then consider increasing the number of generations in each batch. You can set the -DownloadGenerationsPerBatch and -UploadGenerationsPerBatch parameters in the Merge Agent profile.
A pull subscription moves the Distribution Agent from the Distributor to the Subscriber. Relocating the Distribution Agent reduces the amount of processing the Distributor must do for each pull subscription and shifts the processing overhead to the Subscriber. By creating pull subscriptions instead of push subscriptions, you free up more processing capacity at the Distributor for performing other replication or application tasks. By creating anonymous subscriptions, you can further reduce the resource demands on the Distributor because no metadata has to be stored about the Subscriber.
When you create a publication, you have the choice of specifying that one or more Subscribers will be Microsoft Jet 4.0 (Microsoft Access) databases. Enabling Access Subscribers causes the Snapshot Agent to store the snapshot files in character format instead of native SQL Server format. Because it takes additional processing time and storage space for SQL Server to process and store character format files, do not enable Access Subscribers unless you are sure that you will actually have such Subscribers.
text and image columns require more storage space and processing than other column data types. Do not include text and image columns in articles unless absolutely necessary for your application.
Larger tables require more processing time to read and write than smaller ones. Partition your base table into smaller data sets with similar characteristics. Although evaluating the partitioned data requires processing time, the total time SQL Server spends processing and applying changes will be less.
Dynamic filters and partitions are a powerful feature of SQL Server replication. However, even with indexes on the filtered columns, SQL Server must still read each row in the dynamic partition and compare it to the filtered value. Using static filters and partitions reduces the processing time required to complete the merge process.