December 17, 2010 at 2:54 am
This is the error message what we have noticed in the SQL Log.
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): 114408, committed (KB): 231208, memory utilization: 49%.
FYI..
Windows Server 2K3
SQL Server 2005 SP3
December 17, 2010 at 3:40 am
December 17, 2010 at 12:31 pm
Dave said it best Google is your friend, one solution is in this article.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
December 18, 2010 at 11:39 am
Check if SQL Server Service Account has "Lock Pages in Memory" rights on the server.
December 18, 2010 at 11:50 am
palla.sureshbabu (12/17/2010)
This may result in a performance degradation. Duration: 0 seconds.
Let me show you how I read it... MAY result in a performance degradation, duration ZERO seconds.
So... what you initial reseach says about it?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 6, 2011 at 11:50 am
What is the min and max memory setting? I have seen this occur if they are set to the same value. If the OS needs memory it will send a "request" to SQL Server (and other applications) to release memory. If min and max memory is set to the same value, SQL Server will not release any memory, and the OS might, as a last resort, page parts of SQL Servers memory to page file.
This kills performance.
You should also leave some memory to the OS. I've seen recommendations to leave 2GB per 8GB to the OS and other processes that needs memory. If your server has 16GB of memory, then you should set max server memory to 12GB. Min server memory could be set to 10GB.
If you still experience these problems you should monitor the total memory consumption to find peak values of memory usage. Adjust SQL Server min max memory based on these findings.
January 7, 2011 at 7:36 am
1) the cause is often due to bugs, especially in drivers. windows 2003 had a large file copy bug that did this and HP's ILO stuff likewise. I had a client that got hit by BOTH of those and it was ugly.
2) do you have your max memory set for sql server and if so to what value and how much ram on the system? 32 bit or 64 bit?
3) beware locking pages in memory. if you do this and don't know what you are doing you can really screw yourself. http://support.microsoft.com/kb/918483 has this statement: “You should make additional considerations before you assign the ‘Lock pages in memory’ user right. If you assign this user right on systems that are configured incorrectly, the system may become unstable or experience a performance decrease of the whole system. Additionally, event ID 333 may be logged in the event log.”
4) what else besides SQL Server is running on the box?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply