Increased memory to server, but do I need to change page-file size?

Question

I'm running SQL Server 2017 on Windows Server 2012 R2 and have increased the memory from 256 GB to 512 GB. I've noticed the following configuration for Virtual Memory:

• C: [OS] = 2 GB
• G: [DATA] = None
• H: [LOGS] = None
• T: [TEMP] = None

There is a lot of advice out there talking about setting the size to 1.5 or 2 times the size of the memory that is installed. Am I reading this correct or have I misread the articles and they apply to something else?

There is also articles suggesting not to worry about the virtual memory as SQL should only be using RAM and not virtual memory, and the OS only requires 2GB of virtual memory?

Answer 1

I believe the 2x size of RAM recommendation is outdated and unrealistic. If a server only has 32 GB of RAM it might make sense, but it's a waste of expensive disk space to make it 2x on a system that had a massive amount of memory.

There are two primary purposes of the pagefile:

• To use as a swap file for memory. If SQL Server and other applications are configured correctly to limit the amount of memory available, it will never be needed for this purpose. If the system does have to use the pagefile to swap memory, system performance will be so bad you'll effectively have an outage and be forced to deal with it immediately. So if you configure a 1 TB pagefile, it won't help performance when the system starts swapping to disk and you'll be forced to do something about it before it needs to swap even 100 GB.

• To hold a memory dump if the system crashes (on the volume where OS is installed). Again, if a server only has 32 GB of RAM, it makes sense to configure it to do a full memory dump if the system crashes. However, it is rarely necessary for diagnostics purposes to need a full memory dump, anda kernel memory dump is the reasonable choice on a system that has a lot of memory. Also, Windows crashes are much less frequent these days than in 1996 (or whenever) when that 2x recommendation was made. Also, if the system crashes and you get a 1 TB memory dump (you'll need another TB free on c: for it), good luck zipping that sucker and transferring it to Microsoft. I would only configure for a kernel dump and only change that if the system is crashing and Microsoft needs a full memory dump to diagnose the issue.

So if you're super paranoid (which is certainly justified in some situations) and have 2 TB to allocate that will likely never be used, then a gigantic pagefile is a consideration. However, it's rarely going to be really necessary.

So my answer is that you are highly unlikely to need to increase the pagefile size. It probably hasn't been used at the current size, and with more memory now, it's even less likely to be used, so it would follow that there's no need to waste more disk space on a bigger one.

Answer 2

This is spelled out pretty explicitly in How to determine the appropriate page file size for 64-bit versions of Windows

Crash dump setting

If you want a crash dump file to be created during a system crash, a page file or a dedicated dump file must exist and be large enough to back up the system crash dump setting. Otherwise, a system memory dump file is not created.

For more information, see Support for system crash dumps section.

Peak system commit charge

The system commit charge cannot exceed the system commit limit. This limit is the sum of physical memory (RAM) and all page files combined. If no page files exist, the system commit limit is slightly less than the physical memory that is installed. Peak system-committed memory usage can vary greatly between systems. Therefore, physical memory and page file sizing also vary.

So unless you're troubleshooting a crash, you size the page file to accommodate the "Peak system commit charge". This is the maximum total amount of virtual memory allocated in Windows. Your RAM + Page File size should be somewhat greater than the observed or predicted Peak system commit charge.

SQL Server will automatically reduce its memory usage when the system is in a low memory condition. See Memory Management Architecture Guide. So there will be relatively little usage of the page file for a dedicated SQL Server. With SQL Server maintaining its memory usage below the amount of server RAM, the Peak system commit charge should be only a few additional GB as other processes running on the server commit virtual memory.

So 8GB or 16GB is generally more than enough Page File size for a SQL Server. And that's without setting Max Server Memory to cap SQL Server's RAM utilization.

If SQL Server is running at 100% utilization, you want enough RAM + Page file to run ancillary processes, like SQL Agent Jobs, SSIS packages, or even Explorer and SSMS on the server for troubleshooting.

Answer 3

From the "SQL Server 2017 Administration Inside Out" book:

Windows uses the page file for virtual memory for all applications, including SQL Server, when available memory is not sufficient for the current working set. It does this by offloading (paging out) segments of RAM to the drive. Because storage is slower than memory, data that has been paged out is also slower when working from the system page file.

The page file also serves role of capturing a system memory dump for crash forensic analysis, a factor that dictates its size on modern operating systems with large amounts of memory.

This is why the general recommendation for the system page file is that is should be at least the same size as the server's amount of physical memory.

Another recommendation is that the page file should be set to System Managed... However, in systems with large amounts of memory, this can result in a very large page file, so be aware of that if the page file is located on your operating system (OS) volume. This also why the page file is often moved to its own volume, away from the OS volume

Best is avoid SQL server using page file (virtual memory) at all as it dramatically reduces performance. Buy/install enough RAM memory to cover your SQL Server needs so it does not need to use page file at all

To find out how much RAM memory your SQL Server actually uses, you can do it by monitoring Performance Counters:

Target Server Memory
Total Server Memory
Database Cache Memory
Stolen Server Memory
Plan Cache
Lock Memory
Log Pool Memory
SQL Cache Memory
Free Memory
Connection Memory
Optimizer Memory
Reserved Server Memory
Granted Workspace Memory

Looking at those and figuring out how much your SQL Server consumes over time, will give you idea if you need/install or allocate more RAM memory to the server or 512 GB is enough for you (or alternatively, optimize queries, indexing, table design, to make it use less RAM)

Hint: look for Total Server Memory - this is total how much your SQL Server consumes. Roughly Total Server Memory = Database Cache Memory + Stolen Server Memory + Free Memory

Answer 4

A sane server setup does not need a paging file at all. RAM is cheap and users get nervous if the paging process kicks in. Also bear in mind that the very existence of the paging file eats some RAM and some CPU cycles.

Crash dumps are not something you expect on a production server these days. They are only looked at in a really rare situation of recurring crash AND a competent people available to look at the dump. Generally, the server is sweared at and restarted. The automatic restart was the default for Windows servers for the last 20 or so years.

The only more or less useful application of the paging file is to accommodate for a memory leak between the scheduled restarts. In this case it is sized experimentally so the leak settles in the paging file and doesn't crash the server by Out Of Memory condition. Most leaks are not manageable this way and the SQL Server doesn't leak much as of 2020 anyway.

Yes, it is possible to have an off-hours memory hungry task that may spill to the pagefile even when the memory is adequate for the usual load. In this case, just buy more RAM or you will not be able to keep the offending task off-hours as the database grows.