I think this is a very helpful article. There is one aspect that many people take for granted, but I think should get challenged more, even if the challenge can be contentious...
I have seen advice from some tuning experts that turning off paging on a 64-bit machine can be best for performance. This is achieved by specifying a custom page file size of zero MB.
If you do this you will not get a system dump if Windows fails. Therefore you need to balance the risk of this with the inconvenience of making a page file available if you have a failure that needs a memory dump.
The advice from NT4 days that the page file shold be 1.5 time the size of physical memory is now obsolete. The 1.5 ratio is only needed if you want to dump all memory on the box. If you have a 128GB box, then Microsoft will never need a 192 GB dump to disgnose the problem you have, so why set your page file to 192 GB? If you do decide to have a 192 GB page file, you also need to work out how you will get a 192 GB dump to Microsoft.
IMHO that optimum size page file for a 64-bit box is zero MB, and the maximum size is the whatever is needed for a snap dump (400MB for W2008 R2).
Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017
: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara