Custom Indexes for SQL Server Replication (Merge)
Apply these indexes to improve the performance of Merge Replication on SQL Server

Phil Grayson
Published Oct 2017
Distribution databases need to be administered and maintained like any other database, which includes indexes. Microsoft have used index hints in several stored procedures, which limits us slightly, but these indexes have worked well for me on a number of systems and continue to do so.
Custom Indexes To Improve the Performance of Merge Replication
USE distribution;
GO
CREATE NONCLUSTERED INDEX [IX_pubid_uploadoptions] ON [dbo].[sysmergearticles] (
[pubid]
,[upload_options]
);
GO
CREATE NONCLUSTERED INDEX [IX_uploadoptions_Included] ON [dbo].[sysmergearticles] ([upload_options]) INCLUDE (
[artid]
,[pubid]
,[nickname]
);
GO
CREATE NONCLUSTERED INDEX [IX_rowguid_origindatasource] ON [dbo].[MSmerge_conflicts_info] (
[rowguid]
,[origin_datasource]
);
GO
CREATE NONCLUSTERED INDEX [IX_pubid_artid] ON [dbo].[sysmergeschemachange] (
[pubid]
,[artid]
);
GO
CREATE NONCLUSTERED INDEX [IX_art_nick_genstatus_subscriber_number_changecount] ON [dbo].[MSmerge_genhistory] (
[art_nick] ASC
,[genstatus] ASC
,[subscriber_number] ASC
,[changecount] ASC
);
GO
CREATE NONCLUSTERED INDEX [IX_artnick_coldate_genstatus] ON [dbo].[MSmerge_genhistory] (
[art_nick] ASC
,[coldate] ASC
,[genstatus] ASC
);
GO

Phil Grayson
Published Oct 2017
Latest Posts
Here’s what we've been up to recently.

xTEN is now Cyber Essentials Plus certified
At xTEN security is a priority. Recently taken over by the IASME consortium (as of 1 April 2020), the Cyber Essentials certification consists of a self-assessment of 5 basic security controls which is then verified by a qualified assessor.

Tracie Binks
Apr 2020