SQL Server Page File location – where to put it?

Question

There are a lot of recommendations about the sizing of the windows page file. What I am looking for is the correct placement.

We are running a MS SQL server on a Server 2012R2 VM with multiple seperate virtual disks:

• Windows disk
• one disk where the SQL server application is installed
• one disk for the SQL data files (mdf/ndf)
• one disk for the transaction log file (ldf)
• one disk for manual backups and other temporary stuff.

At the moment, Windows has control over the page file. It sized it to 38GB (Memory is 265GB), and placed it on the disk with the log file. For my understanding, this placement is not logical: the disk containing the SQL transaction log file is the disk with the heaviest I/O on an SQL server. Is it really the best location for the page file? As for the storage space, I could easily move the file to another drive.

I know, Windows may have a reason for placing the file there – but if so, what could this be? Or am I wrong with my thinking?

asked 2019-10-31 by Tobias

---

Answer

Before you worry about page file size….

To be honest, you never want your SQL Server to start paging. SQL Server has a tendency to go pear-shaped when it’s memory is paged to disk.

Best practice for SQL Server is to set the Max Server Memory configuration to a number sufficiently large for SQL Server to have enough memory, but sufficiently small that your OS and anything else running on the server have enough memory. There is some great advice on how to configure this here, and here.

For a server with 256GB memory, and only SQL Server running on the server, I’d suggest starting with Max Server Memory configured to 235929 MB, which allows about 25GB of headroom. This should be sufficient to prevent paging.

EXEC sys.sp_configure 'max server memory (MB)', '235929'; 
RECONFIGURE;

What the internet says about that page file…

There’s a lot of advice on the internet saying your page file should be 1.5x the size of your memory. On a server with 256GB of memory, that would call for a 386GB page file. You would want this page file on a drive separate from your data & log files (Else paging into your data/log drives will exacerbate your performance problems caused by paging). Your C:\ drive probably isn’t large enough for this, unless you’re building servers with 512GB C:\ drives. You’ll also want the drive to be fast (Else paging to a slow disk will exacerbate your performance problems caused by paging). Probably, you’ll need a dedicated disk for that 1.5x page file.

That advice assumes that you want your server to page. You don’t want your server to page. SQL Server might crash when it starts paging, and if it doesn’t your performance will slow to a crawl. You could buy some sort of super-fast NVMe drive for your page file, and that will probably make performance acceptable.

The internet is wrong.

What I say about that page file…

I don’t even care how big it is. I’m never going to use it. It can be pretty small. It can be on the C:\ drive. The C:\ drive can be slow.

• Make sure you set Max Server Memory properly (see above)
• If your server needs more memory, install more memory in the server–don’t page to disk.
• You should move your page file off the log drive and onto C:. If you use it, you want it off your data/log drives. If you have to make it smaller to fit onto C:\, go for it.
• Don’t spend money on fast disks for your page file–spend that money on another stick of RAM.

In the event the system crashes and produces memory crash dumps, my pagefile will likely not be large enough to handle that dump. But on systems with large amounts of memory, dumping 1TB of memory to disk for that crash dump will take a long time, even on fast disks, and I usually prefer to get the SQL Server back online fast that capture the crash dump. I can reconfigure a server with recurring problems as part of troubleshooting.

answered 2019-11-01 by Andy Mallon