April 15, 2010 at 3:45 pm
Recently I inherited a series databases that are replicated to many different sites. I'm fairly new to being a DBA but worked semi-closely with the DBA that designed this system, so I have a general understanding of what's going on.
An on-going issue we have is the speed of the business system. After looking online, I checked on missing table indexes using queries that utilize the DMVs. After adding indexes to many of the user tables, the replication system tables keep showing up on my list of tables that need indexes. I can't find any literature online stating if I can safely add an index to the MSmerge_genhistory or MSmerge_contents tables.
Does anyone know if I can add an index to the MSmerge_genhistory or MSmerge_contents tables without hosing replication or otherwise causing damage to the system?
April 16, 2010 at 5:37 am
It is actually recommended if you are having issues with performance. You may want to review the msdn document Enhancing Merge Replication Performance. http://msdn.microsoft.com/en-us/library/aa237440(sql.80,printer).aspx
April 16, 2010 at 9:21 am
I read through the article you posted and it pertains to SS2000 not SS2005. Are the concepts it talked about the same in 2005?
Also, the article only talked about indexing the tables you are replicating not the system tables. Do you have any experience indexing the system tables and is there anything I need to lookout for?
April 16, 2010 at 9:44 am
Tables like MSMerge_History and MSMerge_Genhistory should already have indexes on them. As the document in my previous post mentions it is a good idea to re-index the MSMerge_ tables. By the way I realize the document apply to SQL server 2000 however not much has changed with Replication when it comes to performance tuning. I personally re-index the system merge tables once a week. However I must say I do have quit a high volume. The environment has 25 machines replicating every thirty seconds.
I would verify your indexes via the table itself. Expand the table within Management studio and review the index section
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply