Identifying Unused Indexes
As mentioned previously in this chapter, each index on a table adds additional overhead for data modifications because the indexes also need to be maintained as changes are made to index key columns. In an OLTP environment, excessive indexes on your tables can be almost as much of a performance issue as missing indexes. To improve OLTP performance, you should limit the number of indexes on your tables to only those absolutely needed; you definitely should eliminate any unnecessary and unused indexes that may be defined on your tables to eliminate the overhead they introduce.
Fortunately, SQL Server provides a DMV that you can use to identify which indexes in your database are not being used: sys.dm_db_index_usage_stats ...
Get Microsoft SQL Server 2014 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
