A significant part of sql server process memory has been paged out.

Looking through the SQL Server log shortly after the server started I saw:

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): 128084, committed (KB): 423936, memory utilization: 30%.

This is on a Windows Server 2012 R2 VM with SQL Server 2008 R2, 8GB RAM and a maximum server memory configuration of 6144MB.  Lock pages in memory is enabled for the SQL Server service account.  There are many reports of this error from the Windows Server 2003 x64 and SQL Server 2005 era, but little information from more recent times. 

The message is saying that a large amount of the memory allocated to the SQL Server process, sqlservr.exe, has been moved from RAM to the page file as the process’ working set has been trimmed.  In the case of  the message above the SQL Server process has a working set of 128084KB of memory allocated in RAM and a total memory allocation which could be in RAM or on disk of 423936KB.  The memory utilisation value of 30% is showing that ~30% of the SQL Server process’ total memory allocation is in RAM, which is the working set value.  This warning message is raised when the memory allocation in RAM (working set) is 50% or below the committed memory value.

In the case of this server the working set and committed values are low compared to the maximum server memory setting and Task Manager was showing ~2.5GB of the server’s 8GB RAM in use.  Following research I found this MSDN blog article which discusses working set trim warning messages early in the SQL Server startup phase, or shortly after the server is ready for user connections.  Based on this article it appears that in my case I can ignore the warning as the message values are low compared to the server max memory setting and there is little activity on the server.

 

 

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 )

Google+ photo

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

Connecting to %s