Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Windows Pagefile size for dedicated SQL Servers

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

 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




Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer