AWE and PAE both should be enable on Windows 2008 R2 32 bit?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » AWE and PAE both should be enable on Windows...

AWE and PAE both should be enable on Windows 2008 R2 32 bit?

Rate Topic

Display Mode

Topic Options

Author

Message

ananda.murugesan

Posted Monday, October 01, 2012 12:58 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:17 AM
Points: 829, Visits: 2,423

Hi

Operating system - Windows 2008 Enterprise Edition 32 bit.
SQL SERVER - MS SQLSERVER 2008 R2 32 bit.
Physical Memory - 8GB

whenever enabling AWE option for above setup production box. should be followed to enable PAE also?

Today i have faced issues for Full & log backup failed due to Error Ms 701, Level 17, There is insufficient system memory in resource pool 'default' to run this query.

Those backup taken after restart the SQL Services for timely resolved this issues.

how to fix it? should I enable AWE option?

Thanks

Post #1366332

EdVassie

Posted Tuesday, October 02, 2012 4:29 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:46 AM
Points: 2,636, Visits: 2,788

The /PAE boot switch in Windows enables the code you need. If you have hardware data protection turned on this also enables the PAE functionality so you do not need both switches but there is no harm if you do.

The Lock Pages in Memory right allows the SQL Server service account to make full use of the windows memory extention features. You need to give the service account this right. If you are running SQL Server Standard Edition you will also need trace flag 845

The AWE option in SQL Server allows SQL Server to use memory extention features.

This will allow you to use memory above the 4GB line, but doing this will cause Windows to need a little more memory. What happens in the background is that Windows creates a map for each 4KB page. The more memory you have the larger the map will be - it takes about 250MB for every 4GB memory above the 4GB line.

All the memory above the 4GB line is read-only. When you want to update something, SQL Server gets Windows to copy the 4KB page to a reserved space below the 4GB line. The page is then updated and copied back to its original location. This all sounds slow and complex and it is, but the whole process is much faster than getting data from disk.

These items are needed for 32-bit SQL Server editions prior to SQL 2012 when they run on 32-bit operating system. If you run on a 64-bit operating system the memory extention features are not available in Windows so you are limited to a maximum of 4GB memory within SQL Server. SQL 2012 32-bit also does not use windows memory extention, so you are limited to 4GB on any 32-bit SQL 2012 instance.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 4 June 2013: now over 24,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.

Post #1366940

TheSQLGuru

Posted Tuesday, October 02, 2012 10:46 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:22 PM
Points: 3,678, Visits: 5,177

I STRONGLY recommend that you get a professional to give your system a performance review/health check. I am certain there will be quite a few findings that could be improved, and you can be mentored on what to watch out for and how to effectively monitor/tune the system going forward during the engagement. A good consultant can work wonders in just a few hours/days.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail

Post #1367171

« Prev Topic | Next Topic »

Permissions