In IDS, when a new row containing a column that can vary in length is to be inserted on a page, the row will be inserted only if the page contains sufficient space to allow the new row to grow to its maximum length. This can result in low disk space usage. In IDS 11, you can change this default behavior by setting the configurationparameter MAX_FILL_DATA_PAGES to 1. When MAX_FILL_DATA_PAGES is enabled, the server will add a new row to a page ifafter adding the row, at least 10 percent of the page is free for the future expansion of the rows. The database server needs to be restarted after changing this configuration parameter.

To take advantage of this setting:

Existing tables with variable-length rows must be reloaded OR

Existing pages must be modified, followed by further inserts

Advantages of enabling MAX_FILL_DATA_PAGES are:

More data can be stored in less disk space

Enables the server to use the buffer pool more efficiently

Reduces fetch times as more rows can be accessed by reading less number of pages

The possible disadvantages of enabling MAX_FILL_DATA_PAGES are:

Allowing more variable-length rows per page might store rows in a different physical order

As the page fills, updates made to the variable-length columns in a row could cause the row to expand so itno longer completely fits on the page. This causes the server to split the row onto two pages, increasing the access time for the row

Example: Suppose you have a table with a column of type lvarchar(6000). If the dbspace in which the table resides is of 4K or 8k pagesize, you might see a lot of unused space in the pages. If you do not have to make updates which would cause most of the rows to expand to their full length, you might want to enable MAX_FILL_DATA_PAGES tobetter use the space in each page.