• sqlnaive (5/10/2013)


    It has been around an year or little more than that

    That you've been monitoring and recording the index usage stats, taking care to persist the results before server reboots for over a year?

    And I'm sure that these indexes are not getting used as the clustered indexes on these tables contains the columns which are there in randomly created small small non clustered indexes.

    The clustered index contains all columns in the table (because it is the table), this doesn't make it the replacement for all nonclustered indexes, even if it has lots of those columns as key columns. SQL can only seek on a left-based subset of the index key.

    So let's say you have this...

    CREATE CLUSTERED INDEX idx1 on SomeTable (Col1, Col2, Col3)

    CREATE NONCLUSTERED INDEX idx2 on SomeTable (Col2)

    CREATE NONCLUSTERED INDEX idx3 on SomeTable (Col3)

    The two nonclustered indexes are not redundant and removing them could well affect queries running against that table, depending on what queries there are. Of course, if all queries filter on Col1 and sometimes Col1 and Col2, then those nonclustered indexes are unnecessary

    This is not a small change. Removing indexes is far harder than adding indexes. If you want to prove to your manager that the removal won't cause problems, identify the queries that run against those tables and show that removing the indexes doesn't result in a worse performance. IO statistics, time statistics and execution plan will help there.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass