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 -

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.

Comments (5)
  1. Steve Jones says:

    It would be really helpful to see some examples here. If I have 4GB RAM, 16GB, what is the pagefile. Or with some values of various counters, how do you use them in a specific instance? Some people will struggle to interpret what you have written.

  2. Buck Woody says:

    Steve – you're correct, some samples would be helpful, but any advice I give would probably be wrong. As I mentioned, the best thing to do is to read that KB and understand what you're looking at – and then make a choice. From there, monitor to ensure you've done the right thing. I know, that's vague, but it is what it is.

  3. Joe Fleming says:

    Thanks for the article Buck!  I needed to be able to justify reducing the pagefile size on my SQL Server, and this helped out a lot.

  4. Zoran M says:

    How about no pf at all? If I can add more RAM to a virtualised sql server, would it actually run faster without any pf?

  5. Hi All , We hasve a Wiindows 2008 R2 , 215 Gb Ram , with 11 Drives and Pagefile of 50 Gb on Z Drive (80 Gb FDree)

    Rrom resource point of view (storage/cpu utilization) as we constantly receive delay messages on data files (excess of 15 seconds to complete I/O operation).

    There has been full DBCC check conducted and there are no corruptions on any of the data files. There are CPU peaks up to almost 100% in batches when the server does some processing… Please provide your recommendation whether we should contact the app owner to enhance resources or determine there is nothing that can we do.

    SQL Server has encountered <<>> occurrence(s) of I/O requests taking longer than 15 seconds to complete on file <<>> in database [XWT] (5). The OS file handle is 0x0000000000000928. The offset of the latest long I/O is: 0x000047753d0000


    Occurance at least on ( CEST M/D/YYYY )

    5/23/2017 5:50 AM
    5/22/2017 5:50 AM
    5/22/2017 2:20 AM
    5/22/2017 12:00 AM
    5/21/2017 2:22 PM
    5/21/2017 12:22 PM
    5/21/2017 11:22 AM
    5/21/2017 10:22 AM
    5/21/2017 5:46 AM

    A problem exists in the system I/O path that causes an I/O to be delayed for 15 seconds or longer. Therefore, you should use the necessary methods and techniques to determine the cause of the delay in the operating system, with the drivers, or with the I/O hardware. Resolution of this problem could involve updating all device drivers and firmware or performing other diagnostics that are associated with your disk system.

Comments are closed.

Skip to main content