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

In theory that should not be any different from dropping an index on any other table. Just DROP INDEX. Replication is not going to care.

Check in sys.dm_db_index_stats for unused indexes. Keep in mind that this DMV is cleared when the system restarts.

Answer 2

Subscribers can and do have different indexes. You can drop any index on the subscriber.

However, be aware if your settings in your publisher are set to replicate indexes, the next time you snapshot your table, it will come back.

Answer 3

Thank you Erland and Tom... One question as you said "next time you snapshot your table". Under what circumstances snapshot of a table should be taken, what are the scenarios?

Answer 4

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

Answer 5

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.