Types of Scans Performed During update statistics

The types of scans performed during update statistics, the types of locks acquired, and when sorts are needed.

For leaf level index scans, see When Does SAP ASE Perform Scans and Sorts in the Performance and Tuning Series: Query Processing and Abstract Plans to determine which indexes get scanned if the column for which the statistics is being sampled exists in two or more indexes.

Table Name
update statistics Specifying	Scans and Sorts Performed	Locking
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
update statistics Specifying	Scans and Sorts Performed	Locking
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
update statistics Specifying	Scans and Sorts Performed	Locking
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
update statistics Specifying	Scans and Sorts Performed	Locking
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