March 13, 2012 at 3:11 pm
Currently, without /3GB, AWE, or LPIM enabled, the query returns this:
TotalGB,VASGB,Buffer Committed,Buffer Target
4.00,2.00,1.56,1.56
Page life is averaging under one minute so I'm hoping these changes will help.
March 13, 2012 at 3:38 pm
PHXHoward (3/13/2012)
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/11/awe-and-3gb-an-empirical-picture.aspx
What an excellent article. Thank you for sharing!
The configuration I recently implemented (AWE=Yes,3GB=Yes) apparently enables SQL Server to access all 4GB of the physical memory. At the same time as implementing the changes I also set max memory to 2600 MB to prevent the OS from ever being starved (based on the other stuff running on the server) but it's good to have confirmation that I didn't go off the grid with my changes. As a side note, here are the query results from my system:
TotalGB,VASGB,Buffer Committed,Buffer Target
4.00,3.00,2.48,2.54
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 13, 2012 at 3:41 pm
PHXHoward (3/13/2012)
Thanks for all the great information.The Microsoft rep that we have on site this week confirmed that enabling AWE and LPIM on the 4gb RAM 32bit system would allow SQL Server to use the extra gb for pages. He said it was like the boot.ini /3gb but without having to modify the boot.ini
MS reps can be wrong 🙂
I'm happy to be wrong as well but I'm not convinced yet. If the above is the case why not just set the /3GB switch?
I would very much like to see the results of that query as well, but let us know the exact configuration you use.
The two articles you post don't prove anything about 4GB, Linchis test were on a 16GB machine and the second blog is not explicit enough.
---------------------------------------------------------------------
March 13, 2012 at 6:19 pm
I'll try it with just AWE and see what the VAS space indicates. If it isn't as I hope, I'll enable /3gb and reboot.
Was hoping that the AWE setting in SQL Server would allow the change with only a service restart instead of a server reboot. Also hoping that setting AWE on for SQL Server would more dynamiclly manage the ram instead restricting OS to only 1gb. We'll see.
This article from Microsoft seems to imply that enabling AWE will "allow applications to address more memory than the 2-3 GB that is available through standard 32-bit addressing." The article, like everything else, isn't entirely clear.
March 14, 2012 at 9:21 am
that article is saying on a 32bit system AWE will allow you to address memory above 4GB for the buffer pool. Otherwise you are limited to either 2GB, or 3GB with the .boot.ini change. In your scenario just setting AWE will do nothing.
I'd go for the /3GB switch first. In terms of the outage, restarting SQL or rebooting the server has the same impact.
You could still set SQL max memory to something between 2 - 3GB to leave the OS more, and start setting LPIM if you find SQL memory is being given up to the OS.
---------------------------------------------------------------------
March 14, 2012 at 9:31 am
george sibbald (3/14/2012)
that article is saying on a 32bit system AWE will allow you to address memory above 4GB for the buffer pool. Otherwise you are limited to either 2GB, or 3GB with the .boot.ini change. In your scenario just setting AWE will do nothing.
If I am reading the graph correctly the config "AWE=Yes,3GB=No" (exactly overlays config for "AWE=Yes,3GB=Yes") allows SQL Server to access up to 4GB memory. It does not mention PAE. I would like to know if that was enabled or not throughout the tests.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2012 at 3:19 pm
This is what I have found on a test server. Windows Server 2003 R2, x86, 4gb ram, SQL Server 2005
The first three columns are clear. The last column is interesting. The question I guess is if the buffer pool can surpass the VAS space to reach it's 3gb+ target when AWE is on but /3GB is off.
physical mem, VAS, buffer pool committed, buffer pool target
AWE off, 3GB off
4.00,2.00,0.02,1.56
AWE off, 3GB on
4.00,3.00,0.02,2.56
AWE on, 3GB off
4.00,2.00,0.02,3.14
AWE on, 3GB on
4.00,3.00,0.02,3.19
March 14, 2012 at 4:01 pm
What were the min/max_mem settings when taking the readings?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2012 at 4:03 pm
Still using the defaults. Min 0 Max 2147483647
March 14, 2012 at 4:12 pm
According to this, http://msdn.microsoft.com/en-us/library/windows/desktop/aa366778(v=vs.85).aspx#physical_memory_limits_windows_server_2003_r2, Windows Server 2003 R2 on 32 bit servers only supports 4GB ram.
SQL Server 2005 SE X32 supports what the OS supports, http://msdn.microsoft.com/en-us/library/ms143685(v=sql.90).aspx.
Not sure if AWE is going to help here. The /3GB switch should.
March 14, 2012 at 4:18 pm
Lynn Pettis (3/14/2012)
Not sure if AWE is going to help here. The /3GB switch should.
No evidence of helping or hurting in terms of making more memory avaialble. But it gets interesting when you throw LPIM into the mix. There is wording in BOL saying having LPIM enabled and AWE disabled on in SQL Server in 32-bit systems can lead to "poor system performance".
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2012 at 4:45 pm
The MS technician that is onsite this week gave me some clarification. After looking at my findings, he told me that AWE reaches into the OS reserved area which explains why the target buffer pool can be 3.19GB when VAS is still limited to 2GB.
I asked him if it was dangerous to give SQL Server a target buffer pool of 3.19GB when there is only 4GB total memory. I was told that as long as the Free System Page Table Entries in perfmon are averaging above 7000 then it should be ok. Even so, his recommendation to be safe was to disable AWE and remove LPIM and instead enable /3GB in the boot.ini in order to boost the buffer pool target from 1.5GB to 2.5GB.
Seems a good plan which also seems to be the consensus on this forum.
Thanks
March 14, 2012 at 4:52 pm
It does sound like a good plan. Without LPIM in place I suspect you will not be protected from the memory trims Windows imposes on SQL Server at times...one of my server's initial symptoms.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2012 at 6:30 am
Lynn Pettis (3/14/2012)
Windows Server 2003 R2 on 32 bit servers only supports 4GB ram.
Standard edition yes, Enterprise and above have a much higher memory limit
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 26, 2012 at 7:22 am
opc.three (3/14/2012)
Lynn Pettis (3/14/2012)
Not sure if AWE is going to help here. The /3GB switch should.No evidence of helping or hurting in terms of making more memory avaialble. But it gets interesting when you throw LPIM into the mix. There is wording in BOL saying having LPIM enabled and AWE disabled on in SQL Server in 32-bit systems can lead to "poor system performance".
We have evidence now.
http://www.sqlservercentral.com/Forums/FindPost1272135.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply