For anybody who has worked with software products or recently bought a new piece of computer hardware, the terms “32-bit support” and “64-bit support” would be familiar.

One of the prime differences between the 32-bit & 64-bit systems is the amount of memory that can be addressed by the operating system. 32-bit systems cannot address a memory space that is greater than 3GB. While 64-bit systems can run a 32-bit application under the Windows-On-Windows shell, these applications would not be able to take advantage of the increased memory addressing capabilities of the underlying operating system. Insufficient memory on a SQL Server host puts the system under undue memory pressure . Memory management is therefore a tricky and critical issue for 32-bit applications running on 64-bit environments.

SQL Server 2005 introduced a switch on the SQL Server instance that allows the instance to use “AWE” (Address windowing extensions) for memory allocation. What this switch does is that it allows a 32-bit instance of SQL Server to access memory that is greater than 3GB. The screen-show below shows the location of this switch in the “Server Properties” dialog of the SSMS for SQL Server 2008.

Server properties dialog for SQL Server 2008

However, things are changing with SQL Server 2012 – Address Windowing Extensions (AWE) is no longer supported.

Server properties dialog for SQL Server 2012

This switch also has an equivalent advanced option called – “awe enabled”. Attempting to access this option in SQL Server 2012 results in an error.

~~~–Display advanced configuration options
sp_configure ‘show advanced options’,1
RECONFIGURE
GO
–Attempt to fetch the value of the AWE flag
sp_configure ‘awe enabled’
GO
–Hide advanced configuration options
sp_configure ‘show advanced options’,0
RECONFIGURE
GO~~~

Here’s the result:

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
The configuration option ‘awe enabled’ does not exist, or it may be an advanced option.
Configuration option ‘show advanced options’ changed from 1 to 0. Run the RECONFIGURE statement to install.

Soltuions

There are no workarounds to this (in my opinion, teams shouldn’t even be thinking in this direction). It is imperative that the 64-bit edition of the SQL Server must be used in order to access over 4GB of physical memory.

References:

• On SQL Server Books On Line: Enabling AWE memory for SQL Server
  
• Pinal Dave (B|T) post: AWE Explained in simple words

Until we meet next time,

Be courteous. Drive responsibly.