Deeper insight into used and unused indexes for SQL Server

By:   |   Comments (17)   |   Related: > Indexing


Problem

One of the balancing acts of SQL Server is the use of indexes.  Too few indexes can cause scans to occur which hurts performance and too many indexes causes overhead for index maintenance during data updates and also a bloated database.  So what steps can be taken to determine which indexes are being used and how they are being used.

Solution

In a previous tip, How to get index usage information in SQL Server, we talked about how to get index usage information by using the DMVs sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats.  The one issue with these views is that it only gives you part of the picture and you still need to dig deeper to get all of the information you need to determine which indexes are not used.

In this tip we will take it a step further and provide some additional queries that will shed some much needed light on the issue. 

We will be using the DMV sys.dm_db_index_usage_stats which keeps track of each index that has been used and how it has been used.  This was covered in the tip mentioned above.  These stats are collected whenever an object is accessed.   SQL Server resets these values if SQL Server is restarted or if you detach and reattach the database.

There are seven queries below and each one builds upon the others to give you more and more information. If you can't wait you can just skip right to Query 7.  To select the code you should be able to triple click in the table cell to select all of the code.

Note: the information below was collected by restarting SQL Server and then doing selected queries on the AdventureWorks database.  For a more heavily used database your numbers and index usage will be much higher.

Query 1

In this first query we are just using sys.dm_db_index_usage_stats and sys.objects to get a list of the indexes that have been used and how they are being used.

SELECT DB_NAME(DATABASE_ID) AS DATABASENAME, 
       SCHEMA_NAME(C.SCHEMA_id) AS SCHEMANAME, 
       OBJECT_NAME(B.OBJECT_ID) AS TABLENAME, 
       INDEX_NAME = (SELECT NAME 
                     FROM   SYS.INDEXES A 
                     WHERE  A.OBJECT_ID = B.OBJECT_ID 
                       AND  A.INDEX_ID = B.INDEX_ID), 
       USER_SEEKS, 
       USER_SCANS, 
       USER_LOOKUPS, 
       USER_UPDATES 
FROM   SYS.DM_DB_INDEX_USAGE_STATS B 
       INNER JOIN SYS.OBJECTS C ON B.OBJECT_ID = C.OBJECT_ID 
WHERE  DATABASE_ID = DB_ID(DB_NAME()) 
       AND C.TYPE = 'U' 
database name

Query 2

In this query we are listing each user table and all of the tables indexes that have not been used by using a NOT EXISTS against sys.dm_db_index_usage_stats.

SELECT DB_NAME() AS DATABASENAME, 
       SCHEMA_NAME(A.SCHEMA_id) AS SCHEMANAME, 
       OBJECT_NAME(B.OBJECT_ID) AS TABLENAME, 
       B.NAME AS INDEXNAME, 
       B.INDEX_ID 
FROM   SYS.OBJECTS A 
       INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID 
WHERE  NOT EXISTS (SELECT * 
                   FROM  SYS.DM_DB_INDEX_USAGE_STATS C 
                   WHERE DATABASE_ID = DB_ID(DB_NAME())
                     AND B.OBJECT_ID = C.OBJECT_ID 
                     AND B.INDEX_ID = C.INDEX_ID) 
       AND A.TYPE = 'U' 
ORDER BY 1, 2, 3 			
database name

Query 3

In this query we are listing each user table, all of its indexes and the columns that make up the index.  The issue with this query is that you have a row for each column in the index which could get confusing if you have a lot of indexes.

SELECT SCHEMA_NAME(A.SCHEMA_id) AS SCHEMANAME, 
       A.NAME AS TABLENAME, 
       B.NAME AS INDEXNAME, 
       C.KEY_ORDINAL, 
       D.NAME 
FROM   SYS.OBJECTS A 
       INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID 
       INNER JOIN SYS.INDEX_COLUMNS C ON B.OBJECT_ID = C.OBJECT_ID AND B.INDEX_ID = C.INDEX_ID 
	   INNER JOIN SYS.COLUMNS D ON C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID 
WHERE  A.TYPE = 'U' 
ORDER BY 1, 2, 3, 4 
address

Query 4

In this query we use most of Query 3, but we are doing a PIVOT so we can see the index and the index columns in one row.  This only accounts for 7 index columns, but it could easily be increased to handle more in the PIVOT operation.  Here is another tip related to the use of PIVOT, Crosstab queries using PIVOT in SQL Server. if you would like to better understand how PIVOT works.

SELECT SCHEMANAME, TABLENAME, INDEXNAME, INDEXID, [1] AS COL1, [2] AS COL2, [3] AS COL3, [4] AS COL4,  [5] AS COL5, [6] AS COL6, [7] AS COL7 
FROM   (SELECT SCHEMA_NAME(A.SCHEMA_id) AS SCHEMANAME,
               A.NAME AS TABLENAME, 
               B.NAME AS INDEXNAME, 
               B.INDEX_ID AS INDEXID, 
               D.NAME AS COLUMNNAME, 
               C.KEY_ORDINAL 
        FROM   SYS.OBJECTS A 
               INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID 
               INNER JOIN SYS.INDEX_COLUMNS C ON B.OBJECT_ID = C.OBJECT_ID AND B.INDEX_ID = C.INDEX_ID 
               INNER JOIN SYS.COLUMNS D ON C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID 
        WHERE  A.TYPE = 'U') P 
       PIVOT 
       (MIN(COLUMNNAME) 
        FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7] ) ) AS PVT 
ORDER BY SCHEMANAME, TABLENAME, INDEXNAME; 
table name

Query 5

In this query we tie in our PIVOT query above with sys.dm_db_index_usage_stats so we can look at only the indexes that have been used since the last time the stats were reset.

SELECT SCHEMANAME, TABLENAME, INDEXNAME, INDEX_ID, [1] AS COL1, [2] AS COL2, [3] AS COL3, [4] AS COL4, [5] AS COL5, [6] AS COL6, [7] AS COL7 
FROM   (SELECT SCHEMA_NAME(A.SCHEMA_id) AS SCHEMANAME,
               A.NAME AS TABLENAME, 
               A.OBJECT_ID, 
               B.NAME AS INDEXNAME, 
               B.INDEX_ID, 
               D.NAME AS COLUMNNAME, 
               C.KEY_ORDINAL 
        FROM   SYS.OBJECTS A 
               INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID 
               INNER JOIN SYS.INDEX_COLUMNS C ON B.OBJECT_ID = C.OBJECT_ID AND B.INDEX_ID = C.INDEX_ID 
               INNER JOIN SYS.COLUMNS D ON C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID 
        WHERE  A.TYPE = 'U') P 
       PIVOT 
       (MIN(COLUMNNAME) 
        FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7] ) ) AS PVT 
WHERE EXISTS (SELECT OBJECT_ID, INDEX_ID 
              FROM  SYS.DM_DB_INDEX_USAGE_STATS B 
              WHERE DATABASE_ID = DB_ID(DB_NAME()) 
                AND PVT.OBJECT_ID = B.OBJECT_ID 
                AND PVT.INDEX_ID = B.INDEX_ID) 
ORDER BY SCHEMANAME, TABLENAME, INDEXNAME; 
table name

Query 6

This query also uses the PIVOT query along with sys.dm_db_index_usage_stats so we can also see the stats on the indexes that have been used.

SELECT PVT.SCHEMANAME, PVT.TABLENAME, PVT.INDEXNAME, [1] AS COL1, [2] AS COL2, [3] AS COL3, [4] AS COL4, [5] AS COL5, [6] AS COL6, [7] AS COL7, B.USER_SEEKS, B.USER_SCANS, B.USER_LOOKUPS 
FROM   (SELECT SCHEMA_NAME(A.SCHEMA_id) AS SCHEMANAME,
               A.NAME AS TABLENAME, 
               A.OBJECT_ID, 
               B.NAME AS INDEXNAME, 
               B.INDEX_ID, 
               D.NAME AS COLUMNNAME, 
               C.KEY_ORDINAL 
        FROM   SYS.OBJECTS A 
               INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID 
               INNER JOIN SYS.INDEX_COLUMNS C ON B.OBJECT_ID = C.OBJECT_ID AND B.INDEX_ID = C.INDEX_ID 
               INNER JOIN SYS.COLUMNS D ON C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID 
        WHERE  A.TYPE = 'U') P 
       PIVOT 
       (MIN(COLUMNNAME) 
        FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7] ) ) AS PVT 
INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS B ON PVT.OBJECT_ID = B.OBJECT_ID AND PVT.INDEX_ID = B.INDEX_ID AND B.DATABASE_ID = DB_ID() 
ORDER BY SCHEMANAME, TABLENAME, INDEXNAME; 
table name

Query 7

This last query allow us to see both used and unused indexes.  Since the DMV sys.dm_db_index_usage_stats only tracks when an index is used it is hard to compare the used and unused indexes.  The query below allows you to see all indexes to compare both used and unused indexes since the stats were collected by using a UNION.

SELECT PVT.SCHEMANAME, PVT.TABLENAME, PVT.INDEXNAME, PVT.INDEX_ID, [1] AS COL1, [2] AS COL2, [3] AS COL3, [4] AS COL4,  [5] AS COL5, [6] AS COL6, [7] AS COL7, B.USER_SEEKS, B.USER_SCANS, B.USER_LOOKUPS 
FROM   (SELECT SCHEMA_NAME(A.SCHEMA_id) AS SCHEMANAME,
               A.NAME AS TABLENAME, 
               A.OBJECT_ID, 
               B.NAME AS INDEXNAME, 
               B.INDEX_ID, 
               D.NAME AS COLUMNNAME, 
               C.KEY_ORDINAL 
        FROM   SYS.OBJECTS A 
               INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID 
               INNER JOIN SYS.INDEX_COLUMNS C ON B.OBJECT_ID = C.OBJECT_ID AND B.INDEX_ID = C.INDEX_ID 
               INNER JOIN SYS.COLUMNS D ON C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID 
        WHERE  A.TYPE = 'U') P 
       PIVOT 
       (MIN(COLUMNNAME) 
        FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7] ) ) AS PVT 
INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS B ON PVT.OBJECT_ID = B.OBJECT_ID AND PVT.INDEX_ID = B.INDEX_ID AND B.DATABASE_ID = DB_ID() 
UNION  
SELECT SCHEMANAME, TABLENAME, INDEXNAME, INDEX_ID, [1] AS COL1, [2] AS COL2, [3] AS COL3, [4] AS COL4, [5] AS COL5, [6] AS COL6, [7] AS COL7, 0, 0, 0 
FROM   (SELECT SCHEMA_NAME(A.SCHEMA_id) AS SCHEMANAME,
               A.NAME AS TABLENAME, 
               A.OBJECT_ID, 
               B.NAME AS INDEXNAME, 
               B.INDEX_ID, 
               D.NAME AS COLUMNNAME, 
               C.KEY_ORDINAL 
        FROM   SYS.OBJECTS A 
               INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID 
			   INNER JOIN SYS.INDEX_COLUMNS C ON B.OBJECT_ID = C.OBJECT_ID AND B.INDEX_ID = C.INDEX_ID 
               INNER JOIN SYS.COLUMNS D ON C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID 
        WHERE  A.TYPE = 'U') P 
       PIVOT 
       (MIN(COLUMNNAME) 
        FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7] ) ) AS PVT 
WHERE  NOT EXISTS (SELECT OBJECT_ID, 
                          INDEX_ID 
                   FROM   SYS.DM_DB_INDEX_USAGE_STATS B 
                   WHERE  DATABASE_ID = DB_ID(DB_NAME()) 
                          AND PVT.OBJECT_ID = B.OBJECT_ID 
                          AND PVT.INDEX_ID = B.INDEX_ID) 
ORDER BY SCHEMANAME, TABLENAME, INDEX_ID; 
table name
Next Steps
  • The above queries should give you a jump start to determine how indexes are being used and which indexes are being used.  This will allow you to remove unused indexes as well as look for duplicate indexes that can be removed.
  • Based on the information collected you can determine which indexes can safely be dropped.  Just make sure you collect stats long enough to have a good sampling of queries that are run against your database.
  • Now that you know how to determine which unused indexes you can get rid of, stay tuned for future tips on how to determine which indexes are needed


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, August 7, 2020 - 3:29:20 PM - Jason Back To Top (86253)
I realize I am really late to this party as I am now more than two years later in seeing this post but I wanted to point out something I found while using the scripts to build a long term storage of this data.

With the way the scripts are pulling from sys.dm_db_index_usage_stats, you are going to get a lot of records returned that show zeroes for user activities (seeks, scans, lookups, and updates). This is because they will exists in this view but only because of the system queries that interact with the indexes. If you perform regular index maintenance, you will have a lot of entries in the view that are never used by user queries.

In order to avoid this, place a WHERE clause in your query, such as the following:

WHERE user_seeks > 0
OR user_scans > 0
OR user_lookups >0
OR user_updates > 0

Do this in the queries for both the used, and unused, indexes and you will remove a lot of invalid clutter data from the "used" results and get a better insight into the "unused" results.

Friday, November 2, 2018 - 6:21:02 PM - Greg Robidoux Back To Top (78143)

Hi Shriniwas,

you can use sys.dm_db_index_usage_stats to get infomation about last used date.

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=sql-server-2017

-Greg


Wednesday, October 31, 2018 - 7:23:57 AM - SHRINIWAS AVULA Back To Top (78122)

 Hello,

The above queries were definitely helped. However, i am looking out for more information on indexes when they were last used with date range (for example we can take 6 months). so that we can drop the indexes which were not used for last six months.

 


Thursday, August 18, 2016 - 4:32:27 AM - Dev Back To Top (43136)

Thanks for this wonderful post on finding unused indexes. 

However please note in query 2 above, in the subquery part following clause is missing 

DATABASE_ID = DB_ID(DB_NAME())

Which binds this subquery to current database. This clause is anyways there in Query 7. So if someone is just using query 2 (don't need the detail information of index, as I did :) ) than you should put this clause else your results might be incorrect. Hope it makes sense.


Friday, February 21, 2014 - 3:56:35 PM - Howard Rothenburg Back To Top (29534)

 SELECT Table_Name       =SCHEMA_NAME(st.Schema_id) + '.'
                         + OBJECT_NAME(st.object_id)
    , Index_Name       = si.name
     , Index_type       = LOWER(si.Type_Desc)
                          + CASE WHEN si.is_unique = 1 THEN ', unique'
                                 ELSE '' END
                          + CASE WHEN si.is_primary_key = 1 THEN ', primary key'
                                 ELSE '' END
    , Indexed_Columns  = LEFT(ixColumns, LEN(ixColumns) -1)
     , Included_Columns = LEFT(includedColumns, LEN(includedColumns) -1)
     , si.filter_definition
  FROM sys.tables st
  join sys.indexes si
    on st.object_id = si.object_id
 CROSS APPLY (
            SELECT sc.name + CASE WHEN is_descending_key = 1 THEN ' DESC'
                                  ELSE '' END
               + ', '
            FROM sys.index_columns ic
              JOIN sys.columns sc
              on ic.object_id = sc.object_id
             AND ic.column_id = sc.column_id
            WHERE si.object_id = ic.object_id
              AND is_included_column = 0
              and si.index_id = ic.index_id
            ORDER BY ic.Key_Ordinal
              FOR XML PATH('') ) ix (ixColumns)
 CROSS APPLY (
            SELECT sc2.name + ', '
            FROM sys.index_columns ic2 JOIN sys.columns sc2 on ic2.object_id = sc2.object_id
              AND ic2.column_id = sc2.column_id
            WHERE si.object_id = ic2.object_id
             AND is_included_column = 1
              and si.index_id = ic2.index_id
             ORDER BY ic2.Key_Ordinal
              FOR XML PATH('') ) nc (includedColumns)
 ORDER BY Table_Name,Index_Name;


Thursday, November 15, 2012 - 9:09:06 AM - Greg Robidoux Back To Top (20355)

@RGSKAL - take a look at these tips:

 

http://www.mssqltips.com/sqlservertip/1749/retaining-historical-index-usage-statistics-for-sql-server-part-1-of-3/

http://www.mssqltips.com/sqlservertip/1766/retaining-historical-index-usage-statistics-for-sql-server-part-2-of-3/

http://www.mssqltips.com/sqlservertip/1789/retaining-historical-index-usage-statistics-for-sql-server-part-3-of-3/ 


Thursday, November 15, 2012 - 8:54:28 AM - RGSKAL Back To Top (20354)

First, I am grateful of Greg forposting this great article. I have implemented and am storing all the information into a table for continous usage info, but I like to understand how to compensate for a bounce of the server or attach/detach as DMV is going to be reset? My heap is going to have all the information before a bounce takes place, but how do I use this information continously after the bounce? Appreciate an answer. Regards!


Tuesday, August 7, 2012 - 3:32:21 PM - Brandy Back To Top (18945)

Has anyone figured out how to add the "included" fields into these queries for SQL 2008 and above?


Thursday, June 2, 2011 - 11:42:19 PM - Ludwig Back To Top (13971)

Great article...that is surely helpful.

I have also heard that even though it seems an index is not used (counters 0 all over) system performance goes down once an index classified as unused is removed...therefore somehow in the back the index seems to be used even though it states it is not being used...not sure but maybe the index is used in a way that does not trigger the tracking of its usage.. Anyone with simillar expierence?

Thx Ludwig


Thursday, September 2, 2010 - 8:34:45 AM - Douglas Osborne Back To Top (10109)
I tweaked query 7 a bit since we have a few really long indexes - so a quick way to find your limit is to keep bumping up the pivot number until you return no rows, I had to go to [10] - eg

 

SELECT *
FROM
(
    SELECT A.NAME AS TABLENAME, A.OBJECT_ID, B.NAME AS INDEXNAME, B.INDEX_ID, D.NAME AS COLUMNNAME, C.KEY_ORDINAL
    FROM SYS.OBJECTS A
    INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID
    INNER JOIN SYS.INDEX_COLUMNS C ON B.OBJECT_ID = C.OBJECT_ID AND B.INDEX_ID = C.INDEX_ID
    INNER JOIN SYS.COLUMNS D ON C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID
    WHERE  A.TYPE <> 'S'
) P
PIVOT
(MIN(COLUMNNAME)
FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7], [8], [9], [10] ) ) AS PVT
INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS B ON PVT.OBJECT_ID = B.OBJECT_ID AND PVT.INDEX_ID = B.INDEX_ID AND B.DATABASE_ID = DB_ID()
WHERE [10] IS NOT NULL

Nice script BTW,

Doug


Tuesday, August 5, 2008 - 7:11:07 AM - aprato Back To Top (1569)

 Use sp_MSForEachDB.   This should not be used as production code. 

Double check query too... I didn't test it.

sp_MSForEachDB 'use ?;
                declare @name sysname
                set @name = db_name()
                if @name like ''McBam%''
                   SELECT DB_NAME() AS DATABASENAME, OBJECT_NAME(B.OBJECT_ID) AS TABLENAME, B.NAME AS INDEXNAME, B.INDEX_ID
                   FROM SYS.OBJECTS A
                   INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID
                   WHERE NOT EXISTS (SELECT 1
                                     FROM SYS.DM_DB_INDEX_USAGE_STATS C
                                     WHERE B.OBJECT_ID = C.OBJECT_ID
                                     AND B.INDEX_ID = C.INDEX_ID)
                                     AND A.TYPE <> ''S''
                                     AND B.NAME > ''''
                                     AND B.NAME not like ''%rowid%''
                                     ORDER BY 1, 2, 3'
 


Tuesday, August 5, 2008 - 1:59:19 AM - Rimsky Back To Top (1567)

Very valuable, thank you. I have a number of databases with the same structure and indexes. Is there a way I can run the following query on all databases? I think I need to join to master..sysdatabases where databasename like 'McBam%' , but how do I do that?

SELECT DB_NAME() AS DATABASENAME,

OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,

B.NAME AS INDEXNAME,

B.INDEX_ID

FROM SYS.OBJECTS A

INNER JOIN SYS.INDEXES B

ON A.OBJECT_ID = B.OBJECT_ID

WHERE NOT EXISTS (SELECT *

FROM SYS.DM_DB_INDEX_USAGE_STATS C

WHERE B.OBJECT_ID = C.OBJECT_ID

AND B.INDEX_ID = C.INDEX_ID)

AND A.TYPE <> 'S'

AND B.NAME > ''

AND B.NAME not like '%rowid%'

ORDER BY 1, 2, 3


Wednesday, July 23, 2008 - 5:33:12 AM - ESL Back To Top (1491)

 Yes i do,

i have the solution, this is beacause  QUOTED_IDENTIFIER  is not set on  

 

thank's 


Wednesday, July 23, 2008 - 5:22:29 AM - grobido Back To Top (1490)

Are you using SQL 2005?  PIVOT only works with SQL Server 2005 and up.


Wednesday, July 23, 2008 - 1:44:43 AM - ESL Back To Top (1488)

 Hi,

sorry but all the request with PIVOT option doesn't work, error is :

Line 20: Incorrect syntax near 'PIVOT'.

 

do you have an issue to correct .

 regards ,

 

Eric 


Tuesday, July 22, 2008 - 4:50:01 AM - admin Back To Top (1480)

Not sure why this would be the case. 

The one issue I have found is if you have a case sensitive database the upper case causes an issue.


Tuesday, July 22, 2008 - 2:12:34 AM - Jensch Back To Top (1478)

Realy nice statements,but my SSMS seems to dislike Query 3.
It disconnects at every attempt executing the query.
Any idea as to this?

Best regards
- Jens















get free sql tips
agree to terms