64-Bit Vs. 32-Bit Memory Management

An important difference between SQL Server 2000 (64-bit) and SQL Server 2000 (32-bit) is the way the two versions manage memory. SQL Server 2000 (64-bit) can address up to 512GB of memory on Windows Server 2003. In 64-bit, SQL Server can access all this memory directly through a “flat” virtual-memory address space (unlike in AWE, as I explain in a moment). Thus all the SQL Server components that use memory—including database page cache, plan cache, workspace memory, and locks—can use the additional memory on 64-bit systems. In contrast, SQL Server 2000 (32-bit) virtual memory is limited to 3GB if you use the /3GB switch in boot.ini or 2GB without the switch. SQL Server 2000 (32-bit) can access as much as 32GB of memory, but it has to use Address Windowing Extensions (AWE) to access any memory it needs beyond the virtual memory limit. AWE is a set of APIs in Windows that lets a process map physical memory in and out of the process’ virtual address space. The important point to note about how SQL Server uses AWE memory is that only the database page cache (i.e., data and index pages) can utilize physical memory outside the virtual memory of the process. All the other uses of memory—including plan cache, query workspace memory, locks, and other structures such as user connections, cursors, and space used by utilities such as backup and restore—are limited to the virtual address space. Therefore, if your SQL Server application faces memory pressure in these parts of the system, adding additional memory beyond 3GB on 32-bit systems might yield only limited benefits. In such cases, you might consider migrating to a 64-bit system. 64-bit systems have proven especially effective in handling data warehousing and  large enterprise resource planning (ERP) workloads. For more information about the potential advantages of SQL Server (64-bit), see the Microsoft white paper “SQL Server 2000 Enterprise Edition (64-bit): Advantages of a 64-Bit Environment” at http://www.microsoft.com/sql/64bit/productinfo/SQL64bitAdvantages.asp. For more information about configuring AWE memory with SQL Server, see the Microsoft article “How To Configure Memory for More Than 2GB in SQL Server” at http://support.microsoft.com/default.aspx?scid=kb;en-us;274750.