question

Heisenberg avatar image
0 Votes"
Heisenberg asked Cathyji-msft commented

drop index on subscriber.

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.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SQLServerBro,

Any update for this thread? Did the responses could help you? If the response helped, do "Accept Answer". If it is not , please let us know.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Heisenberg avatar image
0 Votes"
Heisenberg answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Heisenberg avatar image
0 Votes"
Heisenberg answered ErlandSommarskog commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Unless you know that you have all tables in the dbo schema, you may also want to add the schema.

I would also look at the system_seeks, system_scans and system_lookups, just in case.

If this query does not hit that much, you make the filter less permissive or just do a general look at it all and use your common sense.

You may also be interested in trying sp_blitzindex which will spot redundant indexes.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

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.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Heisenberg avatar image
0 Votes"
Heisenberg answered

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 ?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Heisenberg avatar image
0 Votes"
Heisenberg answered Cathyji-msft commented

Thank you so much for that explaination.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SQLServerBro,

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

0 Votes 0 ·