MAX_FILL_DATA_PAGES configuration parameter

onconfig.std value

MAX_FILL_DATA_PAGES 0

values

0 or 1

units

Integer

takes effect

After you edit your onconfig file and restart the database server.

When you reset the value dynamically in your onconfig file by running the onmode -wf command.

When you reset the value in memory by running the onmode -wm command.

Usage

Set the MAX_FILL_DATA_PAGES value to 1 to allow tables with variable-length rows to store more rows per data page. Tables whose schemas contain variable-length columns such as VARCHARs have variable-length rows, and all compressed tables contain variable-length rows regardless of schema. This setting can reduce disk space, make more efficient use of the buffer pool, and reduce table scan times.

If MAX_FILL_DATA_PAGES is enabled, the server will add a new row to a data page if adding the row leaves at least 10 percent of the page free for future row expansion. If MAX_FILL_DATA_PAGES is not set, the server will add the row only if there is sufficient room on the page for that table's maximum row size.

Enabling MAX_FILL_DATA_PAGES will not have an immediate effect on any data pages. Only during subsequent inserts or loads of variable-length rows will additional space be utilized on existing data pages.

When MAX_FILL_DATA_PAGES is not set, the insert algorithm could be described as “sure fit”. In other words when scanning the table’s bitmap if the server finds a data page marked as “partly full” it can be certain it has enough space for the new row. When MAX_FILL_DATA_PAGES is set the server no longer has this certainty, and depending on the new row’s size it may need to consider several “partly full” data pages before the insert succeeds. With non-logged tables, an especially long insert that occurs when a checkpoint is pending may cause a longer block time.

It is possible for inserts to result in a different physical order when MAX_FILL_DATA_PAGES is set, because data pages are chosen for new rows using a different algorithm. Also note that as a data page fills, updates made to the variable-length columns in a row could cause the row size to increase such that it no longer fits entirely on the page. In this case the server will split the row onto multiple pages, which may increase the access time for the row.