64-Bit Processor Usage Scenarios

The key architectural benefits of 64-bit processors include the following:

• Very large memory addressability 64-bit processors break the 4GB/64GB barrier and allow up to 1024GB of direct memory addressability, without any API layer such as AWE.

• Enhanced parallel processing support 64-bit chips include a number of features that enhance parallel processing performance.

• Support for a large number of processors Improvements in parallel processing enable the 64-bit architecture to support a larger number of processors (up to 64). Having a larger number of processors enables SQL Server to scale up. In addition, the on-chip cache results in improved performance on 64-bit platforms.

• Enhanced bus architecture The bus architecture on 64-bit chipsets is faster and wider, allowing more and faster data transfers between the cache and processor; this improves the overall system throughput.

These benefits can be leveraged in a number of scenarios, as described in the following sections.

Improving Performance of Memory-Constrained Applications

Industry benchmarks such as the TPC-C (see www.tpc.org) have shown that the 64-bit architecture can provide immediate performance improvements to applications that are currently memory-constrained on a 32-bit platform. For example, the latest TPC-C benchmark for SQL Server 2005 64-bit running on a HP Integrity Superdome 64-bit delivers two or three times greater TPC-C transaction throughput than any 32-bitbased system while maintaining a much lower total cost of ownership (TCO). Visit www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=105060604 for more details.

As described earlier in this chapter, the 32-bit processor architecture restricts the directly addressable memory space to 4GB. Out of this 4GB of memory space, by default, 2GB is reserved for the operating system kernel, and the remaining 2GB can be used by applications such as SQL Server. Such small virtual address space could be a significant drawback for high-end servers manipulating gigabytes and terabytes of data.

If an application demands more virtual memory, you can put a /3gb switch in your system's boot.ini file. The /3gb switch reduces the kernel memory to 1GB and leaves remaining 3GB for user applications. Limiting kernel virtual address space to 1GB can cause effects from a drop in performance and memory allocation failures to system stalls. It is recommended that you avoid the use of the /3gb switch unless absolutely required and use it only after doing sufficient testing.

If even 3GB is not sufficient, and you are noticing excessive paging and disk or processor queuing, you can put the /pae switch in the boot.ini file. The /pae switch instructs the operating system to use AWE-mapped memory, allowing up to 64GB of addressable memory. The AWE API is based on Intel's Physical Addressing Extension (PAE), which allows Windows to simulate 36-bit memory addressing. Applications such as SQL Server can use the AWE API to scale memory up to 64GB RAM.

The AWE mechanism allocates physical memory and maps it to the given process's virtual address space (VAS). Once physical memory is allocated, the operating system cannot reclaim it until either the process is terminated or the process frees memory back to the operating system. An application can control and even avoid paging altogether for memory allocated by using AWE. In other words, by using AWE, applications can acquire physical memory as non-paged memory and then dynamically map views of the non-paged memory to the 32-bit address space. However, although AWE mapping is efficient, it still has mapping overhead. Also, the additional memory addressability is available only to the relational database engine, not to other engines, such as Analysis Services, Reporting Services, and Full-Text Search. In addition, the relational engine can use AWE-mapped memory only for the data buffer cache and not for other purposes, such as the procedure cache, the log cache, cursors, the sort area, hash memory, per-connection memory, or lock memory.

Note

If there is more than 16GB of physical memory available on a computer, the operating system needs 2GB of process address space for system purposes. Therefore, in order for AWE to use the memory range above 16GB, you need to be sure that the /3gb parameter is not in the boot.ini file. If it is, the operating system cannot address any memory above 16GB.

Many SQL Server resources, such as the procedure cache, per-connection memory, sort space, and so on are restricted to virtual memory only, which has a 3GB limit on 32-bit platforms and do not benefit from AWE. Moving to a 64-bit platform improves the performance of the applications experiencing memory-related problems, such as recompilation of stored procedures. Applications can benefit from massive in-memory caching of data as well as larger data structures for the procedure cache, sort space, lock memory, and connection memory. The 64-bit platform offers massively scalable performance for large, complex queries through large memory addressing, nearly unlimited virtual memory, and enhanced parallelism. On 64-bit systems, the /3gb and /pae switches and AWE are not required because the operating system can directly address up to 1024GB of memory.

Applications that generate complex query plans with a large number of joins, dozens of columns, a large number of open cursors, and an I/O-intensive workload can immediately benefit from a 64-bit processor's large addressable memory. In addition, a large data buffer pool can save considerable I/O costs, resulting in less CPU time spent on I/O and reduced latency spent waiting on I/O.

Server Consolidation

In the simplest terms, consolidation is the process of condensing multiple physical servers, applications, and workloads into a smaller number of physical servers providing an equal or better level of functionality and service.

Consolidation has two primary goals:

• To provide at least the same level of functionality and the service by using fewer servers to lower operational cost and management overhead

• To provide an architecture that can scale efficiently as the business grows

Server consolidation offers the following advantages:

• Centralized management

• Optimized hardware resources

• Standardization of platforms and processes

• Greater return on hardware investment

• Reduced operational costs

The support for very large directly addressable memory, enhanced parallelism, and the ability to scale up and handle a large number of concurrent users and transactions make 64-bit-based servers an ideal choice for large-scale server consolidation.

High-Performance Data Warehousing Applications

Analysis Services 2005 can always benefit from additional memory to provide better query and processing performance, to support very large dimensions, and to support a large number of concurrent users. However, because Analysis Services is unable to take advantage of the memory extensions of AWE, its memory is limited to 3GB in a 32-bit environment, even if more memory is actually available.

Analysis Services in a 64-bit system removes the 3GB memory limit and offers the following benefits:

• Greater parallelism in partition processing

• Faster aggregation processing

• Huge dimensions supported in memory

• Shorter load window

• Support for a large number of concurrent users running complex queries and processing cubes simultaneously

Note

It is important to note that things that could not be done on Analysis Services 2000 (32-bit) and required Analysis Services 2000 (64-bit), can now run successfully and efficiently on Analysis Services 2005 (32-bit). This is possible because of several new architectural enhancements made to Analysis Service 2005, including the new memory management architecture. However, the 3GB memory limit still exists in Analysis Services 2005.

In addition to the scenarios discussed here, the SQL Server 2005 (64-bit) platform can be a powerful alternative to traditional Unix systems for high-end database servers. A 64-bit-based solution can significantly improve overall performance and throughput of enterprise resource planning (ERP) (including supply chain), customer relationship management (CRM), and financial applications.