Why 64-bit?

  • Article

Why 64-bit?

Memory Addressability:

A 32-bit system can directly address only a 4-GB address space. With 2 GB reserved for the operating system by default on 32-bit Windows, only 2 GB of memory remains for the application (in this case, SQL Server 2005). You can increase this amount to 3 GB by setting a /3GB switch in a Windows boot.ini file. Additional memory is indirectly accessible by using Address Windowing Extensions (AWE) on 32-bit platforms, but there are some limitations to that as well.

Limitations of AWE:

· The additional memory addressability is available only to the relational database engine’s management of database page buffers. It is not available to other memory consuming database operations such as caching query plans, sorting, indexing, joins, or for storing user connection information. It is also not available on other engines such as Analysis Services, Reporting Services, Integration Services

In contrast, SQL Server 2005 (64-bit) makes memory available to all database processes and operations. Using the 64-bit version on either IA64 or x64 hardware, a SQL Server instance can address up to 1 terabyte of memory; the current maximum amount of physical memory supported by Windows Server 2003 SP1.

· Mapping memory pages in and out of the virtual address space (a function of AWE usage) requires time and multiple processor cycles. The reduction and/or elimination of this overhead will be most noticeable on systems that have large numbers of concurrent users or systems running complex queries that work with very large volumes of data.

More processing power:

· The 64-bit processor provides enhanced parallelism, thereby providing more linear scalability and support for up to 64 processors, and yielding stronger returns per processor as compared to 32-bit systems.

· The improved bus architecture enhances performance by moving more data between cache and processors in shorter periods.

Reduced Total Cost of Ownership with higher Return on Investment:

In addition to significant gain in performance, the 64-bit platform provides far greater headroom for growth. This means that the hardware lifecycle is likely to be longer, especially with the release of multicore (currently dual-core or quad-core) 64-bit CPUs. The 64-bit platform is also very well-suited for supporting multiple OLTP and analytical workloads within the same server thus reducing hardware, license, operations, and infrastructure costs.

SQL operations that can benefit from extended memory include:

· Very large and complex queries that cannot fit in the relatively small virtual address space available on the 32-bit platform. With the large amount of virtual address space available in 64-bit systems, these queries can run completely in memory very quickly.

· Index creation operations benefit from the existence of larger addressable memory. This is because the entire sort can be done in memory.

· Full-text indexing is restricted to either 2-GB or 3-GB workspaces for 32-bit computers.

· Complex queries that use sorting or large hash joins or hash aggregates perform significantly better when sufficient directly addressable memory is available. Hash joins are very efficient, but can be memory intensive.

· Systems that have large numbers of active stored procedures will benefit through improved plan caching capacity. The 64-bit architecture can substantially reduce overall CPU utilization and latency by eliminating the need to evict procedures from cache and compile frequently.

· Server-side cursors, which are often heavy consumers of memory, can more readily be kept in memory, thereby resulting in better performance.

· Operations such as aggregation and sorting need to work with the entire datasets. These operations can benefit from the increased memory support provided by the 64-bit platform.