Strategies for Using Consistency Checking Commands

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. This table 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