Should we use the Windows paging file?

[Former Member]

By default, every Windows install I've worked with has a paging file.

On a server that is dedicated to running SQL Anywhere database, I'm thinking we really don't need this file. The last thing we want is for Windows to think it knows more than the SQL Anywere engine in determining what should be temporarily written out to disk.

Has anyone tested this?

[Former Member]

no love for this question? Or does everybody run SQL Anywhere on OS X??

[VolkerBarth]

You might post this question on the general newsgroup (sybase.public.sqlanywhere.general) - I guess the relevant iAnywhere experts are (still?) more involved there...

[johnsmirnios · Accepted solution]

I wouldn't worry about the paging file. Running without one could lead to instability if you end up doing something that needs more memory than is available in your system. Perhaps, for example, you decide to run a web browser on that machine or a diagnostic tool such as ProcMon.

Windows isn't all that bad about using the swap space wisely. In fact, SQLAnywhere actually makes its dynamic cache sizing decisions based on what the Windows memory manager is doing. Whenever we evaluate whether to change the cache size, we consider that the amount of memory available to us in the system is "OS_free_memory + database_server_process_working_set_size". In other words, it is "memory that nobody is using + memory that the OS in all its wisdom and knowledge of everything that is going on in the entire system feels that the database server needs/deserves right now". It's actually a very simple yet powerful way for the database server to adapt to what is going on in the system as a whole.

Windows won't swap things to disk just because it can. It will do so because something else in the system needs memory and then, well, it's probably a good thing to swap. If you watch the working set (using perfmon, NOT task manager), you might notice that Windows will trim the server's working set size periodically. It will do so aggressively whenever you minimize the server's window for example. This behaviour is not problematic because those pages show up as "free" (ie the sum of "avail+working_set" stays the same) but, more importantly, they are not truly freed. The OS leaves the contents in memory but essentially protects the pages (marking them in the page tables as "not present") so that the process will get what is called a "soft fault" when it attempts to access them. Soft faults are inexpensive and essentially amount to a mechanism by which the OS can detect that you are still using that memory. If a sudden demand for memory arises in the system, the OS will first choose pages that are completely unused and then choose pages that have not been referenced "recently" (ie since they were trimmed from a working set).

-john.

[Former Member · Accepted solution]

Great answer John! Thank you