June 3, 2008 at 1:28 am
george sibbald (6/3/2008)
so surely /3Gb switch ok up to 16GB RAM, remove for anything over that.:)
Nope. Even under 16GB you risk starving the kernal of memory.
Personally I would not ever suggest enabling both /3GB and AWE no matter what the memory involved. The gains are too small and the risks too large.
I'm not sure why you would want to enable both. /3GB just gives 1GB more of private process space. AWE however makes the entire physical RAM available to the app.
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
June 3, 2008 at 2:09 am
THX.
so if i understand right if i have 32BIT system with 4GB box mem only enable the /3GB switch?
if i only enable the /3GB switch on 32BIT with 4GB mem box do i need to set the Lock Pages in Memory permissions too?
June 3, 2008 at 4:33 am
avipenina (6/3/2008)
THX.so if i understand right if i have 32BIT system with 4GB box mem only enable the /3GB switch?
if i only enable the /3GB switch on 32BIT with 4GB mem box do i need to set the Lock Pages in Memory permissions too?
going back to where we started, yes only enable /3GB switch. lock pages in memory not required.
---------------------------------------------------------------------
June 3, 2008 at 7:59 am
GilaMonster (6/3/2008)
george sibbald (6/3/2008)
so surely /3Gb switch ok up to 16GB RAM, remove for anything over that.:)Nope. Even under 16GB you risk starving the kernal of memory.
Personally I would not ever suggest enabling both /3GB and AWE no matter what the memory involved. The gains are too small and the risks too large.
I'm not sure why you would want to enable both. /3GB just gives 1GB more of private process space. AWE however makes the entire physical RAM available to the app.
Sorry I'm still confused...
http://qa.sqlservercentral.com/articles/Administering/aweadventures/960/
Assuming 8GB of physical memory, after adding the extra RAM, and prior to rebooting the server, your boot.ini should contain both the “/3GB /PAE” switches. Not having /3GB in your boot.ini will translate to 2GB of RAM reserved for the operating system, instead of 1GB remaining free with the “/3GB” switch. The “/PAE” switch is required if you want SQL Server to support more than 4GB of RAM.
The above applies to SQL Server 2000.
Is the situation different for SQL Server 2005?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 3, 2008 at 8:21 am
Marios Philippopoulos (6/3/2008)
GilaMonster (6/3/2008)
george sibbald (6/3/2008)
so surely /3Gb switch ok up to 16GB RAM, remove for anything over that.:)Nope. Even under 16GB you risk starving the kernal of memory.
Personally I would not ever suggest enabling both /3GB and AWE no matter what the memory involved. The gains are too small and the risks too large.
I'm not sure why you would want to enable both. /3GB just gives 1GB more of private process space. AWE however makes the entire physical RAM available to the app.
Sorry I'm still confused...
http://qa.sqlservercentral.com/articles/Administering/aweadventures/960/
Assuming 8GB of physical memory, after adding the extra RAM, and prior to rebooting the server, your boot.ini should contain both the “/3GB /PAE” switches. Not having /3GB in your boot.ini will translate to 2GB of RAM reserved for the operating system, instead of 1GB remaining free with the “/3GB” switch. The “/PAE” switch is required if you want SQL Server to support more than 4GB of RAM.
The above applies to SQL Server 2000.
Is the situation different for SQL Server 2005?
Marios,
looks like it could be an 'it depends ' situation. I have always worked to using the /3Gb switch if RAM under 16GB. This is because 1GB of rAM for the OS should be enough if total ram less than 16GB. Also AWe just uses memory above the 4GB line, so if you do not use /3GB switch you are wasting 1Gb in the first 4Gb SQLserver could use. So it all depends how much you want that extra GB and how much load you think the OS will be under.
Maybe Gail has been burned on this 🙂
---------------------------------------------------------------------
June 3, 2008 at 8:30 am
george sibbald (6/3/2008) Marios,
looks like it could be an 'it depends ' situation. I have always worked to using the /3Gb switch if RAM under 16GB. This is because 1GB of rAM for the OS should be enough if total ram less than 16GB. Also AWe just uses memory above the 4GB line, so if you do not use /3GB switch you are wasting 1Gb in the first 4Gb SQLserver could use. So it all depends how much you want that extra GB and how much load you think the OS will be under.
So in my scenario of 3 SQL instances co-existing on the same server (8-GB RAM total):
Instance1: 3 GB
Instance2: 2 GB
Instance3: 2 GB
OS : 1 GB
I want to allow Instance1 to access 3 GB of the RAM available, while leaving 2 GB of RAM for each of the other 2 instances and 1 GB for the OS.
I also want to allow all 3 SQL instances access to the page file for extra memory.
How should this be configured?
(Sorry for my persistence, I'm just really confused about this and BOL has not helped... :angry:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 3, 2008 at 8:48 am
Marios,
That's an interesting scenario. I haven't dealt with it, but my thoughts are that you enable AWE on all instances. With 2-3GB for each instance, I think you would not use the /3GB switch.
The /3GB switch means that the application space becomes 3GB v the OS space of 1GB. This means more space for the application to use that memory, but the OS has less. It's a virtual 4GB, so each instance really thinks it has 4GB it shares with the OS, but not sure how that would be affected by the /3GB switch and multiple instances. I think the current advice is to go 64bit with multiple instances.
When you use AWE and PAE, there is "windowing" that needs to be done, meaning the OS has to reserve memory space INSIDE the 4GB space (it's 1 or 2GB space) to swap memory from higher spaces to this window area so SQL or anything else can use it. If you reduce the OS space to 1 GB, this window space (depending on memory above 4GB) then can starve the OS, causing pagefile swapping, slowing things down, etc.
I can't remember if it's >8GB or > 16GB but at some point with PAE, you need 1GB just for the windowing.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 3, 2008 at 12:51 pm
Steve Jones - Editor (6/3/2008)
Marios,That's an interesting scenario. I haven't dealt with it, but my thoughts are that you enable AWE on all instances. With 2-3GB for each instance, I think you would not use the /3GB switch.
The /3GB switch means that the application space becomes 3GB v the OS space of 1GB. This means more space for the application to use that memory, but the OS has less. It's a virtual 4GB, so each instance really thinks it has 4GB it shares with the OS, but not sure how that would be affected by the /3GB switch and multiple instances. I think the current advice is to go 64bit with multiple instances.
When you use AWE and PAE, there is "windowing" that needs to be done, meaning the OS has to reserve memory space INSIDE the 4GB space (it's 1 or 2GB space) to swap memory from higher spaces to this window area so SQL or anything else can use it. If you reduce the OS space to 1 GB, this window space (depending on memory above 4GB) then can starve the OS, causing pagefile swapping, slowing things down, etc.
I can't remember if it's >8GB or > 16GB but at some point with PAE, you need 1GB just for the windowing.
Thanks Steve and all of you for your valuable input.
Steve, I think you may be referring to the following:
from http://msdn.microsoft.com/en-us/library/aa213764(SQL.80).aspx
In order to allow AWE to use the memory range above 16 GB, be sure the /3gb parameter is not in the boot.ini file. If it is, Windows 2000 will be unable to address any memory above 16 GB. When allocating SQL Server AWE memory on a 32-GB system, Windows 2000 may require at least 1 GB of available memory to manage AWE. Therefore, when starting an instance of SQL Server with AWE enabled, it is recommend you do not use the default max server memory setting, but instead limit it to 31 GB or less.
from http://msdn.microsoft.com/en-us/library/ms175581.aspx:
If there is more than 16Â GB of physical memory available on a computer, the operating system requires 2Â GB of virtual address space for system purposes and therefore can support only a 2Â GB user mode virtual address space. For the operating system to use the memory range above 16Â GB, be sure that the /3gb parameter is not in the Boot.ini file. If it is, the operating system cannot use any physical memory above 16Â GB.
So it is 16 GB of RAM beyond which the 3-gb switch is an absolute no-no.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 4, 2008 at 3:56 am
I thought I understood the /3GB and AWE settings until this thread.
I have a 4GB box SQL2005 Std Edition if I have the /3GB switch and AWE is NOT enabled, then how does the 32-bit SQL process access more than 2GB of ram? I understand that the /3GB makes 3GB not just 2GB referencable for applications but I thought that the AWE setting was needed to enable 1 32-bit process to access more than 2GB (along with /3GB and/or /PAE) as opposed to having some other application process on the server such as IIS (for example) so IIS and SQL would share the 3GB but neither of them could get more than 2GB but with AWE then SQL could have nearer the 3GB.
Thanks.
June 4, 2008 at 7:26 am
sotn (6/4/2008)
I thought I understood the /3GB and AWE settings until this thread.I have a 4GB box SQL2005 Std Edition if I have the /3GB switch and AWE is NOT enabled, then how does the 32-bit SQL process access more than 2GB of ram? I understand that the /3GB makes 3GB not just 2GB referencable for applications but I thought that the AWE setting was needed to enable 1 32-bit process to access more than 2GB (along with /3GB and/or /PAE) as opposed to having some other application process on the server such as IIS (for example) so IIS and SQL would share the 3GB but neither of them could get more than 2GB but with AWE then SQL could have nearer the 3GB.
Thanks.
I think your understanding is overall correct. It's generally not a good practice having applications other than SQL server installed on the same machine. If you do then the 3-GB switch will just enable all apps (SQL Server and others) to access 3 GB of memory. AWE, on the other hand, is targetted exclusively for SQL Server. Setting AWE to a max of 3 GB memory may just starve other apps from memory access.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 4, 2008 at 7:45 am
Sorry I don't think I stressed the specific understanding issue properly, I meant that for a 32-bit app to access more than 2GB does it require AWE to be enabled rather than just the /3GB switch as that switch just means that 3GB is available to be referenced by an application that is capable of doing so. And without the AWE switch I did not think a 32-bit app could address more than 2GB.
June 4, 2008 at 7:49 am
sotn (6/4/2008)
Sorry I don't think I stressed the specific understanding issue properly, I meant that for a 32-bit app to access more than 2GB does it require AWE to be enabled rather than just the /3GB switch as that switch just means that 3GB is available to be referenced by an application that is capable of doing so. And without the AWE switch I did not think a 32-bit app could address more than 2GB.
I'm not sure...
I would enable both 3gb and AWE in this case, just to be sure.
Maybe someone else could give a better answer?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 4, 2008 at 7:53 am
sotn (6/4/2008)
Sorry I don't think I stressed the specific understanding issue properly, I meant that for a 32-bit app to access more than 2GB does it require AWE to be enabled rather than just the /3GB switch as that switch just means that 3GB is available to be referenced by an application that is capable of doing so. And without the AWE switch I did not think a 32-bit app could address more than 2GB.
the /3GB switch is to access more than 2Gb in the first 4GB of memory. AWE (along with /PAE in the boot.ini) is to access memory above the 4GB line. If you have 16Gb or more actual RAM, remove /3GB switch so as not too starve OS of memory. some people have obviously hit problems with /3GB switch and less than 16Gb RAM, so you may need to test that option.
HTH
george
---------------------------------------------------------------------
June 4, 2008 at 9:22 am
This has been a very interesting thread for me because we are considering 32bit vs 64bit as well.
Our situation is:
We have Enterprise Ed SQL Server 2000 running on Win2003 32bit OS, with Analysis Services also running on this server. The server has 8GB of RAM and we have set /3gb switch so that AS can use up to 3GB of first 4GB of RAM, leaving the OS with 1GB of RAM. Then we set AWE on and have limited SQL Server to using 4GB of RAM.
This seems to work in our environment quite well. But we have recently experienced some out of memory errors, and it is always with AS. So, I am thinking this is due to our AS applications becoming so large that they need more RAM themselves. There are 860 dimensions defined to date, with more coming. I know that AS can use all memory in a 64bit environment. So that is driving us to consider it, not that our currrent environment is being starved of memory due to the usage of both /3gb and AWE.
Just thought I'd share an example of when both might be used, in a real world scenario.
Jeff
June 4, 2008 at 10:00 am
I think George has it right in his last post above here.
Great discussion!
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 15 posts - 16 through 30 (of 64 total)
You must be logged in to reply to this topic. Login to reply