Karthick P.K on SQL Server

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

Posted by Karthick P.K on June 27, 2012

When you get “A significant part of SQL Server process memory has been paged out. This may result in performance degradation.This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 2007640, committed (KB): 4594040, memory utilization: 43%.” message in SQL Server error log

Pay attention to Working set (KB), committed (KB) and memory utilization:% (Percentage of SQL Server memory in RAM) in above warning message. Above warning message is logged in SQL Server error log when working set reaches 50% or  below of the overall committed memory by SQL Server memory manager.


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.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



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


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.


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, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as [Notification_Time],
cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],
cast(record as xml).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %],
cast(record as xml).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [Process_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [System_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[1]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[2]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[3]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS [SQL_AWEMemory],
cast(record as xml).value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS [SinglePagesMemory],
cast(record as xml).value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS [MultiplePagesMemory],
cast(record as xml).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
tme.ms_ticks as [Current Time]
FROM sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' --and cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') = 'RESOURCE_MEMPHYSICAL_LOW'
ORDER BY rbf.timestamp ASC

We can use below query to to check the health of SQL Server including SQL Server working set information in past

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/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [ProcessUtilization],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle %],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/UserModeTime) [1]', 'bigint') AS [UserModeTime],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime) [1]', 'bigint') AS [KernelModeTime],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/PageFaults) [1]', 'bigint') AS [PageFaults],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/WorkingSetDelta) [1]', 'bigint')/1024 AS [WorkingSetDelta],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization) [1]', 'bigint') AS [MemoryUtilization (%workingset)],
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_SCHEDULER_MONITOR') AS R(x)) a  CROSS JOIN sys.dm_os_sys_info sys ORDER BY DATEADD (ms, a.[Record Time] - sys.ms_ticks, GETDATE())
  • Process Utilization shows the percentage of overall used system CPU time that was consumed by sqlservr.exe. Process Utilization is calculated as sql_process_cpu_time/total_system_cpu_time for the current time interval.
  • SystemIdle is the percentage of time that the system’s CPU’s have been idle.
  • Page Faults value is the number of hard + soft page faults that have occurred since the last snapshot.
  • Working Set is the change in the working set size  in KB, since the last snapshot.
  • Memory Utilization is working set/committed memory which is another way of saying that the number is the percentage of the process’ committed memory that is in RAM.  The farther below 100% that this number falls, the larger the percentage of SQL memory that has been trimmed and moved to the page file.

Common Side Effects of Working set Trimming

1.  When OS starts trimming the working set of SQL Server we would see drastic performance drop, increased I/O,non-yielding Resource Monitor / scheduler dumps etc..

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

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.

If you liked this post, do like us on Facebook at and join our Facebook group!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

23 Responses to “A significant part of SQL Server process memory has been paged out”

  1. AZAD MARATHA said

    Clear an Crisp Explanation. Thanks for the article.

  2. […] A significant part of SQL Server process memory has been paged out […]

  3. […] SQL Server responds to low memory resource notification from OS causing negative performance.  This link might throw more […]

  4. […] for more […]

  5. […] Make sure there is no working set trim and system wide memory pressure. You can use second query in significant part of sql server process memory has been paged out to identify and follow the same blog to fix […]

  6. […] A significant part of SQL Server process memory has been paged out […]

  7. […] A significant part of SQL Server process memory has been paged out […]

  8. […] A significant part of SQL Server process memory has been paged out […]

  9. […] A significant part of SQL Server process memory has been paged out […]

  10. […] A significant part of SQL Server process memory has been paged out […]

  11. […] A significant part of SQL Server process memory has been paged out […]

  12. […] A significant part of SQL Server process memory has been paged out […]

  13. Thanks for finally talking about >A significant part
    of SQL Server process memory has been paged out MSSQLWIKI <Loved it!

  14. voyance said

    Keep on writing, gгeat job!

  15. […] A significant part of SQL Server process memory has been paged out […]

  16. […] I search through the internet and read quite a lot about this incident. This article here explains a lot about the resource notification mechanism and the nuts and bolts of […]

  17. Un expert en vous avez un maison a vendre montpellier hotel DSE portails
    de est raisonnablement inexploité se retire n’est si jamais vous rapide puis une lit
    pour se gagnez la confiance plusieurs niveaux dans

    vente maison montpellier tourisme

    maison a vendre montpellier architecte

    vente maison montpellier grande chambre

    acheter maison montpellier code postal

    maison a vendre montpellier aiguelongue

  18. la voyɑnce dee qualité … Vous poսvwz visiter monn webpage

  19. I am really happy to glance at this web site posts which includes lots
    of helpful data, thanks for providing these kinds of information.

  20. domain said

    Thank you for every other magnificent post. The place else could anyone get that type of information in such a perfect approach of writing?
    I have a presentation next week, and I’m on the look for such information.

  21. What’s up, I want to subscribe for this blog to get most up-to-date updates,
    therefore where can i do it please help.

  22. Thanks for another great article. The place else could anybody get that type of information in such
    a perfect approach of writing? I’ve a presentation subsequent week, and I am on the search for such information.

  23. Sammie said

    Blow Your Competition From The Water With These Browse Engine Optimization Tricks

    A website you construct might look truly fancy and pack in a great deal of functions,
    however ask yourself a truthful question right here:
    Exactly what good are all those bells and whistles doing if
    no one’s even visiting your website? This is the problem you’re going to have unless you
    attend to the concern of SEO, so bone up on your skills.

    Increase your website traffic and the profits from your site by ensuring it has
    no broken links or pages. Broken links prevent
    consumers from navigating your site. Broken pages are even worse, because then the material is simply missing
    out on. Broken material can’t be indexed by online search engine
    either, which is also poor marketing.

    Even conserved images and video files on your server can present an opportunity to
    increase in the rankings, so make sure that you’re constantly conserving
    files with keywords. For example: If you have an image on your
    website handling weight reduction, like a before-and-after image, make certain you include a relevant keyword in the title when you conserve the image.

    Avoid utilizing keywords that are of no significance to your website or product.
    Web crawler bots may mistake your site as spam and blacklist your
    website from the search results when you do. On the other hand, make certain to include all pertinent keywords on your home page as this is the page you ought to desire your consumers
    to see initially.

    Be specific about your expressions and keywords and keep it easy.
    If you aim to capture every keyword in your niche, you will certainly have spread yourself too thin. Target a
    choose few and use them artistically and commonly while still
    maintaining a natural flow to your page. By doing this you can push to
    the top of the list much easier.

    Efficient SEO strategies will certainly not need a high level of skill and even a lot of effort.
    What’s crucial is that you take the right technique. As you discover about SEO, you will discover
    that a couple of minor tweaks can correspond to huge results.

    Start on your optimization using exactly what you’ve learned

    For more reference: visit us at SEOBloggerninja

Leave a Reply

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

You are commenting using your 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


Get every new post delivered to your Inbox.

Join 3,552 other followers

%d bloggers like this: