SQL Server 2000 (64 bits) versus SQL Server 2000 (32 bits) - Memory management

  • Article

SQL Server 2000 (64 bits) versus SQL Server 2000 (32 bits) SQL Server 2000 (64-bit) and SQL Server 2000 (32-bit) manage memory in a different manner. 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 since virtual-memory address space is only limited to 8 TB (unlike in AWE, as explained below). Some 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 using 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:

“SQL Server 2000 Enterprise Edition (64-bit): Advantages of a 64-Bit Environment” - https://www.microsoft.com/sql/64bit/productinfo/SQL64bitAdvantages.asp

 “How To Configure Memory for More Than 2GB in SQL Server” - https://support.microsoft.com/default.aspx?scid=kb;en-us;274750

“Memory Limits for Windows Releases “ - https://msdn2.microsoft.com/en-us/library/aa366778.aspx