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.

SQL SERVER - 2008 - Find If Index is Being Used in Database indexused

(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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – Download – Microsoft SQL Server 2008 Books Online (August 2008)
Next Post
SQLAuthority News – Cumulative update package 1 for SQL Server 2008

Related Posts

13 Comments. Leave new

  • HI Pinal

    Nice article.

    Just a recommendation, most of your images come from .

    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.

    Reply
  • 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

    Reply
  • Sir

    In clusterd and Nonclustered indexe

    which one is faster…??

    Reply
  • sir
    is it possible to create temporary columns………..

    Reply
  • 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′ ????

    Reply
  • 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.

    Reply
  • hi,

    HOW to Find If Index is Being Used in Database in MSSQL 2000

    Thanks.
    Cleto

    Reply
  • Thanks excacly what I was looking for.
    best regards

    Reply
  • Hi,

    It is very useful for me.

    Thanks

    Reply
  • The image does not work.

    Reply
  • 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

    Reply
  • Awesome feature. The only issue is that I don’t think the distinct makes sense in your query.

    Reply
  • when will idexes not work ? how it affects with large data?

    Reply

Leave a Reply

Menu