Choosing and Configuring Hardware - Professional SQL Server 2005 Performance Tuning - page 151

Lock Pages in Memory option, which is automatically granted to the service account on install or if you
change the service account using SQL Server Configuration Manager.

Given what you know about memory and AWE, it would seem that there is no reason for using AWE
on a 64-bit system. After all, a 64-bit system has enough address space to address as much memory as it
needs, so why would you want AWE on a 64-bit system? You can't even enable AWE in a 64-bit version
of SQL Server; the option is disabled in the user interface.

It turns out that there are some great reasons for using AWE to access your memory, even on a 64-bit
system. The SQL Server team realized that on 64-bit systems they could improve overall performance by
using AWE in a 64-bit environment. They found that using AWE memory allows memory to be allocated
and accessed a lot faster. In addition, the memory cannot be paged out by the operating system.

Because of this, 64-bit SQL Server was changed so even though you can't enable AWE on 64-bit versions
of SQL Server, if the SQL Server service account has the Lock Pages in Memory advanced user right, SQL
Server will automatically use AWE to access buffer pool memory.

There have been numerous support cases for 64-bit where huge areas of SQL Server memory have been
paged out unnecessarily by the operating system, which is why the standard recommendation is to give
the service account Lock Pages in Memory as above. Figure 6-4 shows the AWE window through which
data is mapped into memory outside the normal virtual address space.

Best Practice
Use AWE in conjunction with PAE on 32-bit systems with more than 4 GB of memory.
For 64-bit SQL Server, give the SQL Server service account the Lock Pages in Memory
advanced user right in Windows to stop SQL Server memory being paged out.

/3GB or /PAE or AWE?
This is a question that gets asked very frequently: a customer has a number of systems with between
2 GB and 16 GB of RAM and they want to know if they should use /3GB , /PAE , AWE, or even all three.
One thing to remember is that this has to be a 32-bit operating system running 32-bit SQL Server 2005. In
any other scenario this question isn't relevant, and you'll see why in the next section.

There are three categories of answers to this question, each becoming more prescriptive than the last, and
your business environment may dictate a favorite:

Supported by Microsoft: All combinations of the above are actually supported except when
physical memory is 16 GB or greater, which makes using /3GB and /PAE together unsupported.
This is because the Operating System requires more than the 1 GB kernel address space that
/3GB leaves you with to manage 16 GB of memory.

❑

❑ Recommended Best Practice: Don't use /3GB and /PAE together at all because they both mod-

ify the amount of resources that are dedicated for various functions. For example, /3GB reduces
the non-paged pool from 256 MB to 128 MB. PAE doesn't reduce the non-paged pool size, but it
does double the size of each allocation from 4 bytes to 8 bytes. Using them both together actually
throttles the system twice.

Keep It Simple: Use these easy rules whenever possible to reduce risk and support costs:

❑

Less than 4 GB RAM: Don't use any of the above.

❑