June 1, 2008 at 6:47 am
Hi,
I've read the article for AWE enabled in SQL Server 2005.
i need you opinion about it.
did you enabled the AWE on your SQL Server 2005?
did you find any improvement in performance or memory usage?
if i understand right the AWE is not support on 64BIT Server only 32BIT.
i'm responsible for more then 50 SQL Server 2005 Standard Edition thats run on 32BIT systems and use Windows Server 2003 32BIT and have 4GB MEM.
should i enable the AWE?. i must say that i enable the /3GB switch in the Boot.ini file to all Server(this switch allow the SQL Server to use 3GB of MEM out of 4GB that i have and leave 1GB mem to OS and other process).
all my Servers are dedicated SQL Server 2005 that runs only one instance.
THX
June 1, 2008 at 2:44 pm
If you only have 4GB of memory installed in your server do not enable AWE. AWE is for memory above 4GB. Leave as is with the /3GB switch enabled.
---------------------------------------------------------------------
June 2, 2008 at 6:50 am
AWE is used for locking memory in a 64bit environment. So if you have al lot of memory, anable AWE and 'lock pages in memory'
If you don't lock your memory, the OS might steal memory from MSSQL, which results in suspending transactions, but I would say a dying server :crazy:.
See:
http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx
http://support.microsoft.com/kb/918483
http://www.mcse.ms/message2444910.html
http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
Wilfred
The best things in life are the simple things
June 2, 2008 at 7:00 am
Wilfred van Dijk (6/2/2008)
AWE is used for locking memory in a 64bit environment. So if you have al lot of memory, anable AWE and 'lock pages in memory'If you don't lock your memory, the OS might steal memory from MSSQL, which results in suspending transactions, but I would say a dying server :crazy:.
AWE is ignored on 64 bit.
Books Online:
Support for AWE is available only in the SQL Server 2005 Enterprise, Standard, and Developer editions and only applies to 32-bit operating systems.Note that the sp_configure awe enabled option is present on 64-bit SQL Server, but it is ignored. It is subject to removal in future releases or service packs of 64-bit SQL Server.
Just set the Lock Pages in Memory permissions to prevent paging out, but make sure that you've set SQL's max memory so that it doesn't starve the OS.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2008 at 7:09 am
Funny,
According to Slave Oks (http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx, very interesting article), he recommends enabling AWE.
Wilfred
The best things in life are the simple things
June 2, 2008 at 7:21 am
Dunno.
Reading over the article it looks like he's mostly taking about the locked pages property than anything else.
See his clarification:
http://blogs.msdn.com/slavao/archive/2006/03/13/550594.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2008 at 7:33 am
Aha! :w00t: thx for the update
Wilfred
The best things in life are the simple things
June 2, 2008 at 7:37 am
avipenina (6/1/2008)
i'm responsible for more then 50 SQL Server 2005 Standard Edition thats run on 32BIT systems and use Windows Server 2003 32BIT and have 4GB MEM.should i enable the AWE?. i must say that i enable the /3GB switch in the Boot.ini file to all Server
If you've got 4GB on 32 bit, leave AWE disabled and just have the /3GB enabled.
AWE's generally for > 4GB memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2008 at 7:58 am
Wilfred van Dijk (6/2/2008)
Funny,According to Slave Oks (http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx, very interesting article), he recommends enabling AWE.
And yet - per BOL - AWE exists but is completely ignored...Who's to be believed? The BOL article is a whole year later than Slava's blog entry.
http://technet.microsoft.com/en-us/library/ms187499.aspx
One way or the other - it sounds to me that the only thing it buys you (if 64-bit even uses it) is not have to lock the memory more than once. Unless you're in one of those scenarios where each and every sub-millisecond counts to your server - you wouldn't even know the difference, would you? That's a minuscule amount of time, isn't it, or am I misunderstanding the effort?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 2, 2008 at 10:35 am
This can be tricky, and I don't think MS has done a great job explaining.
I've tended to look at BOL here (http://msdn.microsoft.com/en-us/library/ms190673.aspx) for guidance.
My understanding was AWE wasn't needed on 46-bit and doesn't make much sense for 4GB or less. It's also very OS dependent.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 2, 2008 at 12:41 pm
Most important don't compare x64 and x32 sql server. The awe tick box remains in x64 but it does absolutely nothing, at all, ever, ever.
x32 - 4gb ram - you can use the 3gb switch or you can use awe - your choice. Which ever way you go there is the chance of causing issues with memory which will manifest themselves as paging, or if you're really unlucky errors.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 2, 2008 at 6:12 pm
colin Leversuch-Roberts (6/2/2008)
Most important don't compare x64 and x32 sql server. The awe tick box remains in x64 but it does absolutely nothing, at all, ever, ever.x32 - 4gb ram - you can use the 3gb switch or you can use awe - your choice. Which ever way you go there is the chance of causing issues with memory which will manifest themselves as paging, or if you're really unlucky errors.
So I take it, AWE and the 3-gb switch should not be used together, it's either or...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 2, 2008 at 10:47 pm
Folks,
I cannot help but add to this thread!
I have configured AWE for a 32 bit SQL Server 2005 Server. The Server had 6GB RAM and I enabled AWE. I then set the MAX Server Memory to 5GB which leaves 1GB to the OS.
What you also have to do, is add the "Lock Pages In memory" for the User Account that Starts the SQL Server Service.
I did however, remove the /3GB switch in the boot.ini file. I had this option in the boot.ini file because the Server originally had 4GB RAM before it was upgraded to 6GB RAM.
I'm not sure if this was the right thing to do, so in light of Mario's question, is it either or?
June 3, 2008 at 12:05 am
It should be an either-or.
As soon as you enable /3GB, you're restricting the kernal to only 1 GB of memory. That's all it gets to run the core of the OS, which includes memory mapping. It needs memory to do the AWE mapping. Te more memory on the box, the more memory the kernal needs to manage that.
/3GB + AWE + 8+ GB memory = problems waiting to happen.
In fact, if you have /3GB enabled, the kernal cannot manage more than 16 GB memory due to the reduced memory it has to work with. (Guru's guide to SQL Architecture and intrnals, chapter 4)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 3, 2008 at 1:24 am
GilaMonster (6/3/2008)
It should be an either-or.As soon as you enable /3GB, you're restricting the kernal to only 1 GB of memory. That's all it gets to run the core of the OS, which includes memory mapping. It needs memory to do the AWE mapping. Te more memory on the box, the more memory the kernal needs to manage that.
/3GB + AWE + 8+ GB memory = problems waiting to happen.
In fact, if you have /3GB enabled, the kernal cannot manage more than 16 GB memory due to the reduced memory it has to work with. (Guru's guide to SQL Architecture and intrnals, chapter 4)
so surely /3Gb switch ok up to 16GB RAM, remove for anything over that.:)
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 64 total)
You must be logged in to reply to this topic. Login to reply