June 27, 2007 at 3:06 pm
I have a question re how to maximize performance using AWE memory. Our 10 databases reside in one instance of SQL Server 2000 Enterprise SP3a residing on Windows 2003 Advanced Server. The hardware is 2 - 8 ways with 16GB - clustered. The boot.ini file has the /PAE switch, AWE is enabled and max memory is set to 13GB. The only apps on the boxes are SQL Server, Anti-Virus, Veritas (replication to DR), Quest performance collectors and Lumigent (auditing).
The servers were initially installed by our hosting company with the /3GB switch. Before going live they removed it stating the OS needed additional headroom. Although we had only started testing, my perception was that complex queries were running 25-30% faster with the 3GB switch in place. Books-on-line notes that the /3GB switch should not be used if there is more than 16GB of RAM – but I’m curious as to the best configuration with exactly 16GB.
Does anyone have experience with a similar configuration? If so, what is the idea memory configuration to maximize performance? Any supporting articles I can reference? Since the environment is hosted by a third-party, I have no access to the boxes or perfmon data and don’t have an opportunity to test various configurations. Changes are quite complex to get approved and the reasoning behind any changes needs to be well supported and will be difficult to reverse if incorrect.
Thanks,
Harley
June 28, 2007 at 2:57 am
Ask them for their detailed reasoning for removing the /3GB switch. If they have trace information or specific incidents that show the /3GB was a problem, you have your answer. If they removed it for not much more than they heard it was a good idea, you should have some leverage for getting the decision reviewed.
With 16 GB you are on the limit of what is safe with using /3GB. If you do get the switch added to your server, you should give an assessment of the risks and benefits to your management for their approval, and get agreement from the hosting company to remove the /3GB quickly if it is implicated in a server crash.
Finally, it is likely that the available memory to Windows will be about 200 MB less than what is on the box, as most servers now come with remote monitoring that takes this memory before the OS gets its share, so your problem is likely to be is /3GB safe when using 15.8 or 15.9 GB memory...
Of course, if you moved to SQL 2005 64-bit you would get better performance than is possible on a 16 GB 32-bit box.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 28, 2007 at 3:39 am
What books online says is true. Yu should not use /3GB switch with 16GB RAM. You should use /PAE switch in boot.ini file and specify max and min server memory in SQL Server configuration.Read more about this in link given below.
http://www.sql-server-performance.com/awe_memory.asp
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 28, 2007 at 4:24 am
the above article actually says the opposite:
The /3GB switch is used to tell SQL Server to take advantage of 3GB out of the base 4GB of RAM that Windows 2000 supports natively. If you don't specify this option, then SQL Server will only take advantage of 2GB of the first 4GB of RAM in the server, essentially wasting 1GB of RAM.
AWE memory technology is used only for the RAM that exceeds the base 4GB of RAM, that's why the /3GB switch is needed to use as much of the RAM in your server as possible. If your server has 16GB or less of RAM, then using the /3GB switch is important. But if your server has more than 16GB of RAM, then you must not use the /3GB switch. The reason for this is because the 1GB of additional RAM provided by adding the /3GB switch is needed by the operating system in order to take advantage of all of the extra AWE memory. In other words, the operating system needs 2GB of RAM itself to mange the AWE memory if your server has more than 16GB of RAM. If 16GB or less of RAM is in a server, then the operating system only needs 1GB of RAM, allowing the other 1GB of RAM for use by SQL Server.
so id your server has 16GB of memory you need the 3GB switch.........
---------------------------------------------------------------------
June 28, 2007 at 9:53 am
Thanks for the responses - the change was made without any supporting metrics - it was based on a blanket statement by an admin that 2003 in a clustered environment will be less stable.
And George, I agree with your read - BOL seems to indicate that /3GB is an option with 16GB or less and should not be used with more than 16GB.
Does anyone have experience with a similar configuration? were any tests done comparing performance with and without the switch? if the switch was used, were there any stability issues? any thoughts on the ideal min/max mem configurations? A referenced article would be ideal as support for initiating a test......
Thanks,
Harley
June 28, 2007 at 5:21 pm
Does the server need more memory? Have you ran perfomance counters (Target Server Memory)?
Currently you do not have the /3GB switch and have 16GB in the box. So, 2GB is given to the OS and 2GB to SQL out of the base 4GB leaving 12GB(PAE) more for SQL for a total of 14GB for SQL. Yet you have max memory set to 13GB.
If you are looking for an extra GB of memory, change the min and max memory settings to 14GB.
June 29, 2007 at 11:35 am
Edogg,
Thanks for the response - actually, the service company recommended lowering the SQL max to 12GB to leave 4 for the OS and other apps noted above - again, I'm not aware of the reasoning used.....
Harley
June 30, 2007 at 1:04 am
I'm runnning a 3-node SQL 2k Ent SP4 cluster, 4 procs (dual-core) for a total of 8 cores per node and 32GB RAM per node. 1 instance per node.
I've previously run single-node and clustered rigs that ranged from 1GB of RAM to 64GB. With SQL from 7.0 to 2005.
At present, I don't run /3GB. Only /PAE. As others have noted, /3GB controls mem allocation below the 4GB boundary. /PAE determines if Windows is allowed to use memory above the 4GB boundary (Of course I'm simplifying here--you should read the Intel x86 processor architecture manual to get the full scoop on what modern computers and operating systems do with 36 address lines--and if you want to bore yourself to death 😉
If you don't use /3GB, you give 2GB of mem to SQL, and 2GB to Windows (this only refers to memory below the 4GB boundary). SQL uses its 2GB to cache important runtime data structures. Windows uses its 2GB to manage its internal processes and window data into and out of the memory above 4GB. If you're not running other apps besides SQL on your machine, this is generally unbalanced--too little to SQL, too much to Windows.
If you use /3GB, you give 3GB to SQL, and 1GB to Windows (again, this only refers to memory below the 4GB boundary.) If a given database executes many SP's and runs a number of complex queries, SQL can take advantage of the additional 1GB of memory that the /3GB switch offers. If you run multiple instances of SQL on a single machine, this becomes even more true, as each instance needs to cache instance-specific data structures below 4GB.
Does Windows suffer when you use /3GB and leave only 1GB of memory below the 4GB boundary for the operating system? Yes. Is it really noticeable? Not if you are only running SQL on the computer. In your case, you're running an anti-virus (is this really necessary for a database server? get rid of it if you can), the Veritas block-level replication engine, Quest sys/app performance probes, and AuditDB. That's not small potatoes. All of these apps need memory from Windows. I wouldn't run with the /3GB option in your case.
As others have pointed out, the minute you grow your memory past the 16GB boundary, Windows needs enough memory to manage the AWE pool that it refuses to shrink its use of the sub-4GB memory to below 2GB.
Please take my free advice for what it's worth--not much more than the paper it's printed on 😉
As others have duly noted, your mileage may (will) vary. The differences in processor family (AMD, Intel), cores (single, dual, quad), systems (HP, IBM, Dell), disk controllers (SCSI, SAS, FC), other hardware (ILO, DRAC), and drivers can make untangling all of the issues outside of SQL server a real challenge. Then you have to profile your application and its use of SQL--that's a matter of a whole bookshelf's worth of advice (and I don't mean BOL 😉
Redd
June 30, 2007 at 8:48 am
Redd has a great explanation and I'll just add my little bit to it.
The memory above 4GB is "swapped" or "mapped" (not sure which) down into the OS section of the 4GB (2GB or 1GB) and that is a part of what the OS needs. The OS can manage this in 1GB with <12GB extra. That's the 16GB. Once you move above 16GB (or 12GB mapped), then more memory is needed and the 1GB shouldn't be allocated elsewhere.
You do want to do some testing and be sure SQL is using that memory. The memory in AWE isn't used for some things SQL does. I believe it is only used for data cache, but I'm not sure. So if you have small queries, smaller data sets, and lots of caching for stored procs and other internal SQL structures, the /3GB makes a huge difference. If you have large data sets, then it might not matter as much.
July 2, 2007 at 5:01 pm
Steve and Redd,
Thanks for the well thought-out responses. Given the supporting apps running on the server, and the lack of an ability to do meaningful comparisons of various configs, it sounds like we should stick with the current configuaration.
Regards,
Harley
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply