I'm sure most of us have seen this message in the SQL Server Logs:
"A significant part of sql server process memory has been paged out. This may result in a performance degradation."
I have a server which is under severe memory pressure (active databases add up to 90GB, but the server has only 8GB of RAM; furthermore, the server is a VM). We decided to provision a new, dedicated physical server; the configuration is:
CPU = Intel CPU Xeon E5630 2.53GHz (4-core plus hyper-threading; single physical CPU)
Physical RAM = 32GB
OS = Windows Server 2008 R2 Standard x64, SP1 (6.1.7601 Service Pack 1 Build 7601)
MSSQL Version = MSSQL Server 2008 R2 Standard Edition x64, with SP2 (10.50.4000)
pagefile.sys = 32GB
The MSSQL Maximum Server Memory is set to 29,696MB (29GB, leaving 3GB free for the OS)
The MSSQL Windows Services run under a Domain User account with minimal privileges (eg. not a member of the local Administrators group).
After completing the install, and before moving to production, I was amazed to see multiple entries in the SQL Server Logs of:
"A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 332 seconds. Working set (KB): 112780, committed (KB): 221936, memory utilization: 50%."
"A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7209 seconds. Working set (KB): 75656, committed (KB): 254880, memory utilization: 29%."
This was exactly what the new server was supposed to solve! And this paging of memory is occurring *before* the user databases exist (only the system databases and one small utility database exist). The Working Set is 110MB and the Committed is 216MB, so I can see where the 50% figure is coming from. But the server has 32,768MB of physical RAM! Performance Monitor confirms that over 29,000MB is available.
I have not enabled the local policy "Lock pages in memory"; I know there is conflicting advice on the matter, but the combination of OS and MSSQL versions, both running 64-bit, should not require it. Reference Glenn Berry:
SQL Server and the "Lock pages in memory" Right in Windows Server
If I query the ring buffers (sys.dm_os_ring_buffers), it confirms that MSSQL thinks it is using more than 40% of the memory:
MemoryUtilization % is typically above 40%.
Any recommendations for how to improve the configuration of this server? How can I encourage MSSQL to "see" more memory?
Thank you for any advice...