/* Database+Disk+Performance™: Calculating How Often An Index Is Used In SQL Server 2005 */ Database+Disk+Performance™ /* */ /* -- -- */ /* Footer ----------------------------------------------- */ #footer { width:660px; clear:both; margin:0 auto; } #footer hr { display:none; } #footer p { margin:0; padding-top:15px; font:78%/1.6em "Trebuchet MS",Trebuchet,Verdana,Sans-serif; text-transform:uppercase; letter-spacing:.1em; }

Monday, January 14, 2008

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

1 Comments:

Blogger bourgon said...

Small typo on page 4: "disable and index". Good idea, though, to mention it since not everybody knows (or remembers, come to think of it) that you can do it.

One other thing, since you mentioned it: you might discuss ways to determine how many levels there are to the index - the more it has to go down, the slower it gets. Naturally, I can't find my notes on it.

10:12 AM  

Post a Comment

<< Home