You've interrogated the sys.dm_db_index_usage_stats Dynamic Management View (DMV), and indentified an index that appears never to have been used. In theory, it's being maintained and so eating up valuable resources, but is offering no benefit in terms of query performance.

And yet, your finger hovers nervously over the metaphorical "delete" button. You can't quite bring yourself to do it. Is it really safe to drop this index? Perhaps there are queries that rely on it but aren't represented in the DMV because they only run periodically, and have not been executed since the metadata was refreshed.

During my recent visit to Oracle World, I was interested to hear about "invisible indexes" in an Oracle RDBMS. The concept is simple and rather akin to being able to "comment out" an index, by altering it to be invisible. In SQL Server, it is possible to disable an index during troubleshooting, and then rebuild it. However, invisible indexes work differently; the index is still fully maintained, but the optimizer cannot see it, and will not use it in an execution plan, and it can be re-enabled without rebuilding.

In Oracle, it's possible that if an index hasn't been used in an execution plan, it will be reported as unused by the index's monitoring usage attribute, even if the optimizer has actually used the statistics associated with the index, in order to derive an optimal execution path. This means that dropping an index that hasn't been "used" might cause the optimizer to misjudge the data cardinality, and result in sub-optimal execution plans, subsequently.

The same does not appear to be true for DMV-based monitoring of index usage in SQL Server but, nevertheless, invisible indexes struck me as an interesting idea, and one I'd like to make its way "across the border". In cases where we believe an index could be removed, but some doubt remains, we could make such the index invisible for a trial period, allowing us to investigate any potential repercussions from removing it, before actually dropping it. Likewise, if for some reason the optimizer begins to use an index in a highly inefficient manner, we can make it invisible, and investigate alternative execution paths.

I'd like to hear the thoughts of the DBW community. Are invisible indexes a good idea, or do we already have too many indexes to worry about? What sort of tests do you currently perform, and how, before dropping any apparently-unused indexes?

Cheers,

Tony.