SQL Server 2017 – Increased Memory, Do I Need to Change Page-File Size?

memorysql serversql-server-2017windows-server

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?

Best Answer

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.

Related Solutions

Sql-server – Determining SQL Server memory requirements

I'm not sure I believe there was ever an actual issue. Just because max server memory was set to 2 petabytes doesn't mean it would ever use that much. This is the functional equivalent of setting it to infinity, and just means you trust SQL Server to use as much memory as it needs.

Setting it to 500 MB seems artificially low to me. While your database is currently 128 MB on disk, you are probably not running any query patterns that lead to any performance issues even with that limit on memory. But when SQL Server needs more than 500 MB of memory, you're going to start noticing. SQL Server is designed to behave as if it is the only server on the machine, so it will take memory as it needs it, and won't release it unless the operating system needs it and applies pressure to SQL Server (discussions of LPIM aside). Restricting SQL Server's memory this way (or taking it away) will just cause performance degradation as it is much more expensive to page data in and out of memory, onto disk, etc. which will most certainly be required as your database grows. Think about trying to put a pig into a tube sock - eventually you're going to need a bigger sock. Why wait until the current sock rips?

I suggest a higher limit, but I can't tell you what that would be. You didn't disclose how much memory is on the server, whether it is x86 or x64, or what else is going to be running there. If the server is dedicated to SQL Server, I see absolutely no reason to save memory - what are you saving it for?

For a bit of an anecdotal observation, I don't think I've ever seen any instance of SQL Server on any kind of physical or virtual machine limited to 500 MB, and I have very serious doubts that this could possibly be the optimal setting for you in the long run.

I'd also suggest doing research into SQL Server settings and then changing them, instead of the other way around. This situation sounds a lot like saying "What does this button do?" after pressing it...

Sql-server – SQL Server taking a lot of memory

SQL Server is known for by default consuming as much memory as the OS allows it to take, so you'll have to reduce it manually.

One way of doing this is described here and here. On the second link, specifically, you will find this example that might be useful for you:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

Instead of 4096, you might want to enter something smaller depending on your available RAM.

And, as @outcoldman says in a comment to your question, SQL Server Express doesn't allow 15 GB databases. This answer specifies 4 GB max for SQL Server Express 2005 and 10 GB max for SQL Server Express 2008 & 2012.

Best Answer

Related Solutions

Sql-server – Determining SQL Server memory requirements

Sql-server – SQL Server taking a lot of memory

Related Question