THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

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 - http://support.microsoft.com/kb/889654/en-us.

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.

Published Tuesday, June 29, 2010 7:48 AM by BuckWoody

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Dan said:

Buck, one thing you should probably mention is that you should always set your the "lock pages in memory" policy to keep SQL from paging to disk.

I still can't believe MS recommends a blanket 1.5x page file.  I understand if you want the core dump, but seriously - I have servers with 128GB of RAM and there's no way I'm reserving 200GB for a page file...

June 29, 2010 11:06 AM
 

Remus Rusanu said:

The KB is valid for general Windows processes. SQL Server behaves differently than most other processes and precommits all memory it reserves. When MEM_COMMIT is passed to VirtualAlloc, the page file reservation must occur before the call returns, so all memory committed by SQL Server must be backed by page file reservations (ie. the page file must be at least the size of memory *comitted* by SQL).

What saves the day is AWE: memory reserved through AWE is not required to have a page file reservation (since is locked in the physical RAM and therefore it cannot be paged). The use of AWE is automatic on x64.

June 29, 2010 1:46 PM
 

GrumpyOldDBA said:

I've found x64 systems will run fine without a page file, however convincing your friendly data centre is another matter! I should clarify that I have 10GB of unallocated memory and I don't use lock pages in memory ( dedicated sql server ). In tests I found that if there is a page file then windows will constantly use it regardless of the amount of free memory you have or if anything is actually running - sadly it seems things like AV and server monitoring ultilites seem to be worst at this. As far as memory dumps are concerned, I've had this argument and got nowhere - the data centre insist on only allocating small amounts to o/s disks - typically 16GB so you'll never do a memory dump of the 64gb on the server, sigh!

June 30, 2010 6:54 AM
 

james said:

Maybe you should stick to blogging about topics you actually know something about. Totally off base here. 70% page file time? Stay away from my servers.

June 30, 2010 9:17 AM
 

BuckWoody said:

Thakns for reading, and posting a response James. I have to disagree with you - I do tend to post about what I know. Perhaps you didn't read the entire article closely enough. I never said that - it's from a KB. You might want to take that up with the author - I can put you in touch with them if you like.

June 30, 2010 11:56 AM
 

DaneDaneDane said:

I am more lost than ever now..  Results from KB article counters -

Server                   Swapfile Size (1)              Bytes Used (2)  Peak Bytes (3)                   Current Swapfile             Physical RAM

SQL Srv1                3.7401                                   40GB                     46GB                                     8GB, 16GB                           48GB

SQL Srv2                5.30628                                 43GB                     46GB                                     32GB, 72GB who              48GB

SQL Srv3                6.3                                          50GB                     85GB                                     4GB, 8GB                             64GB

(1) This column l already calculated -

Note the maximum value for the Page File Bytes Peak counter, and then multiply the value by 0.70. The sum of the equation is the size to set for your page file.

(2) To determine the approximate minimum page file that is required by your system, calculate the sum of peak private bytes that are used by each process on the system. Then, subtract the amount of memory on the system.

(3) To determine the approximate maximum page file space that is required for your system, calculate the sum of peak private bytes that are used by each process on the system. Then, add a margin of additional space. Do not subtract the amount of memory on the system. The size of the additional margin can be adjusted based on your confidence in the snapshot data that is used to estimate page file requirements.

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

Windows 2008 R2

SQL Server 2008 R2

August 4, 2010 1:27 AM

Leave a Comment

(required) 
(required) 
Submit

About BuckWoody

http://buckwoody.com/BResume.html

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement