Extent allocation means that pages are allocated in multiples of eight, rather than one page at a time. This reduces logging activity by writing only one log record instead of eight.
Commands that perform extent allocation are: select into, create index, reorg rebuild, bcp, alter table...lock, and the alter table...unique and primary key constraint options, since these constraints create indexes. alter table commands that add, drop, or modify columns, or change a table’s partitioning scheme sometimes also require a table-copy operation. By default, all these commands use extent allocation.
Specify reservepagegap value in pages, indicating a ratio of empty pages to filled pages. For example, if you specify a reservepagegap value of 8, an operation that uses extent allocation fills seven pages and leaves the eighth page empty.
Extent allocation operations do not use the first page on each allocation unit, because it stores the allocation page. For example, if you create a clustered index on a large table and do not specify a reserve page gap, each allocation unit has seven empty, unallocated pages, 248 used pages, and the allocation page. Adaptive Server can use the seven empty pages for row forwarding and inserts to the table, which helps keep forwarded rows and inserts with clustered indexes on the same allocation unit. Using reservepagegap leaves additional empty pages on each allocation unit.
See Chapter 12, “Creating Indexes on Tables” in the Transact-SQL Users Guide for information about when to use reservepagegap.
Figure 3-1 shows how an allocation unit might look after a clustered index is created with a reservepagegap value of 16 on the table. The pages that share the first extent with the allocation unit are not used and are not allocated to the table. Pages 279, 295, and 311 are the unused pages on extents that are allocated to the table.
Figure 3-1: Reserved pages after creating a clustered index
