Hi All, Hi have read in some articles regarding AWE funtion on SQLSERVER but not clear could you please clarify below queries regarding AWE? 1.How much(Max) memory can be assinged to SQL SERVER x32 if we are not using AWE? 2.Why we are don't need AWE in SQLSERVER x64?
32-bit SQL Server is limited to 2 GB by default. [AWE](
http://msdn.microsoft.com/en-us/library/ms190673.aspx) was server black magic so you could get more than 3 GB of RAM working with SQL Server on a 32-bit installation. A 64-bit application running on a 64-bit version of Windows is capable of addressing a lot more memory, so AWE is no longer necessary once you move to a 64-bit platform.
The limit of addressable memory on a 32 Bit system is ~2GB (max value that can be stored in 32 bits). AWE is a method of allowing a 32 Bit application to access above this 2GB limit. Depending on windows and sql server version and edition you get different limits. 64 Bit operating systems can register up to the maximum of 64 Bits. This is something stupidly high, that is not going to be reached any time in the near future (I'm not going to say never, you end up being wrong anyway!). As 64 Bit can easily register even the highest physical memory limits of current Windows/SQL Server editions you don't need the memory addressing extensions. You can turn it on on a 64 Bit machine, but it will have no effect. If you read the link in @Kevin Feasel's post, it states that the AWE functionality is a deprecated feature and should not be used for future development. Windows will be moving to a 64 Bit only evironment at some point in the future and has had native 64 Bit support/versions since Vista / Windows Server 2003. Intel/AMD servers nowadays are pretty much all x64 compatible (if not all), so there is no worries on that side. If you want to setup a new box today, I would only ever go for 64 Bit. There is no real need to go for 32 Bit anymore.
AWE on a 32 Bit system uses a lookup table of sorts. It basically houses a series of pointers in RAM and does translation of the 32 Bit addresses the application can handle with respect to RAM (~2GB) to addresses in the upper values of the O/S (64 GB if 2003/32 Bit). As @Kevin Feasel said... it's a little black magic at work.
To your question number 2: 64bit SQL Server editions indeed do not consider the AWE settings you set by using sp_configure. However, I have come across situations where other processes (damned developers using sqlcmd and all ;-)) take memory away from the SQL Server memory pool. This resulted in SQL Server hanging with "not enough memory in resource pool" kinda errors. To counter this, if it's a problem for you, you should grant your SQL Server service account the right to lock pages in memory. Run gpedit.msc in [Start menu].[Run] and assign this right to your SQL Server service account to allow SQL Server to manage its memory more autonomously.