Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted November 3, 2016

Which Indexes are not Used?

By Greg Larsen

During the development cycle, indexes will be placed on SQL Server tables to speed up performance when searching for records. Lots of thought probably has gone into creating just the right mix of indexes based on how developers think customers will use the system. But how do you tell if all the indexes are being used once your database has been implemented into production?

After a new application has been in production you should verify if your indexes are being used. If you have unused indexes they are just wasting disk space and valuable computer resources to maintain them. It is fairly simple to determine which indexes are not being used by looking at the index statistics exposed by querying the sys.dm_db_index_usage_stats DMV.

The sys.dm_db_index_usage_stats DMV provides a number of columns that identify how many times an index is used in a seek or a scan operation, or if an index has been used in a bookmark lookup or update operation. The code below uses the sys.dm_db_index_usage_stats DMV to determine which indexes have not been used in the AdventureWorks2012 database. Keep in mind that the sys.dm_db_index_usage_stats DMV only tracks index usage since start up. If your instance has just started up then this query will report that most of indexes have not been used. Therefore you want to run this query only after your system has been up for a while.

USE AdventureWorks2012;
GO
SELECT o.name Object_Name,
       SCHEMA_NAME(o.schema_id) Schema_name,
       i.name Index_name, 
       i.Type_Desc,
       CASE WHEN (s.user_seeks > 0 
               OR s.user_scans > 0 
               OR s.user_lookups > 0) 
              AND s.user_updates > 0
            THEN 'USED AND UPDATED'
            WHEN (s.user_seeks > 0 
               OR s.user_scans > 0 
               OR s.user_lookups > 0) 
              AND s.user_updates = 0
            THEN 'USED AND NOT UPDATED' 
            WHEN  s.user_seeks IS NULL 
              AND s.user_scans IS NULL
              AND s.user_lookups IS NULL
              AND s.user_updates IS NULL
            THEN 'NOT USED AND NOT UPDATED' 
            WHEN (s.user_seeks = 0 
              AND s.user_scans = 0 
              AND s.user_lookups = 0) 
              AND s.user_updates > 0
            THEN 'NOT USED AND UPDATED' 
            ELSE 'NONE OF THE ABOVE'
            END AS Usage_Info,
       COALESCE(s.user_seeks,0) AS user_seeks, 
       COALESCE(s.user_scans,0) AS user_scans, 
       COALESCE(s.user_lookups,0) AS user_lookups, 
       COALESCE(s.user_updates,0) AS user_updates   
 FROM sys.objects AS o
     JOIN sys.indexes AS i
 ON o.object_id = i.object_id 
     LEFT OUTER JOIN
  sys.dm_db_index_usage_stats AS s    
 ON i.object_id = s.object_id   
  AND i.index_id = s.index_id
 WHERE o.type = 'U'
 -- Clustered and Non-Clustered indexes
  AND i.type IN (1, 2) 
ORDER BY user_seeks+user_scans+user_lookups+user_updates ASC

See all articles by Greg Larsen



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM