Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

A common configuration is 4-6GB of paging file for a modern, dedicated x64 SQL Server system with a decent amount of memory. The Microsoft article is not brilliantly worded or laid out in places, but it does provide specific guidance to refine that figure for your specific workload. Unless you plan to send 64GB memory dumps to Microsoft for debugging at some point, of course :)
–
Paul WhiteDec 1 '12 at 4:02

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.

@jcolebrand I have added a link to lock pages in memory. I use this feature on all servers dedicated to hosting only the database.
–
StanleyJohnsAug 5 '11 at 4:58

8

Please stop telling people to turn on lock pages in memory on every server. On servers that aren't setup correctly this can actually cause problems and it can mask bugs in drivers, Windows, other apps, etc. that then don't get fixed because no one knows about them because they aren't seen.
–
mrdenny♦Aug 6 '11 at 6:43

@mrdenny point noted, and I have edited my post.
–
StanleyJohnsAug 7 '11 at 4:12

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.

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.
–
shlomoidAug 7 '11 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 RusanuAug 7 '11 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 LupoltJul 15 '12 at 5:00

3

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 LupoltJul 15 '12 at 5:17