April 24, 2008 at 10:44 am
I received the following in the errorlog:
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): 2556, committed (KB): 1607948, memory utilization: 0%.
Then, a short stack dump/bugcheck dump was produced.
I just ran dbcc checkdb and there are no errors - phew. Is there anything else I should do?
Thanks in advance!
Jen
April 25, 2008 at 12:06 am
It's not a corruption message.
That's telling you that the OS ran short of memory (physical) and paged the SQL server process's working set to the swap file. It's pretty much guarenteed that your performance will go through the floor when that happens.
What edition of SQL are you running? What OS? 32 bit, 64 bit? How mch physical memory? What's SQL Server's memory limits (max and min server memory)?
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
April 25, 2008 at 8:45 am
Thank you for responding Gail. We are running SQL Server 2005 EE on Windows 2003 (32-bit). We have 8GB of memory on the server available. Min and max are currently set to their default settings.
We are in the process of testing and enabling AWE and setting the min and max server memory setting, but just have not gotten to actually applying to production yet. We are also researching which switches would be best for our environment (/PAE, /DEP, /3GB).
The plan is to set the max server memory to 6.5GB and min server memory to 1GB. I think all we need for switches is /3GB since the server itself already recognizes 8GB.
Does anyone see any potential problems with these configurations?
From what I understand, /PAE and /DEP are similar in that they both allow the server to recognize more than 4GB or memory (choose one or the other - not both). The /3GB switch pretty much restricts the OS to using 1GB instead of 2GB - allowing 1GB to be used by other applications (user mode programs). And, enabling AWE will allow sql server to recognize and utilize more than 4GB of memory. Are these statements correct?
Thanks,
Jen
April 25, 2008 at 9:05 am
i had this on 64bit servers in a few cases
solution was to enable lock pages in memory, not sure if you need to enable AWE and the rest on 32 bit servers as well
April 25, 2008 at 9:13 am
i had this on 64bit servers in a few cases
solution was to enable lock pages in memory, not sure if you need to enable AWE and the rest on 32 bit servers as well
I have had this on 64 bit servers (Standard edition of SQL Server)...did enabling 'lock pages in memory' fix the issue completely?
Thanks
Gethyn Elliswww.gethynellis.com
April 25, 2008 at 9:22 am
SQL Noob (4/25/2008)
i had this on 64bit servers in a few casessolution was to enable lock pages in memory, not sure if you need to enable AWE and the rest on 32 bit servers as well
I should have mentioned, we need to enable lock pages in memory for the user that runs the sql server service in order to enable AWE.
I also verified that our network connections have the max data throughput for network applications option selected.
As for 32-bit vs 64-bit - I found this article that explains when to use AWE for 32-bit and states it cannot be configured in 64-bit.
"AWE is not needed and cannot be configured on 64-bit operating systems."
http://msdn2.microsoft.com/en-us/library/ms190731.aspx"> http://msdn2.microsoft.com/en-us/library/ms190731.aspx
April 25, 2008 at 1:12 pm
here's some info which may be of use http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/01/06/configuring-windows-2003-x64-for-sql-server.aspx
x32 and memory. It sort of depends upon what you're actually doing on the server, but - I'd advise against using awe and the 3gb/switch together. On an awe system i usually leave 4gb for the o/s and applications. If your service account is set correctly it will already have lock pages in memory, otherwise it can't use awe. NOTE - once you set awe on, the min memory setting does nothing - whatever you have set as max memory will be taken - there is no dynamic memory with awe enabled. That you've had the error message says that you have not enough memory for the o/s, mem to leave or other applications. There are also a number of o/s patches which can be very harmful to sql server - they're all on my blog too.
Please don't compare x64 and x32 the settings/config are different.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 2, 2008 at 9:19 am
colin Leversuch-Roberts (4/25/2008)
here's some info which may be of use http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/01/06/configuring-windows-2003-x64-for-sql-server.aspxx32 and memory. It sort of depends upon what you're actually doing on the server, but - I'd advise against using awe and the 3gb/switch together. On an awe system i usually leave 4gb for the o/s and applications. If your service account is set correctly it will already have lock pages in memory, otherwise it can't use awe. NOTE - once you set awe on, the min memory setting does nothing - whatever you have set as max memory will be taken - there is no dynamic memory with awe enabled. That you've had the error message says that you have not enough memory for the o/s, mem to leave or other applications. There are also a number of o/s patches which can be very harmful to sql server - they're all on my blog too.
Please don't compare x64 and x32 the settings/config are different.
Colin, you stated that the min setting does nothing and no dynamic memory is used with AWE enabled. But, I found the following on http://msdn.microsoft.com/en-us/library/ms190673.aspx"> http://msdn.microsoft.com/en-us/library/ms190673.aspx
"SQL Server 2005 supports dynamic allocation of AWE memory on Windows Server 2003. During startup, SQL Server reserves only a small portion of AWE-mapped memory. As additional AWE-mapped memory is required, the operating system dynamically allocates it to SQL Server."
Were you referring to Windows 2000 maybe? Or, am I reading something wrong?
May 2, 2008 at 9:40 am
schep021 (4/25/2008)
The plan is to set the max server memory to 6.5GB and min server memory to 1GB. I think all we need for switches is /3GB since the server itself already recognizes 8GB.
Does anyone see any potential problems with these configurations?
For SQL to use 6.5 GB memory, you'll need to enable AWE within SQL. /3GB only allows it to use up to 3 GB. It may be that /PAE is already enabled, that's why the server sees all 8 GB.
Do not, do not, use /3GB and /PAE at the same time.
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
May 2, 2008 at 10:03 am
GilaMonster (5/2/2008)
schep021 (4/25/2008)
The plan is to set the max server memory to 6.5GB and min server memory to 1GB. I think all we need for switches is /3GB since the server itself already recognizes 8GB.
Does anyone see any potential problems with these configurations?
For SQL to use 6.5 GB memory, you'll need to enable AWE within SQL. /3GB only allows it to use up to 3 GB. It may be that /PAE is already enabled, that's why the server sees all 8 GB.
Do not, do not, use /3GB and /PAE at the same time.
Gail, could you please explain why I should not use these two together. From my understanding, /3GB does not restrict sql server to using only 3GB, but it restricts kernel memory to only using 1GB instead of 2GB -- allowing user mode programs to utilize more memory.
When you put a /3GB switch into the Boot.ini file of the operating system, you reallocate the virtual address space distribution to give User mode programs 3 GB of space and limit the kernel to 1 GB. http://support.microsoft.com/kb/328882
This link was also helpful:
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/11/awe-and-3gb-an-empirical-picture.aspx
May 2, 2008 at 1:33 pm
All /3gb does is change the normal division of the virtual addressable 4 GB (all that's directly addressable on 32 bit) from 2GB for user processes and 2 GB for kernal to 3 GB for user processes and 1 GB for the kernal.
There have been a large number of threads on this very issue recently.
http://www.sqlservercentral.com/Forums/Topic492504-9-1.aspx
http://www.sqlservercentral.com/Forums/Topic412378-146-1.aspx
http://www.sqlservercentral.com/Forums/Topic428470-146-1.aspx
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
May 2, 2008 at 1:42 pm
Colin: do you have a good article/blog post that explains /3gb, /pae and AWE on 32 bit systems?
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
May 2, 2008 at 2:05 pm
Gail - thank you for your help. From what I gather from your other posts, I don't want to implement the /3GB switch as it may starve my kernel memory and potentially lead to my system crashing. Is this correct?
May 4, 2008 at 1:00 pm
I'm thinking about a post/page about x32 memory - I thought that this subject was old hat these days but obviously not! I also want to test x32 on x64 o/s but I have to do that at home so have to upgrade the memory on one of my servers.
In theory x32 memory should work fine but if there are lots of other applications on your server, system monitoring, av etc. and maybe there's lots of use of the mem to leave area then there can be problems, although mots of this manifests as sql server paging, I use process explorer to watch this. It's funny in as much as I asked at the official launch of sql 2005 if x64 was the way to go ; there was a rueful smile and a confirmation of such. I've since heard there was pressure to make sql 2008 x64 only - remember exchange is only x64 now.
To be honest working with w2k3 x64 is essentially no different to w2k3 x32.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 4, 2008 at 3:13 pm
colin Leversuch-Roberts (5/4/2008)
I'm thinking about a post/page about x32 memory - I thought that this subject was old hat these days but obviously not!
Please do. It would be nice to have something comprehensive to link to, instead of explaining over and over again.
AWE and /3gb seems to be the topic of the month for April.
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 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply