Setting Space Management Properties for Indexes

The space management properties fillfactor, max_rows_per_page, and reservepagegap in the alter table statement apply to indexes that are created for primary key or unique constraints. The space management properties affect the data pages of the table if the constraint creates a clustered index on an allpages-locked table.

  • Use sp_chgattribute to change max_rows_per_page or reservepagegap for a table or an index, to change the exp_row_size value for a table, or to store fillfactor values.

  • Space management properties for indexes are applied when indexes are:
    • Re-created as a result of an alter table command that changes the locking scheme for a table from allpages locking to data-only locking or vice versa.

    • Automatically rebuilt as part of a reorg rebuild command.

  • To see the space management properties currently in effect for a table, use sp_help. To see the space management properties currently in effect for an index, use sp_helpindex.

  • The space management properties fillfactor, max_rows_per_page, and reservepagegap help manage space usage for tables and indexes in the following ways:
    • fillfactor leaves extra space on pages when indexes are created, but the fillfactor is not maintained over time. It applies to all locking schemes.

    • max_rows_per_page limits the number of rows on a data or index page. Its main use is to improve concurrency in allpages-locked tables.

    • reservepagegap specifies the ratio of empty pages to full pages to apply for commands that perform extent allocation. It applies to all locking schemes.

    You can store space management properties for tables and indexes so that they are applied during alter table and reorg rebuild commands.

  • This table shows the valid combinations of space management properties and locking schemes. If an alter table command changes the table so that the combination is not compatible, the values stored in the stored in system tables remain there, but are not applied during operations on the table. If the locking scheme for a table changes so that the properties become valid, then they are used.

    Parameter

    Allpages

    Datapages

    Datarows

    max_rows_per_page

    Yes

    No

    No

    reservepagegap

    Yes

    Yes

    Yes

    fillfactor

    Yes

    Yes

    Yes

    exp_row_size

    No

    Yes

    Yes

  • This table shows the default values and the effects of using the default values for the space management properties.

    Parameter

    Default

    Effect of Using the Default

    max_rows_per_page

    0

    Fits as many rows as possible on the page, up to a maximum of 256

    reservepagegap

    0

    Leaves no gaps

    fillfactor

    0

    Fully packs leaf pages