If you don’t realize the importance of indexes when you first start to work on a database you’ll soon wake up to the reality, that poor indexing strategies lead to poor systems. Indexes are a doubled edge sword on the one side they can cut the time it takes to return records from the database and on the other they can cut performance when updating the table, remember when you update the table you have to update the indexes on that table as well.
There is one scenario where an index’s usefulness comes under scrutiny and that’s when the index is not being used. But how do we know …the nice people at Microsoft provided us with a DMV, ‘sys.dm_db_index_usage_stats’, in SQL Server 2005 that shows us the usage stats of an index. When we perform a right outer join against this DMV and sys.indexes as in the script below we can identify which indexes have never been utilized by the database (stats on an index are only gathered when the index is used, this includes updates to the index). This script also displays up to ten columns included in an index.
I.Name ‘Index_Name’,
CASE
WHEN I.Is_Unique = 1 THEN ‘UNIQUE ‘
ELSE ”
END + I.Type_Desc ‘Index_Type’,
CASE
WHEN I.Is_Primary_Key = 1 THEN ‘True’
ELSE ‘False’
END ‘Primary_Key’,
INDEX_COL(OBJECT_NAME(I.Object_Id),I.Index_Id,1) AS Indexed_Col_1,
INDEX_COL(OBJECT_NAME(I.Object_Id),I.Index_Id,2) AS Indexed_Col_2,
INDEX_COL(OBJECT_NAME(I.Object_Id),I.Index_Id,3) AS Indexed_Col_3,
INDEX_COL(OBJECT_NAME(I.Object_Id),I.Index_Id,4) AS Indexed_Col_4,
INDEX_COL(OBJECT_NAME(I.Object_Id),I.Index_Id,5) AS Indexed_Col_5,
INDEX_COL(OBJECT_NAME(I.Object_Id),I.Index_Id,6) AS Indexed_Col_6,
INDEX_COL(OBJECT_NAME(I.Object_Id),I.Index_Id,7) AS Indexed_Col_7,
INDEX_COL(OBJECT_NAME(I.Object_Id),I.Index_Id,8) AS Indexed_Col_8,
INDEX_COL(OBJECT_NAME(I.Object_Id),I.Index_Id,9) AS Indexed_Col_9,
INDEX_COL(OBJECT_NAME(I.Object_Id),I.Index_Id,10) AS Indexed_Col_10
FROM sys.dm_db_Index_Usage_Stats S
RIGHT OUTER JOIN sys.Indexes I
ON S.Index_Id = I.Index_Id
AND S.Object_Id = I.Object_Id
AND S.DataBase_Id = DB_ID()–Current DB Name
WHERE S.Object_Id IS NULL
AND I.TYPE IN (1,2)
ORDER BY Table_Name
There is a limitation; the DMV only tracks an index’s usage details since the last restart of the ‘SQL Server’ service. To validly identify whether an index should be removed from a table the database should be under production level usage over a period of time sufficient to see actionable results i.e. running this on an environment the day after a restart and a conversion load won’t be of any use. I would suggest that before you remove any index you repeat this process a number of times to insure the correct decision.
There is no substitution for a good initial indexing strategy but in scenarios where you inherit a legacy database which has been re-worked then using the above script can help unearth those always welcome performance improvements. Remember if an index isn’t working for you its’ working against you.
Technorati Tags: sys.dm_db_index_usage_stats, SQL Server 2005
This article is very good. we like to know the index usage vocabulary (ie) Range scan, Lookup, etc
how to identify those count for analyze our indexes
Hey Ayyappan,
I’d suggest having a look at the site below, I think it should cover your requirements with regard to the terminology and a script is also included there which returns said counts ….
http://sqlblog.com/blogs/louis_davidson/archive/2007/07/22/sys-dm-db-index-usage-stats.aspx
If this doesn’t fulfill your needs then let me know but i’ll need specifics to be of further help to you
Mikey