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; ...
Get Microsoft SQL Server 2012 Performance Tuning Cookbook 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.
