SQL 2005 Merge Agent Blocking in MSmerge_generation_partition_mappings

If you observe blocking in the SQL Server 2005 Merge Agent script out the “nc1MSmerge_generation_partition_mappings” index and verify the “INCLUDE (changecount)” option exists.  TO verify, generate the create index script by expanding the system tables in the published database.  Then expand “MSmerge_generation_partition_mappings” table. Right click the “nc1MSmerge_generation_partition_mappings” index and script to new query windows.  If you do not see the “INCLUDE (changecount)” option use script below to update the index.  We found this new index improves the SQL 2005 Merge Agent performance and is now included by default in SQL 2008 replication.

If you do not see the “INCLUDE (changecount)” option use script below to update the index. We found this new index improves the SQL 2005 Merge Agent performance and is now included by default in SQL 2008 replication.

-- Execute in the Published databases
/****** Object: Index [nc1MSmerge_generation_partition_mappings] ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MSmerge_generation_partition_mappings]') AND name = N'nc1MSmerge_generation_partition_mappings')

DROP INDEX [nc1MSmerge_generation_partition_mappings] ON [dbo].[MSmerge_generation_partition_mappings] WITH ( ONLINE = OFF )
GO

/****** Object: Index [nc1MSmerge_generation_partition_mappings] ******/
CREATE NONCLUSTERED INDEX [nc1MSmerge_generation_partition_mappings] ON [dbo].[MSmerge_generation_partition_mappings] 
([generation] ASC) INCLUDE (changecount) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO