Comparing the performance of dbcc commands

Table 10-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 10-1 shows which checks are performed by the commands.

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