Unused Indexes Best Practices

This DMV only maintains statistics since the last SQL Server restart; the view gets wiped out completely and everything starts from scratch.

More importantly, the rows in this view for any specific index are removed when that index is rebuilt (but not when it is reorganized). If you are performing regular index maintenance, it might be useful to look at the maintenance logs and see if any of the indexes you're considering dropping might have been rebuilt recently.

So, making decisions based on low reads since the last restart, when the last restart may have been for last week's patch Tuesday updates or yesterday's service pack, might not be wise. Or when you are performing index maintenance, since the last rebuild. There may be a report that is only run once a month, or once a quarter, or once a year, and it is run by an important and impatient person.

Further, an index might be there for something that will happen in the future that you don't know about - a series of reports being prepared for tax season, let's say.

So, my advice is:

Use the DMV to identify candidate indexes to remove, but don't make that decision in a bubble - you need to do the legwork to determine why an index might exist before dropping it, even if it looks like it isn't currently being used.

Yup the view only has statistics since the last reboot. To help mitigate that I setup a job that ran a query like the one you posted monthly in the morning before our maintenance window started to capture the information every month before the server rebooted. That allowed me to go back further and look at trends over time. I also had a second query that ran looking for possibly missing indexes.

Another thing to consider is what other index(es) are on the table. It might not be getting used because it is mostly or completely a duplicate of another index. Yes. SQL server lets you create two different but identical indexes so it is possible for it to be completely redundant.

You can also look at what the query plan might end up being for a query that used that index if that index was removed. Would it have another index to use or would it likely have to fall back to a full table scan.

Indexes ends up being as much art as it is science because it is really hard to know everything about what might be run and it ends up changing frequently anyway.