what factors do i need to consider before deleting the unused indexes?

Question

question

jason_clark03 asked Apr 6, '16

what factors do i need to consider before deleting the unused indexes?

We can find the unused index and last read/write time of index from the below DMVssys.dm_db_index_operational_stats sys.dm_db_index_usage_stats if a report is running every 6 months and using an index and we are checking unused indexes for every 4 months, how can we identify this index as usable in every 6 months In a short, on which factors do i need to look before deleting the unused indexes?

indexing sqlserver

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@SQLShark commented · Apr 06, 2016 at 03:24 PM

Also remember that DMVs are cleared when you restart SQL Server. If you are taking the server down for maintenance regularly then this will affect the index usage.

1 ·

JohnM commented · Apr 06, 2016 at 04:53 PM

Also keep in mind there could be other queries that run out side of that time period, say annually, that could be utilizing the index. The best way, IMHO, is to thoroughly test things to the best of your knowledge and move from there. What impact is this additional index causing?

1 ·

Answer 1 · 2016-04-07T08:29:57Z

Wilfred van Dijk answered Apr 7, '16

I would recommend disabling the index first, to be sure it's not used once a year for that very important report... Enabling is in this case an easy job, just rebuild the index

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk commented · Apr 10, 2016 at 07:52 PM

Note: do NOT disable a CLUSTERED INDEX, because this will make the table unaccessible

0 ·

question