Indexes can be great for boosting the performance of a query, but if an index is never used, it can drag down an instance’s performance. This is because unused indexes must be maintained just like any index. For example, anytime a row is inserted, updated, or deleted, indexes must be maintained. If a lot of records are updated or inserted into a table, an index may experience page splitting. During index rebuilding or reorganizing, indexes are defragmented. During DBCC CHECK DB, indexes have to be checked for their integrity. And of course, indexes take up space in the data cache and on disk. All of these resources are wasted on unused indexes, and can hurt the overall performance of an instance of SQL Server.

Now imagine for a moment that a database has lots of unused indexes, and some of the indexes have many multiple columns, and thus are very wide? I have seen databases where virtually every column in every table had an index, just because the developer thought the “more indexes, the better”. I have also seen many indexes with 4, 5 6, or even more columns that are part of the index key. In situations like this, unused indexes can be a huge hidden performance problem.

Of course, your databases or not like that? Or are they? When was the last time you evaluated the indexes in your databases to see if they were being used or not? According to a recent poll of visitors on my website, nearly 45% of responders said that they have never dropped an index on a database. This could be for two reasons. First, because they have never looked for unused indexes, or because they have looked, but didn’t find any unused indexes to drop. I am guessing that most of them have never looked. While this seems like a large number, about 55% of the responders have dropped an index in the past year, which means that most DBAs are familiar with the issue of unused indexes.

I think it is important to periodically review all of the indexes in your databases in order to identify unused indexes. With the introduction of the sys.dm_db_index_usage_stats DMV in SQL Server 2005, this has become relatively easy. You can choose to run SELECT * on the DMV to return all the results, although the raw results are hard to read. Instead, you might want to use a more useful script such as the one written by Glenn Berry, or one of the many dozens of similar scripts available for identifying unused indexes by searching the Internet.

When you are evaluating which indexes are not used or not, you must keep in mind that this DMV only tracks data on index usage since the instance was last restarted. For example, if you ran Glenn’s query just after restarting your server, then most of the indexes will have not been used. Instead, you must wait for your server to be up long enough to be representative of how indexes are used in your environment. This might mean that your server needs to be running for a week, a month, even a quarter if you have many reports that are only run at the end of a quarter. If your time frame is not representative, you may end up dropping indexes that are used, but less often. Another option to consider when collecting index statistics usage over time is to run a periodic job that collects the data over any time period you like, and store it in a database for later analysis.

And that brings up another area to discuss. Should you drop indexes that are only being used occasionally? For example, perhaps a report is only run once a month, once a quarter, or even once a year. Should you keep this indexes, or drop them? This is a question I can’t answer for you, as each situation is different. For example, if an index that is used for a once a month report takes 5 seconds with the index, and 30 seconds without the index, is the benefit of that index, saving 25 seconds once a month, worth the overhead of maintaining that index? This is only a question you can answer based on the business needs of your environment.

So if you are one of the DBAs who have dropped an index in the past year, good for you. If you have never dropped an index, then I suggest you run Glenn’s query on your databases and then decide for yourself if you need to drop some unused indexes.