http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx
and ran the AutoIndex.sql from the attached AutoIndexBlog.zip in my code own CodeCatalog db
The two sp’s I use are ‘auto_create_index’ and ‘auto_drop_index’. Making use of the dynamic management views: dm_db_index_operational_stats, sys.dm_db_index_usage_stats and sys.dm_db_index_physical_stats all databases/tables are analyzed. The result with the suggested create and drop index statements are stored in the ‘recommendations’ table
I schedule the two mentioned sp’s in one SQL Server Agent job every night. The performance impact is low, they’re completed in less then a minute.
Let the sp’s run on a daily basis and monitor the suggestions over time.
It happened to me that indexes where suggested and later one dropped again,
usually when –new- tables change over time.
Results as found in the recommendations table (click to enlarge):
It’s a really great source when having discussions with vendors and finally pinpoint what they are doing wrong!
