Tara Kizer Blog

Tara Kizer

Index Fragmentation

A while ago, I posted my DBCC DBREINDEX and DBCC INDEXDEFRAG stored procedures.  They perform the DBCC operation on all indexes in the database, which is probably not a good idea.  This article provides an abundance of data as to why.  Paul Randal, Lead Program Manager at Microsoft for SQL Server Storage Engine and SQL Express, gives additional information in this thread

So how does one decide when performance degradation is caused by index fragmention? 

Paul Randal's response: “All you need to do is see which queries are using which indexes for range scans rather than singleton lookups.  These are the indexes that you should concentrate on“.

The Microsoft article is lengthy but is well worth your time if you are a SQL Server database administrator or developer.

Legacy Comments


Adam Machanic
2006-04-15
re: Index Fragmentation
Interesting stuff. Thanks for pointing it out!