BT_PREFETCH_SIZE Option

Restricts the size of the read-ahead buffer for the High_Group B-tree.

Allowed Values

0 – 100. Setting to 0 disables B-tree prefetch.

Default

10

Scope

Can be set only for an individual user. Takes effect immediately.

Description

B-tree prefetch is activated by default for any sequential access to the High_Group index such as INSERT, large DELETE, range predicates, and DBCC (Database Consistency Checker) commands.

BT_PREFETCH_SIZE limits the size of the read-ahead buffer for B-tree pages. Reducing prefetch size frees buffers, but also degrades performance at some point. Increasing prefetch size might have marginal returns. This option should be used in conjunction with the options PREFETCH_GARRAY_PERCENT, GARRAY_INSERT_PREFETCH_SIZE, and GARRAY_RO_PREFETCH_SIZE for non-unique High_Group indexes.

Related reference
GARRAY_INSERT_PREFETCH_SIZE Option
GARRAY_RO_PREFETCH_SIZE Option
PREFETCH_GARRAY_PERCENT Option