Scan types, sort requirements, and locking

Table 3-1 shows the types of scans performed during update statistics, the types of locks acquired, and when sorts are needed.

Table 3-1: Scans, sorts, and locking during update statistics

update statistics specifying

Scans and sorts performed

Locking

Table name

Allpages-locked table

Table scan, plus a leaf-level scan of each nonclustered index

Level 1; shared intent table lock, shared lock on current page

Data-only-locked table

Table scan, plus a leaf-level scan of each nonclustered index and the clustered index, if one exists

Level 0; dirty reads

Table name and clustered index name

Allpages-locked table

Table scan

Level 1; shared intent table lock, shared lock on current page

Data-only-locked table

Leaf level index scan

Level 0; dirty reads

Table name and nonclustered index name

Allpages-locked table

Leaf level index scan

Level 1; shared intent table lock, shared lock on current page

Data-only-locked table

Leaf level index scan

Level 0; dirty reads

Table name and column name

Allpages-locked table

Table scan; creates a worktable and sorts the worktable

Level 1; shared intent table lock, shared lock on current page

Data-only-locked table

Table scan; creates a worktable and sorts the worktable

Level 0; dirty reads