Reindexing best practice on 24/7 database

[mutley1]

Hi all,

I have a few DB's that are 25GB+ (not HUGE but big enough) and availability to the customer is supposed to be 24/7. What is the best way to reindex to minimise performance problems to the end user? If I run a DBCC SHOWCONTIG and see some severe fragmentation, then what would be the best approach? As i say it is supposed to be 24/7, what would minimise disruption - reindex / indexdefrag / drop & recreate all indexes etc.......

Thanks,

M.

 

[SQLDenis]

With indexdefrag the data will be available with reindex the data is not available (while the operation runs)
Keep in mind that indexdefrag can be stopped at any time and can be continued the nest time, that said indexdefrag consist of a ton of mini-transactions and this will grow your log file



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Also I assumed you are talking about 2000, SQL server 2005 supports online rebuilding of indexes ;-)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mutley1]

Thanks Denis - Sorry, yes it's 2000. Looks like I'll go with the indexdefrag as the best option.

Thanks again,

M.

 

[SQLBill]

I run DBCC INDEXDEFRAG against my database (24/7 - 200 GB). I don't do all of the indexes at one time as some are large and take several hours. I've defragged each one individually and kept track of how long each takes. The short ones, I schedule to be defragged one after the other, but the longer ones I do separately.

As SQLDenis stated, the log file WILL grow quite huge. As I said, I don't do all the indexes at one time. I will do some, then run DBCC UPDATEUSAGE, and then run more. I keep a close eye on my log size and if needed, I truncate it. But I never shrink it until all the indexes are done. That way it doesn't have to take time to grow again. Speaking of which, I suggest setting your log autogrow to a large number until your index defragging is done and then set it back to the original amount. For example, my log is normally set to grow by 5000mb. When I do defragging, I set it for double that.

-SQLBill

Posting advice: FAQ481-4875