Merge Replication Performance Tuning and Optimization

Updated : May 8, 2002

Microsoft Corporation

Authors: Damian Castro, Alejandro Miguel, Bren Newman

Abstract Merge replication can provide a high-performing and scalable solution for database applications that require data to be updated at multiple locations, but performance and scalability depend on application design and the appropriate setting of replication parameters. Based on tests conducted using a variety of hardware configurations and replication environments, this paper makes suggestions about applying the initial snapshot, optimizing replication settings, and configuring replication for scalability.

On This Page

Introduction
Merge Replication Performance in SQL Server 7.0 and SQL Server 2000
Improving Merge Replication Performance
Conclusion

Introduction

Merge replication is a type of replication provided by Microsoft® SQL Server™ 2000 that allows various sites to work autonomously (online or offline) and merge data modifications made at multiple sites into a single, uniform result at a later time. Merge replication can provide a high-performing and scalable solution for database applications that require data to be updated at multiple locations, but performance and scalability depend on application design and the appropriate setting of replication parameters.

Based on tests conducted with SQL Server 2000 (with no service packs installed) using a variety of hardware configurations and replication environments, this paper makes suggestions about applying the initial snapshot, optimizing replication settings, and configuring replication for scalability. The white paper "Diagnosing and Troubleshooting Slow Partitioned Merge Processes" addresses design issues and troubleshooting methods and gives additional information on performance tuning. Reading both white papers will give you a better understanding of how to tune and optimize SQL Server merge replication.

Merge Replication Performance in SQL Server 7.0 and SQL Server 2000

The performance of SQL Server 2000 merge replication is significantly improved over SQL Server version 7.0, due to important performance modifications and new merge replication functionality. The following charts show the throughput performance gains in SQL Server 2000 replication for insert, update, and delete operations. On similar hardware in our labs SQL Server 2000 replication performs between two and five times faster than SQL Server 7.0 replication. In some areas, new functionality such as dynamic snapshot generation provides even more significant performance gains (see the section "Dynamic Snapshots" later in this paper for more information).

Figure 1: Comparing Merge Replication Performance in SQL Server 2000 and SQL Server 7.0

Improving Merge Replication Performance

You can modify merge replication in a number of ways to improve the performance of your application. To better illustrate these modifications, this paper uses a number of scenarios, ranging from a simple publication with a single article and one Subscriber to a more complex publication in a three-level republishing hierarchy with 2000 Subscribers.

Performance results vary based on environment and user scenario. The performance results documented in this paper highlight the costs and benefits of using a number of merge replication options. The section on triggers deals strictly with costs, because it is necessary to understand how triggers contribute to the overall cost of merge replication.

Note: In this paper, performance results are often expressed as the number of merge changes (uploads or downloads) per second. Each merge operation involves the synchronization of the content of a uniquely identified row change from the Publisher to the Subscriber (download) or from the Subscriber to the Publisher (upload). The total number of merge uploads or downloads per second that can be sustained over time is known as the throughput of merge replication.

Improving Snapshot Performance

Applying the initial snapshot can take a significant amount of time if you are transferring a large amount of data over the network, or if you have a relatively low-speed connection. Try transferring the snapshot using a removable disk, or use the performance optimization features of SQL Server 2000. These features include compressed snapshots, dynamic snapshots, and the two parameters –MaxBCPThreads and –UseInprocLoader.

Using -MaxBCPThreads

In merge replication, the -MaxBCPThreads parameter can be passed to the Snapshot Agent and the Merge Agent. This parameter specifies the number of bulk copy operations that can be performed in parallel. Increasing the number of threads can improve performance because more operations can be completed in the same amount of time.

The maximum number of threads and ODBC connections that can exist simultaneously is the lesser of -MaxBCPThreads or:

• For the Snapshot Agent: the number of bulk copy requests that appear in the synchronization transaction at the distribution database.

• For the Merge Agent: the number of bulk copy requests that appear in the system table sysmergeschemachange in the publication database.

-MaxBCPThreads must have a value greater than zero and has no hard-coded upper limit. The default is 1. When used with the Snapshot Agent, -MaxBCPThreads affects the time it takes to generate a snapshot. When used with the Merge Agent, -MaxBCPThreads affects the time it takes to apply the snapshot at the Subscriber.

Because the Snapshot Agent bulk copies the contents of all the articles of a publication, it writes the entire publication to the snapshot folder. Therefore, the faster the disk subsystem can read and write data to disk, the faster the snapshot is completed. The performance benefit of using -MaxBCPThreads is also dependent on the number of processors on the server. Specifying a high number for -MaxBCPThreads can cause the system to spend too much time managing threads. Using more threads than the total number of articles provides no additional benefit.

Using -UseInProcLoader

The parameter -UseInProcLoader can be passed to the Merge Agent when applying the initial snapshot at the Subscriber. When using this parameter, the Merge Agent will use the in-process BULK INSERT command rather than the out-of-process Bulk Copy Command (BCP), decreasing the time it takes to apply the snapshot. To further enhance performance, -UseInProcLoader can be used in conjunction with -MaxBCPThreads.

In most cases, the use of this parameter improves performance. By default this parameter is off, however, because it is affected by line quality and speed, the amount of available memory on the Subscriber, the type of data transferred, and the number of articles. Test performance using your publication before making a decision about the use of this parameter.

Using Compressed Snapshots

This option is recommended when you are using a pull or remote push Subscriber. It also provides additional benefits when you are using FTP support. Compressing snapshot files in the alternate snapshot folder can reduce snapshot disk storage requirements (the process optionally maintains both the compressed and uncompressed data). Also, in some cases it can significantly improve performance when you are transferring snapshot files over a slow connection.

The cost of compressing the snapshot is that while the snapshot files are generated and applied, the Snapshot Agent and Distribution Agent require additional processing resources. This may slow down overall snapshot generation and increase the time it takes to apply a snapshot. Carefully consider these tradeoffs when deciding whether or not to use compressed snapshots.

Using Dynamic Snapshots

Dynamic snapshots provide a significant performance advantage over static snapshots when using dynamic filters. This functionality, new in SQL Server 2000, uses bulk copy files to apply data to a specific Subscriber instead of using a series of INSERT statements.

Generating a dynamic snapshot for a subscription and storing it in the alternate snapshot location also allows increased flexibility of deployment. You can save the snapshot to removable media (such as a CD-ROM or PCMCIA hard drive) and apply the snapshot at the Subscriber from the media rather than applying it over a slow network connection.

To quantify the performance impact of using dynamic snapshots, we tested a publication with one article and a small number of columns. The number of rows increases from 6,000 to 600,000 over the course of the test. As the following illustration indicates, dynamic snapshots result in a significant improvement in the time it takes to apply the initial snapshot.

Figure 2: Time to Apply Merge Replication Snapshot

Synchronizing Snapshots in Publications with a Large Number of Articles

Implementing initial snapshot synchronization correctly is essential, particularly in large-scale merge replication topologies. Consider how the snapshot will be generated, whether it will be delivered over slow- or high-speed connections, and, for dynamically filtered publications, whether the Merge Agent applies static or dynamic snapshots.

To calculate how long it will take the Snapshot Agent and Merge Agent to complete the initial synchronization, consider scaling factors such as the number of articles defined in the publication and the total number of rows per article.

Preparing Tables for Initial Snapshot Generation

The first step of snapshot generation is table preparation: merge triggers are added to the table, and a uniqueidentifier column is added for tables that do not already have one. If the number of articles in the publication is small, and the number of rows in each table is small, initial table preparation will not have a significant impact on snapshot generation time. However, if there are many articles in the publication and there are many rows in the articles, initial table preparation may have a significant impact on snapshot generation time.

The following illustration shows a publication with an increasing number of articles: 1 article, 64 articles, and 128 articles. Because we are measuring scalability strictly as a function of the number of articles, each article has only one row. As the graph indicates, the time required for 128 articles is approximately 36 times greater than the time required for one article.

Figure 3: Number of Snapshots Generated and Applied for Scaling Number of Articles

If a replicated table does not already have a column of data type uniqueidentifier (with the ROWGUIDCOL property set, a default of NEWID(), and a unique constraint), merge replication adds one. Observing server activity using SQL Profiler, you can see clearly that most of the table preparation time is consumed adding the ROWGUID column.

If you have very large articles or a large number of articles in the publication, define a column with the data type of uniqueidentifier and set the ROWGUIDCOL property for each table before populating the tables with data (the column does not have to be named ROWGUID). This column should have a default of NEWID() and a unique constraint.

If you have large tables that are already populated with data, it might be easier and faster to:

1. Select the data into a temporary table.

2. Truncate the base table.

3. Add the uniqueidentifier column to the base table with the ROWGUIDCOL property set and a default of NEWID().

4. Insert the data back into the base table.

5. Add a unique constraint to the uniqueidentifier column.

The following illustration shows the difference between adding the uniqueidentifier column manually and having it added as part of the snapshot process. The table used in the test is a table containing customer information, similar to one you would find in many databases. It has nine columns and is 127 bytes wide.

The darker bars show the amount of time the snapshot process takes to add the uniqueidentifier column and generate the snapshot. The bottom portion of the other bars shows the time required to generate the snapshot when the uniqueidentifier column is added manually. The top portion of the bar shows the time required to add the GUID column with the five-step process described earlier.

Figure 4: Creating GUID Column Prior to and During Snapshot Generation

Figure 4 shows that the point of inflection is at approximately 2.5 million rows. Beyond that, it is better to add the uniqueidentifier column manually. Adding the column manually also allows the snapshot generation task to be completed in two phases, which may be more manageable for an administrator.

Understanding the Cost of Merge Triggers

Merge replication tracks data changes through triggers and system tables. There is some CPU cost associated with merge triggers, but the cost is primarily I/O related. There is also increased I/O activity involved in the generation and retrieval of the meta data stored in the replication system tables. The additional I/O activity required by merge replication can increase the time necessary for users to execute queries on the published tables.

The following illustration compares the time to execute Transact-SQL statements on a published table that has no triggers, simple triggers, and merge triggers. The published table PKTABLE has three columns of the following data types: int, tinyint, and varchar(400), with a clustered primary key on the int column. The simple trigger declares a variable and assigns a value to it: the total number of rows in the inserted or deleted tables (special tables used by triggers). The merge trigger is significantly more complex; it moves data from the inserted or deleted tables into the merge tracking tables.

Figure 5: Cost of Triggers in Merge Replication

Understanding the Cost of Complex Filters

In addition to triggers, filters can add overhead to a publication. A publication can have no filters; static filters, such as State = 'WA'; or dynamic filters, such as Sales_Person = SUSER_SNAME(). Both static and simple dynamic filters incur approximately a 15 percent cost to merge throughput. However, more complex filters (especially ones using User Defined Functions or filter clauses with sub-queries) can have a much greater cost, sometimes more than 50 percent. Keeping filters as simple as possible reduces the potential for dramatic costs in throughput. For more information on filtering, see the topic "Filtering Published Data" in SQL Server Books Online, and the white paper "Diagnosing and Troubleshooting Slow Partitioned Merge Processes," available on the Microsoft Web site.

Optimizing Synchronization

There are a number of settings that can influence merge synchronization performance. Two of the most important are –DownloadGenerationsPerBatch, a Merge Agent parameter, and @keep_partition_changes, a merge publication property.

Effect of the –DownloadGenerationsPerBatch Parameter

The Merge Agent parameter -DownloadGenerationsPerBatch controls the number of generations processed in a single batch while downloading changes from the Publisher to the Subscriber. A generation is defined as a logical group of changes per article. Setting -DownloadGenerationsPerBatch to an appropriate number can reduce the number of round trips the Merge Agent requires to read the information at the Publisher and download it to a Subscriber. This parameter becomes especially useful when one or both of the following conditions are met:

• A Subscriber has not merged data with the Publisher for an extended period time, during which the Publisher or other Subscribers have made numerous changes.

• There are hundreds or possibly thousands of Subscribers merging changes, and the Subscriber has been scheduled to run last in the list.

Every time an insert, update, or delete operation is executed at a Publisher or Subscriber, it is logged to merge replication system tables and grouped under a generation number. When the Merge Agent runs, the current generation is closed and other Subscribers that are also making changes to the tables included in the subscription open a new generation. There are numerous open and closed generation numbers identifying logical changes to the Publisher and any of the Subscribers. If a Subscriber does not merge changes for a long period of time, it will receive a long list of generations the next time it merges.

The -DownloadGenerationsPerBatch parameter defines the number of generations that the Merge Agent reads and downloads at one time. The default value of this parameter is 100. Increasing the value to 1000 (or even 2000) increases the number of downloads per second, especially given a large number of pending generations.

Note: If your replication topology includes slow or unreliable network connections, setting –DownloadGenerationsPerBatch to a value higher than the default may not be optimal, because slow and unreliable connections can result in more retries by the Merge Agent. Retries are performed at the batch level, so each retry would involve a larger set of data, and retries would be more likely because of the length of time it takes to transmit each batch.

The following illustration shows the results of a test in which a merge process using the default agent profile (with -DownloadGenerationsPerBatch set to 100) took 264 seconds and read 969 KB from the Publisher. It used 970 roundtrips to download 31099 data changes at a Subscriber. Using the "High Volume Server-to-Server Profile" (with -DownloadGenerationsPerBatch = 2000), it completed in 204 seconds and used 706 roundtrips. This represents a 23 percent performance gain and 27 percent fewer reads.

Figure 6: Effect of Different Values for DownloadGenerationsPerBatch Parameter

The tables below provide more information regarding the amount of data moved from the Publisher to the Subscriber: 31099 data changes, consisting of 29910 inserts and 1189 updates.

-DownloadGenerationsPerBatch=100

Server	Reads	Writes	Bytes read	Bytes written
Publisher	970	1474	969250	3553271
Subscriber	2718	2443	9346361	8501761
Distributor	971	1475	969308	3553824

-DownloadGenerationsPerBatch=2000

Server	Reads	Writes	Bytes read	Bytes written
Publisher	706	1361	252512	3477536
Subscriber	2759	2402	9856634	8565128
Distributor	707	1362	252570	3478089

Effect of the @keep\_partition\_changes Property

@keep_partition_changes is a property associated with a publication; set to TRUE, it can improve the efficiency of partitioned data set performance. When a table is published with partitioned data sets, this setting determines whether changes to one Subscriber's data set will impose partition maintenance overhead for the other Subscribers' partitions.

For example, if there are two Subscribers, one for State = 'WA' and another for State = 'OR' and a row in the data table with State = 'WA' is updated to State = 'CA', this row must be removed from the State = 'WA' partition. The process of moving data in and out of partitioned data sets is referred to as realignment.

If @keep_partition_changes is set to FALSE (the default), the merge replication process recognizes that the partitioned data sets have changed, but not which partition has changed. Therefore, the partition evaluation procedures, which populate the Subscribers' data sets at the Publisher, must run for each Subscriber to determine whether the Subscriber has rows that must be removed, even if the Subscriber's partition never contained the moved row in the first place.

If @keep_partition_changes is set to TRUE, merge replication stores extra data in its system tables to track which Subscribers will be affected by partition changes. This means that in the preceding example only the State = 'WA' Subscriber will be involved in the partition cleanup operation.

Because replication creates additional tracking tables and adds some logic at the Publisher, it is possible to reduce the number of roundtrips and network bytes transferred between the Publisher and Subscriber. This option is most beneficial when used in slow connection environments, but its use must be balanced against the overhead of additional logic and tracking tables.

**Benefits of Using @keep\_partition\_changes**

@keep_partition_changes is only relevant for publications that have partitioned data. In addition to setting @keep_partition_changes to TRUE when calling sp_addmergepublication, you must define a subset filter clause when calling sp_addmergearticle:

sp_addmergearticle …, @article = 'pkart', @source_object = 'PKTABLE', 
@subset_filterclause='pkpart=HOST_NAME()'…

To quantify the effects of using this property, we used the table PKTABLE described earlier. We:

1. Populated PKTABLE with 75,000 rows.

2. Created a publication with the filter pkpart=HOST_NAME(). The subset filter partitioned the data into three sets of 25,000 rows; each row had a value 0, 1, or 2 in the pkpart column. There was a Subscriber for partition 0 and partition 1, but none for partition 2.

3. Executed a script that performed a large number of update and delete operations on PKTABLE. The updates moved rows in and out of Subscriber partitions.

With @keep_partition_changes set to FALSE, all the updates and deletes were applied to both Subscribers. With @keep_partition_changes set to TRUE, only the data corresponding to the Subscriber's partition was received. In the test scenario the merge process was twice as fast when @keep_partition_changes was set to TRUE, but results depend on many variables, so you should test your configuration.

Also important is that when the parameter was set to TRUE, the number of roundtrips and network bytes read and written at both the Publisher and the Subscriber was reduced by almost half. The number of bytes sent and received over the network was also reduced. Compared to bytes read and written with @keep_partition_changes set to FALSE:

• Net bytes written at the Publisher was reduced by 64 percent.

• Net bytes written at the Subscriber was reduced by 36 percent.

• Net bytes read at the Subscriber was reduced by 40 percent.

Clearly, if you experience frequent realignment of data, or you have large data sets that are realigned (especially over a slow connection), setting this option to TRUE is beneficial.

**Costs of Using @keep\_partition\_changes**

Using @keep_partition_changes does incur some overhead, consuming extra disk space and processing resources at the Publisher to track partition information for the Subscribers. A tracking table with the name of MSbi_<articleGUID> is created for each article; it maintains the values of columns involved in the partition filter. When data is realigned or the rows are deleted, the beforevalues are stored in the tracking tables. In cases where a small number of rows is updated, or realignment is infrequent (which means the tracking tables remain small), consider setting the @keep_partition_changes property to FALSE to avoid consuming extra space and CPU time.

The following example shows the tracking table generated to maintain partition information:

CREATE TABLE [MS_biC8E4768EB19546EBBF46CE98EE5E3002] (
[pkid] [int] NOT NULL ,
[pkpart] [tinyint] NOT NULL ,
[rowguid] [uniqueidentifier] NOT NULL ,
[generation] [int] NOT NULL ,
[system_delete] [bit] NULL CONSTRAINT 
     [DF__MS_biC8E4__syste__35BCFE0A] DEFAULT (0)
) ON [PRIMARY]

Column	Description
[system_delete]	bit (1 byte)
[generation]	int (4 bytes)
[rowguid]	uniqueidentifier (16 bytes)
[pkid]	User defined primary key. In this scenario it is an int (4 bytes).
[pkpart]	Column used in @subset_filterclause. In this scenario it is a tinyint (1 byte).

In this case the additional data stored in the tracking table totals 26 bytes per row. Multiply that number by the number of rows stored in the table MSbi_% to approximate the total additional space required.

An extra row is added to the MSbi_% table for each row updated or deleted in the replicated table. In this scenario all the rows were first realigned and then deleted.

Extra Space Required

	Rows	Reserved (KB)	Data (KB)	Index size (KB)	Unused (KB)
After Updates - MSbi table size	75000	8168	2800	5240	128
After Deletes - MSbi table size	150000	18920	5504	13312	104

For other operations that do not involve row realignments or deletes, the pertinent factor is mainly downloads per second. The following table shows throughput was reduced from 154 to 140 downloads per second, a 10 percent cost in terms of rows downloaded per second.

Downloads Per Second

@keep_partition_changes	Download updates	Download inserts	Download deletes
FALSE	154.73	252.86	96.83
TRUE	140.63	237.73	85.36

Comparing Row-Level and Column-Level Tracking

Deciding whether to track merge conflicts by column or by row depends on the requirements of your application. You must evaluate whether you want an update to the same row but different column in a published table to be handled as a conflict or not. In row-level tracking, when changes are made to corresponding rows, there is a conflict, whether or not the changes are made to the same column in the row. Your choice of tracking method impacts performance. For example, if column-level tracking means that an update does not cause a conflict, an extra reconciliation step in the merge process is avoided.

For example, suppose one change is made to the address column of a row at the Publisher, and a second change is made to the phone number column (in the same table) of the corresponding row at the Subscriber. With row-level tracking, a conflict is detected because changes were made to both rows. With column-level tracking, no conflict is detected, because changes were made to different columns in the rows. If a conflict occurs, one server is elected as the conflict winner according to the chosen resolution strategy. The row of the winning server then replaces the corresponding row at the losing server.

Column-level tracking can be set when adding an article to a merge replication publication, or it can be changed after the article is added.

• If you are adding a merge article to a publication, set the @column_tracking parameter of the replication stored procedure sp_addmergearticle to TRUE for column-level tracking or to FALSE for row-level tracking.

• If you are changing the properties of an existing inactive merge article in a publication, set the @property = parameter of the replication stored procedure sp_changemergearticle to column_tracking, and then set the @value = parameter to TRUE for column-level tracking or to FALSE for row-level tracking.

Setting @column_tracking to TRUE has the following advantages:

• No conflict is raised if different columns at the same row were updated.

• Network traffic is considerably reduced when a large number of columns are modified, or when the base table has columns of data type text or image.. Columns are only sent over the network if they are modified; so overall efficiency improves, which is especially significant for slow connections.

However, the cost of column-level tracking is increased storage in merge replication meta data tables. This can be significant, depending on the number of rows and columns affected by changes. When @column_tracking is set to TRUE, an extra column, colvl, is sent to each node (all Publishers and Subscribers) in the replication topology. The colvl column contains a value of data type varbinary that totals 8 bytes per column defined in the table. For every column, colvl stores the nickname of the node that made the last update, and when the update occurred. If the total number of columns updated multiplied by the length of each column value is considerably lower than 8 multiplied by the total number of columns, the savings of column-level-tracking are significant.

Some factors to consider when deciding which type of tracking to use:

• Number of columns in the article base table at the Publisher, which affects the amount of data interchanged between the Publisher and the Subscriber.

• Data type and width of columns. For example, tables using text/image data types are good candidates for column-level tracking.

• Concurrent changes to the same row but to different columns are not treated as conflicts: if the application allows, column-level tracking could avoid a conflict when different columns on the same row are updated.

• Total number of rows modified, which defines the reduction in total network bytes sent or received and network reads and writes, as well as the amount of extra storage needed in tracking tables.

Articles with a Smaller Number of Columns

If the tables in a publication have a small number of columns, and the servers in the replication topology are well connected at LAN speeds of 10 megabits per second or higher, column tracking will not have a significant impact on performance.

Consider the following example in which four tables are published for merge replication. The tables are related to each other in a multiple level parent-child relationship, as shown in Figure 7.

Figure 7: Relationship of Articles in Test Publication

The parent table contains a primary key column of data type int, a column of data type varchar(400) and another column of data type int, which stores a value corresponding to the partition to which the row belongs. Each child table contains the same columns as the parent table and an additional foreign key column of data type int, which relates each child to its parent. The following chart shows that setting @column_tracking to TRUE resulted in a 6 percent performance gain for update operations, because the Merge Agent only sends those columns across the network that are modified by the update.

Figure 8: Upload Performance of Column-level Tracking and Row-level Tracking

Although there is some benefit to using column-level tracking for tables with a small number of columns, the benefit is more significant if the table contains a larger number of columns or columns of data type image or text.

Articles with a Larger Number of Columns

The following chart shows the performance of column-level tracking compared to row-level tracking after 20 columns of data type varchar(400) and one column of data type image are added to each table described in the previous section. Column-level tracking resulted in a performance increase for updates of approximately 70 percent. However, deletes and inserts are not significantly affected, because the Merge Agent sends the same number of columns over the network, regardless of which type of tracking is used.

Figure 9: Performance of Column-level Tracking and Row-level Tracking

As mentioned earlier, column-level tracking uses extra space at the Publisher. The size of data in the tracking table MSmerge_contents increased by approximately 300 percent:

MSmerge_contents System Table

15,000 Rows	Data KB
Column-level Tracking	4088
Row-level Tracking	1360
% increase	300%

Reducing Network Traffic with Column-Level Tracking

If column-level tracking is used, Merge Agents send across the network extra information that is stored in the colvl column of the MSmerge_contents system table. However, if row-level tracking is used, the Merge Agent needs the value of each column for any rows that have been updated. The difference in network traffic is minimal if many columns in the replicated table are null, but when most of the columns contain values, column-level tracking significantly reduces network traffic.

During synchronization, the Merge Agent establishes connections to the Subscriber and the Publisher. A pull subscription is used in this test, so the Merge Agent runs at the Subscriber and the connections to the Publisher are established through a LAN and a WAN. All meta data and data that travel between the Merge Agent and the Publisher have to cross network boundaries between the LAN and WAN. The following graphs show the amount of read activity over the network generated by the Merge Agent in this test.

Figure 10: Network Utilization Between the Merge Agent and the Publisher

Figure 11: Network Utilization Between the Merge Agent and the Subscriber

Using column-level tracking reduced network traffic (bytes read) between the Merge Agent and the Publisher and Subscriber by 92.6 percent.

As mentioned earlier, performance of the Merge Agent using column-level tracking increased approximately 70 percent as a result of the decreased amount of data and meta data written to and from the Publisher. However, the amount of traffic between the Merge Agent and the Subscriber increased considerably as well.

Scaling Merge Replication to 2000 Subscribers

To test the performance and scalability of SQL Server 2000 merge replication in a multi-tier publishing topology with two republishers and 2000 Subscribers, we simulated a week of operations in a sales environment. We measured the total time it takes for each merge Subscriber to complete 30 local operations and one merge on a daily basis for a five-day period. (For a diagram of the publishing topology used for the test, see "Replication Environment.")

Figure 12: The Schema for the Publisher and Subscriber

Replication Environment

The first-tier Publisher has two publications, one for Region 1 and one for Region 2, with two known Subscribers using continuous pull subscriptions. Its two Subscribers republish their data (so they are referred to as second-tier Publishers, or republishers) based on a dynamic filter that uses SUSER_SNAME(); the republishers allow anonymous subscriptions. On all publications the CUSTOMER table uses identity range management on the primary key column Cust_Id. Only the CUSTOMER, ORDERS, and PAYMENT articles are modified in this test; they all use column-level tracking.

Figure 13: Republishing Hierarchy for Scaling Test

Row Filter for First-Tier Publication

Article REGION @subset_filterclause = N'Region_Id = 1'
Article REGION @subset_filterclause = N'Region_Id = 2'

Row Filter for Second-Tier Publication

Article INTERESTED_IN @subset_filterclause = 
     N'Host_Nm = lower( substring( suser_sname(), 
charindex( ''\'',suser_sname() )+1, 200))'

Row Filter for All Publishers

Article	Join Article	Join Filter Clause	Join Unique Key
ORDERS	CUSTOMER	[ORDERS].[Cust_Id] = [CUSTOMER].[Cust_Id]	1
PAYMENT	CUSTOMER	[PAYMENT].[Cust_Id] = [CUSTOMER].[Cust_Id]	1
CUSTOMER	STATE	[CUSTOMER].[State] = [STATE].[State]	1
INTERESTED_IN	STATE	[INTERESTED_IN].[State] = [STATE].[State]	1
STATE	REGION	[STATE].[Region_Id] = [REGION].[Region_Id]	1

Note: The column Join Unique Key in the previous table refers to an optimization that is used in this publication. If the join condition is based on a unique column, the @join_unique_key property should be set for the article for best performance. For more information, see the white paper, "Diagnosing and Troubleshooting Slow Partitioned Merge Processes," available on the Microsoft Web site.

First-Tier Publication Row Count (Regions 1 and 2)

Article	Initial row count
CUSTOMER	60,000
PAYMENT	60,000
ORDERS	187,000
STATE	200
INTERESTED_IN	2000
PRODUCTS	10000
SHIP_TYPE	11
REGION	2
NAMES	5,765
JOB_COMMANDS	0

Second-tier Publication Row Count (Region 1)

Article	Initial row count
CUSTOMER	30,000
PAYMENT	30,000
ORDERS	93,500
STATE	200
INTERESTED_IN	2000
PRODUCTS	10000
SHIP_TYPE	11
REGION	2
NAMES	5765
JOB_COMMANDS	0

Second-Tier Subscriber Row Count (2,000 Subscribers)

Article	Initial row count
CUSTOMER	300
PAYMENT	300
ORDERS	935
STATE	200
INTERESTED_IN	2000
PRODUCTS	10000
SHIP_TYPE	11
REGION	2
NAMES	5765
JOB_COMMANDS	0

Applying the Initial Snapshot

The dynamic snapshot for each Subscriber was pre-generated at the Publisher. The process involved Snapshot Agents running concurrently, which generated approximately 1 megabyte (MB) of snapshot data per Subscriber. Each republisher had 1000 Subscribers, so approximately 1 gigabyte (GB) of snapshot data was generated per republisher. Because each republisher was on a separate server, snapshot generation was performed in parallel and completed within 70 minutes.

Two servers hosted the 2000 Subscribers, with 1000 Subscribers per server. All Subscribers were able to initialize their subscriptions within two hours with ten concurrent Merge Agents running per server.

Updating Merge Replication Subscribers

Thirty local modifications were made each day at each Subscriber. The modifications were:

Article	# of Inserts	# of Updates	# of Deletes
CUSTOMER	3	4	0
ORDERS	15	3	1
PAYMENT	3	1	0

The modifications sent to each Subscriber were for data within its partition. However, because ten Subscribers shared the same partition (for example, same REGION and STATE), conflicts were possible, which resulted in additional downloads associated with conflict resolution. Each Subscriber merged a minimum of 30 uploads (21 inserts, 8 updates and 1 delete). With the exception of the first Subscriber within a specific partition on the first day (which had no downloads), Subscribers had multiple downloads per merge.

Synchronizing Subscriber Changes

After local data changes were made at the Subscribers, each Subscriber synchronized with the Publisher using the High Volume Server-to-Server profile for the Merge Agent. Ten Merge Agents per Subscriber (the default maximum, which can be changed with @max_concurrent_merges) were run concurrently against their Publisher.

A large number of generations were opened and closed due to the large number of Subscribers in this replication topology. For that reason, using the High Volume Server-to-Server profile for the Merge Agent with the -DownloadGenerationsPerBatch parameter set to 2000 was optimal. The relatively high setting for this parameter meant that fewer roundtrips were required to fetch all the generations.

After the first day with one round of local Subscriber data changes, 2000 Subscribers merged in 4 hours. After the fifth day (a total of five rounds of local Subscriber data changes and merges, simulating a five-day work week), 2000 Subscribers merged in 4 hours and 40 minutes. Each Subscriber's merge time was approximately 2 minutes and 30 seconds. After five days of changes made at Subscribers and merge operations, it took 3 hours and 40 minutes for 2000 Subscribers to then complete a cycle of downloads-only (after which all Subscribers had the correct data). It took 1 hour to complete 2000 merges with no uploads or downloads (verifying there was nothing more to do).

No data changes were made at the Publisher or republishers, and the republishers were able to merge with the first-tier Publisher within two minutes. The first-tier Publisher was running at 10 percent CPU utilization. The two republishers were running at approximately 55 percent CPU utilization. Because 1000 Subscribers shared one server with ten merges running concurrently, each Subscriber ran at approximately 70 percent CPU utilization.

Hardware

The first tier and second tier Publishers were 450-megahertz (MHz) Xeon multiprocessors with 4 CPUs, 1 GB of memory, and a RAID 0 array (the log files for the tempdb, the published database, and the distribution database were each on separate disks). Because merge replication is more likely to become CPU bound than I/O bound, the RAID configuration will not have as dramatic an impact as it does in other applications. The two Subscriber machines were 733-MHz Pentium III multiprocessors with 2 CPUs, 512 MB of memory, and 3 SCSI hard drives. All four servers were on the same 100 MB network switch.

Conclusion

Given a typical sales application built on the example described above, it is possible to successfully scale to 2,000 merge replication Subscribers using a combination of static and dynamic filters.

Given the 55 percent CPU utilization at the republishers, it is possible to have more concurrent Merge Agents running. The maximum number of concurrent merges can be set with the @max_concurrent_merge parameter of sp_addmergepublication.

Using separate Subscriber machines (rather than 10 concurrent Merge Agents sharing the same server and hard drives) could also yield better performance. Given that the Publisher's CPU was not close to 100 percent utilization, more concurrent merge synchronizations could have been enabled by increasing the @max_concurrent_merge parameter. Also, given the capacity and power of newer, faster machines, better performance and scaling could be obtained. Additionally, if the application is for a large region, such as all 50 states in the United States, the workload would be spread across a longer time period, allowing a larger merge window. This should be enough to accommodate 2,000 merge replication Subscribers, even over a slow connection.

Executing 30,000 Merges to Measure Meta Data Growth

To measure meta data growth and to understand how the growth of merge meta data affects merge performance, especially over an extended period of time, we executed a large number of merges and data changes for each Subscriber.

This test scenario employed one Publisher with one publication configured on a 450-MHz multiprocessor computer with 4 CPUs and 1 GB of memory. The publication used column-level tracking and was dynamically filtered into 50 partitions, using SUSER_SNAME(). One hundred anonymous merge Subscribers were simulated using a 733 MHz computer with 512 MB of memory.

Ten concurrent Subscribers were started; each performed 100 local operations, consisting of 75 inserts, 20 updates, and 5 deletes, and then merged these changes to the Publisher. Because there were two Subscribers for each partition, each Subscriber received approximately 100 downloads: the changes generated by the 100 operations performed at the other Subscriber in the same partition. The data modification and merge process ran 300 times, producing a total of approximately 30,000 merges and approximately 3,000,000 data changes (100 changes multiplied by 100 Subscribers multiplied by 300 total runs). Of the commands, approximately 16,000 or 0.5 percent of the total commands were conflicts.

As indicated in the following graph and table, there is a correlation between the amount of meta data stored and the total time to complete a merge. After the 300th run, merge processing time increased 88 percent, from 18 seconds to 34 seconds, and the total meta data stored at the Publisher increased from 4,368 KB to 838,536 KB. It is important to note that MSmerge_contents plays a very significant part, because it comprises over 93 percent of the total space used in this test (with over 1.6 million rows). After running a DBCC DBREINDEX() on the meta data tables, the size of MSmerge_contents was reduced by 43 percent, MSmerge_genhistory by 17 percent, and MSmerge_tombstone by 35 percent. Merge performance improved by about 11 percent, with the time taken dropping from 34 seconds to 30 seconds. More importantly, after all meta data was cleaned up at the Publisher and Subscribers by running sp_mergemetadataretentioncleanup, the merge time dropped to 20 seconds, very close to the original run.

Most publications in production have a retention period of less than 30 days. (A period of 10-14 days is recommended, so this test represents each Subscriber running more than 10 merges a day and about 1000 local data changes a day [100 data changes multiplied 10 times a day], including weekends.) This level of activity is possible but rare in production systems, so you should not see meta data grow as rapidly as it did in this test.

Figure 14: Merge Agent Run Times

The following table shows merge meta data at the Publisher:

Merge Run Number	Msmerge_contents		MSmerge_genhistory		MSmerge_tombstone
	Rows	Size (KB)	Rows	Size (KB)	Rows	Size (KB)
1 st	9285	4008	349	152	483	208
100 th	602009	284872	33063	7072	47887	10136
200 th	1111856	534704	64299	13472	96080	20056
300 th	1619108	788112	95191	20064	145269	30360
After DBCC DBREINDEX()	1619108	450816	95191	16512	145269	19712
301st run after DBCC	1624163	547448	95504	20128	145759	25392
Run after Cleanup	0	0	112	80	0	0
302nd run after Cleanup	9452	4000	414	144	488	208

However, in SQL Server 2000 Service Pack 1 (SP1), the number of indexes on the merge meta data table MSmerge_contents has been decreased, resulting in less index space and slightly better performance. To clean up meta data in the system tables in SQL Server 2000, the replication topology had to be inactive and data had to be quiesced ("quieted" so that all values for a particular row and column converge to the same value across the entire replication topology).

SQL Server 2000 SP1 now includes retention-based meta data clean up, and meta data can be more easily deleted from the replication system tables. Cleanup does not require that the topology is inactive and quiesced; it is done on a per-node basis and does not have to be coordinated with the other nodes. If the -MetadataRetentionCleanup parameter in agent profiles is set to 1, as it is by default, the Merge Agent automatically cleans up the meta data at the Publisher and Subscriber. However, for automatic retention-based cleanup to occur in a database involved in merge replication, the database and the Merge Agent must both be on servers running SQL Server 2000 SP1 or later. For example:

• A SQL Server 7.0 pull Subscriber will not run cleanup at a SQL Server 2000 SP1 Publisher.

• A SQL Server 2000 SP1 push Merge Agent will not run cleanup in a SQL Server 2000 (without SP1) Subscriber database.

• A SQL Server 2000 SP1 push Merge Agent will run cleanup in a SQL Server 2000 SP1 Publisher database even if it has Subscribers that are SQL Server 2000 or earlier.

If the -MetadataRetentionCleanup parameter in agent profiles is set to 0, the automatic cleanup does not occur. In this case, manually initiate retention-based meta data cleanup by executing sp_mergemetadataretentioncleanup, a new system stored procedure available in SQL Server 2000 SP1. Using retention-based meta data cleanup will ensure less meta data on both the Publisher and Subscribers and the ability to maintain constant performance numbers over an extended period of time.

Adding a Second Concurrent Merge Subscriber

To measure the cost of adding a second concurrent merge Subscriber, we conducted a test under the following conditions. There were three computers: one Publisher and two Subscribers. All the computers had three disks: one for the operating system and SQL Server, one for the data file, and one for the log file.

This test used eight tables, four of which were related using join filters. Each table consisted of three columns of data type int and a column of data type varchar(400).

The test consisted of six steps, each performing different operations, and did not include any conflicts:

• Download - inserts

• Download – updates

• Download – deletes

• Upload – inserts

• Upload – updates

• Upload - deletes

The following table shows the changes made for one Subscriber run (in the two Subscriber run, the number of changes is doubled):

Operation	Downloads	Uploads
Inserts	108,000	108,000
Updates	17,560	46,860
Deletes	13526	26,575

Each operation was run separately, executing the Merge Agent after completion of modifications. The following chart shows the number of uploads and downloads per second based on one or two Subscribers and one or two CPUs.

Figure 15: Merge Performance: Multiple Subscribers and Processors

As indicated in Figure 15, there is no loss in throughput when adding a second Subscriber. In fact, some gain may be obtained in the two-Subscriber environment due to caching. However, if the records are large or the publication is partitioned, there will probably not be a gain from caching, because the data will reside in different data pages and extents.

Action	Upload gain	Download gain
Adding a second processor with one Subscriber	17 percent	34 percent
Adding a second processor with two Subscribers	22 percent	37 percent

Connecting to the Network at a Faster Speed

As you might expect, merge replication performance can be improved by using faster network connection speeds. The following illustration shows replication performance based on rows merged per second at network connection speeds of 28.8 Kbps, 56 Kbps, 128 Kbps, 1 Mbps, and 100 Mbps. Sockets, Named Pipes, and Shared Memory Libraries were used.

Figure 16: Number of Merges as a Function of Connection Speed

Generally, merge upload performance increases as bandwidth increases, achieving almost linear scaling from 28.8 Kbps to 128 Kbps and continuing to scale above 1 Mbps. However, because the number of downloads is restricted by the Publisher determining what rows need to be downloaded (as opposed to uploads, which do not incur the same CPU overhead), near linear scaling is achieved until 128 Kbps, but no benefit is derived above 1 Mbps. If you are planning to roll out a large dial-up merge replication scenario, using current dial-up technologies like ISDN, DSL, and cable modem can make a large difference in total merge time. Even using a modem with a connection speed of approximately 50 Kbps will be significantly better than 28.8 Kbps.

Conclusion

SQL Server 2000 merge replication is an ideal solution for many distributed applications, particularly when data is updated at multiple sites and disconnected Subscribers are used. The performance of SQL Server 2000 merge replication is significantly improved over SQL Server version 7.0, due to important performance modifications and new merge replication functionality. Despite these gains, it is often necessary to performance tune your merge replication topology to achieve maximum throughput and scalability.

This paper has covered a number of options and settings that you can control, including performance tuning in the snapshot and synchronization processes, which are fundamental to merge replication, and scaling to a large number of Subscribers. Following the processes and suggestions outlined in this paper and the paper "Diagnosing and Troubleshooting Slow Partitioned Merge Processes" can help you achieve scalable, high-performance merge replication applications.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

© 2002 Microsoft Corporation. All rights reserved.

Microsoft is a registered trademark of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.