The Windows Page File and SQL Server
What is the best “Page File” size for a Windows system running SQL Server? I see this question over and over – and I see people answering it incorrectly all the time.
Note: I’m talking specifically about 64-bit architectures here. The information is different for 32-bit architectures, but I’ll blog about that at another time.
First, let’s start out with defining a few terms. A “Page File” is simply a file on the hard drive that handles situations where the system wants to move (or “page out”) sections of memory. There are several situations that cause this, but the one you’re most concerned about is when the system is out of physical memory. If the system runs out of memory it can “borrow” some storage from the hard drive to release some memory until it needs that data again.
And that’s exceptionally bad. The reason is that hard drives are amazingly slow in comparison with solid-state memory access. So you REALLY slow down a SQL Server when this happens. In fact, if it happens a lot, that’s a sure sign you need to add more physical RAM.
When you install Windows in the server flavors, it takes a couple of defaults that aren’t always best. As a rule, it will try to allocate 1.5 times the amount of physical RAM to the pagefile. If you have a lot of RAM, that really isn’t necessary. In some Windows versions, it also has a “system managed page file size”, meaning it will grow the file as it needs to. I normally choose a different size for the page file, and set both the “top” and “bottom” values to be the same. So what is that size?
Ah – if only it were that simple. As always, the answer is – wait for it - “it depends”. But this time you’re not left in the dark (completely) about how to find that out. It’s a straightforward matter of monitoring a few counters and adjusting the file as needed. Here are the pertinent counters, from a KB article we have for Windows 2003:
|Counter threshold||Suggested value|
|Memory\\Available Bytes||No less than 4 MB|
|Memory\\Pages Input/sec||No more than 10 pages|
|Paging File\\% Usage||No more than 70 percent|
|Paging File\\% Usage Peak||No more than 70 percent|
|Process\\Page File Bytes Peak||Not applicable|
DO NOT take my word for this. Read the full article here to understand what you are looking at - http://support.microsoft.com/kb/889654/en-us.
There’s one more thing to keep in mind. The pagefile can also be used for troubleshooting. If you have the “Full Dump” option set on the system settings in Windows, you will need a higher value – the full 1.5 times in some cases. As always, your mileage may vary, so spend some time in that KB and understand what you’re looking at.
And if you see anyone giving out a hard number for that Page File value, point them here.