A SQL Server DBA myth a day: (5/30) AWE must be enabled on 64-bit servers

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)

Myth #5: AWE must be enabled on 64-bit servers

FALSE

(This one was suggested by fellow-MVP Jonathan Kehayias (blog|twitter))

There's a *huge* amount of confusion about AWE settings, locked pages, and what works/doesn't work, and what's required/not required on 32-bit and 64-bit servers, and in which editions.

In a nutshell:

  • On 64-bit systems (2005+):
    • AWE is not required (and in fact enabling it does nothing)
    • Turning on the "Locked Pages in Memory" privilege prevents the buffer pool memory (and anything that uses single-page-at-a-time memory allocations) from being paged out
    • When the "Locked Pages in Memory" privilege is set, SQL Server uses the Windows AWE API to do memory allocations as it's a little bit faster
    • "Locked Pages in Memory" is supported by Standard and Enterprise editions (see this blog post for how to enable it in Standard edition)
  • On 32-bit systems (2005+):
    • AWE is required to make use of extended virtual address space
    • The "Locked Pages in Memory" privilege must be enabled before AWE can be enabled
    • AWE is supported by Standard and Enterprise editions

No surprise that it's a bit confusing!

My good friend Bob Ward from CSS wrote a very detailed FAQ blog post that explains all of this – see Fun with Locked Pages, AWE, Task Manager, and the Working Set…

Tune in tomorrow to see what myth is next up for debunking!

7 thoughts on “A SQL Server DBA myth a day: (5/30) AWE must be enabled on 64-bit servers

  1. Heehee :-) Recently we put additional memory into one of the servers (64-bit OS) and SQL just wasn’t using it. It took quite a few "why?"’s before I remembered that we installed 32-bit SQL on there and still needed to update those AWE settings.

    ps: i’m enjoying the "myth a day" series

  2. I know this is way late in responding, so apologies. :)

    In a 32-bit Windows 2003 enterprise machine, should SQL 2000 enterprise have AWE enabled? I notice that you have it for 2005+ so there’s a bit of confusion in finding info for SQL 2000.

    Thanks very much!

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Some thoughts on courage

(This is also the Ponderings – editorial – in today’s SQLskills newsletter.) I want to start out this post by sincerely thanking everyone who emailed

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.