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
SQL Server - Windows Virtual memory and paging file location - pagefile.sys
SQL Server – Memory: Pages sec, memory pressure and thrashing
SQL Server Buffer Cache Hit Ratio and memory pressure
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |