Replicating Non-Clustered Indexes Improves Subscriber Query Performance


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)

Comments (2)

  1. Uttam Parui says:

    Note: If are replicating non-clustered indexes, please be aware of

    1) The issue with replicating non clustered indexes as part of snapshot process is that the non-clustered indexes can take long time and not all non-clustered indexes present at the publisher are required at the subscriber.

    2) The other issue is that if there is any error (timeout etc..) with creating non-clustered indexes, the entire snapshot is re-applied to the subscriber. Creating non clustered indexes is the last step in the initialization and if this fails, it will cause distribution agent to re-apply the snapshot files again.

    Thanks to Sateesh Yele (Senior Escalation Engineer , Microsoft) for sharing these.

  2. Dwayne Lanclos says:

    If I add a couple of tables to a publication and do not replicate the nonclustered indexes, I can run the snapshot and it will just include those two tables. But if the publication is replicating nc indexes and I also have the two new tables replicate their nc indexes, a snapshot will re-initialize all the tables, not just the two new ones I added. Is this by design or am I missing something?