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

Question

I have 512 GB of memory on my physical box, out of which 85% is dedicated to SQL Server. I'm starting to get this message in error log. When this happens, SQL Server closes connection to other processes or users. Any guidance on what should I do here? Nothing runs on the server at this time when this happens. Any guidance would be much appreciated.

A significant part of SQL Server process memory has been paged out. This may result in a performance degradation.

Duration: 602 seconds. Working set (KB): 3860628, committed (KB): 342039316, memory utilization: 1%.

Fiche

Guess it could not be a memory issue, did u tried to check any open transactions when it happened DBCC OPENTRAN – Alathia

Which version of sql server are you using? Have you researched this issue and tried anything? I discovered about 19,000 hits on google for your question title. Many of the ones I looked at on the first page are forum type questions with solutions to this. – Jonathonjonati

Microsoft SQL Server 2014 - 12.0.2480.0 (X64) Jan 28 2015 18:53:20 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) – Fiche

I don't believe there are any open Tran at that time. I can't even connect through DAC at that point – Fiche

This really doesn't have anything to do with programming (which this site is all about), but with DB administration - so it's off-topic here and belongs on dba.stackexchange.com - voting to move. – Hypostatize

Answer 1

You can prevent Windows from paging by granting the Lock Pages in Memory OS privilege to the SQL Server Service Account, or Per-Service SID.

See: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows

This will cause SQL Server to bypass the Windows Virtual Memory manager, and directly allocate physical memory.

Beware that when you do this SQL Server will only respond to OS memory pressure slowly, and so other processes needing memory may not be able to run. SO it's important to set Max Server Memory appropriately.

David

Barkentine

Thanks for your answer... It looks like Windows Group Policy tool is required to enable lock pages in memory which requires Windows 10 Profession/Enterprise. Is there anyway we can do this on Windows 10? – Benne

Win-R + secpol.msc then Local Policies > User Rights Assignment > Lock Pages In Memory – Barkentine