rebuild and reorganize index on databases

[holdahl]

How often is it useful to rebuild and reorganize the index of a database?
Does it depend on the level of activity on the database?
Should it be done on all the databases?


Was planning to run a rebuild and reorganize Monday - Friday at 02:00.

Any comments or suggestions on this?

sH

 

[Catadmin]

Unless you have a high, high, high volume of activity, there really is no need to rebuild your indexes every day of the week. Rebuilds are useful when an index gets fragmented, not when you have next to no updates/deletes/inserts on your database. Use your DBCC commands to check fragmentation issues before wasting processing time & power doing something you may not need to do.

FYI: At my workplace, we do integrity checks once a week along with a reorganization of indexes (not rebuilds) and only rebuild every month or two. Reorganizing may solve your problem without having to drop and recreate the indexes. Try that first if you're having issues.





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[holdahl]

So it is not really necessary to do these kind of tasks (rebuild indexes, update statistics) on any on the system databases? (master, model, msdb and tempdb)


If I use the DBCC commands to check fragmentation:

is there some kind of limit on the fragmentation values when it is a good idea to do a reorganization of indexes?


sH

 

[Catadmin]

You never have to do maintenance on the tempdb of any flavor because it technically is a "memory cache" database for doing transactions and has no real structure, so to speak. Master, Model and MSDB hardly ever change (MSDB only changes as you add jobs, DTS/SSIS packages and replication), so they only ever need FULL/Complete backups done.

I have never done index rebuilds on the system databases and unless you're on a sandbox server and just playing around for kicks, I do NOT recommend it. If you've got a server that no one needs and you want to play around with those commands on the system dbs, go for it. Just realize that there is the possibility of losing that server. Hence my recommendation to never do it on a production box.

As far as your second question goes, you should read up on fragmentation in BOL, check the FAQ section and do some googling of the issue (SQL Server Index Fragmentation). There's plenty of material out there to read. Here's one article that's a fairly decent explanation and puts it better than I could ever explain it:

http://www.sql-server-performance.com/rd_index_fragmentation.asp

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"