Space Management Properties

The space management properties fillfactor, max_rows_per_page, exp_row_size, and reservepagegap help manage space usage for tables.

  • fillfactor leaves extra space on pages when indexes are created, but the fillfactor is not maintained over time.

  • 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, since reducing the number of rows can reduce lock contention. If you specify a max_rows_per_page value and datapages or datarows locking, a warning message is printed. The table is created, and the value is stored in sysindexes, but it is applied only if the locking scheme is changed later to allpages.

  • exp_row_size specifies the expected size of a data row. It applies only to data rows, not to indexes, and applies only to data-only-locked tables that have variable-length columns. It is used to reduce the number of forwarded rows in data-only-locked tables. It is needed mainly for tables where rows have null or short columns when first inserted, but increase in size as a result of subsequent updates. exp_row_size reserves space on the data page for the row to grow to the specified size. If you specify exp_row_size when you create an allpages-locked table, a warning message is printed. The table is created, and the value is stored in sysindexes, but it is applied only if the locking scheme is changed later to datapages or datarows.

  • reservepagegap specifies the ratio of empty pages to full pages to apply for commands that perform extent allocation. It applies to both data and index pages, in all locking schemes.

This table shows the valid combinations of space management properties and locking scheme. If a create table command includes incompatible combinations, a warning message is printed and the table is created. The values are stored in system tables, but are not applied. If the locking scheme for a table changes so that the properties become valid, then they are used.

Property

allpages

datapages

datarows

max_rows_per_page

Yes

No

No

exp_row_size

No

Yes

Yes

reservepagegap

Yes

Yes

Yes

fillfactor

Yes

Yes

Yes

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

Property

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 255

exp_row_size

0

Uses the server-wide default value, which is set with the configuration parameter default exp_row_size percent

reservepagegap

0

Leaves no empty pages during extent allocations

fillfactor

0

Fully packs leaf pages, with space left on index pages