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

Best Regards,

Frank

Call for Content

Finished the last paper and thinking about a new topic. I have probably a dozen or so possibilities, but I'd like to open the floor to the readers and ask the question: What is important to you? What topic would you like written about next that centers around databases + disk + performance?

Please send me an e-mail @ frankmcb@gmail.com and let me know.

Look forward to hearing from you.

 

Best-

Frank