915307 FIX: You experience a slow uploading process if conflicts occur when many merge agents upload changes to the publishers at the same time in SQL

http://support.microsoft.com/kb/915307/

SYMPTOMS

You have many merge agents in SQL Server 2005. For example, you have 500 merge agents. When these merge agents upload changes to the publishers at the same time, you experience a slow uploading process. In some cases, the uploading process never finishes.

This problem occurs if conflicts occur when these merge agents upload changes.

CAUSE

This problem occurs because the following behavior occurs when the merge agents try to log conflicts during the upload process:

• When a merge agent queries the MSmerge_conflicts_info table, the query optimizer performs an Index Scan on the table instead of a Clustered Index Seek on the table.
• No index is defined on the MSmerge_conflict_PublicationName_ArticleName table. When a merge agent queries the table, the query optimizer performs a Table Scan. When these merge agents log conflicts, SQL Server frequently queries the table. However, a Table Scan consumes lots of time if the table is big.

Additionally, a deadlock occurs in the following scenario:

• One merge agent holds a lock on a user table at the publisher.
• The merge agent requests a lock on the MSmerge_conflict_PublicationName_ArticleName table.
• At the same time, another merge agent holds a lock on the MSmerge_conflict_PublicationName_ArticleName table and requests a lock on the same user table.

In this scenario, the merge agents block one another.

If a deadlock occurs, the uploading process never finishes.