Calculating How Often An Index Is Used In SQL Server 2005
Over the last week, I have been working with several customers trying to manage performance by cutting down the number of non-used indexes in the database. In many canned apps, it's not unheard of to see 50 to 80 indexes on a table where only 10 or 15 are actually populated and used. The problems that occur because of this are numerous: extra disk used, longer backups, and of course—INSERTS, UPDATES and DELETES take much longer.
One of the main pains in using the DMVs are not being able to initialize them and then sorting through the extra data generated in the process. I wrote a few scripts and decided to turn them into a small TechNote on this subject. The core of the paper provides a method for solving this common problem by using the DMV sys.dm_db_index_usage_stats.
Examples and ideas covered work for SAP, PeopleSoft, Siebel, JD Edwards, and many other canned applications that provide out-of-the-box schema.
The full paper is posted at: www.computationpress.com