Replicating Non-Clustered Indexes Improves Subscriber Query Performance
Jared Poché, MCSE, MCDBA
SQL Server Sr. Support Escalation Engineer
Product Support Services (PSS) – Charlotte, NC
— updated 2/24/2019
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.
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.
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.
Additional subscriber non-clustered indexes don’t come without a cost. Like all SQL Server indexes, there is additional overhead for INSERT, UPDATE, DELETE, additional locks\blocking, and index maintenance to consider. Add only the non-clustered indexes supporting the business needs of the Subscriber. If all indexes are needed, perhaps solution based on AlwaysOn Availability Groups is a better fit.
MSDN References:
http://technet.microsoft.com/en-us/library/ms175980.aspx (sp_changearticle)
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.
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?
The Non-clustered Indexes have created my Snapshot Application time, to go beyond allowed limits, causing a failure.
In SQL Server 2012, I find “Copy Non-Clustered Index” has been set to true be default contrary to what is given in this article. That is taking long time.
Is there a Way to stop Non-clustered index creation and resume at next step?
Thank you
Arabinda
Is there a way i can add this in script while adding Articles, due to the large database sizes and partitioned tables i may have to add these options along with copy table partitioned schemes, copy index partitioning schemes.