During supporting our product, running on Sybase SQL Anywhere 9, the following problem/question came up: Scenario:
Server installation: - Microsoft Windows Server 2003 Standard - Sybase: SQL Anywhere 9 - RAM: 4 GB In order to minimize I/O operations for the database engine, we tried to configure the DB Server to use as much memory as possible for caching. We set the following switches: - Windows: /PAE /3GB - DB Server: [...] –cw –c 3600M [...] Now we found on customer machines there were resource bottlenecks that led to system errors. After analysis of Microsoft Support at the customer site, the recommendation from Microsoft was to remove the /3GB switch. After various tests on our test systems with no /3GB switch, we found that the DB Server still uses the specified amount of memory for caching (3.6 GB). Nevertheless the documentation of SQL Anywhere 9 says that the /3GB switch is necessary in this scenario.
Are there any side effects, running the DB Server with the following parameters: (no /3GB, but 3.6 GB cache) ? Windows: /PAE DB Server: [...] –cw –c 3600M [...] Is it supported by Sybase to run an installation using those settings ?
Thanks very much for your help and support.
/3GB is recommended but not absolutely necessary. The way AWE works is that address space is allocated separately from physical memory. In order to access a piece of physical memory, you need to map a piece of you address space to reference the physical memory. With a system that needs AWE, there is generally more physical memory than address space so if the server needs to access a database page in cache that doesn't have address space assigned to it, it must steal the address space from another page in cache that isn't currently being used. This is very much akin to "bank switching" in the (very) olden days if that term means anything to you.
On 32-bit Windows without /3GB, a process has 2GB of address space. On 32-bit Windows with /3GB, a process has 3GB of address space. On 64-bit Windows, a 32-bit process has 4GB of address space and a 64-bit process has many terabytes (you would never use AWE in a 64-bit process).
The more address space we have, the fewer remappings we need to do. It's a performance impact and, depending on your queries, you may never notice. Also, the more address space you have the freer the optimizer is to use large in-memory access plans (in-memory sorts, etc) though I'm not sure how much that applies to v9.
By analogy, AWE is similar to having, say, 200 software licenses for a product but 400 users. Whenever a user wants to use the software on his own machine, he must find someone who is not using their license it and reassign it. If you buy 300 licenses instead, there is less overhead of swapping licenses. If only 100 users use the software on a regular basis, the infrequent swapping done by the 300 other users isn't a significant waste of time.
In your particular case, I'd be very surprised if the /3GB was the source of the problem. It's more likely that your cache size has left too little physical memory for the OS to use. AWE allocates and reserves physical memory exclusively for use by the process that allocated it. The memory cannot be used by any other process to satisfy physical or virtual memory requirements. AWE memory is never swapped to disk. Asking W2K3 Server to run with just 496M of physical memory is a pretty tall order (4096M in system - 3600M for the AWE cache). Imagine a machine running Win2K3 Server (without SQLAnywhere) that has just 496MB total installed in it. That's what you are essentially doing on your 4GB machine with a 3600M AWE cache. It sounds extremely tight to me.
On a machine with just 4GB RAM, you might want to leave 1GB for the OS which means that with /3GB you are darn close to being able to use a conventional cache which can auto-tune its size in response to other demands in the system. With a conventional cache, you can get about a 2.5GB cache.
Where possible, I would recommend that 64-bit versions of SQLAnywhere be used for large caches. AWE was deprecated but still supported in v12 and will be removed in future versions.