Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Westley
Posting Yak Master
229 Posts |
 Posted - 2005-09-14 : 00:23:24
|
| Hi, I'm a bit confused with the /3gb switch, I read somewhere that if you have a system that is more then 4G in RAM, you need /3gb switch in order for SQL to use more RAM that is installed, I also read somewhere that the /3GB switch is limit the OS kernal from using 1G ram rather then 2G by default, and I come across another article saying if your server is more then 16G, then you need to turn off /3GB switch (where MS article stated that for more then 8G you should turn it off), since the OS need the extra 1GB to handle the memory etc etc.....so i'm a bit confused here.... Say for example, if you have a 8G ram server, which have win2k adv server and SQL 2k Ent install, would you include the /3GB switch? since without it, you still get 6G out of the box, but is that wasting the 1G for OS (as from other article) said? I understand I need to have /PAE and AWE enable on the server. 1 more thing, AWE is for a single instance that require to use more then 4G ram isn't it? so if I have say 2 instance on a 6G server without the /3gb switch, so earch server can get 2G max and I will not require AWE enable? but also require the /PAE switch for the OS to use the extra ram?Thanks |
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-14 : 12:20:02
|
| Welcome to switch-hell. Here's the deal, though Slava's blog is better at describing it than I am -- get it at http://blogs.msdn.com/slavao/archive/2005/01/29/363181.aspx/3gb changes the default partitioning of a process' 4GB virtual address space from 2GB user/2GB kernel to 3GB user/1GB kernel. It allows processes to use more virtual address space. It has nothing to do with physical memory, only virtual address space. It's described at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ddtools/hh/ddtools/BootIni_de16d3ec-c437-4628-805f-8945ea598a92.xml.aspBy default a 32-bit address space can only access 4GB of space - physical or virtual. If you want more than that, you'll have to use PAE, which is an OS trick to allow access to more memory. If you have > 4GB of memory, you need a combination of /PAE in your Windows boot.ini file (which allows the operating system to access more than 4GB of memory), and the AWE sp_configure option enabled in SQL Server (which allows SQL Server to use > 4GB of memory for data and index pages). PAE is described here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ddtools/hh/ddtools/BootIni_17b4305e-23ac-40ea-99db-4858b29a5d66.xml.asp . AWE is a Windows API, so MSDN has plenty of docs.Here's where it's confusing: /3GB and PAE/AWE are completely unrelated! If you have a 6GB box, then you'll want to turn on PAE & AWE so that we can make use of the extra memory. 3GB might not be a good idea, because it limits the OS' ability to store "metadata" about that memory - if you have a 32GB box, for instance, and have 3GB turned on, you've limited yourself to 16GB of memory, because Windows doesn't have enough VAS to store page table entries for the other 16GB. From Slava:"Having 3GB switch turned on can cause effects from drop in performance and memory allocation failures to system stalls. My suggestion is to avoid usage of 3GB switch unless you really really need it."The best article out there for all of this is http://support.microsoft.com/default.aspx?scid=kb;en-us;274750 , though it's a bit confusing, since you have to pay close attention to OS versions & SQL versions.I'm sure I didn't do a great job of explaining this, so please post some specific "what the hell are you talking about" questions, and I'll be happy to answer them :)----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-09-14 : 12:22:36
|
get an x64 system and forget all the switches... -ec |
|
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-14 : 13:00:07
|
quote:
Originally posted by eyechart
get an x64 system and forget all the switches...-ec
Amen brother. That's actually a pretty important point, since all of the VAS pressure problems that we run into on x86 platforms are eliminated...----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-14 : 16:46:59
|
| These are the guidelines I use for various editions of Windows 2000:If server has 4 GB or more of memory, modify boot.ini as follows and reboot server:Following requires SQL Server 2000 Enterprise Edition, and Windows 2000 Server, Windows 2000 Advanced Server, or Windows 2000 Datacenter Server:Server has 4GB RAM: /3GBFollowing requires SQL Server 2000 Enterprise Edition, and Windows 2000 Advanced Server or Windows 2000 Datacenter Server:Server has > 4 GB to 8GB RAM: /3GB /PAEFollowing requires SQL Server 2000 Enterprise Edition, and Windows 2000 Datacenter Server:Server has > 8GB to 16GB RAM: /3GB /PAEServer has > 16GB RAM: /PAEIf the server has above 4 GB of memory, you have the /PAE switch on, and it is running SQL Server 2000 Enterprise Edition, and Windows 2000 Advanced Server or Windows 2000 Datacenter Server, enable AWE (Address Windowing Extensions) memory as follows:Exec SP_CONFIGURE 'show advanced options', 1 RECONFIGURE goexec SP_CONFIGURE 'awe enabled', 1RECONFIGUREgo/*Optional to limit the amount of memory the SQL Server will use, because SQL Server will not dynamically allocate/deallocate memory when AWE is enabled.Leave about 500 MB available for OS*/exec SP_CONFIGURE 'max server memory', 5500RECONFIGURECODO ERGO SUM |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-09-18 : 22:35:25
|
| Thanks guys for those info, I understand that when using /3gb switch, it will limit the OS kernal to use only 1G, but then my question I guess is is that enough for the OS? since I remember I read somewhere that stating if you have like 8G of RAM and if you are using the /3gb switch, you might run into some known issue where the kernal cannot allocate some key or something since its out of memory (sorry, i can't really remember what it stated). But then if I take off the /3gb switch (by default), does that mean I'm "wasting" the 1G that OS is not going to use anyway? if so, I might as well use that for SQL.Michael, I read it somewhere that >4G to 8G should use /3GB (as /PAE is a must anyway), but then I just don't understand why, is that mean up to 8G system, the OS will only (max) uses 1G ram for its kernal (its virtual memory paging) and it will never go more then that? As it looks like for up to 4G box, the OS will only use 1G, so we are wasting 1G if we don't put the /3GB switch, not too sure if that applies to system up to 8G. From Ryan, seems like we only need 2G for OS if we have more then 16G system, is that a very safe assumption? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-19 : 10:39:01
|
When you do this for 4 GB to 8GB RAM system:/3GB /PAEyou are letting SQL Server use 3 GB of the first 4 GB of RAM, instead if the 2 GB it would be limited to otherwise. I do not think that the OS on most systems will need even 1 GB of memory if you are running a dedicated SQL Server, so it is best to use the extra 1 GB for SQL Server.quote:
Originally posted by Westley
Thanks guys for those info, I understand that when using /3gb switch, it will limit the OS kernal to use only 1G, but then my question I guess is is that enough for the OS? since I remember I read somewhere that stating if you have like 8G of RAM and if you are using the /3gb switch, you might run into some known issue where the kernal cannot allocate some key or something since its out of memory (sorry, i can't really remember what it stated). But then if I take off the /3gb switch (by default), does that mean I'm "wasting" the 1G that OS is not going to use anyway? if so, I might as well use that for SQL.Michael, I read it somewhere that >4G to 8G should use /3GB (as /PAE is a must anyway), but then I just don't understand why, is that mean up to 8G system, the OS will only (max) uses 1G ram for its kernal (its virtual memory paging) and it will never go more then that? As it looks like for up to 4G box, the OS will only use 1G, so we are wasting 1G if we don't put the /3GB switch, not too sure if that applies to system up to 8G. From Ryan, seems like we only need 2G for OS if we have more then 16G system, is that a very safe assumption?
CODO ERGO SUM |
|
|
|
|
|
|
|
|
SQL Memory