Replicating Non-Clustered Indexes Improves Subscriber Query Performance

Jared Poché, MCSE, MCDBA
SQL Server Sr. Support Escalation Engineer
Product Support Services (PSS) - Charlotte, NC

One of the advantages of replication is that subscribing servers can be used for reporting thereby offloading RO query activity from the Publisher. Recently I worked on a performance case on such a replication subscriber, which would have been prevented with a simple change to the article properties for this publication.  We discovered the nonclustered indexes were missing from the subscriber.

Indexes are key to the performance of SQL Server, but only clustered key indexes are replicated by default. Unique constraints are replicated by default, so their indexes will also be created on subscribers, but nonclustered indexes are not replicated by default.  While setting up transaction replication on a database, we can change the default settings for articles on the Articles window to include nonclustered indexes.

Once the necessary tables and other articles have been selected, choose Article Properties and Set Properties of All Table Articles.

image

This page shows us a number of settings for indexes, constraints, and so on. Find the Copy Nonclustered Indexes setting, set this option to true, and click OK. This will cause all nonclustered indexes on the publisher to be included in the snapshot for delivery on the Subscribers.

clip_image002

Changing the article options will effectively create indexes on all subscribers with only a few clicks. Also, if a new snapshot is taken for the publication and applied to the subscribers, any indexes created directly on those subscribers will be removed.  If all indexes are not needed, alternatively, you can create selective indexes directly on a subscriber. However, it reduces administrative overhead to change the option directly in the replication settings for the article.

MSDN References:

http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/51512117-b53d-429b-8c3d-84d9a925126e

http://technet.microsoft.com/en-us/library/ms175980.aspx (sp_changearticle)