ASA SQL User's Guide
Monitoring and Improving Performance
Top performance tips
Fragmentation occurs naturally as you make changes to your database. Performance can suffer if your files, tables, or indexes are excessively fragmented. This becomes more important as your database increases in size. Adaptive Server Anywhere contains stored procedures that generate information about the fragmentation of files, tables, and indexes.
If the decrease in performance is significant, consider
rebuilding your database to reduce table and/or index fragmentation
putting the database on a disk partition by itself to reduce file fragmentation
running one of the available Windows utilities periodically to reduce file fragmentation
reorganizing your tables to reduce database fragmentation
rebuilding your database if you have performed extensive delete/update/insert activity on a number of tables
using the REORGANIZE TABLE statement to defragment rows in a table, or to compress indexes which may have become sparse due to DELETEs. Reorganizing tables can reduce the total number of pages used to store a table and its indexes, and it may reduce the number of levels in an index tree as well.
For more information about detecting and fixing file, table, and index fragmentation, see Fragmentation.
To eliminate operating system file fragmentation problems, periodically run one of the available disk defragmentation utilities. File fragmentation can have a detrimental impact on performance.
The database server determines the number of file fragments in the database file when you start a database on Windows NT/2000/XP, and displays the following information in the server message window when the number of fragments is greater than one:
Database file "mydatabase.db" consists of nnn fragments
You can also obtain the number of database file fragments using the DBFileFragments database property.
Indexes are designed to speed up searches on particular columns, but they can become fragmented if many DELETEs are performed on the indexed table. This may result in reduced performance if the index is accessed frequently and the cache is not large enough to hold all of the index.
The sa_index_density stored procedure provides information about the degree of fragmentation in a database's indexes. You must have DBA authority to run this procedure. The following statement calls the sa_index_density stored procedure:
CALL sa_index_density (['table_name'[,'owner_name']])
If your index is highly fragmented, you can run REORGANIZE TABLE. You can also drop the index and recreate it. However, if the index is a primary key, you will also have to drop and recreate the foreign key indexes.
As well, you can improve performance by creating a clustered index on a table. Clustered indexes cause table rows to be stored in approximately the same order as they appear in the index.
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.
Adaptive Server 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 space available on the current page reserve, the row is split and the initial row location contains a pointer to another page where the continuous 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 use the sa_table_fragmentation stored procedure to obtain information about the degree of fragmentation of your database tables. The following statement calls the sa_table_fragmentation stored procedure:
CALL sa_table_fragmentation (['table_name' [,'owner_name']])
There are three ways to minimize table fragmentation:
You can specify 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.
You can reorganize specific tables. To defragment particular tables or parts of tables, you can run REORGANIZE TABLE. Reorganizing tables does not disrupt database access.
You can rebuild the entire the database. Rebuilding is more comprehensive in that it defragments all tables, including system tables. Additionally, it rearranges the table rows so they appear in the order specified by the clustered index and primary keys