March 28, 2004 at 11:04 pm
Hi,
I'm hoping that somebody can shed some light on the following issues. I'm somewhat confused regarding our SQL Server Memory settings. I've been through the archives and there is some reference to AWE etc but non seem to fit my situation.
We have 2 identical servers (well almost!). One for production and one for Disaster Recovery. Both servers have SQL Server 2000 Enterprise Edition (SP3) running on Windows 2000, 4 processors each, Total memory 4GB each.
What I'm finding is that the Production Server memory is usually running at 1.42GB. This seems to be some sort of max that we can't increase. The configuration settings are Min server memory = 0 and the Max server memory = default (2174483647).
The DR Server has been able to run using about 2.8GB of memory. Its Min and Max settings are the same as production.
The only differences I have found that can maybe explain this is that the DR Server has AWE configured but it does not have the /3GB switch set in the Boot.ini file. I was under the impression that without the /3GB switch the AWE setting is ignored. Is it possible to have the AWE configured without the 3GB switch?
I would appreciate any advice / suggestions on this subject. Thanking you in advance.
John
March 29, 2004 at 3:37 am
Yes you can have AWE enabled without the /3GB switch. The AWE setting is ignored (assumed OFF) if there's less than 3gb physical memory.
However, with your 4gb and AWE enabled it's surprising that DR SQL, given that it should NOT be dynamically managing memory, is not using closer to the full 4gb.
If you are monitoring the memory through Task Manager, I beleive there are problems with it displaying true memory usage for high numbers.
Cheers,
- Mark
March 29, 2004 at 5:42 am
AWE isn't the issue here. With 4 GB of memory the address space is split 50/50 between user mode address space (first 2gb) and kernel mode address space (last 2gb). With this in mind and considering any other services or applications on the box SQL is limited to using 2GB max. When you enable the /3GB option in the boot.ini it effectively tells the system user modes address space is the first 3GB of memory and kernel gets packed into the last 1GB. That is why the one box is over the 2 and the production is under.
March 29, 2004 at 5:19 pm
Thank you for your replies however I'm still unclear as to what is required.
My findings (and Mark's comments) would suggest that you can have AWE enabled without the /3GB switch being set in the Boot.ini file. I have AWE enabled on the DR server, I do not have the /3GB switch set and yet I'm seeing memory usage well over the 2GB.
If I'm reading Antares686 comments correctly this should not be happening without the /3GB switch being set.
My investigation into AWE and /3GB did reveal some conflicting findings and this posting is confirming that. Does anybody have a definitive answer on this subject? I really would appreciate one.
regards
John
March 29, 2004 at 6:01 pm
On a 4GB machine the only way to use more than 2GB of RAM for applications is to have the /3GB switch in place as the Kernel is going to take 2GB automatically. If you are seeing above this then I have 2 questions. Which memory couter are you looking at and when was the last time the DR box was restarted, and are you sure it didn't at one time have the /3GB switch in the boot.ini and just been removed?
AWE can be enabled without the /3GB switch, but AWE and the /3GB switch are not related. As for porduction there has to be an explination of why it is not going above the level you see. Check Task Manager and see what items are using the memory and which are using the most. Also, mak sure you have all unneccessary services stopped and set either to manual or disabled.
Here is an article by Ken Henderson (author of The Guru's Guuide to SQL Server Architecture and Internals) http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp that should help you understand a bit better.
March 29, 2004 at 6:52 pm
There is a good possibility that the /3GB switch has been used at some stage. Last year we had a Microsoft consultant looking at some performance issues that we had. AWE and /3GB were discussed. I wasn't part of that review but I do remember it being mentioned. If the /3GB switch gets set and then removed is it still active? This would certainly explain what I'm seeing. The DR machine was restarted on the weekend. I've been using task manager and the following SQL for memory sizes :
select cntr_value, cntr_type from sysperfinfo
where
object_name = 'SQLServer:Memory Manager' and
counter_name = 'Total Server Memory (KB)'
Thank you for pointing out that AWE and /3GB are not related. I've been under the impression that they had to go together.
Thank you for the link. I will review the info.
Regards
John
March 29, 2004 at 7:25 pm
Here is another reference, which I find a little easier to understand.
http://www.sql-server-performance.com/awe_memory.asp
However, between the two articles (one by Ken Henderson on the MS site & my above reference), a more complete picture comes into view.
GaryA
March 29, 2004 at 8:22 pm
Hmmm. Interesting. After reading both articles I guess I have to accept that I can't use AWE or /3GB.
According to the sql-server-performance article I shouldn't use AWE:
"If you are using SQL Server 2000 Standard Edition under Windows NT 4.0 or Windows 2000 (any version), or are running SQL Server 2000 Enterprise Edition under Windows NT 4.0 or Windows 2000 Server, or if your server has 4GB or less of RAM, the "awe enabled" option should always be left to the default value of 0, which means that AWE memory is not being used."
and I can't use the /3GB because of the warning in the Ken Henderson article regarding the /3GB and Windows 2000 server.
So unless somebody can tell me that they have SQL Server 2000 Enterprise Edition running under Windows 2000 Server with 4GB of memory and that they have been able to achieve some performance gains by using either AWE or /3GB I guess I can't use these features.
Thank you for your time.
John
March 30, 2004 at 2:09 am
You need sql2k ent + w2k adv server to make use of extra memory, without them your extra ram in your server is unused.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 30, 2004 at 2:35 am
This still doesn't explain why my DR Server that has AWE enabled can get close to 3GB of memory being used. When I run this SQL on the DR server I get a figure showing 3061336. I'm assuming that this means 3GB. Is this figure wrong?
select cntr_value, cntr_type from sysperfinfo
where
object_name = 'SQLServer:Memory Manager' and
counter_name = 'Total Server Memory (KB)'
March 30, 2004 at 3:08 am
I have to say I can't be sure without a hands-on, however the counter AWE Lookup Maps/Sec will indicate what is being used.
There may be other processes using memory, I admit that the counter you mention is the one I look at, . Is your o/s adv server perhaps? As far as I know awe doesn't kick in until there is more thna 4 Gb ram.
Looking at my servers the awe counters are only in sysperfinfo if awe is enabled ( in my environments ) have a check and see.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 30, 2004 at 5:23 am
The /3gb switch will still be active if removed and the box hasn't been rebooted.
April 1, 2004 at 7:58 am
If you do not have W2k Enterprise and sql is set to utilize more than 2GB of memory, the SQL server will create a page file of anything over 2GB. This can cause a huge performance hit. We have had customers add memory...more is always better, right?... and end up with a REALLY slow SQL box.
If you are going to use the /3GB switch, you also need to include the /PAE switch after it in the boot.ini. Otherwise AWE will not work correctly. Kind of an undocumented "feature" that will cause lots of headaches until the right 'guru' at Microsoft tells you.
April 1, 2004 at 9:25 am
note that awe uses memory above 4 Gb and pae is not required until you have more than 4 Gb ram. There's an excellent article on the site ( sorry can't remember the url ) on switches and memory usage.
You can also read all about how it works in "The Guru's Guide to SQL Server ...." by Ken Henderson.
note also you do not need the 3Gb switch with the PAE switch.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 1, 2004 at 9:50 am
According to Microsoft Premiere SQL support, you do need both. We recently went thru all of this at a very large install. These were Microsoft guys from Redmond who came onsite to figure out the performance issues. While the documentation does not specify this, they said it is necessary to have both switches...even if you only have 4GB. This is for SQL, not the OS. Don't know why, but it corrected the issues.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply