Export (0) Print
Expand All

Optimizing DBCC CHECKDB Performance

DBCC CHECKDB and related statements typically must read each allocated page from disk into memory so that it can be checked. Running DBCC CHECKDB when there is already a lot of activity on the system therefore impairs DBCC performance for two reasons. First, less memory is available, and the SQL Server Database Engine is forced to spool some of DBCC CHECKDB's internal data to the tempdb database. Second, DBCC CHECKDB tries to optimize the way that it reads data from the disk. If an intensive workload is also using the same disk, optimization will be greatly reduced, resulting in slower execution.

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 CHECKDB against large databases (relative to the size of available memory) causes spooling to the tempdb database. Therefore, tempdb database should be placed on a fast disk or disks, such as a RAID device, separate from user databases. For more information, see ALTER DATABASE (Transact-SQL) and RAID.

NoteNote

Executing DBCC CHECKDB automatically executes DBCC CHECKTABLE for each table in the database, as well as DBCC CHECKALLOC and DBCC CHECKCATALOG, eliminating the need to run them separately.

Community Additions

ADD
Show:
© 2014 Microsoft