SQL index Fragmentation

One of the most common reason I have seen for SQL slow down is improper maintenance of the databases indexes. While I won’t get into the specifics of an index. I’ll just say that the closest analogy is a table of contents for a book.

Once I have a idea of the database that is being used I will run the following in a query window:

exec sp_MSforeachtable @command1=”print ‘?’ DBCC showcontig (‘?’)”

It will return alot of information. The important information to focus on is the page density percentage. If the values falls within the 85-100% range then you can probably getting away with a reindex of the SQL index. If the range falls below the 85% then I would recommend a full off-line rebuild of the indexes.

You can find a definition and creation here:
http://msdn2.microsoft.com/en-us/library/ms188783.aspx

You can find a much more detail index maintenance explination here:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Tags :

About the Author

Leave a Reply

%d bloggers like this: