SQL Server max memory, page file, max degree parallelism Three questions hope you DBA masters of the universe can answer:


I know it is best practice to set SQL Server's max memory. So if a server with 44 GB RAM has SQL Server installed we should set the SQL Server max memory to approx 37GB.

But what should we set if SQL Analysis Service and SQL Reporting Services are installed on the same server as SQL Server? Is it then better to set SQL Server's max memory to 22GB RAM so Analysis can take the other 22GB RAM?


On a server running SQL Server with 44 GB RAM what is best practice for setting the page file size? I read on Brent Ozar (http://www.brentozar.com/archive/2014/06/sql-server-setup-checklist-free-ebook-download/) the following:


SQL Server doest not need a giant page file. if you're installing other applications on the server (which we don't recommend-- SQL Server should be isolated). you may need a larger page file. If SQL Server is the only major service running on the box, we typically create a 2GB size page file on the system drive.

Microsoft says we should set page file system managed to 1.5x the RAM which matches the advice given by Remus (see -&gt; http://rusanu.com/2009/11/22/system-pagefile-size-on-machines-with-large-ram/).
So on our server with 44GB RAM we have SQL Server installed next to SQL Analysis Service and SQL Reporting Services. What size should we set the page file?


For SharePoint the SQL Server that is part of SharePoint's farm and where SharePoint stores it's databases, the max degree of parallelism should be set on 1. Now that we have a server that is not part of the SharePoint farm (call it sql2) on this server we have SQL Server, SQL Analysis and Reporting installed. SharePoint does not store its databases on this server, but we do have reporting and analysis databases/cubes created that SharePoint reads and writes to.

Brent Ozar says:

Rule of thumb: set this to the number of physical cores in a single NUMA node (processor) sockets on your hardware or less

What should we configure on this SQL Server instance for max degree of parallelism when it has 4 VCPU's?

Hope you guys give not only me but more sql admins a bit more insight in the best practices than the jungle on the interwebs.
Your 3 questions touch max memory, page file and max dop setting.

MAX MEMORY



If the server is dedicated SQL Server only without Analysis services (since  SSAS is another beast), then Glenn's recommended best practices is a good starting point. For additional components, you should refer to Jonathan's blog on how to configure max memory.

I have answered on SQL Server Maximum and Minimum memory configuration. This will be helpful if you have lots of SSIS packages running on your server instance.

Page File



I normally set it away from C:\ drive on the drive which has sufficient disk space. I agree with Remus and Brent's recommendation. It should be PAGE FILE FOR SQL SERVER = 1.5 * RAM. A page file will be useful when you encounter yourself in a situation wherein you have to take a FULL MEMORY DUMP (normally asked by Microsoft CSS) for troubleshooting.

Read How to determine the appropriate page file size for 64-bit versions of Windows KB 889654 thoroughly and Buck Woody talks about page file here.

MAX DOP Setting



For sharepoint its recommended that maxdop = 1 be set instance wide. For a normal sql server, I would suggest you to follow  - What is a good, repeatable way to calculate MAXDOP on SQL Server? I have a script written which will calculate it for you.

The gist is below (KB 2806535 also mentions the same thing):

8 or less processors    ===&gt; 0 to N (where N= no. of processors)
More than 8 processors  ===&gt; 8
NUMA configured         ===&gt; MAXDOP should not exceed no of CPU’s assigned to each 
                                 NUMA node with max value capped to 8
Hyper threading Enabled ===&gt; Should not exceed the number of physical processors.


As a side note, I would suggest you to read  - Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads