sql server process memory has been paged out

Question

sql server process memory has been paged out

Pyman

SSChampion

Points: 13830
More actions
April 24, 2012 at 12:09 pm

#273106

Hi Everyone,
Hope all is well.
I have noticed the following error around 5:00AM this morning on our SQL Server staging environment.
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): 232356, committed (KB): 464224, memory utilization: 50%.
The following are the staging environment's configuration details:
Its a Virtual Machine
OS: WIndows Server 2008 R2 SE
Memory: 2GB
SQL Server: SQL Server 2008 R2 SE running under Network Service Account (network service is not part of the local policy: lock pages in memory)
SQL mem settings in MB:
Min Memory: 0
Max Memory: 2147283647
Currently developers are not able to run their queries frequently. The query windows freeze.
Do I need to increase the memory to get rid of this issue? I only see SQL Server running on this server and no other applications.
Please advise.
Thanks.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

Sapen (4/24/2012)
Hi Everyone,
Hope all is well.
I have noticed the following error around 5:00AM this morning on our SQL Server staging environment.
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): 232356, committed (KB): 464224, memory utilization: 50%.
The following are the staging environment's configuration details:
Its a Virtual Machine
OS: WIndows Server 2008 R2 SE
Memory: 2GB
SQL Server: SQL Server 2008 R2 SE running under Network Service Account (network service is not part of the local policy: lock pages in memory)
SQL mem settings in MB:
Min Memory: 0
Max Memory: 2147283647
Currently developers are not able to run their queries frequently. The query windows freeze.
Do I need to increase the memory to get rid of this issue? I only see SQL Server running on this server and no other applications.
Please advise.
Thanks.

Decrease you max memory to 1500. http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/

Jared
CE - Microsoft

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

http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Chapter 4. Page 156.

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

Pyman SSChampion Points: 13830 More actions · Answer 3

Thanks

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

K. Brian Kelley SSC Guru Points: 114703 More actions · Answer 4

Out of curiousity did anyone set the reserved memory for the VM itself? This prevents the balloon driver most virtualization systems use to reclaim memory from telling the guest OS its in a memory short situationa and having the OS try to get memory back from SQL Server.

K. Brian Kelley
@kbriankelley