number of pre-allocated extents

number of pre-allocated extents specifies the number of extents (eight pages) allocated in a single trip to the page manager.

Summary Information

Default value

2

Range of values

1–32

Status

Dynamic

Display level

Comprehensive

Required role

System administrator

Configuration group

SQL Server Administration

Currently, this parameter is used only by bcp to improve performance when copying in large amounts of data. By default, bcp allocates two extents at a time and writes an allocation record to the log each time.

Setting number of pre-allocated extents means that bcp allocates the specified number of extents each time it requires more space, and writes a single log record for the event.

An object may be allocated more pages than actually needed, so the value of number of pre-allocated extents should be low if you are using bcp for small batches. If you are using bcp for large batches, increase the value of number of pre-allocated extents to reduce the amount of overhead required to allocate pages and to reduce the number of log records.