Table 17-2 shows how to estimate the effects of setting space management properties.
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.
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:
|
