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: bcarter@bcarter.com


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 
Syntax:


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


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


   ALTER DBSPACE SYSTEM ADD <page-count>


Add space to the transaction log file:


   ALTER DBSPACE TRANSLOG ADD <page-count>

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