When to use AWE in SQL 2005

Question

When to use AWE in SQL 2005

Dana Bibeault

Hall of Fame

Points: 3670
More actions
July 29, 2010 at 10:07 am

#157113

We have a new server installation. Win Enterprise x64, SQL 2005 std ed. x64. SP3.
NO Cluster:
Do we need to enable AWE in this 64 bit environment?
We will be adjusting SQL memory setting to accommodate vendor requirements.
Minimum server memory will stay at 0
Maximum server memory will be set at 10240
MAXDOP will be set to 8. (have 16 processors)
Enable "lock pages in memory" (Windows) And make sure the SQL account has privileges.
Is there anything we need to know before we do this? Can we do this all at the same time or will multiple re-boots be needed.
thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

AWE is a mechanism for 32-bit applications to access more than 2GB/4GB¹ memory. It is not needed by a 64-bit application. SQL x64 will ignore the AWE setting.

(1) 2GB on 32-bit windows, 4GB on 64-bit windows

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 2

Daniel Bowlin

SSC-Dedicated

Points: 34566

July 29, 2010 at 10:39 am

#1199925

Sounds like a QoTD.

Dana Bibeault Hall of Fame Points: 3670 More actions · Answer 3

Thanks, for the quick reply.

Can all the setting be done at the same time and it looks like setting the maximum server memory or MAXDOP do not need a restart. Did I read this correctly on MS site.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

Hi Dana,

Configure all those at once once and reboot the server. No multiple reboots required.

AWE is not required on x64 as Gail mentioned.

Just remember that MAXDOP setting will result in number of CXPacket waits which may result in blocking and 100% CPU time for the parallell queries. Also MAXDOP is not recommended in OLTP systems.

You can even use MAXDOP hint in specific queries to make use of parallelism at the individual query level.

Thank You,

Best Regards,

SQLBuddy

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sqlbuddy123 (7/29/2010)
Also MAXDOP is not recommended in OLTP systems.

You recommend leaving MAXDOP at 0 for OLTP?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dana Bibeault Hall of Fame Points: 3670 More actions · Answer 6

Great, thanks for the insight.

Thank you everyone for all the input. Much appreciated.

Dana