SQL 2005 Merge Agent Blocking in MSmerge_generation_partition_mappings

Share this Post

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.

image

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

 


Share this Post

About: ReplTalk


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.