Page file defaults

This post is part of the Failover Cluster Checklist series.

For servers with a large amount of RAM, the page file may get very large if you do not change the default settings. Such a large page file is rarely required. To get a more sensible starting point, calculate a page file size using the following formula, and set a fixed page file size.

8GiB + 1GiB for each 8 GiB above 8GiB.

64GiB RAM 8 + (64-8)/8 = 15 15*1024=15360
128GiB RAM 8 + (128-8)/8 = 23 23*1024=23552
256iGB RAM 8 + (256-8)/8 = 39 39 * 1024 = 39936
384GiB RAM 8+(384-8)/8 = 55 55 * 1024 = 56320

I cannot remember where I first saw this formula, but I have seen it used in several posts and books. The actual value is not really important anymore, the most important point is to limit the size of the page file to keep it from filling up your local drives. This has become even more important with the use of local SSD drives, as they tend to be rather small to keep costs down. I do however not recommend setting a value lower than 8GiB, even if you have two terabytes of RAM running downhill at warp speed with turbo engaged. Windows 2012 seems to have more reasonable automation algorithms than those found in Windows 2008, but I see no reason to trust the automation,  as they are probably controlled by the mood of a nearby squirrel. Furthermore, if you are ever in a situation where you need more than the prescribed default page file size on a physical server with more than 64GiB of RAM, the page file is not going to save you. It will only slow down the inevitable decent into the abyss of dreadful performance from which a hard reboot may be the only way out.

Changing the page file size on Windows 2008-2012

clip_image004

clip_image005

 

image

 

Powershell script

The following script automates the process.

#######################################################################################################################
#   _____     __     ______     ______     __  __     ______     ______     _____     ______     ______     ______    #
#  /\  __-.  /\ \   /\___  \   /\___  \   /\ \_\ \   /\  == \   /\  __ \   /\  __-.  /\  ___\   /\  ___\   /\  == \   #
#  \ \ \/\ \ \ \ \  \/_/  /__  \/_/  /__  \ \____ \  \ \  __<   \ \  __ \  \ \ \/\ \ \ \ \__ \  \ \  __\   \ \  __<   #
#   \ \____-  \ \_\   /\_____\   /\_____\  \/\_____\  \ \_____\  \ \_\ \_\  \ \____-  \ \_____\  \ \_____\  \ \_\ \_\ #
#    \/____/   \/_/   \/_____/   \/_____/   \/_____/   \/_____/   \/_/\/_/   \/____/   \/_____/   \/_____/   \/_/ /_/ #
#                                                                                                                     #
#                                                   http://lokna.no                                                   #
#---------------------------------------------------------------------------------------------------------------------#
#                                          -----=== Elevation required ===----                                        #
#---------------------------------------------------------------------------------------------------------------------#
# Purpose: Change page file size                                                                                      #
#                                                                                                                     #
#=====================================================================================================================#
# Notes:                                                                                                              #
#                                                                                                                     #
# Set a fixed pagefile size. Asumes there is only one page file. Size in MB. Changes are applied at next boot.        #
# 32GB RAM	8 + (32-8)/8 = 11 	11*1024=11264                                                                         #
# 48GB RAM	8 + (48-8)/8=13 	13*1024=13312                                                                         #
# 64GB RAM	8 + (64-8)/8 = 15 	15*1024=15360                                                                         #
# 128GB RAM	 8 + (128-8)/8 = 23 	23*1024=23552                                                                     #
# 256GB RAM	 8 + (256-8)/8 = 39 	39 * 1024 = 39936                                                                 #
# 384GB RAM	8+(384-8)/8 = 55 	50 * 1024 = 51200                                                                     #
# 768GB RAM	8+(768-8)/8=103  	50 * 1024 = 51200                                                                     #
#                                                                                                                     #
#######################################################################################################################



##Set a fixed pagefile size. Asumes there is only one page file. Size in MB. Changes are applied at next boot.


$RAM = ((Get-WmiObject -Class "Win32_ComputerSystem" -Namespace "root\CIMV2").TotalPhysicalMemory | Measure-Object -Sum).Sum/1GB
$PageFileSize = (8 + ($RAM - 8)/8) * 1Kb 

if ($PageFileSize -gt 51200)
{
    $PageFileSize = 51200
}  

Set-CimInstance -Query "Select * from win32_computersystem" -Property @{automaticmanagedpagefile="False"}
Set-CimInstance -Query "Select * from win32_PageFileSetting" -Property @{InitialSize=$PageFileSize;MaximumSize=$PageFileSize}

Author: DizzyBadger

SQL Server DBA, Cluster expert, Principal Analyst

One thought on “Page file defaults”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.