AWE Problems on SQL Cluster
-
We just topped up the memory in our SQL Cluster to 8 GB. I fired up perfmon to make sure that the instances were taking advantage of the extra RAM and found that the Memory Manager Total Server Memory is way lower than the Target Server Memory. According to the Total Server Memory counter each instances is taking up an average of 120 MB! These are boxes are dedicated to SQL server. I can't see why the instances can't grab more memory than they are. Any input would be much appreciated.
I have the following setup:
SQL 2000
Windows 2003
2 Node 4 Instance Active/Passive Cluster
8 GB RAM on each node
/PAE and /3GB in boot.ini and OS recognizes full 8 GB
AWE enabled on each instance
Each Instance configured with max server memory = 1536 MB
-
A couple of questions to work with:
Does the account that SQL Server is running as have the lock pages in memory policy?
Did you have AWE enabled (and working) prior to the memory upgrade?
Have you tried starting only one instance, with a larger amount of target memory (or even no target level set, meaning it will try and grab all memory except ~128MB)?
Now, my theory, even though I have never actually used AWE with multiple instances, is that the low setting for target memory is making SQL Server start in dynamic memory mode with AWE not enabled. After all, AWE is used for accessing memory above 4GB. Have you had a look in the SQL Server error log? What does it say when SQL Server is started? It should say "Address Windowing Extension enabled".
-
- The lock pages in memory is very importand (check this for the domain account SQL Server is running under)
- The min en max memory setting is needed (without this, and multiple instances, is asking for problems)
- The AWE enabled setting must be on (check log of SQL Server instance, it is mentioned during startup)
What I dit see on my cluster, is that Taskmanager showed only about 128Mb for each instance, but it was using the extra memory because AWE access the memory direct! (check point 1 in this list). The used memory was 2,25Gb for the instance. When I did take all other instances off line and on line, the total memory in use went up and down with it.
I am still confused about the need of the /3Gb switch... Is it really needed on servers with >4Gb and <16Gb with multiple instances... Anyone? (please do not come up with links where it states to do so, but please come up with links why
) At this moment I have a server cluster with 12Gb and some instances use more than 2Gb without the /3Gb switch.I see that you have a max memory setting of 1536Mb. Then you do not need the AWE enabled setting I think. On my cluster the errorlog file did not show the AWE enabled line on instances that use less than 2Gb of memory as max setting.... Confusing this all...
Also check out this topic: http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=92&messageid=172046#bm173212
-
I think i have the same memory issue..
When i look at my memory of the sql server instance in Taskmanager, it says 64MB used. This never changes.
We have an active active cluster with 9GB memory in both on Windows 2003. I can see that my Total en target service are OK at 4.3GB, my awe in the log says awe enabled.
But my lock pages in memory says 480 and it stays that way...
I have checked the policy and that is ok as well? Can someone give me a hint ?
Maybe i switch off awe and set to 4GB, but i rather not because i want to have the possibility to give one instance more than the other?
-
I was told, that normally on a server with 4 GB RAM Windows uses the upper 2 GB, and userspace is in the lower 2 GB. If you add more RAM the memory would be fragmented, because userspace would reside in the lower 2 GB and in the RAM above 4 GB, with Windows sitting in between.
The /3GB option now tells Windows to use the lower 1 GB, with userspace being the 3 GB above. First you get 1 GB more of userspace, and second with more RAM the userspace is contiguous...
One disadvantage might be that some applications don't run in that mode, but on a dedicated sql server that schould not be an big issue...
A good article about AWE is on SQL-Server-performance.com :
AWE Memory SQL Server Performance Tuning Tips
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.
The AWE (Advanced Windowing Extensions) API allows applications (that are written to use the AWE API) to run under Windows 2000 Advanced Server or Windows 2000 Datacenter Server to access more than 4GB of RAM. SQL Server 2000 Enterprise Edition (not SQL Server 2000 Standard Edition) is AWE-enabled and can take advantage of RAM in a server over 4GB. If the operating system is Windows 2000 Advanced Server, SQL Server 2000 Enterprise Edition can us up to 8GB of RAM. If the operating system is Windows 2000 Datacenter Server, SQL Server 2000 Enterprise can use up to 64GB of RAM.
By default, if a physical server has more than 4GB of RAM, Windows 2000 (Advanced and Datacenter), along with SQL Server 2000 Enterprise Edition, cannot access any RAM greater than 4GB. In order for the operating system and SQL Server 2000 Enterprise Edition to take advantage of the additional RAM, two steps must be completed.
Exactly how you configure AWE memory support depends on how much RAM your server has. To configure Windows 2000 (Advanced or Datacenter), you must enter one of the following switches in the boot line of the boot.ini file, and reboot the server:
4GB RAM: /3GB (AWE support is not used)
8GB RAM: /3GB /PAE
16GB RAM: /3GB /PAE
16GB + RAM: /PAE
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.
Once this step is done, the next step is to set the "awe enabled" option to 1 within SQL Server Enterprise Edition, and then restart the SQL Server service. Only at this point will SQL Server be able to use the additional RAM in the server.
One caution about using the "awe enabled" setting is that after turning it on, SQL Server no longer dynamically manages memory. Instead, it takes all of the available RAM (except about 128MB which is left for the operating system). If you want to prevent SQL Server from taking all of the RAM, you must set the "max server memory" option (described in more detail later in this article) to a figure that limits SQL Server to the amount or RAM you specify. (7.0, 2000) Updated 1-2-2004
*****
regards karl
Best regards
karl -
You were correct Chris. The problem seems to be related to the 1536 Max Server Memory Setting. It seems that that magic number is 3 GB. When I have the max server memory at anything below 3 GB the "AWE Enabled" message does not show up in the error log. When I set it to 3 or above "AWE Enabled" shows up and Target and Total memory are the same.
So, now my problem is that I have 8 GB that I need to use on a 4 instance Active/Passive cluster. Obviously I can't split that memory equally among the 4 instances so I was hoping I could do the following.
-Leave AWE enabled on 1 instance setting "Max Server Memory" = 3 GB
-Disable AWE on the remaining 3 instances and set SQL to dynamically manage memory.
Any thoughts on this? Will it work?
Thanks to everyone for your comments.
Noel
-
awe is a server setting not a database setting.
You should set /PAE and this will allow SQL Server to use the memory.
The 3Gb switch affects certain pools and it is a matter of choice as to if you set this or not. I've found if you manipulate xml it's not a good idea to use the 3gb switch.
Each instance should have it's memory set to 1/4 of the usable memory - about 1.6Gb for each - or cut which ever way you like but the total memory used by all the instances should not exceed around 6.5Gb To make an instance use more than 1.6gb of ram you'll have to enable awe for that instance
Use sysperfinfo table to view the actual memory each instance uses. If you set one instance to use 3Gb of ram the other 3 should be set to use 1.1 Gb each ( say ) You must leave memory for the o/s, at least 1 Gb.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply