SQL Server max memory, page file, max degree parallelism

Question

Three questions hope you DBA masters of the universe can answer:

---

1. 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?

---

2. 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 -> 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?

---

3. 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.

Answer 1

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    ===> 0 to N (where N= no. of processors)
More than 8 processors  ===> 8
NUMA configured         ===> MAXDOP should not exceed no of CPU’s assigned to each 
                                 NUMA node with max value capped to 8
Hyper threading Enabled ===> 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

Answer 2

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?

Well adding to what Kin already mentioned I suggest you use Perfmon Counters to calculate what should be your page file size. I also agree to Remus recommendation of page file should be 1.5 times the RAM on system. But this recommendation is considering all possible aspects and scenario and 'general recommendation'. I guess you are more interested in concrete value. So here it is

The page file needs of an individual system will vary based on the role of the server, load etc. There are some performance counters that you can use to monitor private committed memory usage on a systemwide or per-page-file basis. There is no way to determine how much of a process's private committed memory is resident and how much is paged out to paging files.

Memory: Committed Bytes: Number of bytes of virtual memory that has been committed. This does not necessarily represent page file usage - it represents the amount of page file space that would be used if the process was completely made nonresident

Memory: Commit Limit: Number of bytes of virtual memory that can be committed without having to extend the paging files.

Paging File: % Usage Percentage of the paging file committed

Paging File: % Usage Peak Highest percentage of the paging file committed

Please use above counters to set proper value for page file. You can read This Link to get more information about page file.

Is it then better to set SQL Server's max memory to 22GB RAM so Analysis can take the other 22GB RAM?

No, I dont think so you missed OS requirements here. You need to first set optimum max server memory for SQl Server. This SE thread has more details about setting correct max server memory value. Again I heavily and always reply on perfmon counters to see whay would be optimum value. I suggest you to read This article and take help from counters to set optimum value for SSAS. I once used this blog to set memory value for SSAS. I am not so much into SSAS so my comments will be limited to this article only.

You should keep MAXDOP=1 for Sharepoint instances its widely accepted parameter. If IIRC the recomendation became widely accepted after frequent deadlocks were encountered when max degree of parallelism was left to default value

Answer 3

All of the other answers given so far for the page file size reference relatively out of date articles. This Technet Blog post is more up to date (October 2015) and gives a more detailed way to calculate ideal page file size on modern systems.

They clearly state that the old rule of thumb of 1.5 x RAM no longer applies.

I've pasted the core content of that article below to avoid link rot.

Note that they recommend collecting metrics from your server running at peak usage for at least 1 week - so at the planning and implementation stages of your projects you may not be in a position to do that, and will have to make time later in your project to do so.

---

When sizing the page file we need to consider our applications memory needs and crash dump settings.

How do you know how much memory your application needs? The best way is to take a baseline.

Run Performance Monitor (Perfmon)
Go to Data Collector Sets\User Defined
Right click on User Defined and select New
Select Create Manually and next
Check Performance counter
Add the following counters:

        Memory\Committed Bytes - Committed Bytes is the amount of committed virtual memory, in bytes.
        Memory\Committed Limit - Amount of virtual memory that can be committed without having to extend the paging file
        Memory\% Committed Bytes In Use - Ratio of Memory\Committed Bytes to the Memory\Commit Limit 

Note: Make sure you collect the information over a long period (one week at least), and the server is running at peak usage.

The page file size formula should be:

(Max value of Committed Bytes + additional 20% buffer to accommodate any workload bursts)-RAM size

For example: If the server has 24 GB RAM and the maximum of Committed Bytes is 26 GB, then the recommended page file will be: (26*1.2)-24) = 7.2 GB

What about the second factor: the size we need to record information when the system crashes?

The size of the memory dump is determined by it's type:

Complete Memory Dump  RAM Size + 257 MB
Kernel Memory Dump  The amount of kernel-mode memory in use (on 32-bit maximum is 2 GB, on 64-bit the maximum can go up until 8 TB)
Small Memory Dump  64KB – 512 KB

In most cases the Kernel Memory Dump is good enough for root cause analysis, as Complete Memory Dump is only required in specific cases, for example you want to see what happened in the user mode.

From my experience, the size for Kernel Memory Dump is usually the following:

On System with up to 256GB RAM =  8-12 GB size for Kernel Memory dump
On System with up to 1.5TB RAM = 8-32 GB size for Kernel Memory dump

However, these numbers are NOT a Microsoft official recommendation, and may be different on your servers so always test before you apply.

Answer 4

Answering since I'm one of the people you're quoting, heh.

1. What should I set max memory to when I also run Analysis Services?

You won't find guidance out there because SSAS is just like any other app that gets installed on your SQL Server: we just don't know how much memory you're going to use. Treat SSAS/SSIS/SSRS as third party applications - they just happen to be included as "free" in the SQL Server box. They have totally different CPU, memory, and storage needs.

2. What should I set the page file size to?

My recommendations are so that Windows can do a mini-dump. If you run into a problem where you need to call Microsoft support, you can start with the mini-dump. If the problem recurs, and they can't figure it out from the mini-dump (or other sources of information, like the error log), then they may ask you to go to 1.5x RAM size.

Microsoft's 1.5x RAM recommendations are so that Windows can do a FULL dump of memory when your system crashes.

These days, with servers having 64-128-256GB RAM, it's not usually a good idea to have Windows pause to write out the entire contents of memory during a crash. You'd rather have the SQL Server back up and running, and deal with the mini-dump contents rather than the full. Save the full dumps for later - you can usually go your whole career without needing to upload a 64+GB dump file to Microsoft. (Good luck on that one.)

3. What do I set MAXDOP to for SharePoint?

What you call "my" recommendations are from Microsoft KB 2806535. Start there in general, but when a specific application tells you something different, then they know something about their application that's different from SQL Server in general - follow that.