Optimizing your page file drive
We got a great question asking what cluster size to use for the page file drive: NTFS defaults to 4kb, but the customer was concerned that it may not be the proper size when dealing with a very large page file. The system in question housed a multi-terabyte database with a lot of memory, so the customer was considering using a 64kb cluster size for more efficient reads and writes. The question: what’s the best cluster size for a page file drive?
Not so fast – it’s a trick.
A properly tuned SQL Server should not be using the page file for memory. If the server swaps to disk, that’s a huge performance hit. If the server doesn’t have enough memory, then the DBA needs to turn down the maximum memory size until SQL Server doesn’t swap out to disk. Sometimes the best answer to memory problems is to actually decrease the max memory size parameter.
When doing performance tuning, if the page file drive is seeing any read or write activity at all, that’s an indication to stop right there and focus on memory use. Tuning the page file cluster size is like rearranging deck chairs on the Titanic.
October 22nd, 2008 at 10:13 am
Could you elaborate more? Very interesting.
November 11th, 2008 at 8:24 pm
ok Brent you are killing me…..I just read in your article last week to make the page file needs to be large enough for a memory dump?
now you got me confused
November 13th, 2008 at 9:45 pm
Doug – absolutely, it does need to be large enough for a memory dump, but you don’t need to optimize it for read or write performance if the SQL Server has enough memory. If your server is doing memory dumps often enough that you’re worried about how fast it recovers from a memory dump, then you’ve got some pretty ugly problems. Ouch!