Menu
Does any know a good rule of thumb for the appropriate pagefile size for a Windows 2003 server running SQL Server?
263
SQL Server doest not need a giant page file. if you're installing other applications on the server (which we don't recommend-- SQL Server should be isolated). you may need a larger page file. If SQL Server is the only major service running on the box, we typically create a 2GB size page file on the system drive.
Your paging file size should be 1.5 times your physical memory at a minimum and up to 4 times the physical memory at most to ensure system stability. Your minimum paging file size can be calculated by 8 GB x 1.5, and your maximum paging file size by 8 GB x 4.
Minimize the paging file The paging file is typically 1.25 GB on 8 GB systems, 2.5 GB on 16 GB systems and 5 GB on 32 GB systems. For systems with more RAM, you can make the paging file somewhat smaller.
The "pagefile" is a WINDOWS RAM concept, not a SQL Server concept. Windows swaps RAM to the page file when WINDOWS decides it needs more RAM. SQL Server has no control or knowledge of the Windows page file.
With all due respect to Remus (whom I respect greatly), I strongly disagree. If your page file is large enough to support a full dump, it will perform a full dump every time. If you have a very large amount of RAM, this can cause a tiny blip to became a major outage.
You do NOT want your server to have to write out 1 TB of RAM to disk if there is a one-time transient issue. If there is a recurring issue, you can increase the page file to capture a full dump. I would wait to do this until you have been isntructed by PSS (or someone else qualified to analyze a full dump) request you to capture a full dump. An extremely small percentage of DBAs know how to analyze a full dump. A mini-dump is sufficent for troubleshooting most issues that pop up anyway.
Plus, if your server is configured to allow a 1 TB full dump and a recurring issue occurs, how much free disk space would you recommend having on hand? You could fill up an entire SAN in a single weekend.
A page file 1.5*RAM was the norm back in the days when you were lucky to have a SQL Server with 3 or 4 GB of RAM. This is not the case any more. I leave the page file at Windows default size and settings on all production servers (except for an SSAS server that is experiencing memory pressure).
And just for clarification, I've worked with servers ranging from 2 GB of RAM to 2 TB of RAM. After more than 11 years, I have only had to increae the paging file to capture a full dump one time.
99
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.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
