Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Monitoring and Improving Performance » Performance improvement tips » Reduce fragmentation

Reducing file fragmentation Next Page

Reducing table fragmentation

Table fragmentation occurs when rows are not stored contiguously, or when rows are split between multiple pages. Performance decreases because these rows require additional page accesses. Table fragmentation is distinct from file fragmentation.

The effect that fragmentation has on performance varies from one situation to the next. 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 may 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 thus 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' ] ] );

See sa_table_fragmentation system procedure.

Methods to reduce fragmentation

The following methods help control table fragmentation:

See also