Dropping unused indexes - Assessing the unexpected dangers

Question

We have a very large database with hundreds of unused indexes according to DMV statistics, which have been accumulating since the server was last rebooted in July. One of our DBAs made the following cautionary statements, which don't make sense to me:

1. Before we drop an index we need to make sure if it not enforcing a uniqueness constraint, as the query optimizer may need this index to exist.
2. Whenever an Index is created, statistics related to that index are also created in SQL Server. A query might not be using the index but it might be using its statistics. So we may run into a situation , after dropping an index a particular query performance goes really bad. SQL Server does not keep the usage stats of statistics. Although we have “Auto Create Statistics” feature enabled on our database, I don’t know which all parameters have to be met internally before the query optimizer will create the missing statistics.

Regarding #1, it seems to me that SQL Server would actually do a seek on the index to determine uniqueness before an insert / update is done, and therefore, the index wouldn't show as being not used.

Regarding #2, is this really possible?

By the way, when I say an index is not used, I mean no seeks and no scans.

Answer 1

Your DBA's concerns are both valid.

Regarding #1, it seems to me that SQL Server would actually do a seek on the index to determine uniqueness before an insert / update is done, and therefore, the index wouldn't show as being not used.

The uniqueness guarantee may be used by the optimizer in deciding which logical transformations or physical operations can be used to obtain correct results. The fact that the optimizer relies on a uniqueness guarantee to, for example, transform an aggregation or choose a one-to-many merge join, will not be reflected in index usage statistics, unless the index is also physically accessed in the final execution plan. One should therefore be very careful about removing (or disabling) any unique index or constraint.

Regarding #2, is this really possible?

Yes, it is possible for the optimizer to use statistics associated with an index without the final execution plan featuring any access using that index. The processes of loading 'interesting' statistics, calculating cardinality estimates, and producing a finished execution plan are quite independent activities.

Dropping the index would also remove the associated index statistics, which may affect plan quality next time the statement is recompiled. The index statistics may be used in a cardinality estimation calculation that the final plan relies on, even where the index is not physically present in the final plan.

Your DBA knows his/her stuff.

None of this should be taken to mean that apparently-unused indexes should never be removed. I'm simply saying that your DBA's concerns are valid ones, and you should plan the change with them accordingly, with appropriate testing, and a recovery plan. In my experience, point #1 is more likely to be problematic than #2, but I have no way of knowing whether that applies to your situation.