O'Reilly logo

Microsoft SQL Server 2014 Unleashed by Paul Bertucci, Alex T. Silverstein, Chris Gallelli, Ray Rankins

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required