ASA SQL User's Guide
Monitoring and Improving Performance
Top performance tips
The page size you choose can affect the performance of your database. Adaptive Server Anywhere supports page sizes of (in bytes) 1024, 2048, 4096, 8192, 16384, or 32768, with 2048 being the default. There are advantages and disadvantages to whichever page size you choose.
While smaller pages hold less information and may force less efficient use of space, particularly if you insert rows that are slightly more than half a page in size. However, small page sizes allow Adaptive Server Anywhere to run with fewer resources because it can store more pages in a cache of the same size. Small pages are particularly useful if your database must run on small machines with limited memory. They can also help in situations when you use your database primarily to retrieve small pieces of information from random locations.
By contrast, a larger page sizes help Adaptive Server Anywhere read databases more efficiently. Large page sizes also tend to benefit large databases, and queries that perform sequential table scans. Often, the physical design of disks permits them to retrieve fewer large blocks more efficiently than many small ones. Other benefits of large page sizes include improving the fan-out of your indexes, thereby reducing the number of index levels, and allowing tables to include more columns.
Keep in mind that larger page sizes have additional memory requirements. And since the maximum number of rows stored on a page is 255, tables with small rows will not fill each page and therefore use space inefficiently. As well, extremely large page sizes (16 kb or 32 kb) are not recommended for most applications unless you can be sure that a large database server cache is always available. Investigate the effects of increased memory and disk space on performance characteristics before using 16 kb or 32 kb page sizes.
The server's memory usage is proportional to the number of databases loaded, and the page size of the databases. It is strongly recommended that you do performance testing (and testing in general) when choosing a page size. Then choose the smallest page size (>= 4K) that gives satisfactory results. It is particularly important to pick the correct (and reasonable) page size if a large number of databases are going to be started on the same server.
You cannot change the page size of an existing database. Instead you must create a new database and use the -p option of dbinit to specify the page size. For example, the following command creates a database with 4K pages.
dbinit -p 4096 new.db
For more information about larger page sizes, see Setting a maximum page size.
If you are working with a Windows NT Service Patch 2 or higher system, or with a Windows 2000/XP system, a page size of at least 4K allows the database server to read a large contiguous region of database pages on disk directly into the appropriate place in cache, bypassing the 64K buffer entirely. This feature can significantly improve performance.