SQL Server 2008 R2 – RAM = 128GB, PageFile = 192GB

TL;DR version: Let Windows handle your memory/pagefile settings. The people at MS have spent a lot more hours thinking about these issues than most of us sysadmins.

Many people seem to assume that Windows pushes data into the pagefile on demand. EG: something wants a lot of memory, and there is not enough RAM to fill the need, so Windows begins madly writing data from RAM to disk at this last minute, so that it can free up RAM for the new demands.

This is incorrect. There's more going on under the hood. Generally speaking, Windows maintains a backing store, meaning that it wants to see everything that's in memory also on the disk somewhere. Now, when something comes along and demands a lot of memory, Windows can clear RAM very quickly, because that data is already on disk, ready to be paged back into RAM if it is called for. So it can be said that much of what's in pagefile is also in RAM; the data was preemptively placed in pagefile to speed up new memory allocation demands.

Describing the specific mechanisms involved would take many pages (see chapter 7 of Windows Internals, and note that a new edition will soon be available), but there are a few nice things to note. First, much of what's in RAM is intrinsically already on the disk - program code fetched from an executable file or a DLL for example. So this doesn't need to be written to the pagefile; Windows can simply keep track of where the bits were originally fetched from. Second, Windows keeps track of which data in RAM is most frequently used, and so clears from RAM that data which has gone longest without being accessed.

Removing pagefile entirely can cause more disk thrashing. Imagine a simple scenario where some app launches and demands 80% of existing RAM. This would force current executable code out of RAM - possibly even OS code. Now every time those other apps - or the OS itself (!!) need access to that data, the OS must page them in from backing store on disk, leading to much thrashing. Because without pagefile to serve as backing store for transient data, the only things that can be paged are executables and DLLs which had inherent backing stores to start with.

There are of course many resource/utilization scenarios. It is not impossible that you have one of the scenarios under which there would be no adverse effects from removing pagefile, but these are the minority. In most cases, removing or reducing pagefile will lead to reduced performance under peak-resource-utilization scenarios.

Some references:

• Windows Internals book(s) (4th edition and 5th edition)
• Pushing the Limits of Windows: Physical Memory
• Pushing the Limits of Windows: Virtual Memory
• Inside the Windows Vista Kernel: Part 1
• Inside the Windows Vista Kernel: Part 2
• Inside the Windows Vista Kernel: Part 3
• Understanding Virtual Memory
• RAM, Virtual Memory, Pagefile and all that stuff (here's a longer version)
• The Out-of-Memory Syndrome, or: Why Do I Still Need a Pagefile?

dmo noted a recent Eric Lippert post which helps in the understanding of virtual memory (though is less related to the question). I'm putting it here because I suspect some people won't scroll down to other answers - but if you find it valuable, you owe dmo a vote, so use the link to get there!

It does exactly what it says on the tin: the OS manages the size of the pagefile, which can shrink or grow dynamically. The lower and upper bounds are 1x your RAM size and 3x your RAM size or 4 GB (whichever is larger) as explained more elaborately here. The pro is that you don't have to worry about sizing your pagefile, the con is that your pagefile can become fragmented.

Where it is useful is in exactly the situation it's designed to avoid: sizing your pagefile. You can set it to System Managed and check in every several minutes (via a script), recording the smallest and largest sizes it uses over a typical usage period of a month or so. You should then have a very good idea how large to manually set your pagefile to be.