The advent of SQL Server 2005 x64

64-bit processors are expected to dominate the markets. It is clear that the industry is moving towards the 64-bit platform fast, especially for server hardware. The move towards the 64-bit platform has been driven by lower hardware prices and the introduction of x64 architecture by both Intel (EM64T) and AMD (AMD64).

Before x64, the only 64-bit architecture available for SQL Server was the IA-64 architecture from Intel. The IA-64 architecture is very scalable but since it is not based on the 32-bit x86 processor, it cannot run native 32-bit applications.

The following table illustrates the various processors available in the market from Intel and AMD. Note that SQL Server 2005 (64-bit) is available for both x64 and the IA-64 Itanium:

Advantages of 64-bit computing

64-bit computing has many advantages over the 32-bit architecture. The following is a list of 64-bit advantages:

Large memory addressing: The 64-bit architecture offers a larger directly-addressable memory space. SQL Server 2005 (64-bit) is not bound by the 4 GB memory limit of 32-bit systems. Therefore, more memory is available for performing complex queries and supporting essential database operations. This greater processing capacity reduces the penalties of I/O latency by utilizing more memory than traditional 32-bit systems.

Enhanced parallelism: The 64-bit architecture provides advanced parallelism and threading. Improvements in parallel processing and bus architectures enable 64-bit platforms to support larger numbers of processors (up to 64) while providing close to linear scalability with each additional processor. With a larger number of processors, SQL Server can support more processes, applications, and users in a single system.

Same application code: You can migrate to SQL Server 2005 (64-bit) and achieve new levels of application scalability without having to change any application code. Migrating current databases to a 64-bit environment can be achieved by performing a simple database backup from the 32-bit server and restoring the backup on a 64-bit server. You can also use detach (sp_detach_db) and attach (sp_attach_db) to move a database to a 64-bit server. Microsoft invested a lot of effort to make sure that database file structures remain compatible between the 32-bit editions and the 64-bit editions of SQL Server. Applications that access 32-bit servers can connect to and access 64-bit servers without changing application code. This gives users a great deal of flexibility in determining the appropriate rate of 64-bit adoption for their organizations. It is also possible to migrate data back to the 32-bit version for SQL Server 2005 databases. However, downgrading to SQL Server 7.0 from SQL Server 2005 (64-bit) is not supported.

Reduced TCO: Total cost of ownership (TCO) can be reduced with a 64-bit system through consolidation, reduced licensing fees, and higher transaction throughput. By leveraging the tremendous scalability and memory addressability of the 64-bit architecture, SQL Server 2005 (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 using SQL Server 2005 (64-bit) databases can benefit from massive in-memory data caching as well as larger data structures for multiple parallel workloads, concurrent user connections, plan cache, sort space, and lock memory.

Server consolidation: The additional processor and memory headroom available within the 64-bit environment enable significant scale-up and server consolidation opportunities. A large number of consolidated databases can fit into a single SQL instance that has sufficient memory to support the data structures (connections, locks, hash, and so on) needed for multiple coexisting applications. The same benefits also apply to consolidation of multiple instances onto a single physical server.

Improved bus architecture and cache: The improved bus architecture enhances performance by moving more data between cache and processors in shorter periods. A larger on-die cache allows for faster completion of user requests and more efficient use of processor time.

Memory addressing with 64-bit vs. AWE

Inherently, A 32-bit system can manage a maximum of 4 GB of memory. This limits the addressable memory space for Windows 2000 and Windows 2003 systems to 4 GB. With 2 GB reserved for the operating system by default, only 2 GB of memory remains for SQL Server. To allow a 32-bit system to address memory beyond the 4 GB limit, a set of memory management extensions to the Microsoft Win32 API called Address Windowing Extensions (AWE) is used. 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. By using AWE, SQL Server Enterprise Edition can address up to 32 GB of physical memory on Windows Server 2003 Enterprise Edition and up to 64 GB of memory on Windows Server 2003 Datacenter Edition.

Although AWE provides a way to use more memory, it imposes overhead and adds initialization time leading to weaker performance as opposed to 64-bit systems. Also, the additional memory addressability with AWE is available only to the SQL Server's data 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. 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. This memory is available to all components of SQL Server, and to all operations within the database engine.

SQL Server 2005 Editions

64-bit computing is supported in SQL Server 2005 Enterprise Edition and SQL Server 2005 Standard Edition. SQL Server 2005 Workgroup Edition and Express Edition are not available in 64-bit. However, the 32-bit editions work in 64-bit under Windows on Windows (WoW).