Chapter 15. SQL Sever 2005 Support for 64-Bit Processors

IN THIS CHAPTER

• 64-Bit Processor Usage Scenarios

• SQL Server 2005 (64-Bit) in Detail

One of the top reasons for insufficient performance and limited scalability is memory bottlenecks. A system’s being low on memory stresses both the disk and the processor. Excessive paging results in very high CPU utilization, increased queuing, and slow response times. Adding more memory to the server is one option for improving performance in such scenarios. However, there is a limit to the amount of RAM supported by servers. A server with a 32-bit processor has a limit on the directly addressable memory space of 4GB. This is because a 32-bit pointer cannot hold a memory address larger than 4GB (that is, 2³²). The Address Windowing Extensions (AWE) API (discussed later in this chapter) is sometimes used to enable up to 64GB of memory support on 32-bit systems. However, AWE has some limitations and overhead associated with it, which makes AWE an impractical solution for a lot of memory-intensive, performance-critical enterprise applications.

When using AWE is impractical, you can consider using a 64-bit processor-based server that has support for up to 1024GB of directly addressable memory. Theoretically, a 64-bit processor supports up to 18 billion gigabytes (that is, 2⁶⁴) of flat or linear memory addressability. However, the maximum memory supported by the operating system is 1024GB on Windows Server 2003 Datacenter Edition. In addition to large memory support, the 64-bit processor architecture offers additional benefits, including enhanced parallelism, increased availability, and improved bus and I/O bandwidth architecture for faster and wider throughput.

Microsoft SQL Server 2000 was one of the first Microsoft enterprise product family members to leverage the 64-bit platform and provide true scalability to customer applications. In April 2003, Microsoft announced that Windows Server 2003 (64-bit) and SQL Server 2000 (64-bit) would enable SQL Server 2000 to run on the Intel Itanium 64-bit processor. The 64-bit version of SQL Server is functionally very similar to the 32-bit product. Essentially the same code base as SQL Server 2000 (32-bit) is compiled on the 64-bit platform to leverage the key improvements of the 64-bit operating system and processor architecture. The upgrade path from SQL Server 32-bit to SQL Server 64-bit is straightforward because the on-disk structure is the same on the two platforms; you can detach and attach or back up and restore the databases from a 32-bit to a 64-bit platform.

Intel Itanium Processor Family (IPF) is based on Explicitly Parallel Instruction Computing (EPIC) technology, which has a different architecture and instruction set than 32-bit x86 processors. On the other hand, x64 processors, such as the Intel EM64T or the AMD Opteron, extend the x86 instruction set to support running both 32-bit and 64-bit applications. SQL Server 2000 Service Pack 4 (SP4), released in May 2005, enables the running of SQL Server 2000 (32-bit) on x64 processors. In other words, now you can run SQL Server 2000 on either Itanium 2 or on x64 processors.

SQL Server 2005 is the first release to natively support Itanium 2 as well as x64 processors. SQL Server 2005 addresses some of the limitations of SQL Server 2000 (64-bit) and is designed and optimized to provide superior performance on 64-bit platforms. You can run SQL Server 2005 (64-bit) on 64-bit operating system such as Windows Server 2003 (64-bit) and 64-bit hardware from vendors such as Hewlett-Packard, Unisys, IBM, Dell, and NEC, in 4-way (that is, 4 processors) to 64-way (64 CPUs) configurations. This chapter introduces concepts related to the 64-bit processor and contains details on SQL Server 2005’s support for 64-bit processors.

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-bit–based 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.

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

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.

SQL Server 2005 (64-Bit) in Detail

SQL Server 2005 is the first SQL Server release to natively support Itanium and x64-based 64-bit processors. A single SQL Server 2005 source code base is compiled for three platforms: x86 (32-bit), Itanium (64-bit), and x64 (64-bit). SQL Server 2005 (64-bit) can run on Itanium-based servers with Windows Server 2003 Service Pack 1. SQL Server 2005 (64-bit) for the x64 platform can run on Windows XP x64 Professional 2003 (64-bit) or on Windows Server 2003 x64, which is based on the code for Windows Server 2003 SP1. In addition, you can run SQL Server 2005 (32-bit) on x64-based hardware and operating systems, such as Windows XP x64 Professional 2003 and Windows Server 2003 SP1 64-bit x64, in WOW64 mode.

SQL Server 2005 (32-bit) supports up to 32 processors, and SQL Server 2005 (64-bit) supports up to 64 processors.

Here are some of the enhancements in SQL Server 2005 (64-bit) compared to SQL Server 2000 (64-bit):

• SQL Server 2000 (64-bit) does not include management and monitoring tools such as Enterprise Manager, Query Analyzer, Profiler, Analysis Manager, and so on. SQL Server 2005 (64-bit) bundles all the management tools, including SQL Server Management Studio, Profiler, Database Engine Tuning Advisor, SQL Server Configuration Manager, and so on.

• Only Enterprise Edition of SQL Server 2000 (64-bit) is supported on 64-bit platforms, and SQL Server 2000 Service Pack 4 is required for x64 support. Both Standard and Enterprise Editions of SQL Server 2005 (64-bit) support 64-bit computing, which includes both the Intel Itanium 2 family of processors and the x64 family of processors from both Intel and AMD. In addition, Standard Edition does not have a memory limitation and is only limited to the level of memory supported by the version of Windows on which you run the product.

• SQL Server 2000 (64-bit) and SQL Server 2005 (64-bit) do not support MAPI-based SQL Mail. However, on SQL Server 2005 (64-bit) you can use SMTP-based Database Mail to send emails from T-SQL code.

• The SQL Server 2005 database engine and other subsystems are better aware of the 64-bit architecture than SQL Server 2000. Therefore, SQL Server 2005 can effectively make use of 64-bit computing to provide better performance and system throughput.

• SQL Server 2000 (64-bit) does not allow designing and running Data Transformation Services (DTS) packages. SQL Server 2005 (64-bit), on the other hand, fully supports SQL Server Integration Services (SSIS). You can use Business Intelligence Development Studio on a 64-bit machine to design, debug, and run an SSIS package. Also, because SSIS packages are saved as XML files (with the .dtsx file extension), you can create a package on one platform (for instance, on a 32-bit server) and then edit or run that package on another platform (for instance, on a 64-bit server).

• SQL Server 2000 Reporting Services and SQL Server 2000 Notification Services do not support 64-bit systems. SQL Server 2005 Notification Services is supported on both x64 and Itanium platforms. However, Notification Services Management Objects (NMO) is not supported in WOW64 mode on AMD and Intel x64 platforms. SQL Server 2005 Reporting Services is also supported on both x64 and Itanium platforms; however, a few things, such as the RSKeyMgmt.exe tool, subscription deliveries to reports in TIFF format, and so on, are not fully implemented.

Summary

32-bit memory addressing is often limiting for performance-critical and memory-intensive enterprise applications that support thousands of concurrent users and run very complex queries. If memory bottlenecks are identified, one option to tackle performance and scalability issues is to add more RAM and enable AWE. However, as discussed in this chapter, AWE has some limitations, and the only way to gain performance and scalability in such situations is to consider moving to 64-bit computing. In addition to being used in memory-constrained scenarios, 64-bit systems are also being used in other scenarios, such as for server consolidation, data warehouse applications, Unix migration, and implementing ERP and CRM solutions.

This chapter begins with an introduction to 64-bit computing, including its benefits and a description of scenarios in which the 64-bit architecture can be helpful. The second part of the chapter describes SQL Server 2005’s support for 64-bit systems. SQL Server 2005 natively supports Itanium and x64 platforms and fixes a lot of the limitations of SQL Server 2000 (64-bit).