I've written in the past about [[The Problems with the Missing Index DMVs]]. I'm not opposed to using them, I'm just concerned people will use them INSTEAD OF doing proper analysis. In that post I listed some gotchas.
Once people start to use the missing index DMVs they naturally ask themselves, "Are there any indexes I've created that are NOT being used?" There are many entries on Google that show various algorithms for determining if an index is helping or hurting you. These all involve looking at some ratio of user_seeks/scans/bookmarks to user_updates. This is all good in theory but we've all worked on systems where there is critical path performance requirements that may counterintuitively require an indexing strategy that goes contrary to these algorithms. ie, an index may almost never be used, but when it is used it is super important. So just like the missing index DMVs, caveat emptor.
Here are a few issues I've heard about/experienced with sys.dm_db_index_usage_stats:
- Although an index may not be used, it's underlying stats *might* be. (http://www.sqlskills.com/blogs/joe/exceptions-what-sys-dm_db_index_usage_stats-doesnt-tell-you/). I have no clue how you can test for this in your environment. No clue.
- In that same blog post, in paragraph 2, the author alludes to the DMV not being updated for unique non-clustered indexes where the index is solely being used for unique constraint enforcement. I always therefore take this into consideration. I've never actually experienced the problem, but common sense dictates that if a non-clustered index is UNIQUE and I really need it to be unique, then regardless of what a DMV says, I *need* the index.
- I've heard, but can find no documentation anywhere on Google, that indexes on FK columns may not have their usage data maintained if the index is used solely to guarantee no orphans during parent table delete activity. You should definitely be aware of the problem and consider testing for it.
I've written a little test script that proves that this is *not* actually an issue. It works on all versions of SQL Server that I have in my work environment, but it may be an issue in other environments.