I am turning on DBCC CHECKDB in our OLTP environment (SQL 2005,2008). System overhead is a very visible thing on our serversso I want them to be as efficient as it makes sense for them to be. HENCE – I want to turn on the NOINDEX option, an option I've never used before. My thoughts are these: if there is a problem with an index that is detected outside the integrity check, that I can just rebuild the index. Also the duration of the integrity checks will be drastically reduced, and the nastier corruption will be detected.
What is the flaw in my plan?
Thanks,
Deb
Best Answer
DBCC CHECKDB needs to be done off-hours or at low usage periods. On a huge database, it will (by design) hammer the disk subsystem of the server so badly that it becomes almost unusable. It needs to read every page in the database. Using the NOINDEX option doesn't help you much, because you need to check that none of the (re-buildable) indexes are corrupt. If one of those were corrupt, errors could be returned to your applications or inside procedures/transactions. If all of those errors are not handled properly by your application code or procedures (that is, rolling back nested transactions properly), you could end up with logical data corruption (such as a debit without a credit in an accounting system).
We do weekly CHECKDBs of all databases in the wee hours of Sunday morning, as usage is very low then. For our largest and most heavily used 24x7 operations, we run DBCC CHECKTABLEs instead with a WAITFOR between tables to minimize the end-user impact. If you go this route, you also need to run DBCC CHECKALLOC and DBCC CHECKCATALOG on the database periodically (those are included in a full CHECKDB).
Finally, if you are experiencing any form of corruption in a SQL Server database, you need to immediately look at your storage hardware stack. We haven't seen DB corruption of any sort since SQL 6.5 back in the 1990s in our shop, and we have dozens of high-volume SQL servers. The only time I've seen DB corruption in SQL 7 and later was because of a faulty RAID controller at a customer site (Promise really sucks).