A significant part of SQL Server process memory has been paged out
A newer version of this post is available in THIS LINK
A significant part of SQL Server process memory has been paged out. This may result in performance degradation.
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): 2007640, committed (KB): 4594040, memory utilization: 43%.
What is working set: Memory allocated by the process which is currently in RAM.
Committed: Total memory that is allocated by process (allocated bytes can be in RAM or Page file)
Working Set trimming: Windows is moving the allocated bytes of the process from physical RAM to page file because of memory pressure. Memory pressure is most commonly caused by applications or windows components that are requesting more memory causing OS to start trimming working set of other processes to satisfy these new requests.
Before we step in to troubleshooting working set trimming warnings, here are few basics about how SQL Server memory management is designed to dynamically adjust the committed memory based on the amount of available memory on the system.
SQL Server uses CreateMemoryResourceNotification to create a memory resource notification object and SQL Server Resource monitor threads calls QueryMemoryResourceNotification every time it runs to identify if there is any notification. If a low memory notification comes from Windows, SQL Server scales down its memory usage and when Windows sends the high memory notification, SQL Server Server can grow its memory usage target. Low memory notification is signaled by windows when the available physical memory drops approximately below 32 MB per 4 GB, to a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value. As soon as the SQL Server resource monitor threads finds low-memory-resource notification it scales down SQL Server memory usage.
Why do I see “ A significant part of sql server process memory has been paged out. This may result in performance degradation.” By SQL Server In spite of having above mechanism to detect the system level memory pressure and scale SQL Server memory?
There are couple of situations where SQL Server Process working set might be paged out by Windows despite these memory resource notification mechanism.
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.Conditions in Windows where working sets of all processes are trimmed
4.Windows might decide to trim a certain percentage of working set of various or specific processes
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) -> 1000
System Reboot is required to take effect.
Default values as per MSDN:
“The default level of available memory that signals a low-memory-resource notification event is approximately 32 MB per 4 GB, to a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value.”
We can use the below query to extract information about the condition of OS memory and SQL memory using a query like the following. Looking at this query, you will be able to easily determine the various indicators that would have triggered the Windows to page various processes including SQL Server. Use the following query to obtain the memory notification-related information from the XML data of the 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())
Common Side Effects of Working set Trimming
1. When OS starts trimming the working set of SQL Server we normally see non-yielding Resource Monitor dumps.
2. IS-alive check failures resulting in SQL Server resource failure.
3. Resource monitor thread can start Garbage collector if SQLCLR is enabled on this instance of SQL. When Garbage collector is kicked off during memory pressure all other threads in the process are suspended. So if Garbage collector is taking a long time reosurce monitor thread appears stuck and hence the non-yielding errors and dumps are generated. (Refer https://support.microsoft.com/kb/2504603)
How to troubleshoot?
1. Capture perfmon counters (Process: Private bytes and Working set ) to determine which applications / windows component are requesting memory and causing OS to start trimming the working set of processes including SQL Server.
2. Use This exe which will print the memory information of all the processes and system wide memory information (Global memory status) when the operating system signals low memory notification.
3. Cap the SQL Server MAX Server Memory after considering the memory required by other applications, Operating system, Drivers , SQL Server Non- Bpool allocations etc. Make sure you have adequate available physical memory even when the system is under heavy load.
4. We can consider using the Lock pages in memory privilege. Remember it protects only the BPool from paging and Non-Bpool allocations can still be paged out.
https://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture/
https://mssqlwiki.com/sqlwiki/sql-performance/troubleshooting-sql-server-memory/
https://mssqlwiki.com/sqlwiki/sql-performance/io-bottlenecks/
If you liked this post, do like us on FaceBook at https://www.facebook.com/mssqlwiki and join our FaceBook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/
Thank you,
Karthick P.K |My Facebook Page |My Site| Blog space| Twitter
Comments
Anonymous
June 23, 2012
Very good article mainly because of the definitions you gave at the beginning of your article, it is a part which is too often "forgotten" in the MSDN blogs. For the How to troubleshoot ? part, it is simple , easily understandable by everybody beginning in SQL Server. I hope you will produce more articles as valuable as this one. Many thanksAnonymous
June 23, 2012
The comment has been removedAnonymous
June 27, 2012
Hello ArthurZ, Thanks for letting me know about the broken EXE link. I have fixed it now. Regards Karthick