GilaMonster (5/10/2013)
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, This above example is the case in my environment. Seems well before I joined they have made this a best practice and created same kind of indexes on almost all tables. As fas as queries are concerned, almost 98% are using Col1, Col2, Col3 (considering the above example). So i feel that having non clustered indexes on Col2 or Col3 are unnecessary.
One more thing, even if these indexes are getting used, the stats from dmv sys.dm_db_index_usage_stats says that index_seek+index_scan_index_lookups are almost none as compared to index_write. The question here is also that should we consider these stats or not ? (My analysis is entirely based on my experience with the system as well as these stats)