DBCC CHECKDB Recommendations

In Microsoft® SQL Server™ 2000, you can run DBCC CHECKDB while users are using the database because of a change in the type of locks that DBCC CHECKDB holds on the database tables as it checks each one.

In SQL Server 7.0 and earlier, DBCC CHECKDB (which in turn runs DBCC CHECKTABLE and CHECKALLOC on each table in the database) used to hold shared locks (S) on the tables, thus blocking all data modification language (DML) statements.

In SQL Server 2000, DBCC CHECKDB holds a schema lock on the table to prevent meta data changes while the table is being checked, thus allowing DML statements but not any data definition language (DDL) statements on the tables being checked. This change provides greater flexibility as to when you can run DBCC CHECKDB because DBCC CHECKDB does not deny system usage completely to the users.

DBCC CHECKDB is a CPU- and disk-intensive operation. Each data page that requires checking must first be read from disk into memory. In addition, DBCC CHECKDB uses tempdb to do sorting.

If actively performing transactions while DBCC CHECKDB is running, the transaction log continues to grow because the DBCC command blocks log truncation until it has finished reading the log.

It is recommended that DBCC CHECKDB be run during hours when the load is light on the server. If DBCC CHECKDB is run during heavy peak usage time, expect a performance hit on the transaction throughput as well as DBCC CHECKDB completion time.

Recommendations for Good DBCC Performance

• Run CHECKDB when the system usage is low.
  
  
• Be sure that you are not performing other disk I/O operations, such as disk backups.
  
  
• Place tempdb on a separate disk system or a fast disk subsystem.
  
  
• Allow enough room for tempdb to expand on the drive. Use DBCC with ESTIMATE ONLY to estimate how much space will be needed for tempdb.
  
  
• Avoid running CPU-intensive queries or batch jobs.
  
  
• Reduce active transactions while a DBCC command is running.
  
  
• Use the NO_INFOMSGS option to reduce processing and tempdb usage significantly.

Consider using DBCC CHECKDB with the PHYSICAL_ONLY option to check the physical structure of the page and record headers. This operation performs a quick check if hardware-induced errors are suspect.