Optimizing DBCC Performance
The database console command (DBCC) tends to be both CPU and disk intensive because DBCC must read each data page that requires checking from disk into memory (unless the data page is already cached in memory). Running DBCC when there is a lot of activity on the system, such as intensive query processing, impairs DBCC performance because less memory is available and Microsoft® SQL Server™ 2000 is forced to spool data pages to the tempdb database. Therefore, DBCC statements execute faster if more memory is made available for DBCC processing because more of the database can be cached.
Because the tempdb database resides on disk, the bottleneck from I/O operations as data is written to and from disk impairs performance. Regardless of system activity, running DBCC against large databases (relative to the size of available memory) causes spooling to the tempdb database. Therefore, it is recommended that the tempdb database be placed on a separate fast disk or disks, such as a RAID (redundant array of independent disks) device, from user databases. For more information, see ALTER DATABASE and RAID.
Note Executing DBCC CHECKDB automatically executes DBCC CHECKTABLE for each table in the database and DBCC CHECKALLOC, eliminating the need to run them separately.