Estimating the effects of space management properties

Table 17-2 shows how to estimate the effects of setting space management properties.

Table 17-2: Effects of space management properties on space use

Property

Formula

Example

fillfactor

Requires (100/fillfactor) * num_pages if pages are currently fully packed

fillfactor of 75 requires 1.33 times current number of pages; a table of 1,000 pages grows to 1,333 pages.

reservepagegap

Increases space by 1/reservepagegap if extents are currently filled

reservepagegap of 10 increase space used by 10%; a table of 1,000 pages grows to 1,100 pages.

max_rows_per_page

Converted to exp_row_size when converting to data-only-locking

See Table 17-3.

exp_row_size

Increase depends on number of rows smaller than exp_rowsize, and the average length of those rows

If exp_row_size is 100, and 1,000 rows have a length of 60, the increase in space is:

(100 - 60) * 1000 or 40,000 bytes; approximately 20 additional pages.

For more information, see Chapter 14, “Setting Space Management Properties,”.

If a table has max_rows_per_page set, and the table is converted from allpages locking to data-only locking, the value is converted to an exp_row_size value before the alter table...lock command copies the table to its new location.

The exp_row_size is enforced during the copy. Table 17-3 shows how the values are converted.

Table 17-3: Converting max_rows_per_page to exp_row_size

If max_rows_per_page is set to

Set exp_row_size to

0

Percentage value set by default exp_row_size percent

1–254

The smaller of:

  • maximum row size

  • 2002/max_rows_per_page value