31 May,2013 by Jack Vamvas
Windows Pagefile - what maximum size to set for a dedicated SQL Server?
This is the second part of SQL Server - Windows Virtual memory and paging file location - pagefile.sys dealing with location of the Windows paging file.
Factors for deciding on Windows Pagefile size on dedicated SQL Server
1) In the event of a severe SQL Server crash – a paging file size of 2 GB is useful to capture a crash dump file.
2) 200 MB is required for a Kernel dump
3) The “traditional” recommendation for the paging file is 1.5 x the Physical RAM – but is this relevant for a dedicated server with SQL Server installed?
A dedicated SQL Server makes the calculation simple. For a start if SQL Server is using the paging file – you’re in trouble!
Let’s say you had a dedicated SQL Server with 128 GB RAM . If you also had a paging file of 192 GB – and there were enough programs open to use all the available memory – the response time would be slow.
Massive paging file are not necessary on dedicated SQL Server servers.
4) If you need a formula – the one I use is –
Minimum = SUM(peak private bytes of every process) - Physical RAM
Maximum = double the value from Minimum
5) Start cautiously with SQL Server Max Memory. Set the SQL Server maximum memory at a conservative level , I normally 15GB – 18 GB for the OS on a large server and monitor. Focus on the Available Mbytes counter. If the level of Available Mbytes is consistently high , you can think about dedicated some extra meory to SQL Server