Take the 2-minute tour ×
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.

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?

share|improve this question
3  
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 White Dec 1 '12 at 4:02
    
@SQLKiwi - thanks for the info. –  Kev Dec 1 '12 at 17:00

3 Answers 3

up vote 11 down vote accepted

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... ;-)

share|improve this answer
    
thanks that work like magic. Many thanks –  user15756 Nov 29 '12 at 18:25

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.

share|improve this answer
    
Can you expound on this any further? –  jcolebrand Aug 4 '11 at 14:51
4  
'Lock pages in memory' could perhaps be better described as a safeguard, to prevent SQL memory being paged out by the OS. support.microsoft.com/kb/918483 –  Mark Storey-Smith Aug 4 '11 at 14:59
    
@jcolebrand I have added a link to lock pages in memory. I use this feature on all servers dedicated to hosting only the database. –  StanleyJohns Aug 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. –  StanleyJohns Aug 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.

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...

share|improve this answer
    
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 '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 Rusanu Aug 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 Jul 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 Jul 15 '12 at 5:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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