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 |