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.
