My guess is that you will get many different answeres to this question. A lot will depend on the individual experiences of the DBA offering the advice.
My own background is not extensive (< 3 years). From what I have read in several books, the need for CHECKDB was greatly reduced in SQL 7.0. In addition, SQL periodically updates statistics on the indices.
I run 3 production SQL servers with database sizes ranging from 1 GB to 25 GB. Historically, I have chosen to focus in on a couple of hot tables and keep those up to snuff with a nightly DBCC DBREINDEX. On a quarterly basis, I do a DBCC CHECKDB, DBCC DBREINDEX (on all tables), defrag the hard drives (and also do any O/S updates).
Most of the time performance is quite acceptable. I do feel sometimes as though my horse is "panting" as I get to the end of a quarter, but in general things run quite smoothly.
I hope this helps; I would certainly appreciate reading other responses as to the necessity of some of the DBCC procedures.
Originally posted by sqlserver2k
Every week we do the maintenance for our database and we run the general DBCC CheckDB,Update Statistics,DBCC IndexDefrag,Update Statistics,sp_recompile ..
We are just wondering that whether it's a right procedure or not and also should we Defrag the Index before we run the Update Statistics or it should be run after Update Statistics.
DBCC DBREINDEX is a good command but we can't use it as our database is 24 X 7,hence we have to compermise with DBCC INDEXDEFRAG.
With Sql 2000(and perhaps with Sql7 also) you can set the Auto Update Statistics but it doesn't hurt to run the DBCC CheckDB on a weekly basis as it can take care of most of the problems.
Again it varies from situation to situation.