Custom Indexes for SQL Server Replication (Merge)

How to Improve the Performance of Merge Replication With Custom Indexes

Phil Grayson
Phil Grayson

Published Oct 2017

Share:

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.


How to Improve the Performance of Merge Replication With Custom Indexes

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
Phil Grayson

Published Oct 2017

Share: