Values for fillfactor, exp_row_size, reservepagegap and max_rows_per_page can affect object size.
The fillfactor you specify for create index is applied when the index is created. The fillfactor is not maintained during inserts to the table. If a fillfactor has been stored for an index using sp_chgattribute, this value is used when indexes are re-created with alter table...lock commands and reorg rebuild. The main function of fillfactor is to allow space on the index pages, to reduce page splits. Very small fillfactor values can cause the storage space required for a table or an index to be significantly greater.
With the default fillfactor of 0, the index management process leaves room for two additional rows on each index page when you create a new index. When you set fillfactor to 100 percent, it no longer leaves room for these rows. The only effect that fillfactor has on size calculations is when calculating the number of clustered index pages and when calculating the number of non-leaf pages. Both of these calculations subtract 2 from the number of rows per page. Eliminate the -2 from these calculations.
Other values for fillfactor reduce the number of rows per page on data pages and leaf index pages. To compute the correct values when using fillfactor, multiply the size of the available data page (2016) by the fillfactor. For example, if your fillfactor is 75 percent, your data page would hold 1471 bytes. Use this value in place of 2016 when you calculate the number of rows per page. For these calculations, see “Step 2: Compute the number of data pages” and “Step 8: Calculate the number of leaf pages in the index”.
Setting an expected row size for a table can increase the amount of storage required. If your tables have many rows that are shorter than the expected row size, setting this value and running reorg rebuild or changing the locking scheme increases the storage space required for the table. However, the space usage for tables that formerly used max_rows_per_page should remain approximately the same.
Setting a reservepagegap for a table or an index leaves empty pages on extents that are allocated to the object when commands that perform extent allocation are executed. Setting reservepagegap to a low value increases the number of empty pages and spreads the data across more extents, so the additional space required is greatest immediately after a command such as create index or reorg rebuild. Row forwarding and inserts into the table fill in the reserved pages. For more information, see “Leaving space for forwarded rows and inserts”.
The max_rows_per_page value (specified by create index, create table, alter table, or sp_chgattribute) limits the number of rows on a data page.
To compute the correct values when using max_rows_per_page, use the max_rows_per_page value or the computed number of data rows per page, whichever is smaller, in“Step 2: Compute the number of data pages” and “Step 8: Calculate the number of leaf pages in the index”.
