What MAXDOP setting should be used for MS SQL Server 2012?

Question

I am getting a performance degradation error in one of my cluster production server. The error is like that

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 608612, committed (KB): 1204128, memory utilization: 50%.

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 619280, committed (KB): 1226552, memory utilization: 50%.

And My working Environment is like that

OS - Microsoft Windows Server 2012 R2 Standard
Hardware information - VMware, Inc, VMware Virtual Platform
Processors - Intel(R) Xeon(R) CPU E5-2670 0 @ 2.60GHz
Installed memory (RAM) - 4 GB

And MS SQL Server version is

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)

After execution of this TSQL The 'max degree of parallelism' & 'max server memory' is like that

exec sp_configure 'max degree of parallelism';

Name                       minimum  maximum   config_value    run_value
max degree of parallelism     0     32767       1               1   



exec sp_configure 'max server memory';

Name                       minimum         maximum        config_value    run_value
max server memory(MB)       128           2147483647     6144             6144

I have also gone through the StackExchange and found a similar question SQL Server R2 Standard Edition MAXDOP setting, but didn't get the proper answer there. That's why I ask the question.

And I am getting this error from yesterday (i.e; 27/12/2015).

Any suggestion will be appreciated.

Answer 1

Based on the message

A significant part of sql server process memory has been paged out. This may result in a performance degradation.

You might be having OS memory pressure - based on your RAM and MAX Memory configuration. Other causes might be leaking driver or incorrectly configured VMWARE especially - Brent has a good post on How to Set SQL Server Max Memory for VMware ?

---

The WHAT ?

From kb 2028324 -

SQL Server monitors the various memory related information about the SQL Server process. In this case it has detected that the working set of the process is less than 50% of the committed process memory. As a result this warning is printed. The normal causes of this warning are:

• The operating system pages out large portions of the the SQL Server committed memory to the paging file.

• This could be due to sudden increased demand for memory from other applications or operating system needs.

• This could also happen when certain device drivers request contiguous memory allocations for their needs.

Bob Dorr has written an excellent article on The SQL Server Working Set Message which is worth reading.

---

The WHY ?

Based on your sp_configure output, you have configured 6GB of max memory. Your installed RAM is ONLY 4GB ! <-- This is the problem

From BOL :

---

Recommendation :

Based on your workload, you should get more RAM and then set a good value for your MAX MEMORY. As per kb - 918483, you should enable LPM (Lock Pages in Memory).

Also, monitor the sys.dm_os_ring_buffers especially RING_BUFFER_RESOURCE_MONITOR for any low memory notifications - be it inside SQL Server as a process or from windows being under memory pressure.

RESOURCE_MEMPHYSICAL_HIGH - SQL can grow memory usage RESOURCE_MEMPHYSICAL_LOW - System or internal physical memory - shrink RESOURCE_MEM_STEADY RESOURCE_MEMVIRTUAL_LOW – Virtual address range for SQL Server process is becoming exhausted.

---

Also, on a side note, unless the server instance is a sharepoint instance, you should configure a sensible MAXDOP setting.

Answer 2

Not sure, if looking MAXDOP initially for this error would be a good start towards troubleshooting, as it is more related to the memory being paged out.

You will need to focus more on the terms like Working set (KB): committed (KB): , Working Set trimming

There are couple of situations where SQL Server Process working set might be paged out by Windows despite these memory resource notification mechanism as explained here

1.If windows is not sending the correct notifications to all listening processes at the right moment and thresholds.

2.If SQL Server is not responding fast enough to the low memory resource notification from Windows

3.When low physical memory notification is received by SQL Server it will scale down its memory usage by trimming internal caches. This effect is applied for 5 seconds and then paused for 1 minute. This is to avoid any hungry/faulty application to consume all the memory making SQL Server to scale its usage continuously. If low memory conditions still exist after 1 minute pause, the effect is applied again. So if there is physical memory pressure even after SQL Server scaling its usage for 5 seconds windows will still page out SQL Server’s working set.

4.Conditions in Windows where working sets of all processes are trimmed

5.Windows might decide to trim a certain percentage of working set of various or specific processes

Therefore, i believe you need to gather and collect more data on memory usage with help of memory performance counters and see if there is any process which caused so.

Also, the system being on very low memory end, i.e 4 GB (not sure of the load o n you're system), try analyzing the load and cap the MAX server memory settings accordingly.

Additionally consider using the Lock pages in memory privilege. Here to note is that it protects only the Buffer Pool from paging and Non-Bpool allocations can still be paged out.

Answer 3

Your error seems more related to memory pressure. Maxdop is a CPU setting. The Max degree of parallelism is heavily depending on your queries and your needs. You need to try it out. What should know is the following:

• MAXDOP default is 0, which means ALL cores will be used IN CASE the query goes in parallel

I think you have 8 cores in your CPU E5-2670 0 @ 2.60GHz , correct? I would try out MAXDOP settings of 2 and 4 and check how fast your parallel queries go.

tip: you can add the maxdop setting as a query hint by appending

OPTION (MAXDOP 1) 

to your T-SQL code.

But also you should understand how to check if you are under memory pressure. You have only 4 GB of memory, which seems very low, understanding that these 4 GB are also for the operating system. Since I can't explain it better than Brent, here is a link which may help you understanding whether you are under memory or not. brent ozar memory pressure

Good Luck :)

Answer 4

MAXDOP can not solve this problem you need more RAM