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 controll over the page file. It sized it to 38GB (Memory is 265), 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?

Solution 1

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.

Solution 2

Do not put the Windows Page File (Pagefile.sys) on the same disk where database or log files are located. You can leave it on the system drive or assign a new drive for it. If the system drive storage is SSD you can leave the page file residing there, and set the starting size of the page file base on the amount RAM x 1.5.

Comments

[djdomi]

you may move it to a really fast storage (i. e. ssd/nvme) and seperate it, however remind that its only possible due a outage for maintainace

[Tobias]

The SQL Server is currently located on a storage with some kind of ssd cache enabled - high I/O blocks are automatically located on the first tier with 3 400GB SSDs. My question is more about the fact of the page file and the SQL log file residing on the same disk, which does not make sense, at least for my basic understanding of MS SQL servers...

[Tobias]

Would you recommend to overrule Windows and place the file manually to another drive? Or may there be any reason why the system has placed the file where it is?

[AMtwo]

@Tobias Windows always places the page file as C:\pagefile.sys. If it is anywhere else, someone (not Windows) put it there. I would guess that is the default location because it's a location that is guaranteed to exist, rather than some more thoughtful reason. And, as mentioned in my answer, I just leave it there.

[Tobias]

but there is the thing: the file is set to automatic sizing and location, we did not move it. I know that Windows is trying to locate the file at the best place, and according to the configuration I would say the disk with the log file is maybe the most performant disk in the VM. I am wondering if Windows is only considering the disk performance and does not care about the actual load on this disk.