ab.sqlservercentral (8/18/2010)

---

Guys,

those pagefile discussion drives me crazy. First, most of the posts are off-topic. The discussion is about MEMORY DUMPS, not about paging file calculation. Second, most people don't know the internals about paging:

- for all those who complained to need "hacks" and "tricks" and want to configure it to a minimum: Windows doesn't need a paging file.

- for those of you who calculate the paging file based on RAM: This is a wrong approach. The paging file doesn't need to be "2x RAM or 4x RAM" - it just depends. Basically: the more RAM you have, the less paging file you need. If you configure your SQL Server in a reasonable way and have enough RAM, you can leave the paging file very small (and as I told in a previous post: Configure a kernel memory dump, that's enough in 98% of all cases).

- for those of you who care about fragmentation in the PF: The performance drop WHEN the paging file is used (it won't be often!) is THAT high that fragmentation just doesn't matter.

- Despite all myths about it: paging doesn't happen often. It's very hard to see (and DON'T take the hard faults as an indicator of paging!). It happens only if the system cache is empty AND if other processes demand much memory. Then the OS will trim the least heavily used pages from other processes and store them in the paging file.

- A thumb rule: monitor your server look how the "peak commit limit" behaves. This is what might get paged out at MAX on your server to the paging file in the worst case. This number should be the basis of your calculation, not the size of RAM.

- If you use AWE in your SQL Server to support more than , your pages will NEVER be paged out.

- IF you set "lock pages in memory" your SQL Server pages will not get trimmed in case of high memory demand.

regards

Andreas