Posted by Erland Sommarskog on 04/13/06 01:05

pb648174 (google@webpaul.net) writes:
> Does anyone have a resource on index defragmentation that could be done
> on a schedule for an entire database based on some automated
> statistics? I see plenty of info in BOL, but whether I should use fill
> factors of 80, 30 or what I have no idea. We have never done any kind
> of index defragmentation - is there some way that the database itself
> can just handle it?

I guess that you can set up a maintenance job, but I think it's better
to run a separate job.

The easy way is just to run a cursor over sysobjects and run DBREINDEX
on all tables. As long as you have enough off-hours to permit this, this
may be good enough. And it's certainly far better than nothing at all.

A colleage of mine has composed a reindexing job for our system. It uses
DBCC SHOWCONTIG to check for fragmentation, and runs DBREINDEX only if
fragmentation is over 30%. For the tables with lower fragmentation, he
instead runs UPDATE STATISTICS WITH FULLSCAN INDEX on my insistence. This
is because some tables have monotonically growing keys, so they don't
fragment, but statistics easily gets out of date, as new rows are always
added outside the current intervals in the histogramme.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]