SQL Server Performance Forum – Threads Archive

AWE

Hi , I have a server installed with 1. Win 2003 R2 32 bit
2. Sql Server 2005 32 bit
3. 4 Processor 2800 Mhz
4. 8 GB of RAM. 1. What is the setting I should go for it base on these spec ?
2. This server is use for Datawarehouse purpose. ETL will run on mid night and
user need to query the DB on office hour Knowing the Sql Server 2005 have AWE setting which allow to play around with memory. If I set min memory = 1 and max memory = 6 and enable AWE. Do this means it reserve 6 GB of memory to Sql Server and 2 GB for windows ? Thanks ,

---

In order to enable memory for other application and also in order to use AWE, the Lock Pages in Memory privilege must be enabled for the account that runs SQL Server. On 32-bit editions of Windows, applications have 4 gb of virtual address space available. The virtual address space is divided so that 2-GB is available to the application and the other 2-GB is available only to the system. Configuring max server memory alone does not make SQL Server allocate all the memory at startup—not under SQL Server 2005. But, depending on the real load, the configured amount can be consumed very quickly. Using SQL Server 2005 under Windows Server 2003 (editions that support AWE), you get dynamic memory management even if you switch on AWE support. On almost all other configurations, SQL Server switches dynamic memory management off as soon as you switch AWE on. Following links shoudl give more on the subject:
http://www.sql-server-performance.com/sql_server_configuration_settings.asp
http://msdn2.microsoft.com/en-us/library/ms190673.aspx
http://msdn2.microsoft.com/en-us/library/ms190731.aspx
http://blogs.msdn.com/slavao/archiv…ted-reserved-in-dbcc-memorystatus-output.aspx
http://blogs.msdn.com/slavao/archive/category/9005.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

for DW apps,
you really want full 64-bit, OS & SQL
since you are SQL 2005, there is no impediment
start with W2K3 64-bit & 32-bit SQL if you are conservative (chicken)
then install 64-bit SQL side-by-side to convince yourself it is ok there have been some 64-bit issues, most of which have been corrected the reason you want 64-bit for DW is that large queries aggregating values will generate hash and sort operations, for which you want a large VAS,
the 32-bit AWE trick is only useful for OLTP app

---

Hi Satya , 1. Means that if I did not enable AWE , the server will only use up 4GB of RAM
although the server have 8 GB 2. From the article , we should add the "Lock pages in memory" before enable AWE which
prevent the memory to write into virtual memory. 3. My intention is just to make sure Sql Server 2005 able to fully utilize all the
memory on the server. Please advised. Thanks ,
Travis

---

True, ensure to add that option before enabling the AWE and reboot may be required.
You can easily manage the DW with the above specified hardware, provided you have a predicted growth for next 5 years and be assured to handle the sizes with this hardware. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

What about the /3GB switch? This would allow SQL Server to use a 3GB address space and additional memory above the 4GB bar for data buffers when AWE is enabled. I agree on the 64 bit recommendation.

---

If you are using SQL Server 2005 to configure memory to use more than 2 GB of physical, see the following topics in SQL Server 2005 Books Online:• Memory Architecture
• Server Memory Options
• Using AWE
• Enabling Memory Support for Over 4 Gb of Physical Memory
• Enabling AWE Memory for SQL Server
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Hi, If the OS is windows 2003 standard, even with the AWE enabled and even with the max server memory set to 8 GB, SQL 2k5 can only use up to 4 Gbytes, correct? I am still not sure when to use /3GB switch. Our server has 6 Gbytes of memory and again we are using W2k3 standard with SQL 2k5 sp2. It is a dedicated SQL server with no other applications running. If I want OS use 1 Gb and SQL use 5 GB, is it doable under W2k3 standard?

---

Yes as per KBAhttp://support.microsoft.com/kb/889654 Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

I got some idea from that link. I assume your ‘yes’ is for my first question. Just in case, please give me an answer on each question below. 1. Even our server has 6 Gb of RAM, the W2k3 standard can only see 4 GB so no matter if I turn on /PAE and /3GB and enable AWE in SQL 2k5, SQL 2K5 can only use 3 GB, right? 2. With 6 Gbyte of RAM, W2k3 and SQL 2k5, what switches do I really need to use the most RAM that SQL 2k5 can utilize?

---

]]>