O'Reilly logo

Microsoft SQL Server 2012 Performance Tuning Cookbook by Bihag Thaker, Ritesh Shah

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

How to find unused indexes

By this time, it is crystal clear that an index can boost up performance, but it comes at a price. Indexes need space in your desk to accommodate their own B-Tree and get updated each time a DML statement gets executed, so it is a good idea to check for any unused indexes in every business cycle.

Getting ready

Before executing the query to find the unused index, remember that we are going to use a sys.dm_db_index_usage_stats dynamic management view that removes all the data at every restart of a SQL Server instance and starts collecting data from scratch again.

If we just restart the server or SQL Server instance and look for the statistics, it will show that no index is used and will suggest dropping (DROP) all indexes; ...

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