Overcoming AWEful performance

For SQL Server 2000, 64 bits means never having to work around memory limits

The question of why SQL Server applications would greatly benefit from moving to a 64-bit platform can be answered in three letters: AWE ( Address Windowing Extensions). With 32-bit SQL Server constrained by a memory limit of 3GB, many DBAs went in search of a work-around. Some found the solution in AWE, but most did not.

In a nutshell, AWE is a dedicated swap file for SQL Server. It maps memory addresses that extend past the 4GB limit (3GB for SQL Server and 1GB for Windows) directly to locations on the disk. This extended mapping allows you to take full advantage of the memory you have in your server. On the surface, AWE seems like a viable way to get around memory restraints, but there are two big problems with it. First, SQL Server can’t use AWE for caching stored procedures or any system data structures (views, cursors). This means that all of those memory-intensive operations you encapsulated into stored procedures to make them run faster are still bound to your original memory space.

Second, even if you found that your database application produced the sort of load that could take advantage of AWE, you often found that the extra system resources it took to manage the memory addressing nullified any improvement you gained in database performance.

Furthermore, although AWE can achieve great things for certain tasks, it can also encourage bad database practices. For example, you might tweak I/O operations to take advantage of AWE, then discover that the database performs poorly when ported to another server where AWE isn’t an option.

Moving SQL Server to 64 bits shatters the 3GB memory ceiling and puts AWE on the endangered-features list. Today, 64-bit SQL Server is constrained only by the physical memory limits of Windows Server 2003 (64GB in Enterprise and 512GB in Datacenter edition). AWE is still available in the 64-bit program, but it’s just a throwback. Turning it on will have no effect.