SQL Log : A significant part of sql server process memory has been paged out

If you seeing this message “A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1851 seconds. Working set (KB): #####, committed (KB): #####, memory utilization: ##%. ” 

What does it mean: System is running low on memory basically windows is moving the allocated objects from memory into a paged file to deal with memory pressure.

Is SQL Server taking way too much memory? : May be not this is known behavior of windows to trim down the memory used by the current process so it can allot memory to new requests.

SQL Server has mechanism in place to use low resource memory notification it uses a function called CreateMemoryResourceNotification  to create a system wide memory notification if windows detects low memory it sends out a notification with a signal 0 (available physical memory is running low ) or 1 (available physical memory is high) based on the result SQL Server reacts accordingly by performing the necessary action increasing the buffer pool or decreasing it. Note: if neither of these are signaled SQL Server maintains a constant memory foot print.

 

If SQL Sever has a mechanism to deal with low memory why is windows moving the allocated in memory objects to page file?

Can be many reasons for it

  1. Windows might not be sending the notifications in appropriate instances or SQL Sever might not be receiving those events.
  2. SQL Server might not be responding in timely manner may be too slow

When SQL Server receives low physical memory notification 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.

We can query these memory notifications in ring buffer

 

 

SELECT
CONVERT (varchar(30), GETDATE(), 121) as runtime,
DATEADD (ms, a.[Record Time] – sys.ms_ticks, GETDATE()) AS Notification_time,
a.* ,
sys.ms_ticks AS [Current Time]
FROM
(SELECT x.value(‘(//Record/ResourceMonitor/Notification)[1]’‘varchar(30)’AS [Notification_type],
x.value(‘(//Record/MemoryRecord/MemoryUtilization)[1]’‘int’AS [MemoryUtilization %],
x.value(‘(//Record/MemoryRecord/TotalPhysicalMemory)[1]’‘bigint’AS [TotalPhysicalMemory_KB],
x.value(‘(//Record/MemoryRecord/AvailablePhysicalMemory)[1]’‘bigint’AS [AvailablePhysicalMemory_KB],
x.value(‘(//Record/MemoryRecord/TotalPageFile)[1]’‘bigint’AS [TotalPageFile_KB],
x.value(‘(//Record/MemoryRecord/AvailablePageFile)[1]’‘bigint’AS [AvailablePageFile_KB],
x.value(‘(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]’‘bigint’AS [TotalVirtualAddressSpace_KB],
x.value(‘(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]’‘bigint’AS [AvailableVirtualAddressSpace_KB],
x.value(‘(//Record/MemoryNode/@id)[1]’‘int’AS [Node Id],
x.value(‘(//Record/MemoryNode/ReservedMemory)[1]’‘bigint’AS [SQL_ReservedMemory_KB],
x.value(‘(//Record/MemoryNode/CommittedMemory)[1]’‘bigint’AS [SQL_CommittedMemory_KB],
x.value(‘(//Record/@id)[1]’‘bigint’AS [Record Id],
x.value(‘(//Record/@type)[1]’‘varchar(30)’AS [Type],
x.value(‘(//Record/ResourceMonitor/Indicators)[1]’‘int’AS [Indicators],
x.value(‘(//Record/@time)[1]’‘bigint’AS [Record Time]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = ‘RING_BUFFER_RESOURCE_MONITOR’AS R(x)) a
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY DATEADD (ms, a.[Record Time] – sys.ms_ticks, GETDATE())

 

We can also increase the LowMemoryThreshold value so the OS will notify applications such as SQL on low memory conditions much earlier and SQL Server can respond to memory pressure much early before the system is starving for memory.

 

How to set the LowMemoryThreshold value (in MB)?

 

In Regedit -> go to

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SessionManager\MemoryManagement

Right click on the right pane,

Select New -> select click DWORD Value -> enter LowMemoryThreshold

Double Click LowMemoryThreshold -> value (choose decimal) -> 512

System Reboot is required to take effect.

 

https://msdn.microsoft.com/en-us/library/windows/desktop/aa366541(v=vs.85).aspx

 

http://blogs.msdn.com/b/karthick_pk/archive/2012/06/22/a-significant-part-of-sql-server-process-memory-has-been-paged-out.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s