Advertisement
Advertisement


Appropriate Windows O/S pagefile size for SQL Server


Question

Does any know a good rule of thumb for the appropriate pagefile size for a Windows 2003 server running SQL Server?

2014/12/16
1
17
12/16/2014 6:29:20 PM

Accepted Answer

Irrelevant of the size of the RAM, you still need a pagefile at least 1.5 times the amount of physical RAM. This is true even if you have a 1 TB RAM machine, you'll need 1.5 TB pagefile on disk (sounds crazy, but is true).

When a process asks 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.

Bare some extreme odd cases, SQL Server will always ask for MEM_COMMIT pages. And given the fact that SQL uses a Dynamic Memory Management policy that reserves upfront as much buffer pool as possible (reserves and commits in terms of VAS), SQL Server will request at start up a huge reservation of space in the pagefile. If the pagefile is not properly sized errors 801/802 will start showing up in SQL's ERRORLOG file and operations.

This always causes some confusion, as administrators erroneously assume that a large RAM eliminates the need for a pagefile. In truth the contrary happens, a large RAM increases the need for pagefile, just because of the inner workings of the Windows NT memory manager. The reserved pagefile is, hopefully, never used.

2009/11/22
11
11/22/2009 10:11:18 PM


According to Microsoft, "as the amount of RAM in a computer increases, the need for a page file decreases." The article then goes on to describe how to use Performance Logs to determine how much of the page file is actually being used. Try setting your page file to 1.5X system memory for a start, then do the recommended monitoring and make adjustments from there.

How to determine the appropriate page file size for 64-bit versions of Windows

2013/01/27

The bigger the better up to the size of the working set of the application where you will start to get into diminishing returns. You can try to find this by slowly increasing or decreasing the size until you see a significant change in cache hit rates. However, if the cache hit rate is over 90% or so you're probably OK. Generally you should keep an eye on this on a production system to make sure it hasn't outgrown its RAM allocation.


We were recently having some performance issues with one of our SQL Server that we weren't able to completely narrow down, and actually used one of our Microsoft support tickets to have them help troubleshoot. The optimal pagefile size to use with SQL Server came up, and Microsoft's recommendation is that it be 1 1/2 times the amount of RAM.

2009/09/10

If you're looking for high performance, you are going to want to avoid paging completely, so the page file size becomes less significant. Invest in as much RAM as feasible for the DB server.

2008/08/11

After much research our dedicated SQL Servers running Enterprise x64 on Windows 2003 Enterprise x64 have no page file.

Simply, the page file is a cache for files that gets managed by the OS, and SQL has it's own internal memory management system.

The MS article referenced does not qualify that the advice is for the OS running out-of-the-box services such as file sharing.

Having a page file simply burdens the disk I/O because Windows is trying to help, when only the SQL OS can do the job.

2011/05/24

Source: https://stackoverflow.com/questions/2588
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]