Reducing table fragmentation

Table fragmentation occurs when rows are not stored contiguously, or when rows are split between multiple pages. These rows require additional page access and this reduces the performance of the database server.

The effect that fragmentation has on performance varies. A table might be highly fragmented, but if it fits in memory, and the way it is accessed allows the pages to be cached, then the impact may be minimal. At the other end of the scale, a fragmented table may cause much more I/O to be done and can result in a significant performance hit if split rows are accessed frequently and the cost of extra I/Os is not reduced by caching.

While reorganizing tables and rebuilding a database reduces fragmentation, doing so too frequently or not frequently enough, can also impact performance. Experiment using the tools and methods described in the section below to determine an acceptable level of fragmentation for your tables.

If you reduce fragmentation and performance is still poor, another issue may be to blame, such as inaccurate statistics.

Determine the degree of table fragmentation

Use the sa_table_fragmentation system procedure to obtain information about the degree of fragmentation of your database tables. Running this system procedure just once is not helpful in determining whether to defragment to improve performance. Instead, rebuild your database and run the procedure to establish baseline results. Then, continue to run it periodically over an extended length of time, looking for correlation between the change in its output to changes in performance measures. In this way you can determine the rate at which tables become fragmented to the degree that performance is impacted, and so determine the optimal frequency at which to defragment tables.

You must have DBA authority to run this procedure. The following statement calls the sa_table_fragmentation system procedure:

CALL sa_table_fragmentation( [ 'table-name' [, 'owner-name' ] ] );

Methods to reduce fragmentation

The following methods help control table fragmentation:

Use PCTFREE SQL Anywhere reserves extra room on each page to allow rows to grow slightly. When an update to a row causes it to grow beyond the original space allocated for it, the row is split and the initial row location contains a pointer to another page where the entire row is stored. For example, filling empty rows with UPDATE statements or inserting new columns into a table can lead to severe row splitting. As more rows are stored on separate pages, more time is required to access the additional pages.
You can reduce the amount of fragmentation in your tables by specifying the percentage of space in a table page that should be reserved for future updates. This PCTFREE specification can be set with CREATE TABLE, ALTER TABLE, DECLARE LOCAL TEMPORARY TABLE, or LOAD TABLE.
Reorganize tables You can defragment specific tables using the REORGANIZE TABLE statement. Reorganizing tables does not disrupt database access.
Rebuild the database Rebuilding the database defragments all tables, including system tables, provided the rebuild is performed as a two-step process, that is, data is unloaded and stored to disk, and then reloaded. Rebuilding in this manner also has the benefit of rearranging the table rows so they appear in the order specified by the clustered index and primary keys. One-step rebuilds (for example, using the -ar, -an, or -ac options), do not reduce table fragmentation.