The following sections compare the performance of the dbcc commands, provide suggestions for scheduling and strategies to avoid serious performance impacts, and provide information about dbcc output.
Table 11-2 compares the dbcc commands. Remember that dbcc checkdb, dbcc checktable, and dbcc checkcatalog perform different types of integrity checks than dbcc checkalloc, dbcc tablealloc, and dbcc indexalloc. dbcc checkstorage performs a combination of the some of the checks performed by the other commands. Table 11-1 shows which checks are performed by the commands.
Command and option |
Level |
Locking and performance |
Speed |
Thorough-ness |
|---|---|---|---|---|
checkstorage |
Page chains and data rows for allocation pages, page linkages for indexes, OAM pages, device and partition statistics |
No locking; performs extensive I/O and may saturate the system’s I/O; can use dedicated cache with minimal impact on other caches |
Fast |
High |
checktable checkdb |
Page chains, sort order, data rows, and partition statistics for all indexes |
Shared table lock; dbcc checkdb locks one table at a time and releases the lock after it finishes checking that table |
Slow |
High |
checktable checkdb with skip_ncindex |
Page chains, sort order, and data rows for tables and clustered indexes |
Shared table lock; dbcc checkdb locks one table at a time and releases the lock after it finishes checking that table |
Up to 40 percent faster than without skip_ncindex |
Medium |
checkalloc |
Page chains and partition statistics |
No locking; performs extensive I/O and may saturate the I/O calls; only allocation pages are cached |
Slow |
High |
tablealloc , indexalloc , and textalloc with full |
Page chains |
Shared table lock; performs extensive I/O; only allocation pages are cached |
Slow |
High |
tablealloc , indexalloc, and textalloc with optimized |
Allocation pages |
Shared table lock; performs extensive I/O; only allocation pages are cached |
Moderate |
Medium |
tablealloc , indexalloc, and textalloc with fast |
OAM pages |
Shared table lock |
Fast |
Low |
checkcatalog |
Rows in system tables |
Shared page locks on system catalogs; releases lock after each page is checked; very few pages cached |
Moderate |
Medium |
