Saturday, February 14, 2009

Let SQL Server automatically suggest indexes!

I found this link on the Microsoft website
and ran the AutoIndex.sql from the attached 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!

Bookmark and Share