Space Management Properties

fillfactor, max_rows_per_page, and reservepagegap help manage space on index pages in different ways.

  • fillfactor applies to indexes for all locking schemes. For clustered indexes on allpages-locked tables, it affects the data pages of the table. On all other indexes, it affects the leaf level of the index.

  • max_rows_per_page applies only to index pages of allpages-locked tables.

  • reservepagegap applies to tables and indexes for all locking schemes.

reservepagegap affects space usage in indexes when:
  • The index is created.

  • reorg commands on indexes are executed.

  • Nonclustered indexes are rebuilt after creating a clustered index.

When a reservepagegap value is specified in a create clustered index command, it applies to:
  • The data and index pages of allpages-locked tables

  • Only the index pages of data-only-locked tables

The num_pages value specifies a ratio of filled pages to empty pages on the leaf level of the index so that indexes can allocate space close to existing pages, as new space is required. For example, a reservepagegap of 10 leaves 1 empty page for each 9 used pages.

reservepagegap specified along with create clustered index on an allpages-locked table overwrites any value previously specified with create table or alter table.

You can change the space management properties for an index with sp_chgattribute. Changing properties with sp_chgattribute does not immediately affect storage for indexes on the table. Future large scale allocations, such as reorg rebuild, use the sp_chgattribute value.

The fillfactor value set by sp_chgattribute is stored in the fill_factor column in sysindexes. The fillfactor is applied when an index is re-created as a result of an alter table...lock command or a reorg rebuild command.