Table 10-2: Comparison of the performance of
dbcc 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
|
