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

 Read More

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