Understanding Memory - SQL Server 2012 Internals and Troubleshooting - page 59

Sizing the Page File
Determining the optimal size of a page i le has been a long-running debate for years. By default,
Windows will manage the size of your page i le recommending a page i le size of 1.5 times the size
of RAM.
It won't hurt performance to leave the default in place but the debates start to ensue when there are
large amounts of RAM in a server and not enough disk space on the system drive for a full-size
page i le.
The primary purpose of a page i le is to allow Windows to temporarily move data from RAM to
disk to help it manage resources effectively. When a page i le is heavily used, it indicates memory
pressure; and the solution is to optimize your memory resources or buy more RAM, rather than to
optimize your page i le.
If you have disk space concerns on your page i le drive, then setting the page i le to 50% of total
available RAM would be a safe bet.
At one client, where I was delivering a SQL Server Health Check, one of their servers had 96GB
of RAM and a 96GB page i le. Page i le usage was minimal during the day, but every night a SQL
Server Analysis Services cube was being rebuilt, which required so much memory that 20GB of the
page i le was being used during the build. This amount of page i le usage is extreme but even a page
i le sized at 50% would have been more than enough. They upgraded the RAM to 128GB the
next week.
Another argument for full-size page i les is that they are required to take full memory dumps. While
that is correct, it is extremely unlikely that Microsoft support will ever investigate a full memory
dump because of the sheer size of it, and certainly never on the i rst occurrence of an issue. This
then gives you time to increase the size of your page i le temporarily at Microsoft's request to gather
a full dump should the need ever actually arise.
NUMA
Non-Uniform Memory Architecture (NUMA) is a hardware design that improves server scalability
by removing motherboard bottlenecks. In a traditional architecture, every processor has access to
every memory bank across a shared system bus to a central memory controller on the motherboard.
This is called symmetric multiprocessing (SMP) and it has limited scalability because the shared
system bus quickly becomes a bottleneck when you start to increase the number of processors.
In a NUMA system, each processor has its own memory controller and a direct connection to a
dedicated bank of RAM, which is referred to as local memory, and together they're represented as a
NUMA node .
NOTE To ensure the consistency of data held in the small amount of cache memory
present on each CPU, all mainstream implementations use cache-coherent
NUMA (ccNUMA), which ensures that when data held in one CPU's cache is
modii ed, any other copies of that data cached on other CPUs are also updated.