April 1, 2015 at 9:49 am
Hi,
In my SQL Server Errorlog, I see the below error. The system has 8 GB of RAM with enough free RAM, something I can do to prevent this alert? (Note: I have no MIN/MAX memory set on this Instance)
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 76896, committed (KB): 167628, memory utilization: 45%.
Thanks.
April 1, 2015 at 9:59 am
Set a max server memory based on the available memory on your system (take into account other processes running on the box).
If you want to disallow paging altogether, grant the service account the "Lock Pages in Memory" privilege in "secpol.msc".
-- Gianluca Sartori
April 1, 2015 at 11:31 am
Definitely set the max memory.
How big is your database? How many people are accessing it at the same time?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2015 at 9:21 am
I would also point out that here the amount of memory involved is very ,very low. About 167 MB of total "memory" used, of which only 77 MB was in physical RAM. That's less than 50% of total in physical RAM, which is the trigger for this alert.
I usually only see an alert like that, with the incredibly small amounts of memory involved, on completely idle mirror instances. In those cases, it's not really a problem, as it just means SQL Server has seen no activity at all and isn't putting anything in the buffer pool (SQL Server always has some tiny amount of "memory" paged out, so if your server is completely idle, with nothing read into the buffer pool, the percentage that is in physical RAM can go below 50% and trigger the error).
If it's a machine dedicated to SQL Server, with the max server memory set appropriately, then it's probably just idle, and that's not a big deal.
The more insidious cases where memory pressure forces pages to be paged out in a production server usually involve much larger amounts of memory.
Long story short, definitely check to make sure your max server memory is set appropriately, but also check whether there's any activity at all on the server.
Dollars to donuts it's just nearly completely idle with the small total amount of memory in play there.
If that's the case, you can make the error go away just by running some query that reads data into the buffer pool and tips the percentage over 50%, but it's also not a big deal.
Cheers!
April 2, 2015 at 9:39 am
Thanks for spotting it.
-- Gianluca Sartori
April 2, 2015 at 10:03 am
I suspect that's during the startup process, given the tiny amounts of memory. If so, just ignore it.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply