SQL Server 2000 Enterprise Edition (64-Bit): Advantagesof a 64-Bit Environment

SQLServer2000 Enterprise Edition (64-bit): Advantages of a 64-Bit Environment

March 2004

Abstract: Microsoft SQL Server 2000 Enterprise Edition (64-bit) offers dramatic improvements in memory availability and parallel processing performance compared with SQL Server software running in a 32-bit environment. This paper describes the capabilities of the 64-bit solution, highlights differences from the 32-bit environment, and discusses some of the applications and usage scenarios that can benefit from running on a SQL Server platform optimized for a 64-bit environment. The paper is intended to help the reader identify potential applications for Microsoft SQL Server 2000 (64-bit) and set appropriate expectations for deployment and performance.

1

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

 2004 Microsoft Corporation. All rights reserved.

Microsoft and MSDN are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners

1

Table of Contents

Introduction

Audience

Overview: Database Performance and Scalability

SQL Server 2000 (64-bit)

Requirements

SQL Server 2000 (64-bit) Components

Advantages of the 64-bit Architecture

Comparing SQL Server 2000 (32-bit) with AWE and 64-bit

AWE: An Overview

Limitations of AWE

Other Differences in SQL Server 2000 (64-bit)

Considerations for Choosing SQL Server 2000 (64-bit)

Relational Database Performance Factors

Analysis Services Considerations

Server Consolidation Considerations

Alternatives to 64-bit Architecture

Limitations of SQL Server 2000 (64-bit)

SQL Server 2000 (64-bit) Deployment Considerations

Integration

Migration

Compatibility Issues

Summary

Appendix A: Case Studies

Information Resources, Inc. (IRI)

JetBlue Airways

Liberty Medical

123MultiMedia

Appendix B: Sources for Additional Information

1

Introduction

Microsoft SQL Server 2000 Enterprise Edition (64-bit) offers dramatic improvements in memory availability and parallel processing performance compared with SQL Server software running in a 32-bit environment. This paper describes the capabilities of the 64-bit solution, highlights differences from the 32-bit environment, and discusses some of the applications and usage scenarios that can benefit from running on a SQL Server platform optimized for a 64-bit environment. The paper is intended to help the reader identify potential applications for Microsoft SQL Server 2000 (64-bit) and set appropriate expectations for deployment and performance.

Audience

This paper is intended for a technical audience of IT professionals, database administrators and system architects interested in the performance and scalability offered by Microsoft SQL Server 2000 Enterprise Edition (64-bit). This document assumes that you have an understanding of databases in general and Microsoft SQL Server 2000 in particular.

Overview: Database Performance and Scalability

To make timely and informed business decisions in dynamic and competitive environments, organizations must store and analyze massive amounts of business data. Always a critical part of the IT infrastructure, databases are at the heart of a number of converging trends in IT:

• Applications and data are growing in complexity and size. Applications running on 32-bit platforms may be reaching the limits of the platform, specifically regarding the number of processors and addressable memory.
• High-end DBMS capabilities have become a business necessity as organizations gather and analyze data from numerous databases, and serve that data to growing numbers of business users.
• Organizations are consolidating servers to simplify critical data center operations. Consolidation can reduce management complexity and cost while reducing physical space requirements in the data center.

In response to these trends, Microsoft has developed a 64-bit version of its powerful SQL Server 2000 database, optimized for the 64-bit Intel Itanium 2 processor.

By leveraging the tremendous scalability and memory addressability of the 64-bit architecture, SQL Server 2000 (64-bit) can host increasingly large and complex database and analysis applications, and support server consolidation on large-scale SMP systems with many processors. Applications can benefit from massive in-memory caching of data as well as larger data structures for procedure cache, sort space, lock memory and connection memory.

By eliminating platform and processing bottlenecks, the 64-bit platform offers a new level of scalability for business applications at an affordable cost.

SQL Server 2000 (64-bit)

SQL Server 2000 (64-bit) is optimized to run on servers using Intel Itanium 2 processors and Microsoft Windows Server 2003, offering exceptional performance and scalability. SQL Server 2000 (64-bit) is based on the successful 32-bit version of SQL Server 2000, providing an easy migration path for existing applications and capitalizing in existing tools and expertise.

The primary differences between the 64-bit and 32-bit versions of SQL Server 2000 are derived from the benefits of the underlying 64-bit architecture. These include:

• Larger direct-addressable memory space; SQL Server 2000 (64-bit) is not bound by the memory limits of 32-bit systems, so more memory is available for performing complex queries or supporting essential database operations.
• Enhanced parallelism, providing better linear scalability and support for up to 64 processors.
• Improved BUS architecture, accelerating performance by moving more data between cache and processors.

By take advantage of these architectural advantages, SQL Server 2000 (64-bit) can handle large and complex query workloads, consolidate many database applications, and otherwise scale to meet the increasing processing and performance demands of today's IT environment.

Requirements

SQL Server 2000 (64-bit) is currently supported on IA64 servers from a number of hardware vendors including HP, Unisys, IBM, Dell and NEC, in 4-way to 64-way configurations.

SQL Server 2000 (64-bit) does not have a separate hardware compatibility list (HCL). Use hardware that is otherwise certified for use with the Microsoft Windows Server 2003 64-bit operating system and that meets the following minimum requirements:

Hardware / Minimum requirements
Computer / Intel Itanium 2 processors with 64-bit CPU
Memory (RAM) / Enterprise Edition: 1 GB
Additional memory may be required, depending on configuration and operating system requirements.
Hard disk space / SQL Server database components: 190 to 320 MB
Analysis Services: 230 MB
Books Online only: 40 MB
Actual requirements will vary based on your system configuration and the applications and features you choose to install
Pointing device / Microsoft Mouse or compatible
CD-ROM drive / Required

Table 1Hardware Requirements

SQL Server 2000 (64-bit) requires a 64-bit version of the Windows Server 2003 operating system.

SQL Server 2000 (64-bit) Components

The components of SQL Server 2000 Enterprise Edition (64-bit) include:

• 64-bit database server
• 64-bit server agent
• 64-bit analysis server (OLAP and data mining)

These 64-bit components are code-compatible with the 32-bit version of SQL Server 2000, making it easy to integrate a 64-bit server with other SQL Server 2000 database servers.

Advantages of the 64-bit Architecture

The enhanced scalability and performance of SQL Server 2000 (64-bit) is made possible by a number of architectural features of the Itanium 64 bit architecture. The key architectural benefits are discussed briefly as follows.

Memory addressability

32-bit systems can generally address only a 4G address space. (Exceptions available with Address Windowing Extensions, or AWE, on 32-bit platforms are described in a following section.) Windows Server 2003 running on the Intel Itanium 64-bit architecture supports up to 1,024 Terabytes of physical memory and 512 Gigabytes of addressable memory.

Enhanced parallel processing support

The Intel Itanium chips include a number of features that enhance parallel processing performance. The Itanium 2 features a wider system bus, more registers and Explicitly Parallel Instruction Computing (EPIC) technology, which enables a processor to execute multiple (up to 6) instructions simultaneously.

Larger numbers of processors

Improvements in parallel processing enable the 64-bit architecture to support larger numbers of processors (up to 64). With a larger number of processors, SQL Server can support more processes, applications, and users in a single system.

Server platforms offering more than 32 CPUs are available exclusively on 64-bit architecture. The highest TPC-C benchmark figures for SQL Server have been achieved on 64-bit systems leveraging both large amounts of memory and the superior scaling of 64-bit processors in a 64-bit architecture.

Enhanced bus architecture

The bus architecture on current 64-bit chipsets is faster and wider than earlier generations. More data is passed to the cache and processor; this is somewhat analogous to the improvement broadband connections offer over dial-up connections.

For a more detailed discussion of the Itanium architecture, see

Comparing SQL Server 2000 (32-bit) with AWE and 64-bit

Microsoft SQL Server 2000 Enterprise Edition (32-bit) uses the Microsoft Windows 2000 Address Windowing Extensions (AWE) API to support very large amounts of physical memory. For some applications, using AWE may be a viable alternative to upgrading to the 64-bit platform.

AWE: An Overview

Standard 32-bit systems can map at most 4 GB of memory, limiting the addressable memory space for Windows 2000 systems to 4GB. With 2GB reserved for the operating system, only 2GB of memory remains for the application (in this case, SQL Server 2000). You can increase this amount to 3 GB by setting a 3GB switch in a Windows boot.ini file.

AWE is a set of memory management extensions to the Microsoft Win32 API that allows applications to address memory beyond the 4GB limitation. Using AWE, applications can acquire physical memory as nonpaged memory, and then dynamically map views of the nonpaged memory to the 32-bit address space. AWE enables memory-intensive applications, such as large database systems, to address large amounts of information. More information on AWE can be found on the MSDN page on the Microsoft Web site.

Using the AWE extension enables a SQL Server Enterprise Edition instance to address significantly more memory than standard implementations:

• Up to 32 GB of physical memory on Windows Server 2003, Enterprise Edition using AWE
• Up to 64 GB of memory using Windows Server 2003, Datacenter Edition using AWE

Although using AWE imposes some overhead and adds initialization time, it remains an important strategy for many memory-intensive database applications.

Limitations of AWE

Using SQL Server 2000 (32-bit) with AWE has some limitations. The additional memory addressability is available only to the relational database engine, not to other engines such as Analysis Services. The benefits are limited to those operations that can directly access database pages stored in the expanded buffer cache — operations such as building indexes on large tables, or simple queries from cache. AWE does not enhance other memory-intensive operations using the stored procedure cache or log cache.

Although its benefits are significant, the potential performance enhancements from AWE do not apply to the following operations or components:

• Analysis Services
• Procedure Cache for compiled plans
• Cursors
• ODBC parameter caching
• Sort area
• Index creation, including full-text indexing
• Hash memory
• Per-connection memory
• Lock memory

In contrast, SQL Server 2000 (64-bit) makes extended memory available to all database processes and operations. Using this 64-bit version on Itanium 2 hardware, a SQL Server instance can address up to 512 GB, the current maximum memory supported by Windows Server 2003. (There is a theoretical addressable limit of 18 Exabytes.) This memory is available to all components of SQL Server, and to all operations within the database engine.

As a result, SQL Server 2000 (64-bit) enhances performance of a wide range of memory-intensive database applications.

Other Differences in SQL Server 2000 (64-bit)

Although memory management is the most significant difference between the 64-bit and 32-bit versions of SQL Server 2000, there are other differences that you should consider when choosing which architecture to deploy.

SMP processor support

Previously, with the 32-bit version of SQL Server, symmetric multiprocessing (SMP) support was limited to 32 processors. With SQL Server 2000 (64-bit) Enterprise Edition this limit has been raised to 64 processors with Windows Server 2003 Datacenter edition.

Availability

High availability is critical for many high-end applications. SQL Server 2000 (64-bit) on Windows Server 2003 supports an 8-node failover cluster. This is an increase from the 4-node cluster supported by Windows 2000 Datacenter Server

Installation enhancements

The 64-bit version of SQL Server is installed using the Windows Installer Service, enabling the "Add or Remove Programs" option in the Control Panel. The setup process displays a tree of features to be installed, which can be deselected as needed.

Meta data repository

The 64-bit version of Analysis Services uses SQL Server instead of Jet (.MDB) for its meta data repository. The repository databases are created within SQL Server when Analysis Services is installed.

Replication

The Replication features of SQL Server 2000 (64-bit) are nearly identical to the 32-bit version, with the following features being supported:

• Snapshot Replication
• Transactional Replication
• Merge Replication
• Immediately updating subscribers
• Queued updating subscribers

However, there are a few special cases:

• As the Microsoft Jet engine is not supported, Microsoft Jet push subscriptions for merge replication are not supported.
• Unless the subscriber provides a 64-bit ODBC or OLE DB driver, transactional or snapshot push subscriptions for ODBC or OLE DB subscribers are not supported.
• Because of the unavailability of 64-bit Data Transformation Services (DTS), transformable push subscriptions are not supported.

In addition, the 64-bit versions of Windows Server2003, Enterprise Edition and Windows Server2003, Datacenter Edition do not include certain features that are included in the 32-bit versions of those operating systems.

These features are not included because of underlying differences between the 32-bit and 64-bit architectures or because the features are not appropriate in the environments where the 64-bit versions of Windows Server2003, Enterprise Edition and Windows Server2003, Datacenter Edition are deployed.

For a complete list of differences, see "Differences Between 64-bit and 32-bit Releases (64-bit)"[1] in SQL Server 2000 (64-bit) Books Online and "Features unavailable on 64-bit versions of the Windows Server2003 family"[2].

Considerations for Choosing SQL Server 2000 (64-bit)

Although it offers significant scalability and performance for many applications, not every application will benefit from the 64-bit version of SQL server. This section is designed to help you determine if and when it is appropriate to either use existing SQL Server instances or develop new applications using the 64-bit architecture.

The improved memory and parallel processing capabilities of SQL Server 2000 (64bit) are compelling in a number of different usage scenarios, including:

• Improving performance for memory-constrained relational applications.
• Creating or accelerating large OLAP systems with rapid response time requirements.
• Consolidating multiple Windows-based databases and applications to fewer, larger systems. By hosting multiple databases on a single 64-bit system, you simplify management, improve storage utilization, and generally improve operational efficiency.
• "Scaling up" current applications experiencing significant growth; migrating existing database servers that are meeting platform limitations doesn't affect the other tiers of multitiered applications.
• Replacing UNIX systems/applications. The 64-bit platform offers a powerful alternative to UNIX systems for high-end database servers.

The following sections list some of the factors you should consider when evaluating specific applications for the 64-bit platform.

Relational Database Performance Factors

Memory-intensive SQL Server relational workloads are good candidates for 64-bit SQL Server. Many SQL Server resources are restricted to 3 GB limit using the 32-bit environment, including

• Sort space
• Hash tables used in joins and aggregates
• Index creation, including full-text indexing
• Per-connection memory
• Procedure cache for compiled plans

Systems starved of real memory will exhibit degraded performance because of waits for resources and delays as the processors compile plans that have been evicted from cache. They may also experience excess disk activity to write objects such as hash tables that cannot fit into the available memory to disk.

For example, moving to SQL Server 2000 (64-bit) would immediately improve the performance of applications experiencing the following memory-related performance problems:

• Recompilation of stored procedures because of eviction from memory. The 64-bit environment provides a large plan cache for high volume transaction applications with large numbers of stored procedures. This reduces the need to compile stored procedures that have been evicted from memory, reducing CPU utilization and reducing query latency.
• Resource semaphore waits associated with queries waiting for memory grants. Multiple queries utilizing large-scale hash joins — especially those executing against a data warehouse and spanning large datasets — can be affected by resource semaphore waits.

Other operations that can benefit from extended memory include: