Table 25-2 compares the speed, thoroughness, the level of checking and locking, and performance implications of 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 25-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 all indexes, allocation pages, 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 full indexalloc full with full |
Page chains |
Shared table lock; performs extensive I/O; only allocation pages are cached |
Slow |
High |
tablealloc indexalloc with optimized |
Allocation pages |
Shared table lock; performs extensive I/O; only allocation pages are cached |
Moderate |
Medium |
tablealloc indexalloc 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 |
