Sql-server – Troubleshooting memory pressure – how to avoid paging out SQL Server

memorysql serversql-server-2005

I have received the email below that states that an alert has been fired and

a significant part of SQL Server process memory has been paged out

should I raise the Minimum server memory to 1740 too?

Microsoft SQL Server 2005 – 9.00.5000.00 (X64) Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition
(64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

enter image description here

There are other applications, even monitoring applications running on that server.

You can have a glimpse of those applications by looking at the picture below.

enter image description here

We received an alert at 12:15:37 PM on the wrsleic1 default instance that "a significant part of SQL Server process memory has been paged out."

We've investigated the alert and found that SQL Server previously reported this condition last year on 13/04/2015. SQL Server is currently allocated a maximum memory of 1.7 GB while the server has 4 GB physical memory available to it (and a dynamically sized page file that is currently 5 GB).

At the time of the alert SQL Server had it's maximum memory reserved while not necessarily actively using the cached data. Therefore when the server detected memory pressures from an external process (potentially IIS but we can't determine this retrospectively), SQL Server trimmed its working set to accommodate the requirement and is currently utilising approximately 900 MB.

The OS is responsible for memory management and SQL Server is designed to respond to memory release requests as it did at the time of the alert. Therefore if there was no perceivable impact to performance on the application that uses the hosted databases on this instance then we don't recommend any further action.

Lock Pages in Memory

The lock pages in memory has not been enabled for any sql server account.

enter image description here

I was going to change this, however, I decided to leave this server as it is because:

1) it is running on standard edition sql server 2005 SP2 and on this page it says

Standard Edition Up until SQL Server 2005 SP3 Cumulative Update
Package 4 and SQL Server 2008 SP1 Cumulative Update Package 2 'Lock
Pages in Memory' was not supported in SQL Server Standard Edition.

2) after enabling it – needs a reboot according to this page

Question: “Weather we require Lock Pages in Memory in SQL
Server 64-Bit”?

Answer: YES

3) We should (hope so) migrate this server to sql server 2014 soon

this page has a bit more about lock pages in memory and a reminder:

enter image description here

Best Answer

a significant part of sql server process memory has been paged out

Let me explain you what this message means. This means SQL Server process was paged to disk because the OS or applications running on OS requested memory and OS could not find continuous chunk of it so paged SQL Server to disk, temporarily, and provided the physical memory to the process requesting.

If you read Karthick PK's blog there are couple of situations where SQL Server Process working set might be paged out by Windows

  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

Why did SQL Server allowed OS to take away its memory, well this is how SQL Server is designed.

should I raise the Minimum server memory to 1740 too?

I don't know from where you got this idea, min server memory has no role here don't touch it. Its OS which is facing memory pressure here NOT the SQL Server.

We received an alert at 12:15:37 PM on the wrsleic1 default instance that "a significant part of sql server process memory has been paged out."

As I can read your question you got this message previous year and one occurrence now. Keeping its mind in occurrence I would suggest its not that grave issue something running on OS sometimes requests more memory and SQL Server becomes victim.

Workaround

The workaround would be here to provide SQL Server service account Locked pages in memory privilege. This will avoid paging out of buffer pool but non buffer pool can still be paged out.

Solution.

  1. Find out what is that which is running on system and requesting more memory. Perfmon counters Process: Private bytes and Working set can come handy here.

Or download RAMMAP tool, that is there is Karthick's blog, and see memory consumption.

  1. What I can feel is there are other services running on SQL Server machine and sometimes there occurs situation where memory requirement increases and thus forcing OS to take hard step of paging. I suggest you also add more RAM on windows machine
Related Question