16

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.

  • 3
    I would suggest you to disable the index when you are absolutely sure that that index is not used even running year end reports. – Kin Shah Nov 19 '15 at 20:58
17

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.