This post was originally written by Glenn Paulley and posted to sybase.com in October of 2008.
A common problem on Windows is identifying how much physical memory is in use by a SQL Anywhere database server. The Windows Task Manager, which lists all of the active processes on the system, does not actually report true memory usage in the “Memory Size’ column, but instead reports each task’s working set size. Roughly speaking, on Windows XP and other similar Windows operating systems, working set size is the amount of physical RAM resident in memory and recently in use by the process.
The issue here is how Windows defines “recently in use’. Windows periodically “trims’ the working set of a process by marking most or all of the memory allocated to it as “not present” – as if it has been swapped out. However, Windows doesn’t actually swap that memory out, although those memory pages will be among the first to be re-used for other purposes. If the process references that memory again, the process incurs a virtual memory fault. Windows catches this “soft fault” and, knowing that the process is actually using that piece of memory, increments the process’s working set accordingly. Typically, Task Manager will report a drastically reduced working set size for a process when that process is minimized: Windows is making an assumption – invalid for a database server – that a minimized application will utilize very little memory, and severely trims the working set size for that process. As a result, a process’s working set size as displayed by Task Manager can fluctuate significantly, and neither it nor the “VM Size” value reported by Task Manager are useful metrics to use in determining memory consumption of the server.
Aside. With Windows AWE extensions in use with 32-bit servers, the database cache pages are physically locked in memory and cannot be swapped out. Hence they are not considered part of the process’s working set, and not reported by Task Manager. In fact, no builtin Windows utility exists to properly report the memory usage of a process that utilizes Address Windowing Extensions.
Instead of using Task Manager, one should use the values reported for the “Process/Private Bytes” and “Process/Virtual Bytes” counters in Windows Performance Monitor to track server memory consumption and the size of the server’s address space respectively (note that neither of these values include memory for Address Windowing Extensions, either). On all platforms, you can look in the server window to see the cache size at startup, or
Select property('CacheSize') to get the current cache size.
A caveat: while using the Windows NT Performance Monitor can provide extremely useful information, its use can perturb the results of any performance evaluation because the NT Performance Monitor also consumes some degree of server resources. In addition, Performance Monitor statistics can be difficult to interpret properly without additional background knowledge.
The above is an excerpt from Capacity Planning with SQL Anywhere.