question

jason_clark03 avatar image
jason_clark03 asked

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?
indexingsqlserver
2 comments
10 |1200 characters needed characters left characters exceeded

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

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 Like 1 ·
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 Like 1 ·

1 Answer

· Write an Answer
Wilfred van Dijk avatar image
Wilfred van Dijk answered
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 comment
10 |1200 characters needed characters left characters exceeded

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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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