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

Question

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

Andy sql

SSCrazy Eights

Points: 9481
More actions
August 26, 2013 at 8:02 am

#282404

Hi Everyone,
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
http://sqlserverperformance.wordpress.com/2011/02/14/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...
Andy

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Andy sql (8/26/2013)
The MSSQL Maximum Server Memory is set to 29,696MB (29GB, leaving 3GB free for the OS)

That's a little high. On a 32 GB server, I'd probably be looking at 26-28GB, unless monitoring showed that it can go higher.

You said it's a VM. Does the VM actually have 32 GB or does it just think it has 32 GB? Speak to the VM admin, make sure it's the former.

As for monitoring memory, use the perfmon counter total server memory and target server memory, not task manager.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Andy sql SSCrazy Eights Points: 9481 More actions · Answer 2

Hi,

I did mention the current, production server is a VM. However, the new server - not yet in production and the subject of this post - is a physical box with real, physical sticks of SDRAM DIMMS. I have confirmed, using several different methods, including Performance Monitor (eg. the counter "Available MBytes"), that over 29GB of RAM is available.

Is there a specific counter called "Total Server Memory", if so can you tell me under which Performance Counter it is hiding?

I will reduce the Max Server Memory as you suggest; but I can't see that it will help here - I currently have 3.5GB committed and 29GB free RAM....

Andy

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

In that case you need to figure out what is requesting memory from Windows such that it's paging SQL out. It'll only do that if it is under memory pressure (the OS) and SQL is not responding fast enough in reducing memory.

Probably will need a lot of time looking at perfmon, the process object, the memory counters for all the processes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Andy sql SSCrazy Eights Points: 9481 More actions · Answer 4

Sounds like good advice. I will expand the Performance Counters I'm collecting, and let Performance Monitor run for some days. Hopefully I can link an MSSQL-memory-paging event with a specific process..... I'll post again when I find something.

Andy

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Also watch out for large file copies and make sure you're running updated drivers for RAID/storage.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

The size of the working set here is very small and this is not likely a real problem. See the update on this blog post that states this could happen on a system with no databases and no activity:

http://blogs.msdn.com/b/psssql/archive/2009/05/12/sql-server-reports-working-set-trim-warning-message-during-early-startup-phase.aspx

If you see a working set trim of a larger working set with activity then it would be a problem. I'd also recommend reading this article on LPIM:

https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/[/url]

I take the stance that an ounce of prevention is worth more than a pound of cure since this would prevent problems if you setup your instance correctly.

Andy sql SSCrazy Eights Points: 9481 More actions · Answer 7

Thanks GilaMonster and Jonathan.

The update, at the end of the MSDN blog, explains exactly the behaviour I am experiencing. Thank you for the link.

I am still a little mystified as to what, exactly, the memory utilization % means; with gigabytes of free memory, why does MSSQL think it is using a high percentage?

In the example log entry:

Duration: 28853 seconds. Working set (KB): 76176, committed (KB): 170992, memory utilization: 44%.

If I understand correctly: the SQL Process has committed memory of 167MB and a working set of 74MB; thus 44% is used; the process only requires 74MB of 167MB allocated, which seems absolutely fine.

So why is a "significant part of sql server process memory" paged out? Especially when there are gigabytes of free memory waiting to help?

I do understand that, without a load on the system, the memory figures here are slightly artificial. I will start to migrate databases over the next few days, and will carefully monitor what happens to the memory allocations.

Andy