none
Page File configuration

    Question

  • I have SQL servers running 2005, 2008, and 2012. I do have about 64, 128 GB memory in some of them. I have also configured SQL to manage memory and given only 6 GB to the OS.

    The question is how should the pagefile be configured? with which formula? I have been using 1.5 * the RAM in the past. if 1.5 * RAM is right, should it be 1.5 * 6 GB (memory allocated to OS) or 1.5 * total memory installed on the box?

    and does VM matters?

    I have already read this article http://support.microsoft.com/kb/2160852

    Thanks!


    Alert from TechNet Posting

    Saturday, December 08, 2012 1:11 AM

Answers

  • Hello Kojo,

    I would like to first point out that 6 GB of ram is NOT enough to leave for the OS on those servers - especially the ones with 128 GB.

    Now, the question about the page file is this. How often do you expect to need a full memory dump? If you're server is setup correctly then it shouldn't need to page very much and if you're not expecting to need full memory dumps then I would set it to an average size around 24 GB for the 128GB and maybe 16 for the 64 GB box. Obviously the risk here is if there is a dump that happens it won't be able to be captured.

    The other option is that the drive that holds the memory dump starting with windows server 2008 to the NON-OS drive and only allocate memory + 1 MB (for the page file header and structures though it only take about another 64k) for the file. You can read more about it here: http://support.microsoft.com/kb/254649

    -Sean


    Sean Gallardy | Blog | Twitter

    Saturday, December 08, 2012 1:26 AM
    Answerer
  • In regards to SQL Server specifically, let me point out that in modern systems, SQL Server should almost never touch the page file at all, because if it does performance falls off hugely.  So configure the page file only for non-SQL server activities as may be.

    VM matters a little or a lot.  When you get to big SQL Server instances, you probably do NOT want them to be VM, and in fact many VM systems won't support big instances anyway - or at least, didn't up to last year, I wouldn't be surprised if VM capacities are now growing too.  For small and not too busy SQL Server instances it's OK, I was just using a moderately large and moderately busy system and the VM didn't seem a noticeable issue.  BUT just as you don't want SQL Server touching the page file, IF you run SQL Server in a VM, you do NOT want that VM to be swapping large parts of SQL Server in and out or otherwise overburdening the resources, or performance falls off very very quickly.  You use VM for manageability, but NOT for resource overbooking, if one of those systems is your SQL Server!

    Josh

    Saturday, December 08, 2012 1:51 AM

All replies

  • Hello Kojo,

    I would like to first point out that 6 GB of ram is NOT enough to leave for the OS on those servers - especially the ones with 128 GB.

    Now, the question about the page file is this. How often do you expect to need a full memory dump? If you're server is setup correctly then it shouldn't need to page very much and if you're not expecting to need full memory dumps then I would set it to an average size around 24 GB for the 128GB and maybe 16 for the 64 GB box. Obviously the risk here is if there is a dump that happens it won't be able to be captured.

    The other option is that the drive that holds the memory dump starting with windows server 2008 to the NON-OS drive and only allocate memory + 1 MB (for the page file header and structures though it only take about another 64k) for the file. You can read more about it here: http://support.microsoft.com/kb/254649

    -Sean


    Sean Gallardy | Blog | Twitter

    Saturday, December 08, 2012 1:26 AM
    Answerer
  • In regards to SQL Server specifically, let me point out that in modern systems, SQL Server should almost never touch the page file at all, because if it does performance falls off hugely.  So configure the page file only for non-SQL server activities as may be.

    VM matters a little or a lot.  When you get to big SQL Server instances, you probably do NOT want them to be VM, and in fact many VM systems won't support big instances anyway - or at least, didn't up to last year, I wouldn't be surprised if VM capacities are now growing too.  For small and not too busy SQL Server instances it's OK, I was just using a moderately large and moderately busy system and the VM didn't seem a noticeable issue.  BUT just as you don't want SQL Server touching the page file, IF you run SQL Server in a VM, you do NOT want that VM to be swapping large parts of SQL Server in and out or otherwise overburdening the resources, or performance falls off very very quickly.  You use VM for manageability, but NOT for resource overbooking, if one of those systems is your SQL Server!

    Josh

    Saturday, December 08, 2012 1:51 AM
  • The article provides a walk through method for how to evaluate the most suitable Page File size for your environment.


    John Sansom | SQL Server DBA Blog | @SQLBrit on Twitter

    Saturday, December 08, 2012 1:57 PM
  • 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

    http://support.microsoft.com/kb/889654/en-us


    Manish

    Saturday, December 08, 2012 3:16 PM
  • Dude, if you're going to copy directly from Buck Woody's blog, at least do the courtesy of referencing the source.

    John Sansom | SQL Server DBA Blog | @SQLBrit on Twitter


    • Edited by John Sansom Saturday, December 08, 2012 3:28 PM
    Saturday, December 08, 2012 3:27 PM