8

Building statistics on a clustered columnstore table always seems to read the whole table, even if I ask for a small sample. Why is this?

1 Answer 1

12

Sampled statistics on a heap or b-tree use TABLESAMPLE SYSTEM. This algorithm employs an allocation-ordered scan and chooses pages from the scan to sample. All rows on the selected pages contribute to the statistics histogram. You can find details of the selection process in my answer to Strange behaviour with sample sizes for statistics updates.

Clustered columnstore implements TABLESAMPLE SYSTEM differently. There are two separate sampling strategies:

The first algorithm is only used when selecting rows for the primary dictionary build phase of a columnstore index creation (highlighted in the clustered columnstore build plan below).

Primary dictionary build

The implementation is optimized for performance but gives up some accuracy. It uses cluster sampling similar in concept to the method for heaps and b-trees: A set of row groups is randomly selected, followed by a random sample of rows within each selected group. The number of rows and row groups selected is derived from the sampling percentage. Row groups that are not selected in the initial step are not read from disk.

The primary dictionary build phase can be skipped in SQL Server 2019 or later by enabling global trace flag 11611.

The second algorithm is always used for statistics builds. It uses truly random row-level sampling but has a higher I/O and CPU cost. It scans all segments for a column and randomly selects a subset of rows. This can produce more accurate histograms than for b-trees and heaps, which only sample whole pages.

The final statistics object will reflect the desired sampling rate. The implementation makes this more difficult to see than with heaps or b-trees because all segments are read during the process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.