sys.sp_MSenumgenerations90 and high CPU problem

This article presents a solution of a problem with high CPU in environment with merge replication which are caused by a huge number of calls sys.sp_MSenumgenerations90 system stored procedure like

declare @p6 bigint
set @p6=9992683
exec sys.sp_MSenumgenerations90 9779693,'56F9A1C8-8A0A-4DB3-A1B8-C04FEB475A90',0,500,9893895,@p6 output
select @p6

First thing you can do is to create a missing index on [dbo].[MSmerge_genhistory]

CREATE NONCLUSTERED INDEX <index name> ON [dbo].[MSmerge_genhistory](generation, guidsrc, art_nick, pubid, [nicknames], [genstatus], [changecount])

With this index, execution will be faster, high CPU interval shorter, but your problem will not disappear.
You might try to reduce “Retention period” for your replication (default value is 14 days) but that will just drop number of records in replication system tables like MSmerge_contents, MSmerge_tombstone, MSmerge_genhistory, MSmerge_partition_groups,MSmerge_current_partition_mappings and MSmerge_past_partition_mappings
Still you will have a huge number of calls sys.sp_MSenumgenerations90.

SOLUTION:

The real fix of the problem is to update genstatus column of [dbo].[MSmerge_genhistory] for all rows which have genstatus equal to 4 to 0. So the statement should be:

UPDATE [dbo].[MSmerge_genhistory] SET [genstatus] = 0 WHERE [genstatus] = 4

Although, that genstatus 4 is not documented in BOL, it appears in the table and all of problematical records have this status. After updating them to genstatus = 0 system will pick up them on the next synchronization and your system will work fine without high CPU and without a lot of calls to the sys.sp_MSenumgenerations90.

Leave a Reply

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