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:
- 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.
- 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.