It should be set to 1.5 times your RAM amount. This was best practice in '95 and still is today. Better yet set it to system managed and forget about it.
If you are running a 32-bit desktop environment with a small amount of memory this advice may make sense. If you are running a 64-bit machine dedicated to SQL Server and have more than (say) 4GB RAM then this advice wastes resources.
If you are worried about keping a specific size pagefile so you can get a memory dump, two things to consider:
1) Think about how often you get a repeatable system crash and how much time you want to wait while a dump file is written. Also think about how you will export the dump file to Microsoft. If you decide you will never give Microsoft a 48GB dump file then you have no justification for a 48GB pagefile.
2) Look at http://blogs.msdn.com/ntdebugging/archive/2010/04/02/how-to-use-the-dedicateddumpfile-registry-value-to-overcome-space-limitations-on-the-system-drive-when-capturing-a-system-memory-dump.aspx if you are running W2008 or above.
Mike Russinovich has a good blog about setting Windows memory use, see http://blogs.technet.com/markrussinovich/archive/2008/11/17/3155406.aspx. Read all of it but look out for the section about pagefile size. I have also seen advice from PSS engineers (but cannot find it now) about the specific situation of using a zero size pagefile.
FWIW, my machine at home has 12GB RAM with W2008 R2 as the main OS running Hyper-V. I run the host and all my guests with zero pagefile and have no crashes or hangs. The guests all seem to run faster than before I made this change, but I have not done tests to quantify this.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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