Chapter 12: Creating Indexes on Tables

Using fillfactor, max_rows_per_page, and reservepagegap

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. For a detailed discussion of setting space-management properties for indexes, see create index in the Reference Manual.Table 12-1 summarizes information about the space-management properties for indexes.

**Table 12-1: Summary of space-management properties for indexes**
Property	Description	Use	Comments
fillfactor	Specifies the percent of space on a page that can be filled when the index is created. A fillfactor under 100% leaves space for inserts into a page without immediately causing page splits. Benefits: Initially, fewer page splits. Reduced contention for pages, because there are more pages and fewer rows on a page.	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: Can reduce contention for pages by limiting the number of rows per page and increasing the number of pages.	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: Can reduce row forwarding and lessen the frequency of maintenance activities such as running reorg rebuild and re-creating indexes.	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

View this book as PDF