Limiting the memory used by the cache

Dynamic cache sizing (Windows NT/2000/XP, Windows 95/98/Me)

ASA SQL User's Guide
Monitoring and Improving Performance
Using the cache to improve performance

Limiting the memory used by the cache

The initial, minimum, and maximum cache sizes are all controllable from the database server command line.

Initial cache size You can change the initial cache size by specifying the database server -c command-line option. The default value is as follows:
- Windows CE The formula is as follows:
```
max( 600K, min( dbsize , physical-memory ) )
```
  where dbsize is the total size of the database file or files started, and physical-memory is 25% of the physical memory on the machine.
- Windows NT/2000/XP, Windows 95/98/Me, NetWare The formula is as follows:
```
max( 2M, min( dbsize , physical-memory ) )
```
  where dbsize is the total size of the database file or files started, and physical-memory is 25% of the physical memory on the machine.
  
  If an AWE cache is used on Windows 2000, Windows XP, or Windows Server 2003 the formula is as follows:
```
min( 100% of available memory-128MB, dbsize )
```
  An AWE cache is not used if this value is smaller than 2 Mb.
  
  For information about AWE caches, see -cw server option.
- UNIX At least 8 Mb.
  
  For information about UNIX initial cache size, see Dynamic cache sizing (UNIX).
Maximum cache size You can control the maximum cache size by specifying the database server -ch command-line option. The default is based on an heuristic that depends on the physical memory in your machine. On non-UNIX machines, this is usually approximately 90% of total physical memory, but not more than 256 Mb. On UNIX machines, this is the greater of the following:
```
(25% total physical-memory,
   25%(available-physical-memory + available-swap-space),
   sum-of-file-sizes-on-the-command-line)
```
but is no more than 256 Mb and is no less than 24 Mb.
Minimum cache size You can control the minimum cache size by specifying the database server -cl command-line option. By default, the minimum cache size is the same as the initial cache size.

You can also disable dynamic cache sizing by using the -ca command-line option.

For more information on command-line options, see The database server.