Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am working on a Data warehouse. I have tables with up to 200M records. Some of these tables have around 20+ indexes (I can't provide a reason why they have been created in the first place). This is making the job of maintaining these indexes too painful and has a direct impact on the DWH import job in both performance and run time.

How can I find the least used indexes on each table? (in order to get rid of them)

share|improve this question
2  
System view sys.dm_db_index_usage_stats provides that information. –  Nenad Zivkovic Sep 2 '13 at 8:00
add comment

migrated from stackoverflow.com Sep 2 '13 at 10:32

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 3 down vote accepted

Try this script, It has helped me in past - http://blog.sqlauthority.com/2011/01/04/sql-server-2008-unused-index-script-download/

share|improve this answer
add comment

I have found that Brent Ozar Unlimited's free BlitzIndex script (written by Kendra Little) is the best way to isolate unsused indexes (as well as indexes that are beneficial to be added, indexes that are duplicating the work of other indexes etc)

http://www.brentozar.com/blitzindex/

It will tell you the number of times any index has been read since the last time the statistic counts were reset (or an index was created/recreated).

I seem to remember Brent Ozar saying in webcast that a good rule of thumb is no more than 10 indexes for a table that is read often, 20ish for tables that are static/historical/archived data that will not change frequently.

If you are still having issues with import speed is there a time when the database isn't being actively queried (perhaps this is out of office hours). It may be beneficial to drop the index, import the data and then re-apply the indexes. (Statistics will be reset of course.) The reason for this is that the index(es) will be updated as each record goes in, pages will get reordered and that takes time and disk I/O. Building the indexes after requires a single scan of the table.

No hard and fast rule you may have to experiment with this depending on the types of index and the data involved. Indexes should be regularly reviewed as needs/queries change.

share|improve this answer
add comment

Try this:

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
             I.[NAME] AS [INDEX NAME], 
             USER_SEEKS, 
             USER_SCANS, 
             USER_LOOKUPS, 
             USER_UPDATES 
    FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
             INNER JOIN SYS.INDEXES AS I 
               ON I.[OBJECT_ID] = S.[OBJECT_ID] 
                  AND I.INDEX_ID = S.INDEX_ID 
    WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 

Raj

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.