SQL SERVER – 2008 – Find If Index is Being Used in Database

It is very often I get query that how to find if any index is being used in database or not. If any database has many indexes and not all indexes are used it can adversely affect performance. If number of index is higher it reduces the INSERT / UPDATE / DELETE operation but increase the SELECT operation. It is recommended to drop any unused indexes from table to improve the performance.

Before dropping the index it is important to check if index is being used or not. I have wrote quick script which can find out quickly if index is used or not. SQL Server 2005 and later editions have Dynamic Management Views (DMV) which can queried to retrieve necessary information.

We will run SELECT on Employee table of AdventureWorks database and check it uses Indexes or not. All the information about Index usage is stored in DMV – sys.dm_db_index_usage_stats. Let us run following query first and save our results.

USE AdventureWorks
GO
SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sc.Name AS ColumnName,
sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
WHERE sis.Database_ID = DB_ID('AdventureWorks') AND sis.OBJECT_ID = OBJECT_ID('HumanResources.Employee');
GO

Now let us run following two SELECT statement which will utilize Indexes on table Employee.

USE AdventureWorks
GO
SELECT *
FROM HumanResources.Employee WITH (INDEX = 1)
WHERE EmployeeID = 1
GO
SELECT *
FROM HumanResources.Employee WITH (INDEX = 2)
WHERE LoginID = 'adventure-works\guy1'
GO

Note : WITH (INDEX  = Number) is not required but I have used it to make sure that first query uses Index 1 and second query uses Index 2. Both the query will return the same result. Now once again we will run our initial query  getting data from sys.dm_db_index_usage_stats and compare our result with initial data.

(Click on image to see larger image)

It is clear from comparing both the result set that when running query on tables it updates sys.dm_db_index_usage_stats and increment column user_seeks.

Above whole process explains that any index usage is stored in the sys.dm_db_index_usage_stats. DMV sys.dm_db_index_usage_stats stores all the usage since SQL Server is restarted. Once SQL Server service is restarted sys.dm_db_index_usage_stats is reset to zero but over the period of the time it updates the values in the columns. If we run our initial query without WHERE condition we can get many rows which contains IndexName and their usage. That will give us idea how many indexes are heavily used. If using WHERE condition we do not find our index in the table it is clear indication that Index is not used much.

If SQL Server services are not restarted in reasonable amount of time and if any index usage is not found, the index should be dropped. Again, make sure you have test your performance after dropping the index. If it gets worst put that index back and continue exercise.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

14 thoughts on “SQL SERVER – 2008 – Find If Index is Being Used in Database

  1. HI Pinal

    Nice article.

    Just a recommendation, most of your images come from http://www.pinaldave.com/blogfolder/.

    In our organization “blog” is filtered, and thus I cannot see the screen shots.

    I guess it is in most of the companies.

    Can u do something about it, may be changing the folder name, or the virtual directory name.

    Thanks.

    Like

  2. This is what I saw elsewhere for similar idea
    It shows indexes NOT USED

    SELECT
    OBJECTNAME = OBJECT_NAME(I.OBJECT_ID)
    , INDEXNAME = I.NAME
    , I.INDEX_ID
    FROM
    SYS.INDEXES I
    JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
    WHERE
    OBJECTPROPERTY(O.OBJECT_ID, ‘IsUserTable’) = 1
    AND I.INDEX_ID NOT IN ( SELECT
    S.INDEX_ID
    FROM
    SYS.DM_DB_INDEX_USAGE_STATS S
    WHERE
    S.OBJECT_ID = I.OBJECT_ID
    AND I.INDEX_ID = S.INDEX_ID
    AND DATABASE_ID = DB_ID() )
    ORDER BY
    OBJECTNAME
    , I.INDEX_ID
    , INDEXNAME ASC
    GO

    Like

  3. Is there any other cases where we would use the hint (index = number) ?

    FOr example I have Index 1 = Clustered (ID)
    Index 2 = NoneClustered(Date)

    Then I run the follwing a query and I forced it to use index=2, Would I reduce the amount of page scans???

    Select ID from MYtable (index =2)
    where date < = ’11/12/2008′ ????

    Like

  4. Hi,

    How to do the same in SQL 2000?

    By the way, I’ve on more query.
    Is it possible to list the Indexes at the time of executing an SQL Query? Means, any way to list the Indexes associated with an SQL Query when that particular SQL Query is executed? Hoping, I havent confused you much. :-)

    Thanks.

    Like

  5. Hi Sir,
    I am newbie in SQL Server.I was going through above mention query. I was able to achieve the least used indexes as

    select OBJECT_NAME(u.object_id) TableName,i.name Indexname,u.user_seeks,u.user_scans,u.user_lookups,u.user_updates
    from sys.dm_db_index_usage_stats u inner join sys.indexes i on u.object_id=i.object_id

    I don’t get why did you included sys.Indexes_columns and sys.columns table in your above query

    Like

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s