drop index on subscriber.

Question

we have a transaction replication that with 1 publisher and 2 subscribers. I have seen at a times subscribers lag behind in data sync. it happens for only 1 table, this table is sized around 100G and index size is around 200G. im sure there are lots of unwanted indexes on the tables. is there any way i can drop indexes on subscriber without affecting replication. Any step by step article to do it will be of big help.

Answer 1

Thank you so much for that explaination.

Answer 2

Hi @SQLServerBro,

Set the Copy nonclustered indexes value for articles to True. This action will ensure that nonclustered indexes are included whenever the snapshot of the tables is generated. This means that when you do a re-initialization is that when a new snapshot is re applied at the subscriber database ,the non clustered indexes would get copied over.

I test this in my environment, when the Copy nonclustered indexes value for articles to True, the nonclustered indexes will not be copied automatically to subscriber, unless you re-initialize the replication using a new snapshot, this process will make a new snapshot, this snapshot include nonclustered indexes.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

Answer 3

Cathy,
when you say "so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there."
But what if the option in article properties that say "copy nonclustered indexes" set to true. in that case shouldnt nonclustered indexes get copied automatically to subscriber ?

Answer 4

Hi @ SQLServerBro,

> is there any way i can drop indexes on subscriber without affecting replication.

Drop indexes on subscriber will not affect replication.

Quote from MS document;

How do I add or change indexes on publication and subscription databases?

Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

> Under what circumstances snapshot of a table should be taken, what are the scenarios?

When you need to initial your SQL Server replication, it will create a new snapshot of the table.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

Answer 5

Erland, how about following query to find out unused indexes. Also, will it be safe to drop these indexes on the basis of output of these queries? we dont have non prod environment where we can replicate the similar scenario as that of prod.

SELECT
objects.name AS Table_name,
indexes.name AS Index_name,
dm_db_index_usage_stats.user_seeks,
dm_db_index_usage_stats.user_scans,
dm_db_index_usage_stats.user_updates
FROM
sys.dm_db_index_usage_stats
INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE dm_db_index_usage_stats.user_lookups = 0
AND
dm_db_index_usage_stats.user_seeks = 0
AND
dm_db_index_usage_stats.user_scans = 0