Drop or keep less frequently used indexes

Question

In a database, there many indexes created for most of the tables. some indexes are highly frequently used while some are used once in a month. Due to many indexes the insert is very slow. But the indexes are used once every month by monthly report queries. So do I drop or keep the indexes? What is the solution to this situation?

Answer 1

There's no "solution" to this. Only your prioritization. Do you prioritize the reports or the inserts? You can of course disable the indexes and rebuild then before the reports, but chances are that the rebuild takes longer time than running the reports without the indexes.

Answer 2

If the report is only once per month then it shouldn't be a big issue if it runs slow.

Deactivate the index and run the report to see the result; if it's inadmissible slow then you can activate (rebuild) the index again.

Answer 3

Hi @Surendra Adhikari ,

We can use DMV monitor the usage of these indexes, to identify the indexes that are badly used, or not used or less used, and drop it or replace it with more optimal ones if required. Please check if below blog could help you.

Gathering SQL Server indexes statistics and usage information

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.