fillfactor, max_rows_per_page, and reservepagegap are space-management properties that apply to tables, and indexes and affect the way physical pages are filled with data.
Property |
Description |
Use |
Comments |
---|---|---|---|
fillfactor |
Specifies the percent of space on a page that can be filled when the index is created. A fillfactor less than 100% leaves space for inserts into a page without immediately causing page splits. Benefits:
|
Applies only to a clustered index on a data-only-locked table. |
The fillfactor percentage is used only when an index is created on a table with existing data. It does not apply to pages and inserts after a table is created. If no fillfactor is specified, the system-wide default fillfactor is used. Initially, this is set to 100%, but can be changed using sp_configure. |
max_rows_per_page |
Specifies the maximum number of rows allowed per page. Benefit:
|
Applies only to allpages-locked tables. The maximum value that you can set this property to is 256. |
max_rows_per_page applies at all times, from the creation of an index, onward. If not specified, the default is as many rows as will fit on a page. |
reservepagegap |
Determines the number of pages left empty when extents are allocated. For example, a reservepagegap of 16 means that 1 page of the 16 pages in 2 extents is left empty when the extents are allocated. Benefits:
|
Applies to pages in all locking schemes. |
If reservepagegap is not specified, no pages are left empty when extents are allocated. |
This statement sets the fillfactor for an index to 65% and sets the reservepagegap to one empty page for each extent allocated:
create index postalcode_ind2 on authors (postalcode) with fillfactor = 10, reservepagegap = 8