-1

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.

closed as off-topic by Shawn Melton, Philᵀᴹ, Max Vernon, RLF, RolandoMySQLDBA Dec 29 '15 at 21:43

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – Shawn Melton, Philᵀᴹ, Max Vernon, RLF, RolandoMySQLDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Did you went through your own question which you asked couple of days back. It had the same issue mentioned – Shanky Dec 28 '15 at 9:40
  • @Shanky, Yes I have gone through that but unable to short out the problem. still problem is resist. – Md Haidar Ali Khan Dec 28 '15 at 10:33
  • I am sure you did not because you have not provided Locked pages in memory privilege, have you ? – Shanky Dec 28 '15 at 10:58
  • 3
    Why do you have max memory set to 6GB (6144) when your server only has 4GB? – Shawn Melton Dec 28 '15 at 17:03
  • 1
    How large is the data on this server? What is the Page Life Expectancy for this instance? – Max Vernon Dec 29 '15 at 15:46
4

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 :

enter image description here


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.

  • Paging does not always says you are having memory pressure. Are you referring to SQL Server memory pressure then this is incorect it might be correct to say OS is faing memory pressure. It can be because of some leaking driver or incorectly configured VMWARE – Shanky Dec 29 '15 at 7:57
  • I am referring to OS pressure. If the OP adds more RAM and balance the max memory, that should alleviate the memory problem. – Kin Shah Dec 29 '15 at 13:25
  • So then you should add that in your answer to make it more clear. Plus paging does not necessarily means memory pressure system wide that can be solved by adding more RAM. In this case suppose baloon driver or memory leaking application is taking memory do you really think adding RAM is correct solution ? – Shanky Dec 29 '15 at 13:28
  • 1
    Lets not play guessing games - how do we know if balloon driver is taking more memory, etc. I am suggesting to address the low hanging fruit first. I will add more to the answer later for completeness. – Kin Shah Dec 29 '15 at 13:33
  • If you read correctly that was an example I am not suggesting this exactly is the cause. The whole point is your first sentence start with memory pressure (OS or SQL SERVER who knows ) is not correct. You are straight away defining the culprit. Though the reason mentioned in The Why seems more logical – Shanky Dec 29 '15 at 13:54
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.

1

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 :)

-3

MAXDOP can not solve this problem you need more RAM

  • 3
    Hi, First of all, welcome to the site. I see your answer attracted some downvotes and that's probably because you could elaborate a bit. You are most likely right but you could do a better job at explaining why. Please read how to answer for pointers. – Tom V Dec 29 '15 at 10:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.