Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.

[Home] [Table of Contents] [Previous Section] [Next Section]

Breck Carter
Last modified: February 12, 1998
mail to:

Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques

31 - Deal With Disk Fragmentation

Performance can suffer as your database grows if the file becomes severely fragmented. The example of disk and file fragmentation shown in Figures 31A and 31B is not severe enough to slow things down but trouble could arise if the database continues to grow in a fragmented fashion.

Figure 31A - Norton Speed Disk Shows Disk Fragmentation

Figure 31B - Norton Speed Disk Shows File Fragmentation

One way to deal with fragmentation is to periodically run a defragmentation utility like the Speed Disk component of Norton Utilities. Another way is to put the database all by itself in an empty partition so it can allocate contiguous space as it grows. A third alternative is to use the ALTER DBSPACE statement to preallocate more than enough space for future expansion.

If the ALTER DBSPACE approach is used on a disk that is initially unfragmented then you will avoid future problems. That's because your database file will continue to use contiguous space even if the rest of the disk becomes fragmented.

The ALTER DBSPACE approach also avoids another performance bottleneck where SQL Anywhere must take time to allocate and initialize more disk space every time it runs out. This is done in units of 32 pages and can slow down a database load process as shown in Figure 31C.

Figure 31C - Preallocating Database Disk Space

CREATE TABLE testload1

( primary1 INTEGER NOT NULL,

data1 CHAR ( 100 ) NOT NULL,

PRIMARY KEY ( primary1 ) );

Test 1 - Load without preallocating disk space...

INSERT 100,000 rows: 91.64 seconds

Test 2 - Preallocate space before loading...

ALTER DBSPACE SYSTEM ADD 5000: 7.91 seconds

INSERT 100,000 rows: 76.65 seconds


Total ALTER DBSPACE + INSERT: 84.56 seconds

It isn't just the database file that can grow haphazardly, the transaction log file can cause problems too. ALTER DBSPACE can be used to preallocate log file space as shown in Figure 31D.

Figure 31D - The ALTER DBSPACE Statement


ALTER DBSPACE <dbspace-name> [ ADD <page-count> | RENAME '<file-name>' ]

Add space to the database file with the default dbspace name:


Add space to the transaction log file:


[Home] [Table of Contents] [Previous Section] [Next Section]