25

This Microsoft article - How to determine the appropriate page file size for 64-bit versions of Windows Server 2008 and or Windows 2008 R2 provides guidance for calculating the page file size for 64 bit Windows 2008 and Windows 2008R2. This no doubt works fine for general purpose servers. I'm wondering what the guidance is for SQL Server 2008R2 running on Windows 2008/R2 64 bit?

I'm presuming we want as little in memory data to be hitting the page file otherwise SQL could be hitting the disk twice for data. Does SQL Server even allow data in memory to hit the page file? I've hunted through SQL Server 2008 R2 Books Online for guidance but haven't yet found any mention of page file use.

Here's a potential usage scenario: Given a physical server with 64GB of RAM, is a pagefile necessary for the entire 64GB of RAM? Should we gear it up for 96GB of pagefile? That does seem a bit excessive for a single file. I know conventional wisdom has been that Windows couples pagefile to memory in an attempt to make swapping out apps easier on RAM, but is that true? Will a less than 64GB pagefile hinder performance here?

Improve this question
0

3 Answers 3

Reset to default
15

There are no special settings for SQL Server which only uses physical memory normally

Just do what MS say for Windows and that's it

Oh, and buy more RAM anyway while we're one the subject... ;-)

Improve this answer
0
6

Look into lock pages in memory. This way, you can give preference for your SQL service account to use available RAM rather than paging to disk. To read more on lock pages in memory, check this link. A snippet follows:

The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not required on 64-bit operating systems.

Please test this feature before using on your systems.

Improve this answer
1
4

Yes, for 64GB RAM you need at least 64GB swap file (96GB recommended). Not because of potential swapping, but because of the design of the Windows Memory Manager. I've wrote about this issue before in System pagefile size on machines with large RAM:

When a process asks for MEM_COMMIT memory via VirtualAlloc/VirtualAllocEx, the requested size needs to be reserved in the pagefile. This was true in the first Win NT system, and is still true today see Managing Virtual Memory in Win32:

When memory is committed, physical pages of memory are allocated and space is reserved in a pagefile.

The alternative would be something like the oom_killer.

So do follow the recommendation, sometimes things are a bit more complex than they look. And I haven't even touched the complications brought by AWE and the lock pages privilege...

Improve this answer
5
  • Very interesting... How does it work then when you set a swap file smaller than the RAM in the machine? If indeed we need to reserve the space in the pagefile for every memory allocation, we wouldn't be able to use more than the page file size's worth of memory? I am not sure this is how it works in practice.
    – shlomoid
    Aug 7, 2011 at 7:02
  • 1
    That's exactly how it works in practice. A committed VA region must be backed by real swap reservation. A reserved VA region does not have to be, but SQL Server virtually never requests non-committed reservations.
    – Remus Rusanu
    Aug 7, 2011 at 7:29
  • 2
    I don't think this is correct. My understanding from various sources such as the Windows Internals books is that committed virtual address space has to be backed by something physical, either page file or RAM. So if you try to commit virtual memory > ( [Physical memory Windows sees] + [Paging file size]), you'll get the infamous "Your system is low on virtual memory" error message. Mark Russinovich talks about this in the section headed "committed memory" here.
    – James Lupolt
    Jul 15, 2012 at 5:00
  • 5
    I think you can confirm to yourself that committed VA regions don't have to be backed by swap reservations just by booting a system with no paging file and confirming that Windows starts, & therefore there must be more than 0 bytes of committed VAS.
    – James Lupolt
    Jul 15, 2012 at 5:17
  • This post is wrong - it's perfectly possible to run without a page file if you have more memory than your max commit requirements. It will mean you can't write crash dumps however.
    – Steve365
    Nov 10, 2016 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.