Docstoc

Moving

Document Sample
Moving Powered By Docstoc
					 Moving
SQL Server

  x64
  to the



  Platform By Michael Otey


                   sponsored by
                                                                                                                                                           i




Contents
Chapter 1: The x64 64-Bit Architecture . . . . . . . . . . . . . . . . . . . . . . . . . .                                                            1
  Limitations of the 32-Bit Platform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                                          1
  64-Bit Platform Alternatives              ........................................                                                                  2
             Sidebar: A 64-Bit Timeline . . . . . . . . . . . . . . . . . . . . . .      ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .    3
     Intel’s IA-64 Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . .    ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .    3
     Intel’s Xeon EM64T x64 Architecture . . . . . . . . . . . . . . . . . . .           ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .    5
     AMD’s x64 (AMD64) Architecture . . . . . . . . . . . . . . . . . . . . .            ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .    6
             Sidebar: A 64-Bit Platform Comparison . . . . . . . . . . . . .             ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .    9
         The HyperTransport Bus Detailed . . . . . . . . . . . . . . . . . .             ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .    9
         Integrated Memory Controller . . . . . . . . . . . . . . . . . . . . .          ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .   10
         Multicore Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . .     ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .   10
         32-Bit and 64-Bit Application Compatibility . . . . . . . . . . . .             ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .   10
             Long Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .     ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .   10
             Legacy Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .     ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .   11
     Microsoft Support for the x64 Architecture . . . . . . . . . . . . . . .            ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .   12
         Windows 2003 Standard, Enterprise, and Datacenter Editions                       .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   13
             WOW64 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .   ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .   14
         SQL Server 2005 Standard and Enterprise Editions . . . . . . .                  ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .   14
  Next: Relational Database Engine . . . . . . . . . . . . . . . . . . . . . . .         ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .   15
1


Chapter 1:

The x64 64-Bit Architecture
Not long ago, 64-bit systems were a future technology reserved almost exclusively for high-end
enterprise implementations. The widespread adoption of the 64-bit capabilities for x86 architecture
(referred to as x64) by both computer manufacturers and Microsoft, however, dramatically changed
the computing landscape. Now, 64-bit systems are a reality for both high-end enterprise systems and
small-to-midsized businesses (SMBs). The 64-bit technology is quickly becoming the standard for
mainstream computing. Microsoft foresees significant adoption of 64-bit-capable systems. In his
presentation at the Windows Hardware Engineering Conference (WinHEC) 2005, Microsoft Chairman
and Chief Software Architect Bill Gates predicted that by the end of 2006, the majority of server
processors and more than half of the processors sold for client PCs will support 64-bit computing.
Gates is on the mark with his predictions about the adoption of 64-bit technology. In fact, it’s difficult
to find a mainstream server system that doesn’t provide 64-bit support.
      The convergence of two separate but parallel computing trends has sparked the rapid move to
64-bit technology: the emergence of the 64-bit x64 technology and the need for a more powerful
computing platform that can handle high-powered tasks such as server consolidation, scale-up com-
puting, and virtualization. Relational database management system (RDBMS) servers, such as Microsoft
SQL Server, are prime examples of applications that can derive major benefits in moving from 32-bit
systems to 64-bit systems.
     In Moving SQL Server to the x64 Platform, you’ll learn about the new 64-bit x64 technology.
Chapter 1 discusses the limitations of current 32-bit systems, then presents the different 64-bit
architectures. You’ll get a more detailed looked at the x64 architecture that’s on its way to becoming
the standard for the next generation of both server and desktop systems. Subsequent chapters will
show how SQL Server and its various subsystems, such as Analysis Services, Integration Services, and
Reporting Services, derive specific benefits from running on the 64-bit platform. In addition, you’ll
learn about the impact of several closely related technologies (e.g., dual-core processors) and about
the implications of different 64-bit systems’ power consumption and other requirements.

Limitations of the 32-Bit Platform
The x86 platform was the mainstay processor for PC architecture from its 1981 adoption as a 16-bit
processor in the IBM PC through its evolution to the 32-bit processor 386 in 1985 to the current full
64-bit implementation. Its two-decade long life cycle is a testament to the robust x86 architecture.
However, the 32-bit x86 platform had a couple of important limitations. First, the 32-bit implementa-
tion was limited to a maximum of 4GB of addressable memory. For many of today’s memory-hungry
applications, 4GB is simply not enough. Database applications such as SQL Server derive definite
benefits from larger memory address spaces.
     The way the 32-bit Windows OS allocates memory actually exacerbates the addressable-memory
limitation. Under 32-bit Windows, 2GB is reserved for the OS—which leaves only 2GB to run all the
applications. Microsoft developed a technology for Windows 2000 Server—Address Windowing


                                   Brought to you by AMD and Windows IT Pro eBooks
2   Moving SQL Server to the x64 Platform


Extensions (AWE)—designed to help circumvent this problem by letting AWE-compliant applications
access up to 8GB of RAM on Win2K Advanced Server (Win2K AS) and 64GB of RAM on Win2K
Datacenter Server. However, AWE has limitations as well. Applications must be specially written to
take advantage of AWE. Also, AWE’s paged memory access scheme imposes overhead without
providing any benefits to the OS.
     In addition to the 4GB RAM limitation, the performance of the front-side bus architecture limits
memory-access speeds. The x86 processor is also limited in its ability to process floating-point
instructions. A floating-point processor wasn’t part of the original processor design, and the floating-
point capabilities later added to the 486 didn’t perform as well as competing RISC processors. As CPU
clock speeds increased, the legacy system design the x86 platform used became the limiting factor for
overall system performance. The legacy system architecture uses a front-side bus design in which all
I/O pathways to the CPUs are routed through the memory controller. The front-side bus speed
became an even bigger factor than CPU clock speed in determining system performance.
     The 64-bit platform solves these problems. The maximum addressable memory using the AMD64
architecture is increased to 1TB. And, unlike the AWE implementation, both the OS and the
applications can use this memory. In addition, in x64 editions of SQL Server, not only the buffer
cache but also the procedure cache and other SQL Server mechanisms can use the memory. The new
x64 architecture uses multiple new floating-point processors to drastically improve floating-point
performance. The larger 64-bit address space lets the system move and process essentially twice as
much data per CPU cycle as the older 32-bit design. Effectively doubling the raw data processing
power can have a significant impact on data-centric applications such as SQL Server. In addition, with
the move to the x64 platform, new system designs based on the high-performance system bus design
dubbed the HyperTransport technology solve the system I/O bottleneck associated with the legacy
front-side bus system design. (I present more detailed information about the x64 architecture and the
HyperTransport bus later in this chapter.)

64-Bit Platform Alternatives
Two types of 64-bit systems are currently available for the Windows platform: Itanium and x64. Intel
first released the Itanium system in 2000, touting it as the future of computing. The Itanium used a
completely new processor architecture that was incompatible with the 32-bit x86 instruction set that’s
the basis of virtually all of today’s desktop and server systems. In fact, the Itanium processor required
that all new 64-bit binary execute programs be created with a new Itanium-compatible compiler.
Existing 32-bit applications could run only under an emulation layer. The requirement for all new
64-bit OSs and applications coupled with its 32-bit incompatibility created significant adoption hurdles
for the Itanium architecture.
      AMD first released the x64 Opteron processor in 2003. In sharp contrast to the Itanium, AMD
pioneered a different path to 64-bit computing. The x64 architecture essentially extended the existing
32-bit x86 architecture to a full 64 bits. Unlike the Itanium, the x64’s x86-compatible architecture is
binary compatible with 32-bit x86 executable programs. This binary compatibility lets the x64
simultaneously run both 32-bit and native 64-bit programs at full speed with no additional overhead
or performance penalties. No emulation layer is required. This ability to run 32-bit OSs and
applications in addition to native 64-bit OSs and applications has proven to be one of the critical
factors fueling adoption of the x64 architecture. The AMD64 architecture comprises more than just a
new 64-bit processor; it’s coupled with the HyperTransport technology.

                                   Brought to you by AMD and Windows IT Pro eBooks
                                                                           Chapter 1 The x64 64-Bit Architecture 3




                                        A 64-Bit Timeline
 1995 November: Intel announces its working on the Itanium.
 1999 September: AMD announces plans for the x86 compatible 64-bit architecture code-named Sledgehammer
 and later dubbed Opteron
 2001 June: Intel ships the Itanium.
 2001 August: Microsoft ships Windows 2000 Advanced Server (Win2K AS) support for IA-64
 2002 July: Intel ships the Itanium2.
 2003 April: AMD ships the Opteron
 2003 April: Microsoft ships Windows Server 2003 with support for IA-64.
 2003 April: Microsoft ships SQL Server 2000 64-bit with support for IA-64.
 2004 June: Intel ships x64 compatible EM64T XEON processors.
 2005 April: Microsoft ships Windows 2003 x64 and IA-64.
 2005 May: AMD ships first dual-core Opteron processors.
 2005 Oct: Intel ships dual-core Xeon EMT64T processors.
 2005 Nov: Microsoft ships SQL Server 2005 32-bit and 64-bit editions.
 Standard Edition and the Enterprise Edition support both the IA-64 and x64 architectures.
 2005 Dec: Microsoft drops IA-64 support from Windows 2003 R2.


     In June 2004, more than a year after AMD’s release of the Opteron platform, Intel also released
an x64-compatible version of its Xeon processor. However, although both the Intel and AMD64
processors can run the same 32-bit and 64-bit executable programs, the Intel implementation of the
x64 architecture and the AMD implementation of the x64 architecture are quite different. (For a more
detailed history of the development and release of major 64-bit hardware and software platforms, see
the sidebar “A 64-Bit Timeline.”) To help you better understand the strengths and weaknesses of the
64-bit Windows platforms, I briefly cover the Intel Itanium, then explore the differences between
Intel’s x64 EM64T technology and AMD64 Opteron platform in more depth.

Intel’s IA-64 Architecture
Released in May 2001, the Itanium was the first true 64-bit processor for the PC platform since the
demise of the 64-bit DEC Alpha. Now in its second version, the Itanium2 processor uses an architec-
ture that Intel dubbed IA-64. The Itanium design is radically different from the 32-bit x86 design. The
x86 design uses CISC architecture. With the CISC approach, the processor is designed to process a
series of complex instructions within each clock cycle. Being able to execute multiple instructions
makes the processor more efficient but also makes designing the processor more difficult because it
must be able to predict and optimize the code that will be executed.
      In contrast, the IA-64 uses a different instruction set whose effectiveness is ultimately dependent
on new compiler technology. The Itanium is a Very Long Instruction Word (VLIW) processor. The
VLIW architecture has been used in several specialized single-purpose CPUs, but the Itanium is its
first implementation as a general-purpose microprocessor. Architecturally, the Itanium more closely
resembles a RISC processor than a CISC-based x86 processor. The key difference between the IA-64
architecture and RISC processors is the use of enhanced parallel processing techniques. Most RISC
systems process instructions in parallel poorly. The IA-64, however, can process up to six instructions

                                    Brought to you by AMD and Windows IT Pro eBooks
4   Moving SQL Server to the x64 Platform


in parallel per clock cycle. Intel’s name for Itanium’s parallel processing design is Explicitly Parallel
Instruction Computing (EPIC). A number of factors in addition to raw CPU clock speed influence
system performance. The ability to execute multiple instructions per cycle makes traditional speed
measurements based solely on clock speed misleading for the Itanium processor because the
processor performs multiple parallel actions for every clock cycle.
     With EPIC, the CPU doesn’t need to perform complex out-of-order processing, as is the case with
the x86 architecture. But that doesn’t mean the problem of parallelizing the machine instructions goes
away. Instead, that job is shifted to the compiler. A compiler is a software program that creates the
executable instructions the processor performs. Theoretically, the compiler knows more about the
code than the CPU and can make better estimates about upcoming code paths. However, this
scheme is also largely dependent on the compiler’s ability to optimize that code for parallel
processing, and IA-64 compilers are still a relatively immature technology.
     Moving the Itanium away from the x86 architecture eliminated the floating-point weakness that
has plagued the x86 architecture. However, it introduced a significant problem: 32-bit compatibility.
To run existing 32-bit applications, the Itanium provides x86 hardware emulation designed to provide
compatibility with the 32-bit x86 instruction set. Unfortunately, Itanium’s x86 emulation imposes a
significant performance overhead, which causes 32-bit applications to run significantly slower on the
64-bit Itanium than on a native 32-bit x86 system. This tradeoff clearly shows that Itanium was
designed for specific custom-built applications in the high-end server market, in which compatibility
with existing desktop software isn’t a high priority. Figure 1.1 presents an overview of Itanium’s 64-bit
and 32-bit application compatibility.

                                                  Figure 1.1
                     IA-64 32-bit and 64-bit application compatibility overview


                                                          32-bit Emulation
                             64-bit
                           Applications                 32-bit Applications


                               Windows for 64-bit Itanium System

                                        64-bit Itanium Drivers

                                                    Itanium



    As Figure 1.1 shows, Itanium-based systems must run a native 64-bit IA-64 compatible OS. That
OS interfaces with the hardware by using specifically built IA-64 device drivers. Likewise, the 64-bit
OS supports native 64-bit applications compiled expressly for the IA-64 platform by using proprietary


                                   Brought to you by AMD and Windows IT Pro eBooks
                                                                          Chapter 1 The x64 64-Bit Architecture 5


Intel compilers. An x86 emulation layer that converts x86 instructions to their IA-64 equivalents
provides support for existing 32-bit applications.
     The Itanium2 is a processor designed to accommodate very high-end computing scenarios
running the limited set of specifically built native 64-bit IA-64 applications. However, the Itanium2
isn’t designed for 32-bit compatibility. Microsoft doesn’t support running any of its 32-bit server
applications in Itanium’s 32-bit emulation mode. These limitations will keep the Itanium from
becoming a mainstream technology for the foreseeable future.

Intel’s Xeon EM64T x64 Architecture
Intel released the 64-bit Xeon in July 2004. An understandably staunch supporter of the Itanium chip,
Intel was reluctant to follow AMD’s lead with the x64 architecture. Even so, the versatility of the x64
platform and its highly competitive pricing compelled the company to release its own version of the
x64 architecture. Intel named its new x64 64-bit technology Extended Memory 64 Technology
(EM64T). Following the lead of the AMD64 platform, the new Intel Xeon uses the x86-64 architecture
and is instruction-level compatible with the x64 AMD Opteron processor. Like the 32-bit x86 and
AMD64 platforms, Intel’s EM64T processors are CISC-based and offer complete 32-bit binary
compatibility. As with the AMD64 platform, Windows Server 2003 x64 editions support Intel’s EM64T
processors.
     Intel’s x64 system design follows the same layout as the legacy 32-bit system design. Figure 1.2
shows the legacy system architecture design that the Intel X64 EM64T architecture uses.

                                                  Figure 1.2
                                           Legacy system design



                                      CPU                              CPU




                                                   Memory                            PCI-E
                          I/O Hub                 Controller                         Bridge
                                                    Hub




                                   Brought to you by AMD and Windows IT Pro eBooks
6   Moving SQL Server to the x64 Platform


     This system design has essentially been used for the past decade in 32-bit systems. A Northbridge
chipset on the motherboard controls memory access; the memory controller and the CPUs are
separate system components. A physical pathway on the system known as the front-side bus
connects the CPUs, the memory controller, and the system memory. Unfortunately, the front-side
bus design suffers from a couple of important limitations that reduce the amount of memory that the
CPUs can effectively use. In the legacy system design, all I/O pathways to the CPUs are routed
through the memory controller and the front-side bus, which creates a system-performance
bottleneck.
     The bottleneck has long been a problem for 32-bit systems because the speed of the front-side
bus restricts overall system performance. The problem increases in significance in 64-bit and multicore
configurations because the bandwidth requirements of the front-side bus are essentially multiplied. In
addition to adversely affecting performance, the bottleneck also increases overall system power
consumption and heat generation as the components in the front-side bus are utilized at near
maximum capacity. The speed of the front-side bus combined with the speed of memory affects
overall system performance more than CPU clock speed. With the legacy front-side bus design, the
processor’s access to the memory, the I/O subsystem, and any I/O devices (e.g., network interface
cards—NICS) must compete for bandwidth. Essentially, this legacy design doesn’t let the system take
full advantage of the 64-bit x64 architecture.

AMD’s x64 (AMD64) Architecture
When AMD released its x64 platform, the company also adopted a new system architecture designed
to accommodate the increased processing power and I/O requirements of the Opteron processor.
Unlike the Intel Itanium, which moved away from the x86 instruction set, AMD embraced and
extended the x86 platform. AMD moved the x86 platform from 32-bit to 64-bit and introduced a new
system architecture. AMD’s x86-64 architecture is a logical extension of the x86-32 architecture that
the vast majority of today’s 32-bit systems use. Like the 32-bit x86 architecture but different from the
Itanium, AMD64 processor is a CISC-based processor, in which the CPU performs its own code
optimizations and scheduling. AMD addresses the x86 floating-point weaknesses by incorporating
new floating-point instructions and 16 new floating-point registers.
     One of the most significant differences between the x64 platform and the IA-64 platform lies in
the area of 32-bit application compatibility. The x64 platform can run 32-bit and 64-bit applications
side-by-side with 100 percent efficiency. In fact, thanks to the larger data path the x64 architecture
provides, 32-bit applications typically perform better running on x64 systems than they do on native
32-bit systems. The most important benefits of the AMD64 architecture include
     • Increased system capacity and scalability—The new 64-bit x64 architecture completely
        eliminates the 4GB limitation of the 32-bit x86 architecture. The current x64 implementation
        supports up to 1024GB (1TB) of addressable memory; 64-bit OSs and applications can use this
        added memory immediately, with no limitations such as those that the 32-bit AWE technology
        imposed. It’s worth noting that the x64 platform running the 32-bit version of Windows Server
        still supports Physical Address Extensions (PAE) and AWE. For all programs running in 32-bit
        mode, memory is still limited to 4GB.




                                   Brought to you by AMD and Windows IT Pro eBooks
                                                                        Chapter 1 The x64 64-Bit Architecture 7


    • Increased system resources for applications—The x64 platform’s larger virtual address space
      lets applications effectively use the increased system resources. The x64 architecture can
      address up to 256TB of virtual address space, compared to a maximum of 4GB for 32-bit
      systems.
    • Increased computing performance—The addition of a set of new 64-bit and 128-bit registers is
      another improvement that increases the AMD64 architecture’s raw computing performance.
      The number of general-purpose registers has increased from 8 in the 32-bit x86 platform to 16.
      The size of the registers has increased from 32 bits to 64 bits. In addition, the number of
      128-bit floating point single instruction, multiple data (SIMD) Multimedia (XMM) registers
      increases from 8 to 16 registers.
    • Improved system security—The new No-Execute flag improves system security by blocking a
      common security exposures. The NX flag is a processor feature that prevents buffer-overrun
      code exploits by marking memory blocks as read-only. In other words, if the NX flag is set,
      the processor won’t execute any code in the memory block. Buffer overruns are one of the
      most common malicious code exploits, and the NX flag prevents code from running in areas
      where it shouldn’t run.
    • Code compatibility—Because the x64 architecture uses an instruction set based on x86
      architecture, porting 32-bit code to the new x64 platform is much easier than porting code to
      the incompatible IA-64 platform. This x64 architecture compatibility has enabled the rapid
      creation of a number of new 64-bit x64 product versions, including the Windows 2003 x64
      editions, the SQL Server 2005 x64 editions, and the .NET Framework 2.0. In addition, Visual
      Studio 2005 can generate code that targets the x64 platform.

    Although the AMD64 platform is an extension of the x86 architecture, the AMD64 system
architecture has been designed for 64-bit computing from the outset. In addition, the line of AMD
Opteron processors is server oriented. The processors support SMP system configurations and can run
in configurations with up to 8 CPUs.
    One of the most important features of the AMD64 platform is the use of the HyperTransport bus,
which directly connects memory, I/O, and other CPUs for maximum throughput. Figure 1.3 presents
an overview of AMD64 architecture’s HyperTransport system bus design.




                                 Brought to you by AMD and Windows IT Pro eBooks
8   Moving SQL Server to the x64 Platform



                                                  Figure 1.3
                            HyperTransport system bus design overview


                        6.4 GB/S               8 GB/S                      6.4 GB/S




                         8 GB/S                                            8 GB/S



                                  PCI-E                        PCI-E
                                  Bridge                       Bridge

                          8 GB/S


                                                     USB                         = HyperTransport
                               I/O Hub
                                                      PCI                          links


     Perhaps the most important benefit of Direct Connect Architecture is the means by which CPUs
access memory. The AMD Direct Connect Architecture uses an integrated memory controller to let
CPUs directly connect to the system memory. You can find more details about the differences
between the AMD Opteron and Intel EM64T x64 architectures in the sidebar “A 64-Bit Platform
Comparison.”
     In much the same way that the Itanium parallel processing architecture makes it impossible to
equate system performance with simple CPU speed, the AMD64 architecture makes it impossible to
measure performance based solely on CPU frequency. Improved processor efficiency results in
shorter pipelines, reducing the risk of delays because of CPU branch mispredictions and cache
misses. The result is more instructions executed per clock cycle. Likewise, shorter pipelines also
requires less extensive branch prediction algorithms and target buffers—which results in reduced
system logic and system power requirements.




                                   Brought to you by AMD and Windows IT Pro eBooks
                                                                                        Chapter 1 The x64 64-Bit Architecture 9




 A 64-Bit Platform Comparison
 Table A summarizes the differences between the three primary 64-bit platforms available for Windows and SQL Server.

 Table A Comparing Three 64-bit Platforms Available for Windows and SQL Server
 Platform                              Itanum2                           Opteron™                       Xeon EM64T
 Architecture                            IA-64                               x64                            x64
                                          EPIC                               CISC                          CISC
 Clock Speeds                          1.5GHz                          850* 2.4GHz                        3.6GHz
                                       1.4GHz                           848 2.2GHz                        2.8Ghz
                                       1.3GHz                           846 2.0GHz
 SMP Support                            64-way                              8-way                           4-way
 L1 Cache                                32 KB                             128KB                            16KB
                                                                  (64data /64 instructions)
 L2 Cache                             256KB                                  1MB                              1MB
 L3 Cache                              6MB                                   N/A                              N/A
 Maximum RAM                          16TB*                                  1TB                             64GB
                                   64-bit virtual                 48-bit virtual addressing        48-bit virtual addressing
                              addressing and 50-bit                 and 40-bit physical                36-bit physical
                              physical address space                     addressing
 addressing
 System Bus                        PCI / PCI-X                      HyperTransport™                 PCI / PCI Express
 Bus Speed                          400MHz                              800MHz                           800Mhz
 Bus Size                             128-bit                            128-bit                          64-bit
 Bus Throughput                      6.4GBps                             8GBps                           6.4GBps
 Die Size                          .18 micron                          .13 micron                       .9 micron
 64-Bit OS Support         Windows 2000 Advanced                   Windows Server 2003             Windows Server 2003
                           Server (Win2K AS) Limited                   x64 Edition                     x64 Edition
                            Edition; Windows Server
                                2003 for Itanium-
                                  based Systems
 64-Bit Application                IA-64 only                          x64 applications               x64 applications
 Support
 32-Bit OS Support                       No                                    Yes                           Yes
 32-Bit Application                   Using x86                                Yes                           Yes
 Support                              emulation
 * Theoretical limit—512GB is the largest implementation
 * Opteron 100 Series is 1-way; 200 series is 2-way; and 800 series is 8-way



The HyperTransport Bus Detailed
HyperTransport is an open standard, chip-to-chip interconnect system architecture that provides
high-speed I/O capabilities and improved scalability for multiprocessor systems, The HyperTransport
system bus connects directly to the CPU, giving it a high-speed link to the system’s PCI and PCI
Express channels, system I/O, and peripheral Interconnects. AMD’s HyperTransport bus supports a
total throughput of up to 8GBps per connection.


                                                 Brought to you by AMD and Windows IT Pro eBooks
10   Moving SQL Server to the x64 Platform




n Note   The improved HyperTransport system bus uses memory bandwidth to its fullest potential and
         simultaneously reduces memory latency.

    AMD’s HyperTransport technology isn’t a new 64-bit technology. It has been available in the
AMD 32-bit processors for several years. However, with the x64 architecture, the HyperTransport
technology has a more significant system impact than it had with the previous generation of 32-bit
processors. With the x64 HyperTransport system design, memory, I/O, and system devices don’t
compete for bandwidth. This lack of contention for available bandwidth reduces latency. The
HyperTransport technology supports up to three direct CPU 8GBps connections, which provide up to
24GBps peak bandwidth per processor. The net result is improved system stability and performance.

Integrated Memory Controller
With the AMD64 architecture, the memory controller is integrated with the CPU, which eliminates the
need for an external memory controller. The integrated memory controller provides high-speed
bandwidth that reduces latencies during memory access, resulting in enhanced OS and application
performance. This architecture is particularly beneficial to SMP and multicore designs. With AMD’s
integrated memory controller, the available memory bandwidth can scale as the number of processors
increases because each processor has its own memory controller. The memory bandwidth in the
current AMD64 system architecture is 8GBps per processor.

Multicore Support
The HyperTransport system design lets the AMD64 architecture fully exploit the capabilities of the
new dual-core and upcoming multicore system designs. Each CPU uses its own integrated memory
controller to directly access system memory. There is no limiting front-side bus or external memory
controller. Instead, each CPU has a direct pipeline to its own memory, which improves scalability.
The HyperTransport system design in combination with the integrated memory core lets dual-core
and multicore systems achieve higher levels of performance and make more efficient use of system
resources than can be achieved with the legacy front-side bus design.

32-Bit and 64-Bit Application Compatibility
The x64 architecture enables full 32-bit compatibility and native 64-bit operations by supporting two
operating modes: Long mode and Legacy mode. AMD designed Long mode to run native 64-bit OSs
and Legacy mode to run existing 32-bit or 64-bit OSs. Both modes provide binary compatibility with
existing 16-bit and 32-bit applications.

Long Mode
Long mode is used for native 64-bit OSs, such as the Windows 2003 x64 editions. Under Long mode,
only native 64-bit OSs can use the 64-bit mode. Long mode uses native 64-bit addresses and a flat
64-bit address space. To take advantage of the 64-bit extensions, applications must be recompiled
with a 64-bit compatible compiler (e.g., Visual Studio 2005). Any 32-bit applications that run in Long
mode can access only the first 32-bits of the available registers.


                                  Brought to you by AMD and Windows IT Pro eBooks
                                                                           Chapter 1 The x64 64-Bit Architecture 11


Legacy Mode
Legacy mode lets existing 16-bit DOS and 32-bit Windows OSs run unchanged on the 64-bit
processor. In Legacy mode, 32-bit applications run exactly as they run on a 32-bit processor—and
continue to use 32-bit addresses and registers. Legacy mode provides full binary compatibility with
existing 32-bit applications. This ability to run both 64-bit and 32-bit applications is a primary strength
of the x64 platform: You can run 64-bit OSs and applications without sacrificing 32-bit performance.
Table 1.1 presents an overview of the x64 platform’s 32-bit and 64-bit OS application compatibility.

Table 1.1 x64 Platform’s 32-Bit and 64-Bit OS Application Compatibility
                                       Application                                                    General Purpose
                            OS         Recompile           Address          Operand       Register     Register (GPR)
Mode                     Required       Required          Size (Bits)      Size (Bits)   Extensions     Width (Bits)
         64-Bit mode       New            Yes                 64                            Yes              64
Long
         Compatibility    64-Bit                              32                32
mode                                        No                                              No              32
         mode               OS                                16
                          Legacy                              32                32
Legacy mode              32-bit or          No                                              No              32
                                                              16                16
                         16-bit OS

     The x64 platform’s binary 32-bit compatibility gives it an implementation flexibility that might
seem confusing. A system built with the x64 architecture can run either a 32-bit OS and 32-bit
applications—or a 64-bit OS and 32-bit and 64-bit applications. For example, an x64 system can run
either the 32-bit Windows 2003 OS, which supports 32-bit applications, or the 64-bit Windows 2003
x64 OS, which supports both native x64 64-bit applications and 32-bit applications. Figure 1.4
presents an overview of the x64 platform’s 32-bit and 64-bit compatibility.
                                                     Figure 1.4
                                       x64 32-bit and 64-bit overview

                              32-bit                32-bit                    64-bit
                            Applications          Applications              Applications

                             32-bit O/S                   64-bit O/S
                             Windows                Windows Server 2003 x64
                            Server 2003                    Editions

                           32-bit Drivers                     64-bit Drivers



                                                        x64

                                      Brought to you by AMD and Windows IT Pro eBooks
12   Moving SQL Server to the x64 Platform


     In Figure 1.4, you can see how the x64 platform relates to 32-bit and 64-bit OSs and applications.
The 32-bit compatibility of the x64 architecture lets it run 32-bit OSs (e.g., Windows 2003) directly in
Legacy mode. The 32-bit OS runs exactly as if it were running on native 32-bit hardware. It uses
existing 32-bit device drivers to interface with the system hardware, and it supports existing 32-bit
applications (e.g., SQL Server 2003) with no changes whatsoever. The x64 architecture also supports
native 64-bit OSs and applications running in 64-bit Long mode.
     When you run a native x64 OS, such as one of the Windows 2003 x64 editions, the OS requires
native 64-bit device drivers to interact with the system hardware. As you would expect, the 64-bit OS
can run native x64 applications, such as the SQL Server 2005 x64 Standard Edition and the or SQL
Server 2005 Enterprise Edition. In addition, the 64-bit Windows 2003 OS can take advantage of the
x64 32-bit compatibility mode to run 32-bit applications. These 32-bit applications could be one of
the 32-bit versions of server software (e.g., the 32-bit version of SQL Server 2000, the 32-bit version of
SQL Server 2005) or any other 32-bit application (e.g., Microsoft Office). Unlike the 32-bit emulation
found on the Itanium, the 32-bit applications run at full speed with no performance penalty. I present
more information about the x64 editions of Windows 2003, Windows on Windows64 (WOW64), and
SQL Server 2005 in the following section.

Microsoft Support for the x64 Architecture
The first official Microsoft support for the x64 platform came with the April 2005 release of the x64
editions of Windows Server, when the company shipped x64 editions of Windows 2003 Standard,
Enterprise, and Datacenter editions.
     Internally, Microsoft had begun a trial deployment of the x64 platform using a prerelease version
of Windows 2003 x64 edition for the Web servers that comprise its http://www.microsoft.com site. By
April 2005, all of the production Web servers for the site were running on the native 64-bit x64–based
platforms. Microsoft reported several significant performance benefits resulting from the migration to
the 64-bit x64 systems. According to Microsoft’s internal performance measurements, the CPU load on
the servers decreased by 50 percent. In addition, page response times for some applications were
more than 15 times faster. For more information about the Microsoft x64 deployment, go to
http://www.microsoft.com/technet/itsolutions/msit/operations/mscom64bitarchi.mspx Windows server
2003 x64 editions.
     Microsoft used the improved scalability that the x64 architecture provides in the Microsoft
Treasury project. The Microsoft Treasury manages the financial assets and foreign currency exposure
for Microsoft. In this instance, the Microsoft Treasury reduced the time required to run its risk-analysis
software from 40 hours on the 32-bit platform to 13 hours on the x64 platform, a 325 percent
increase in overall system performance. This improvement required no code upgrades and resulted
from the 32-bit applications’ greater memory use and the more efficient system architecture. For more
information about the Microsoft x64 Treasury project, go to http://enterprise.amd.com/downloads/
ms_treasury_en.pdf.
     A number of Microsoft partner projects also demonstrate the scalability and performance
improvements that the x64 architecture enables. CROSSMARK, a point-of-sale (POS) data warehouse,
implemented its 3TB data warehouse on the x64 platform using SQL Server 2005 and achieved a
12-fold increase in data-loading capability with 64-bit Integration Services. For more information
about the CROSSMARK project, go to http://members.microsoft.com/customerevidence/search/
evidencedetails.aspx?evidenceid=13672&languageid=1.

                                   Brought to you by AMD and Windows IT Pro eBooks
                                                                                Chapter 1 The x64 64-Bit Architecture 13


    Bank Leu, a leading Swiss private bank replaced its management account system with a solution
based on the SQL Server 2005 Enterprise Edition x64 and achieved better system responsiveness that
resulted in a 40 percent improvement in management account productivity. For more information
about the Bank Leu project, go to http://members.microsoft.com/customerevidence/search/
evidencedetails.aspx?evidenceid=13764&languageid=1.

Windows 2003 Standard, Enterprise, and Datacenter Editions
As you might expect, the 64-bit x64 editions of Windows 2003 can take advantage of the increased
system capabilities that the x64 platform provides. Table 1.2 compares the maximum capacities of the
32-bit edition of Windows 2003 with the 64-bit edition of Windows 2003.

Table 1.2 Windows 2003 32-Bit and x64 Editions
OS                                         32-Bit Maximums                                   64-Bit Maximums
Windows 2003, Standard Edition             4GB                                               32GB
                                           Up to 4 processors                                Up to 4 processors
Windows 2003, Enterprise Edition           4GB                                               1TB
                                           (64GB using AWE)                                  Up to 8 processors
                                           Up to 8 processors
Windows 2003, Datacenter Edition           4GB                                               1TB
                                           (64GB using AWE)                                  Up to 64 processors
                                           Up to 32 processors

     By increasing the available system memory, the x64 platform solves the virtual memory
limitations that have restricted the scalability of the 32-bit versions of Windows Server. The 32-bit
version of Windows Server is limited to 4GB of addressable memory split evenly between the OS and
applications. You can change this allocation in the 32-bit versions of Windows 2003 by using the
/3GB switch in the boot.ini file. Doing so lets the Windows 2003 OS increase the applications’
memory allocation to 3GB. However, this change also reduces the amount of memory available to
the OS, which can result in performance degradation for kernel-intensive implementations. Table 1.3
compares the memory capacities of the Windows 2003 32-bit edition with those of the Windows 2003
64-bit edition.

Table 1.3 Memory Availability in Windows 2003 32-Bit and x64 Editions
Windows Server Memory Usage                           32-Bit                                       64-Bit
Total virtual address space                           4GB                                          16TB
Virtual address space per 32-bit process              2GB (3GB with /3GB switch)                   2GB (4GB if compiled with
                                                                                                   /LARGEADDRESSAWARE)
Virtual address space per 64-bit process              NA                                           8TB
Paged pool                                            47MB                                         128GB
Nonpaged pool                                         256MB                                        128GB
System Page Table Entry (PTE)                         660MB to 900MB                               128GB

    The 64-bit x64 architecture increases the amount of virtual memory that Windows can address
from 4GB to 16TB. As is true of the 32-bit version of Windows, the virtual address space is split


                                           Brought to you by AMD and Windows IT Pro eBooks
14    Moving SQL Server to the x64 Platform


evenly between the OS and the applications (i.e., between kernel mode and user mode). The net
result is that native 64-bit x64 applications have 8TB of available virtual memory address space.

WOW64
As Figure 1.4 showed, the 64-bit x64 edition of Windows Server can run 32-bit applications with no
changes. The Windows Server mechanism that supports this capability is the WOW64 subsystem.
Although the WOW64 subsystem doesn’t support every type of application, it does let x64 Windows
Server run almost all 32-bit existing Windows applications. The types of applications that aren’t
supported are low-level applications such as antivirus programs or programs that attempt to directly
access system hardware. The 32-bit applications that run in the WOW64 subsystem are executed at
full speed. In fact, because of the larger data path the x64 architecture provides, many 32-bit applica-
tions will actually run faster in the WOW64 subsystem than they would on native 32-bit hardware.
     The WOW64 subsystem isolates the execution of 32-bit applications from 64-bit applications.
However, WOW64 does permit 32-bit to 64-bit application interaction through COM or remote
procedure calls (RPCs). WOW64 prevents file and registry collisions between 32-bit and 64-bit
versions of applications by redirecting the 32-bit registry and file system accesses.
     • File system redirection—All 32-bit file access to the %systemroot%\system32 folder is
        redirected to the %systemroot%\syswow64 folder (the %systemroot%\syswow64 that contains
        a copy of 32-bit Windows system executable programs). This redirection lets a 32-bit
        applications load the correct 32-bit system executables rather than attempting to access the
        64-bit OS executable programs.
     • Registry redirection—Likewise, 32-bit applications that attempt to read or write to the
        HKEY_LOCAL_MACHINE\Software registry subkey are redirected to the
        HKEY_LOCAL_MACHINE\Software\Wow6432Node\ subkey. This redirection lets
        you maintain separate configurations for 32-bit and 64-bit processes.

SQL Server 2005 Standard and Enterprise Editions
When Microsoft released SQL Server 2005 in November 2005, the company provided support for the
x64 platform in the Standard and Enterprise editions. Table 1.4 shows the SQL Server 2005 support
for x64.

Table 1.4 SQL Server 2005 32-Bit and 64-Bit Compatibility
SQL Server 2005 Edition                                  32-bit                        64-bit
SQL Server 2005 Express Edition                          Yes                           WOW64
SQL Server 2005 Workgroup Edition                        Yes                           WOW64
SQL Server 2005 Standard Edition                         Yes                           Yes
SQL Server 2005 Enterprise Edition                       Yes                           Yes

     Both the SQL Server 2005 Standard Edition and the SQL Server 2005 Enterprise Edition provide
native support for the x64 architecture, which means that they must run on the 64-bit Windows 2003
x64 OS. The SQL Server 2005 Express Edition and the SQL Server 2005 Workgroup Edition are 32-bit
only. However, they can use the WOW64 subsystem to run on the 64-bit Windows 2003 x64 edition.



                                     Brought to you by AMD and Windows IT Pro eBooks
                                                                       Chapter 1 The x64 64-Bit Architecture 15



Next: Relational Database Engine
The x64 architecture has ushered in a new era of 64-bit computing. The 64-bit x64 platform steps
over the memory and system I/O limitations found in the previous generation of 32-bit systems,
paving the way to vastly improved system scalability without losing compatibility for existing 32-bit
OSs and applications. Combining significantly improved system scalability and 32-bit application
compatibility with hardware costs comparable to 32-bit systems makes the x64 platform a compelling
choice for organizations as they deploy new systems.
     Chapter 1 has presented an overview of the 64-bit x64 architecture and the benefits it provides.
Chapter 2 will explore the advantages that the 64-bit x64 architecture provides for the SQL Server
relational database engine in more depth. I’ll consider some of the specific areas in which the
Windows 2003 OS and SQL Server can achieve significant performance improvements by moving to
the x64 platform.




                                  Brought to you by AMD and Windows IT Pro eBooks
 Moving
SQL Server

  x64
  to the



  Platform By Michael Otey


                   sponsored by
ii




Contents
Chapter 2: SQL Server 2005 64-Bit Advantages . . . . . . . . . . . . . . . . . . . . 16
     Windows Server 2003 and SQL Server 2005 64-Bit Editions . . . . . . . . . . . . . . . . 16
         64-Bit Relational Database Benefits . . . . . . . . . . . . . . . . . . . . . .                                                             .   .   .   .   .   .   .   .   .   .   .   .   .   .   18
            Database Performance Enhancements . . . . . . . . . . . . . . . . . .                                                                    .   .   .   .   .   .   .   .   .   .   .   .   .   .   19
            Sidebar 1: Powering Up the Enterprise . . . . . . . . . . . . . . . . .                                                                  .   .   .   .   .   .   .   .   .   .   .   .   .   .   20
                Faster Query Performance . . . . . . . . . . . . . . . . . . . . . . . .                                                             .   .   .   .   .   .   .   .   .   .   .   .   .   .   21
                Faster Statement Execution and Improved Engine Efficiency                                                                            .   .   .   .   .   .   .   .   .   .   .   .   .   .   22
                Faster Indexing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                                                        .   .   .   .   .   .   .   .   .   .   .   .   .   .   22
                Faster SQL Operations . . . . . . . . . . . . . . . . . . . . . . . . . .                                                            .   .   .   .   .   .   .   .   .   .   .   .   .   .   22
                Support for More Concurrent Users . . . . . . . . . . . . . . . . .                                                                  .   .   .   .   .   .   .   .   .   .   .   .   .   .   23
                Greater System Throughput . . . . . . . . . . . . . . . . . . . . . . .                                                              .   .   .   .   .   .   .   .   .   .   .   .   .   .   23
                Improved Availability . . . . . . . . . . . . . . . . . . . . . . . . . . .                                                          .   .   .   .   .   .   .   .   .   .   .   .   .   .   23
     x64 Performance System Benchmarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
         TPC-C Benchmarks . . . . . . . .        .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   24
            TPC Benchmark Scores . . .           .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   24
               4-P Server TPC-C Scores           .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   24
               2-P Server TPC-C Scores           .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   25
         SAP SD Users Benchmarks . . .           .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   26
            Microsoft SAP Conversion .           .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   28
     Next: Business Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
16


Chapter 2:

SQL Server 2005 64-Bit Advantages
Sixty-four-bit (x64) is seen as the natural progression for next generation computing. The architectural
advantages that 64-bit computing offers can be particularly beneficial to several application workloads.
The server applications that probably derive the most benefit from running on a 64-bit platform (i.e.,
a 64-bit server running a 64-bit OS) are relational database and business intelligence (BI) workloads.
The servers that run these workloads often process very large data sets and benefit greatly from the
added memory capacity and improved I/O bandwidth that the 64-bit platform offers. In this chapter, I
discuss the benefits of running Microsoft SQL Server 2005 on a 64-bit platform. I review the hardware
and the Windows Server 2003 OS editions required to run the 64-bit versions of SQL Server. I then
consider specific areas in which the SQL Server relational database benefits from the 64-bit platform.

Windows Server 2003 and SQL Server 2005 64-Bit Editions
Beginning with the release of Windows 2003 x64 in April 2005, Microsoft has put its full development
weight behind 64-bit computing. This support continued unabated with the release of SQL Server
2005, which offered native x64 editions of both SQL Server 2005 Enterprise Edition and the SQL
Server 2005 Standard Edition. The upcoming release of Exchange 12 clearly shows Microsoft’s
direction for its server products: The Exchange 12 product drops 32-bit support and will be available
only as a native x64 application.
     As Microsoft moves its server products toward 64-bit computing, you can expect to see the
company dropping 32-bit support in favor of x64 versions. Although this move might seem surprising,
several reasons support it. First, top-tier hardware manufacturers have already adopted 64-bit com-
puting; even today, the majority of available server systems are 64-bit capable. Also, the 32-bit binary
compatibility that AMD64 technology offers can take advantage of the increased hardware capabilities
yet remain completely compatible with existing applications. In addition, the x64 platform provides a
number of advantages over the older 32-bit platform—especially for servers. Because server products
service multiple users, they typically have much higher memory and I/O requirements than desktop
applications. The 64-bit architecture directly addresses both of these needs. Although the move to the
x64 64-bit platform might seem like a huge leap, the new platform isn’t that different from the 32-bit
platform. And the AMD64 platform’s compatibility with 32-bit software—which doesn’t decrease
performance—makes it a risk-free move.
     Both Windows 2003 and SQL Server 2005 are available in 32-bit and 64-bit versions. The 32-bit
versions run on traditional x86 or x64 hardware. For 64-bit support, you have options: Windows 2003
and SQL Server 2005 support the 64-bit Itanium, EM64T, and AMD64 platforms. Table 2.1 summarizes
the 64-bit support you’ll find in the current editions of Windows 2003 and SQL Server 2005.




                                  Brought to you by AMD and Windows IT Pro eBooks
                                                               Chapter 2 SQL Server 2005 64-Bit Advantages 17


Table 2.1 Windows 2003 and SQL Server 2005 64-Bit Support
                                                            32-Bit                               64-Bit
Product                                                     x86                        Itanium            x64
Windows 2003 Datacenter Edition                              X                            X               X (R2)
Windows 2003 Enterprise Edition                              X                            X               X (SP1)
Windows 2003 Standard Edition                                X                                            X (SP1)
Windows 2003 Web Edition                                     X
SQL Server 2005 Enterprise Edition                           X                           X                X
SQL Server 2005 Standard Edition                             X                           X                X
SQL Server 2005 Workgroup Edition                            X
SQL Server 2005 Express Edition                              X



n Note    To take full advantage of the 64-bit hardware to run one of the 64-bit editions of SQL Server
          2005, you must have a 64-bit OS.

     You can choose to move to Itanium-based hardware, which requires Windows 2003 Datacenter
Edition or Windows 2003 Enterprise Edition and the Itanium version of SQL Server 2005 Enterprise
Edition or SQL Server 2005 Standard Edition. Or you can move to x64 hardware (e.g., servers running
AMD Opteron™ processors) and run the Windows 2003 Datacenter Edition, Windows 2003 Enterprise
Edition, or Windows 2003 Standard Edition coupled with the 64-bit version of SQL Server 2005
Enterprise Edition or SQL Server 2005 Standard Edition—and get all of the 64-bit benefits.
     The x64 architecture’s full 32-bit binary compatibility lets it run 32-bit OSs and applications or
64-bit OSs with 32-bit and 64-bit applications. The 32-bit applications can run on either a 32-bit OS or
a 64-bit OS by using Windows on Windows64 subsystem (WOW64) support. (For more information
about the x64 platform’s ability to run both 32-bit and 64-bit applications and WOW64, see Chapter
1.) The key point to remember is that 64-bit applications require a 64-bit OS. All editions of Windows
2003 except the Windows 2003 Web Edition support both the 32-bit and the 64-bit platforms.
Windows 2003 Web Edition is 32-bit only.


n Note    Only Windows 2003 Datacenter Edition and Windows 2003 Enterprise Edition offer 64-bit
          Itanium support.

     SQL Server 2005 Enterprise Edition and SQL Server 2005 Standard Edition are available for 32-bit
editions of Windows 2003 as well as for 64-bit Itanium and 64-bit (AMD64 and EM64T) editions of
Windows 2003. The lower-end SQL Server 2005 Workgroup Edition and SQL Server 2005 Express
Edition are available only as 32-bit editions. Therefore, you have two options for running SQL Server
2005 as a native 64-bit application and two options for running the 32-bit SQL Server 2005 (x86 or
x64 using WOW64). Table 2.2 lists these options.




                                     Brought to you by AMD and Windows IT Pro eBooks
18    Moving SQL Server to the x64 Platform


Table 2.2 Matching 32-Bit and 64-Bit Windows 2003 and SQL Server 2005
Processor   Operation System                              SQL Server Edition Supported
x64         Windows 2003 Release 2 (R2)                   SQL Server 2005 Enterprise Edition x64 (64-bit)
             Datacenter Edition x64 (64-bit)              SQL Server 2005 Standard Edition x64 (64-bit)
            Windows 2003                                  SQL Server 2005 Enterprise Edition (32-bit) using WOW64
             Enterprise Edition x64 (64-bit)              SQL Server 2005 Standard Edition (32-bit) using WOW64
            Windows 2003                                  SQL Server 2005 Workgroup Edition (32-bit) using WOW64
             Standard Edition x64 (64-bit)                SQL Server 2005 Express Edition (32-bit) using WOW64
Itanium     Windows 2003 Datacenter Edition               SQL Server 2005 Enterprise Edition for Itanium
             for Itanium                                  SQL Server 2005 Standard Edition for Itanium
            Windows 2003 Enterprise Edition
             for Itanium
x86         Windows 2003 Datacenter Edition (32-bit)      SQL Server 2005 Enterprise Edition (32-bit)
            Windows 2003 Enterprise Edition (32-bit)      SQL Server 2005 Standard Edition (32-bit)
            Windows 2003 Standard Edition (32-bit)        SQL Server 2005 Workgroup Edition (32-bit)
                                                          SQL Server 2005 Express Edition (32-bit)



     Licensing is another important consideration for all organizations. Microsoft licensing for 64-bit
editions of all of its products is just like licensing for the 32-bit product editions. Therefore, no
financial hurdles should prevent businesses from taking advantage of the benefits of the 64-bit
platform. Microsoft has committed to licensing OSs by CPU (not by cores), making the move to
dual-core CPUs (e.g., AMD Opteron) more attractive.

64-Bit Relational Database Benefits
The 64-bit versions of SQL Server 2005 Enterprise and SQL Server 2005 Standard editions can achieve
higher levels of scalability than the previous 32-bit editions of these products could achieve. A
primary reason for the improved scalability is an increase in available physical and virtual memory.
Relational databases such as SQL Server 2005 must often execute queries and other SQL statements
that work with very large amounts of data. The larger memory address space that 64-bit processors
provide lets SQL Server process larger datasets in memory—thereby avoiding expensive and slow I/O
operations. Disk access is always the slowest operation that relational database servers perform. (RAM
memory access is hundreds of times faster.) Increasing the server’s available memory lets it process a
greater portion of its workload in memory, which significantly reduces the number of times it needs
to perform disk I/O. With memory prices dropping, adding more memory to a server becomes more
economical than adding disk drives.
     The 32-bit x86 architecture is limited to a maximum of 4GB of memory. Although that amount
might be adequate for most desktop applications or smaller scale databases, 4GB isn’t adequate to
support larger relational databases applications. Also, running on 32-bit Windows, the actual address-
able memory for applications (e.g., SQL Server) is only 2GB; the other 2GB is reserved for the OS.
     Microsoft developed a technology called Address Windowing Extensions (AWE) that lets
AWE-enabled applications access up to 64GB on Windows Datacenter Server. Many SQL Server
installations have adopted AWE because of the increased memory support that it provides to SQL
Server. (For more information about the limitations of 32-bit Windows and AWE, see Chapter 1.) In
summary, AWE is restricted because it

                                     Brought to you by AMD and Windows IT Pro eBooks
                                                             Chapter 2 SQL Server 2005 64-Bit Advantages 19


    • has no OS benefits—Only applications can access AWE memory. It does nothing to let the OS
      access more memory.
    • requires specially written applications—Only AWE-enabled applications (applications written
      expressly to support AWE) can take advantage of the increased memory. Other applications
      get no benefits.
    • incurs performance overhead—AWE uses a paged memory access scheme whose performance
      is inferior to that of typical flat memory access. During checkpoint intervals, databases take up
      processor time; any overhead to the processor (e.g., AWE) will only slow down system
      performance
    • offers limited SQL Server benefits—Even though SQL Server supports AWE, only specific areas
      (e.g., the buffer cache) can use the increased memory AWE provides. Other areas (e.g., the
      procedure cache) receive no benefits from AWE. Likewise, other subsystems (e.g., Analysis
      Services, Integration Services, Reporting Services) aren’t AWE-enabled and receive no benefits
      from AWE.

     Running on Windows 2003 Datacenter, Enterprise, or Standard editions, the 64-bit processors let
SQL Server 2005 Enterprise and Standard editions break through the 4GB memory ceiling that the
32-bit version of Windows imposes. By using the AMD Opteron processor, the system can access up
to 1TB of physical and 256TB of virtual memory. The increased memory capacity lets SQL Server
2005 handle larger databases and more complex application requirements. In addition, the more
modern Direct Connect Architecture that the AMD Opteron processor provides (through the use of
HyperTransport™ technology for high-speed interconnect capabilities) also enhances other aspects of
system performance—reducing memory latency and improving I/O performance. HyperTransport
technology is an open-standard, chip-to-chip interconnect that provides high-speed I/O capabilities
and improved scalability for multiprocessor systems. Let’s look more in depth at some of the specific
ways in which SQL Server 2005 benefits from the 64-bit implementation.

Database Performance Enhancements
Many elements of database performance benefit from the 64-bit implementation. For example, queries
submitted to the database engine derive substantial benefits from the increased memory available for
in-memory buffer caching. Extended plan caching benefits statement execution. Index creation is
improved by allowing larger in-memory data sorting. The increased memory also lets various types of
sort conditions execute more efficiently, and it supports a higher maximum number of concurrent
users. Processor parallelism can better support multithreaded workloads. In the next section, I’ll
consider in more detail how the x64 architecture benefits these and additional areas.


n Note   Although you might not expect it, the 64-bit architecture can also provide availability and
         infrastructure benefits. You can reduce power consumption while maintaining higher
         performance levels, which effectively lowers datacenter operational costs by reducing power
         and cooling requirements. For more information about power and infrastructure benefits, see
         the sidebar “Powering Up the Enterprise.”



                                   Brought to you by AMD and Windows IT Pro eBooks
20    Moving SQL Server to the x64 Platform




                                     Powering Up the Enterprise
 Many of today’s Microsoft SQL Server systems use rack-mounted servers that are part of a larger IT infrastructure.
 Using rack-mounted hardware has considerable advantages in terms of both space and manageability. By their
 nature, rack-mounted installations require significantly less floor space because the rack-mount unit efficiently
 groups multiple 1U, 2U, and 4U systems together in an integrated enclosure. In terms of manageability, the rack
 provides channels for running the required cabling. A Power Distribution Unit (PDU) typically supplies power.
       Although you achieve significant operational advantages from using rack-mounted enclosures, your
 organization needs to address some important considerations, especially in planning for future growth. First, in
 terms of physical capacity, the standard rack is 42U, which places an overall ceiling on the amount of computing
 power you can place in the rack. However, before you hit that ceiling, it’s far more likely that you’ll exceed the
 PDU’s capacity. Equally important is the fact that adding servers will create a corresponding increase in heat
 generated, which direct affects the amount of cooling equipment required to maintain the desired operating
 environment. As more equipment is required, operational and infrastructure costs rise correspondingly. On the
 operations front, you face direct added electrical costs for servers and cooling as well as indirect costs for IT
 support. Additional infrastructure costs stem from software licensing for additional servers and UPS requirements.
 When you exceed rack capacities, you face higher infrastructure costs including the cost of rack enclosures and the
 need for added floor space.
       As computing requirements increase, the organization must attempt to minimize the number of servers
 required to meet its demands. The x64 platform lets the enterprise more effectively use its existing rack-mounted
 infrastructure by simultaneously increasing the available processing power per rack while maintaining or
 decreasing the related infrastructure requirements and associated costs. Figure A shows how the x64 platform
 addresses these requirements.
       Figure A compares the
 performance and power               120,00
                                            0
 characteristics of several 2-
                                                                                                                               0
 way x64 systems. The                100,00
                                            0
                                                                          109633                                        120,00
 performance was gauged                                                     144%
                                                                                                                               0
                                       80,000                                                                           100,00
 using the Transaction
 Processing Performance                60,000                                        76214                              80,000
 Council’s (TPC’s) TPC-C                                                             100%      74298
 OLTP benchmark, which                 40,000                                                   97% 71413               60,000
 measures the system                                                                                  94% 68010
                                       20,000                                                               89%         40,000
 throughput of database
 servers. The overall                                     95                                                            20,000
                                            0            Watts        95
 processing power that                                              Watts        120
 the dual-core Opteron         2-P Servers                                      Watts       95                          0
                                                                                          Watts 111
 Model 280 system                                    80
                                                  l2             4                                Watts
                                               de             25
 provides resulted in a                     Mo e           el            L2
                                         on or          od
                                                       M e             B               2
 44 percent                           ter l-C      on or            2M
                                                                                 el
                                                                                    25
                                    Op Dua      ter le-C         Hz ore       od            B
                                                                                              L2
 improvement over              AM
                                  D           Op g             G C
                                                           3.6 gle-        n M ore       1M
                                            D Sin                        o              z re               Figure A
 similar single-core                    AM              on in         ter e-C       GH Co
                                                      Xe S         Op gl
                                                                  D Sin       n 3.6 gle-           Comparing x64 Opteron and
 Opteron systems while                                        AM            eo Sin
                                                                    X
                                                                                            Xeon power/performance

                                          Brought to you by AMD and Windows IT Pro eBooks
                                                                Chapter 2 SQL Server 2005 64-Bit Advantages 21



 having the same 95-watt power profile. The
 dual-core Opteron Model 280 system achieved                  AMD                        Xeon             Xeon
 an even greater (47 percent) advantage over the          Opteron     ™

 single-core Xeon processors with a 2MB cache.             (Dual-Core)
 However, at 120 watts, the Xeon requires 21
 percent more power. By comparing single-core
 systems alone, you can see that the single-core
 Opteron Model 254 provides 11 percent better
 performance than the Xeon with a 1MB L1
                                                                                        Power         Additional
 cache. Although the 1MB configuration uses                                            Maxed              Rack
 less power, it still requires 111 watts, 15 percent                                     Out           Required
 more than the Opteron processor. You can find
 more SQL Server and 64-bit performance
 benchmarks at http://www.tpc.org.
       Figure B shows how the x64 platform’s
 power/performance advantages translate into
 infrastructure benefits.
       Both the Opteron and the Xeon
 configurations that Figure B shows provide
                                                                             42U
 equivalent levels of computing power. The
                                                                            Racks
 dual-core Opteron configuration with its higher              17                        23             8
 processing power per system can deliver the
 same overall computing power using 17 servers
 that the Xeon platform delivers with 31 servers.
 The more powerful platform reduces IT                                            Figure B
 infrastructure costs by requiring fewer servers to         Comparing x64 Opteron and Xeon rack and
 perform the same overall workload, which                                   power requirements
 translates into lower hardware costs, lower
 software licensing costs, and reduced IT management requirements. The higher power requirements of the Xeon
 configuration coupled with the lower processing power per server causes it to exceed the power capacities of a
 single rack. The configuration requires an additional rack to handle the same computing workload. The two-rack
 configuration has higher power, cooling, and floor-space requirements. The added number of servers results in
 higher hardware and software costs as well as increased IT administrative requirements.



Faster Query Performance
Sixty-four-bit computing offers a larger addressable memory space, which SQL Server 2005 can use to
increase the size of its buffer cache. The increased size of the buffer cache is probably the single area
that benefits SQL Server most. (The need for buffer cache space is the reason that 32-bit AWE
support focused on increasing the available buffer cache size.) The SQL Server relational database
engine’s buffer cache contains recently used data as well as read-ahead data that the SQL Server
engine anticipates using. When the SQL Server relational database engine executes a query, it first
looks in the buffer cache for data to satisfy that query. The larger buffer cache lets SQL Server store

                                      Brought to you by AMD and Windows IT Pro eBooks
22   Moving SQL Server to the x64 Platform


more data in and pull more data from the buffer cache, which helps eliminate the need for disk I/O.
The more memory the buffer cache has available, the more effectively the SQL Server caching
mechanism works. The net result is less disk I/O and faster query performance.

Faster Statement Execution and Improved Engine Efficiency
With the 32-bit platform, AWE support was limited to the buffer cache. With the x64 platform, many
system components can benefit from the added memory that the platform makes available. One of
the most important of those components is the SQL Server procedure cache. Whereas the buffer
cache contains data, the procedure cache contains query plans for recently executed T-SQL
statements and stored procedures. Because of the dynamic nature of database requests, old execution
plans must be continually deleted from the procedure cache. The greater memory capacity of the
64-bit architecture (and the integrated memory controller in the AMD Opteron processor that further
increases bandwidth, reduces memory latencies, and increases processor performance) lets SQL Server
create a larger procedure cache and hold more execution plans. The increased number of execution
plans enables faster statement execution for cached plans because no additional time is required to
recreate the query plan. Finding more execution plans in the cache reduces demand on the CPU
because fewer new execution plans need to be compiled.

Faster Indexing
Index creation is one SQL Server relational database engine operation that benefits from added
memory. SQL Server supports clustered, nonclustered, and full-text indexes. The creation process for
all of three types of indexes benefits from the expanded addressable memory that 64-bit processors
(e.g., the AMD Opteron™ processor) make available. With the memory controller running at
processor speeds, as it’s integrated into the AMD Opteron processor, memory latency is further
reduced and indexing capabilities are improved. When an index is created, the column data that
comprises the index is brought into memory and sorted. The increased amount of available memory
lets more of the sort be performed in memory without the need to write intermediate results to disk.
This increase in available memory is even more important for full-text indexes because they typically
use much more data. Early Microsoft customer deployments of full-text searches on the x64 platform
have shown tremendous gains when rebuilding full-text indexes.

Faster SQL Operations
In addition to benefits to the SQL Server engine itself, the x64 architecture also provides performance
enhancement for specific SQL operations. Hash joins, aggregates, sorting, and server-side cursors are
all SQL operations that can derive significant benefits from the x64 platform’s added memory. Hash
joins are fast and highly memory-intensive. More available memory lets the system execute hash joins
more freely rather than being forced into slower but less memory-demanding execution plans.
Aggregates and sorting perform significantly better with more working memory because they often
use temporary storage to process large data sets. Because temporary storage in turn requires disk I/O,
you can drastically increases the time and system requirements needed to satisfy the operation.
Server-side cursors are memory- and system resource–intensive operations that can benefit from a
move to 64-bit platforms running AMD Opteron processors.




                                  Brought to you by AMD and Windows IT Pro eBooks
                                                             Chapter 2 SQL Server 2005 64-Bit Advantages 23


Support for More Concurrent Users
SQL Server must also allocate system memory for each open database and each user connected to
the system. Because the number of databases is finite and typically a known quantity, database
allocations are seldom an issue. However, that’s not always the case with user connections—
especially for systems that service Web applications for which the number of concurrent user
connections can be enormous and isn’t predictable. Each user connection requires the system to
create a user context that’s stored in memory. The increased memory capacity of the 64-bit processors
lets the system create more simultaneous users contexts—thereby increasing the number of
concurrently connected users the server can support.

Greater System Throughput
The AMD64 architecture has improved I/O throughput for greater multitasking and threading capabili-
ties. In particular, AMD64 architecture provides a wider data path that can benefit both 32-bit and
64-bit applications. Plus, the improved bus AMD64 Direct Connect Architecture enhances overall
system performance by providing multiple 8GBps connections between CPUs and system I/O
channels using HyperTransport technology. This enhanced architecture lets more data move through
the system in shorter periods of time, which increases system scalability. The dual-core capabilities of
the Direct Connect Architecture also improves the system’s ability to perform parallel processing,
which is used effectively in simultaneous data loading from multiple data sources as well as in
parallel database backup and recovery operations.

Improved Availability
When implemented as part of a high-availability Microsoft Windows Clustering Services solution, the
x64 platform can also improve availability. The greater x64 system performance reduces the time to
complete a failover operation, making your system available again sooner.

x64 Performance System Benchmarks
In the database industry, benchmarks are serious business. They provide information to help you
make investment decisions when planning for future growth. Vendors (e.g., Microsoft, IBM, Oracle)
spend hundreds of thousands of dollars putting together sets of hardware and teams of IT profes-
sionals to demonstrate system scalability. DBAs and database developers sometimes question the
value of these benchmark scores, claiming that the scores aren’t applicable to their environments.
They say that the systems used are typically far and above the average database server hardware
platform, that the software doesn’t perform the same workload that their organization runs, and that
the disk subsystem of the systems used in these tests is typically configured for ultimate performance
using RAID 0 (rather than configured with RAID 1 or RAID 5 like a more typical production system,).
Although their comments are often accurate, they don’t negate the value of benchmarking systems.
     System benchmarks are the only useful way to compare the performance of different hardware
and database system configurations, especially when industry standards associations develop and
audit the benchmark tests. Although many vendors aim for the top TPC-C scores, other benchmark
scores focus on 1-way, 2-way, and 4-way systems. The latter benchmark scores illustrate the
scalability of midrange systems like those you probably run in your environment. Although the
benchmark workload of the systems tested won’t be identical to your organization’s workload, you
can use the benchmark scores to fairly compare tested system configurations. It’s almost certain that

                                   Brought to you by AMD and Windows IT Pro eBooks
24   Moving SQL Server to the x64 Platform


the hardware/software configuration that performed better in the benchmark will also perform better
in your environment. And, given that the systems tested tend to use the highest possible performing
benchmark, it’s safe to conclude that the benefits would apply equally—and that even if the systems
were to use real-world disk configurations, the relative ranking of the benchmarked systems wouldn’t
change.

TPC-C Benchmarks
Comprised of the leading hardware and software vendors—including AMD, HP, IBM, Intel, Microsoft,
NEC, Oracle, Sun Microsystems, Unisys and others—the Transaction Processing Performance Council
(TPC) designed the TPC-C benchmark. The involvement of all of the major hardware and software
vendors helps ensure that the benchmarks provide an even playing field for all participants.
     The TPC-C benchmark tests are designed to demonstrate the high-end scalability of a database
system. They can also successfully demonstrate the price-performance of a database system by
drawing a correlation between the performance scores and the total cost of the system tested. Both
results are of interest to organizations that want to run the fastest possible system they can afford.
     The TPC-C benchmark measures sustained system performance. It does so by simulating an
Online Transaction Processing (OLTP) order entry/shipping application through which multiple users
execute transactions against a database. The database transactions include activities such as entering
new orders, checking order status, delivering shipments, adjusting stock levels, and recording
payments. The performance measurement used in the TPC-C benchmark is the transactions per
minute (tpmC) score. The tpmC is essentially a measurement of how many new orders users can
enter into the system while in the background the system performs a given workload of other
activities, simulating a realistic business environment. For this reason, the tpm-C metric is considered a
measure of business throughput. Although tpm-C is the overall performance metric, $/tpm-C indicates
how much performance each $ provides relative to other systems. You can find more details about
the TPC-C benchmark at http://www.tpc.org/tpcc/detail.asp.

TPC Benchmark Scores
The TPC-C scores show how each database system reacts under a comparable load, stressing the
processor, I/O, and network, with database software implemented. The TPC organization uses input
from all its members to design the test environment, making the test fair to different database
products. The vendors conduct the tests, which lets each vendor apply its technical skills in full
measure. Clearly, the expertise is present to install the system and fine-tune the database and
hardware. An independent TPC-certified reviewer then audits the results.

4-P Server TPC-C Scores
One of the most pertinent TPC scores for business is the top 4-way score. Many organizations in
need of affordable high-performance systems implement 4-way database servers. The highest 4-way
TPC-C performance ratings achieved have deployed AMD Opteron Model 880 processors. Figure 2.1
offers an overview of some of the top TPC-C scores for 4-way systems.




                                   Brought to you by AMD and Windows IT Pro eBooks
                                                                                     Chapter 2 SQL Server 2005 64-Bit Advantages 25



                                                                         Figure 2.1
                                                         TPC-C scores for 4-P servers

                250,00
                            0
                                                                 202557
                                                                                                                                  0
                200,00
                                                                                  197669                                    250,00
                            0                                                          187296
                                                                                                                                  0
                150,00                                                                               161217                 200,00
                            0                                                                             150704
                                                                                                                                  0
                100,00
                                                                                                               138846       150,00
                            0                                                                                      136111
                                                                                                                                  0
                                                                                                                            100,00
                                             $/tmpC
                                              $2.40




                     50,000
                                                       $/tmpC




                                                                                                                            50,000
                                                        $3.93


                                                                $/tmpC


                                                                         $/tmpC
                                                                 $2.04




                            0
                                                                          $3.94


                                                                                   $/tmpC
                                                                                    $6.00



                                                                                            $/tmpC
                                                                                             $7.02

          4-P Servers                                                                                                       0
                                                                                                      $/tmpC
                                                                                                       $3.04
                                     880
                                el              Hz
                               d               G       75
                            Mo             1.9      l8          L3
                        n         r5             de        MB            L2
                    ero       w e              o                       B            4
               Op
                 t
                            Po            on
                                             M          z9         1M            85        L3
                          M          te r          G H           z          d el
         AMD           IB       Op             1.6          6 GH         Mo           6M
                                                                                         B
                                             2
                              D
                                         ium            3.6         ron           Hz
                           AM         an          MP            pte          .5G
                                   It          on
                                                              O            1
                                            Xe         AM
                                                           D            m2
                                                                    niu
                                                                Ita


    The top 4-way score was set using an HP ProLiant DL585 configured with 4 AMD dual-core
Opteron processor Model 880 and 128GB of memory. The system used Windows 2003, x64
Enterprise Edition with Service Pack 1 (SP1); SQL Server 2005 x64 Enterprise Edition was the database
server.
    This TPC-C benchmark marks the first time any 4-way server—including other, more expensive
64-bit platforms such as IBM’s Power5 RISC processors and Intel’s Itanium2 processors—has broken
the 200,000 tpmC mark. Just as important is the overall cost of the system. Although the scores set
using the x64 Opteron servers were higher than those of the other 4-way systems, the tpm-C was 40
percent lower than that of the next-closest system in performance.

2-P Server TPC-C Scores
Perhaps an even more important TPC-C score is the 2-way system benchmark. More businesses
implement SQL Server on 2-way systems than on any other type of hardware platform. Figure 2.2
presents the results of the top 2-way TPC-C scores.




                                                      Brought to you by AMD and Windows IT Pro eBooks
26   Moving SQL Server to the x64 Platform



                                                              Figure 2.2
                                                 TPC-C scores for 2-P servers

                120,000


                100,000

                  80,000


                  60,000


                  40,000


                  20,000


                         0
       2-P Servers                        0                      4            L2                   2
                                    l   28               l   25                                  25                L2
                                 de                   de                 MB               de
                                                                                             l
                                                                                                            1M
                                                                                                               B
                             Mo                   Mo                 z2                 Mo
                        on                   on                   H                 n                  Hz
                    ter                  ter                 3.6G             t ero               3.6
                                                                                                      G
                  Op                Op                  on                 Op                on
             MD               MD                    Xe                MD                 Xe
            A                A                                       A


    For 2-way systems, another x64 system holds the top TPC-C score: the HP ProLiant DL385 using
two AMD Opteron Model 280 processors with 32GB of RAM. This system ran the Windows 2003
Enterprise Edition and SQL Server 2005 Enterprise Edition. The scores are 47 percent higher than
those of comparable Xeon-based systems.

SAP SD Users Benchmarks
Although the TPC scores show relative system scalability and price/performance, another important
benchmark is the SAP Sales and Distribution (SD) Users benchmark. SAP is one of the most widely
used ERP software applications. The SD Users benchmark indicates the total number of concurrent
users that a system will support and the overall performance of a system running an ERP application
with a database server back end. For the SD Users benchmark, clients create an order with five
transactions, create a delivery for the order, display the customer order, change the delivery date, list
40 orders, and create an invoice. Figure 2.3 shows the result of the top 4-way systems running the
SAP SD Users benchmark.



                                              Brought to you by AMD and Windows IT Pro eBooks
                                                              Chapter 2 SQL Server 2005 64-Bit Advantages 27



                                                 Figure 2.3
                             SAP SD Users top ten results for 4-P servers

                        HP Proliant™ DL585
        w/AMD Opteron Processors Model 875
                         ™                                                                                1772
                           HP Proliant BL45
         w/AMD Opteron Processors Model 875
                                                                                                     1716
                          HP Proliant DL585
         w/AMD Opteron Processors Model 865                                                   1545
                       FSC Primergy™ RX800 S2
                     w/Dual-Core Xeon™ 3.0GHz
                                                                                           1345
                     IMB eServer™ p5 Model 570
                            w/Power5™ 1.9GHz
                                                                                           1313
                Egenera pBlade™ 950-000096
         w/AMD Opteron Processors Model 875                                                1310
                             HP Proliant DL580
                      w/Dual-Core Xeon 3.0GHz                                       1090
                         FSC Primergy TX600 S2
                      w/Dual-Core Xeon 3.0GHz                                       1080
                    Dell PowerEdge™ Model6850
                     w/Dual-Core Xeon 3.0GHz                                        1050
                          Sun Fire™ Model V490
                    w/UltraSPARC™ IV+ 1.5GHz
                                                                                    1050
                                                          0              450         900          1,350      1,800




     These results are for the top ten 4-P servers running the SAP SD Users benchmark. They clearly
demonstrate that the x64 platform provides the highest performance of any 4-way x86-compatible
system. HP ProLiant systems using dual-core AMD Opteron processors achieved the top three results.
These performance levels exceed those of IBM’s much more expensive Power5 RISC processor as
well as Sun’s proprietary UltraSparc processor.
     Figure 2.4 shows the results for the top 2-way systems running the SAP SD Users benchmark.




                                  Brought to you by AMD and Windows IT Pro eBooks
28   Moving SQL Server to the x64 Platform



                                                 Figure 2.4
                             SAP SD Users top ten results for 2-P servers

        IBM eServer™ Blade Center Model LS20
       w/AMD Opteron™ Processors Model 280                                                        995
                        HP Proliant™ DL385
        w/AMD Opteron Processors Model 275
                                                                                                 983
                         HP Proliant BL25p
        w/AMD Opteron Processors Model 275                                                       974
                         HP Proliant BL35p
        w/AMD Opteron Processors Model 275
                                                                                                 949
                      FSC Primergy™ RX220
        w/AMD Opteron Processors Model 275
                                                                                            870
                        HP Proliant DL380 G4
                    w/Dual-Core Xeon™ 2.8GHz                                              788
                         HP Proliant ML370 G4
                               w/Xeon 3.6GHz                                        597
                           IBM eServer 326
        w/AMD Opteron Processors Model 252                                          588
         IBM eServer Blade Center Model HS20
                              w/Xeon 3.6GHz                                         588
                         HP Proliant DL385
        w/AMD Opteron Processors Model 252
                                                                                    584
                                                        0              450          500    750          1,000




     The top ten results for 2-P systems provides even more compelling evidence of the effectiveness
of the x64 platform. HP ProLiant systems running dual-core AMD Opteron processors hold the top
four scores for all 2-way systems. The fifth score is held by an FSC system also using Opteron
processors. And, in fact, the systems that round out the top ten 2-way scores are all x64-compatible
systems.

Microsoft SAP Conversion
Microsoft is itself an example of how the adoption of the x64 platform boosted the sales functionality
of a SAP ERP implementation. Microsoft’s SAP implementation supports more than 55,000 employees,
plus partners and customers. Roughly 2300 employees use SAP full-time, and it supports core finance,
supply chain, and HR functions. With the company’s existing 32-bit servers near capacity, in early
2004, Microsoft began a conversion of its SAP ERP system to the 64-bit platform. For servers,
Microsoft chose the HP DL585 using AMD Opteron processors running Windows 2003 Enterprise
Edition and SQL Server 2005. In the process, Microsoft consolidated from nine application servers to



                                  Brought to you by AMD and Windows IT Pro eBooks
                                                             Chapter 2 SQL Server 2005 64-Bit Advantages 29


six servers. A single 4-way x64 server created 40 percent more sales orders in the first hour than two
8-way servers. In addition, it completed 12 batch jobs 38 percent faster than the two 8-way servers.

Next: Business Intelligence
Chapter 2 has shown some of the advantages that the x64 architecture provides for SQL Server 2005
as a relational database engine. Chapter 3 will explore in detail the benefits that the x64 architecture
provides to BI workloads running on SQL Server 2005. You’ll learn about some of the performance
and scalability enhancements that the x64 architecture can bring to Analysis Services, Integration
Services, and Reporting Services, as well as some of the benefits that the new multicore processor
technology offers.




                                   Brought to you by AMD and Windows IT Pro eBooks
 Moving
SQL Server

  x64
  to the



  Platform By Michael Otey


                   sponsored by
                                                                                                                                                                                              iii




Contents
Chapter 3: Sixty-Four-Bit Business Intelligence Advantages . . . . . . . . . . . . 30
  SQL Server 2005 x64 Editions and BI Features . . . . . . . . . . . . . . . . . . . . . . . . . . 31
  Sidebar: Dual-Core and Multiple-Core Processors . . . . . . . . . . . . . . . . . . . . . . . . 32
      Dual-Core Benchmarks . . . . . . . . . . .           ...   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   32
         SPECjbb2000 and WebBench . . . .                  ...   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   32
         SAP SD Users Windows 4-P Servers                   ..   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   33
         TPC-C . . . . . . . . . . . . . . . . . . . .     ...   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   34
      Multiple-Core . . . . . . . . . . . . . . . . . .    ...   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   35
  Sixty-Four-Bit BI Benefits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
      Thirty-Two-Bit BI Limitations . . . . . . . . . . . . . . . . . . . . .                                ...........                                 .   .   .   .   .   .   .   .   36
      Analysis Services Performance Enhancements . . . . . . . . .                                           ...........                                 .   .   .   .   .   .   .   .   37
          Support for Very Large Cubes . . . . . . . . . . . . . . . . . .                                   ...........                                 .   .   .   .   .   .   .   .   37
          Huge Dimensions Supported in Memory . . . . . . . . . .                                            ...........                                 .   .   .   .   .   .   .   .   37
          Faster Query Processing . . . . . . . . . . . . . . . . . . . . . .                                ...........                                 .   .   .   .   .   .   .   .   37
          Faster Cube Processing . . . . . . . . . . . . . . . . . . . . . .                                 ...........                                 .   .   .   .   .   .   .   .   38
          Faster Parallel Processing of Partitioned Cubes . . . . . .                                        ...........                                 .   .   .   .   .   .   .   .   38
          Faster Parallel Processing and Querying . . . . . . . . . . .                                      ...........                                 .   .   .   .   .   .   .   .   38
          Larger Number of Concurrent Users . . . . . . . . . . . . .                                        ...........                                 .   .   .   .   .   .   .   .   38
          Increased Performance and Lower Costs Through Server                                               Consolidation                               .   .   .   .   .   .   .   .   38
      Integration Services Performance Enhancements . . . . . . . .                                          ...........                                 .   .   .   .   .   .   .   .   40
          Improved Performance for Row-by-Row Calculations .                                                 ...........                                 .   .   .   .   .   .   .   .   40
          Improved Performance for Aggregations . . . . . . . . . .                                          ...........                                 .   .   .   .   .   .   .   .   40
          Faster Loading of Multiple Data Sources . . . . . . . . . . .                                      ...........                                 .   .   .   .   .   .   .   .   40
      Reporting Services Performance Enhancements . . . . . . . .                                            ...........                                 .   .   .   .   .   .   .   .   41
          Faster Report Rendering . . . . . . . . . . . . . . . . . . . . . .                                ...........                                 .   .   .   .   .   .   .   .   41
  x64 BI Performance              . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
      TPC-H Benchmarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                                                                       41
         TPC-H 100GB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                                                                     41
         TPC-H 300GB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                                                                     42
  Next: Sixty-Four-Bit Application Development . . . . . . . . . . . . . . . . . . . . . . . . . . 43
30


Chapter 3:


Sixty-Four-Bit Business
Intelligence Advantages
In Chapter 2, you saw the benefits that the 64-bit x64 platform provides for the SQL Server relational
database engine. As you might expect, the advantages of the x64 architecture don’t stop there. SQL
Server 2005 is much more than a relational database engine. Since the release of SQL Server 7 in
1998, SQL Server has been an industry leader in the Business Intelligence (BI) market. Microsoft was
the first major relational database vendor to include BI capabilities in its products (rather than offering
them as additional-cost add-ins). This inclusion helped make BI a widely available and affordable
technology. BI and its related online analytical processing (OLAP) and reporting technologies have
moved from being niche technologies found only in the largest of enterprises into the mainstream of
corporate computing. One of the driving factors behind the rapid growth of BI is its ability to let
organizations derive more meaningful information from the growing stores of data and information
that they possess. SQL Server 2005 extends Microsoft’s BI position in the database industry by
providing an end-to-end BI stack that consists of Analysis Services, Integration Services, Reporting
Services, and Data Mining.
     BI and OLAP technologies have brought important capabilities into the data processing environ-
ment. OLAP lets organizations discover relationships in the data that they wouldn’t be aware of
otherwise. OLAP works by aggregating data from detailed relational online transaction processing
(OLTP) data. Reports against this summary data can run in seconds; getting the same type of
information from the underlying relational data might take hours or days. OLAP reports read only a
few pieces of aggregated data whereas comparable relational reports might process thousands or
even millions of rows. The summarization of data and the high-speed reporting capability enable
rapid ad hoc reporting and what-if analysis that would be too time-consuming using pure relational
database query technologies. Another important benefit that BI and data mining provide is the ability
to perform predictive data analysis. With data-mining technology, organizations can use various
data-mining models to process their existing relational data—uncovering trends and better predicting
a variety of important business conditions and factors (e.g., the sales of product lines, important
customers’ purchasing patterns).
     BI technologies such as OLAP, extraction, transformation, and loading (ETL), reporting, and data
mining must be able to efficiently process, aggregate, and query very large data sets. Servers running
these BI workloads must process large amounts of data and can benefit tremendously from the
higher processing power, increased memory capacities, and improved I/O bandwidth that the x64
platform offers (e.g., servers running AMD Opteron™ processor technology). In this chapter, you’ll
learn about the benefits that 64-bit computing provides for SQL Server BI subsystems. First, I’ll discuss
the BI features that the Itanium and x64 editions of SQL Server 2005 support. Next, I’ll cover some of
the ways in which the 64-bit platform provides specific benefits to Analysis Services, Integration



                                    Brought to you by AMD and Windows IT Pro eBooks
                                                 Chapter 3 Sixty-Four-Bit Business Intelligence Advantages 31


Services, and Reporting Services. Finally, I’ll present some of the key BI benchmarks that have been
set using SQL Server and the x64 platform.

SQL Server 2005 x64 Editions and BI Features
Just as x64 support differs between the editions of SQL Server 2005, support for the BI feature set
differs for each edition as well. In general, you’ll find both x64 and BI support in SQL Server 2005
Enterprise Edition and SQL Server 2005 Standard Edition. However, you’ll also find some exceptions.
Table 3.1 summarizes SQL Server 2005 support for the x64 and BI feature sets.

Table 3.1 SQL Server 2005 x64 editions and BI features
Feature                               Express              Workgroup                   Standard    Enterprise
x64 Support                         Windows on             Windows on                     ✓            ✓
                                    Windows64              Windows64
                                     (WOW64)                (WOW64)
Number of CPUs                           1                     2                     4                64
Maximum Ram                            1GB                    3GB                OS maximum       OS maximum
Import/Export                                                    ✓                       ✓            ✓
Integration Services                                                                     ✓            ✓
Analysis Services                                                                        ✓            ✓
Unified Dimensional Model                                                                ✓            ✓
Business Analytics                                                                       ✓            ✓
Proactive Caching                                                                                     ✓
Advanced Data Management                                                                              ✓
Full Writeback Support                                                                                ✓
Data Mining                                                                              ✓            ✓
Data Warehousing                                                                         ✓            ✓
Report Server                           ✓                        ✓                       ✓            ✓
Report Builder                                                   ✓                       ✓            ✓
Scale Out Report Servers                                                                              ✓
     All editions of SQL Server 2005 provide 32-bit support. However, only SQL Server 2005 Enterprise
Edition and SQL Server 2005 Standard Edition are native 64-bit applications. The SQL Server 2005
Workgroup Edition and the SQL Server 2005 Express Edition are 32-bit applications that run in the
Windows on Windows64 (WOW64) subsystem that the Windows Server 2003 x64 editions offers to
provide 64-bit support.
     Reflecting their small business orientation, the SQL Server 2005 Workgroup and SQL Server 2005
Express editions offer limited support for CPUs and RAM. The SQL Server 2005 Workgroup Edition is
limited to two CPUs and 3GB of RAM; the SQL Server 2005 Express Edition is limited to one CPU
and 1GB of RAM. (Note that a processor with dual-execution cores—a dual-core processor—is
counted as a single-core processor in terms of software licensing.) It’s possible to run SQL Server
2005 Express Edition on a dual-core processor or run SQL Server 2005 Workgroup Edition on two
dual-core processors. For more information about multiple-core processors, see the sidebar
“Dual-Core and Multiple-Core Processors.”



                                     Brought to you by AMD and Windows IT Pro eBooks
32   Moving SQL Server to the x64 Platform



                       Dual-Core and Multiple-Core Processors
 The availability of dual-core processors is another major leap forward for x64 computing technology. Today, dual-
 core processors are available for server, desktop, and laptop systems. Dual-core processor means that two
 execution cores reside on a single die—the silicon wafer that encloses the CPU. In other words, a dual-core
 processor has two CPUs implemented on the same physical form factor as a single-core processor. A motherboard
 that supports a standard single-core processor will have one socket into which the processor is inserted. The
 physical motherboard implementation for a dual-core processor is identical.
       In a dual-core configuration, instead of having a single CPU installed on the die, the dual-core processor will
 have two CPUs installed on the die. For example, AMD’s dual-core processors are socket-compatible with existing
 single-core processors. Both the dual-core and single-core AMD Opteron processors use a 940-pin socket.
 Likewise, 939-pin motherboards can support single- and dual-core AMD Athlon64 processors. A BIOS upgrade is
 required to enable dual-core support on the motherboard. Figure A shows a simple view of the dual-core processor
 technology.



                     Figure A                                          CPU                  CPU
                                                                      Core 1               Core 2
        Dual-core processor technology
                                                                               Processor Die



       AMD designed its Opteron processor from the beginning with multiple-core technology in mind. It combines
 the load-sharing benefits of symmetrical multiprocessing with the reduced latency of the Direct Connect
 Architecture. Because this approach spreads the computing load across multiple processors, your system can do
 more work in less time. AMD Opteron dual-core processors have the same footprint as single-core processors. Just
 as importantly, they consume the same power as single-core processors. From the server standpoint, you get more
 computing power with fewer systems.

 Dual-Core Benchmarks
 The scalability of dual-core systems has been demonstrated by several different benchmarks. Some of the most
 important are the Java Business Benchmark (SPECjbb), which tests application server performance; WebBench,
 which tests Web server performance; the SAP Sales and Distribution (SD) Users benchmark, which tests enterprise
 resource management (ERP) application scalability; and the TPC-C benchmark, which tests database server
 performance. Dual-core technology has shown significant advantages in each of these areas.

 SPECjbb2000 and WebBench
 The SPECjbb2000 benchmark tests a system’s ability to act as a middle-tier application server in an n-tier
 application architecture. This benchmark is designed to represent the architecture commonly used in ERP,
 customer relationship management (CRM), and other n-tiered applications in which a Web browser is used to
 access the data contained in an enterprise database. This three-tier application benchmark tests a front-end client
 tier, a middle-tier Java application server, and a back-end database tier. The Java application server contains the
 business logic and application objects that respond to incoming client requests. The application tier retrieves the



                                         Brought to you by AMD and Windows IT Pro eBooks
                                                    Chapter 3 Sixty-Four-Bit Business Intelligence Advantages 33


required data from the back-end database and returns that data to the browser-based client. The SPECjbb2000
benchmark is designed to represent a wholesale company with warehouses serving multiple districts. The
SPECjbb2000 benchmark measures the rate per second at which the application performs various business
operations.
     WebBench is a program that measures the performance of a Web server. The WebBench benchmark uses
client PCs to simulate Web browsers. It measures the time it takes for the Web server to respond to the client
requests. Figure B shows the results of the SPECjbb2000 and WebBench tests, which pitted single-core systems
against comparable dual-core systems.

                                                        Figure B
                     Single-core and dual-core SPECjbb2000 and WebBench results

      200%
      180%
      160%                                                                                Dual-Core AMD Opteron
      140%                                                                                Model 275/875 (2.2GHz)
      120%                                                                                Single-Core AMD Opteron
                                                                                          Model 248/848 (2.2GHz)
      100%
        80%
        60%
        40%
        20%
            0
                SPECjbb2000 SPECjbb2000 SPECjbb2000 WebBench 5.0 WebBench 5.0
                   (4-P)       (2-P)       (1-P)       (2-P)        (1-P)



      On the left side of Figure B, you can see a comparison of the SPECjbb2000 results achieved using one-
processor (1-P), 2-P, and 4-P single-core and dual-core systems. In each case, the dual-core configuration achieved
results that were significantly better than the single-core results. The biggest difference was recorded in the 4-P and
2-P tests: The dual-core system performed 86 percent and 87 percent better, respectively, than the comparable
single-core systems. The single-processor system tests also demonstrated a very significant 82 percent
improvement. Although the move to dual-core didn’t provide absolute linear scalability, it came close—managing
an improvement of more than 80 percent in all tests.
      The WebBench benchmark scores comparing 2-P and 1-P processor results for single- and dual-core Web
server performance showed similar improvement. In the 2-P test, the dual-core system provided a 70 percent
performance improvement over the single-core system. In the 1-P test, the dual-core system outperformed the
single-core system by 71 percent.

SAP SD Users Windows 4-P Servers
The SAP SD Users benchmark measures the scalability of the SAP ERP applications. The benchmark indicates the



                                        Brought to you by AMD and Windows IT Pro eBooks
34   Moving SQL Server to the x64 Platform




 total number of concurrent users that a system will support. Clients create an order with five transactions, create a
 delivery for the order, display the order, change the delivery date, list 40 orders, and create an invoice. The SAP SD
 Users benchmark measures the number of users that the system can support simultaneously. Figure C shows the
 results of the single-core and dual-core SAP SD Users benchmark.

                                                         Figure C
                           Single-core and dual-core SAP SD User benchmark results

               200,000

                                                                                                 Line Items
               150,000
                                                                                                 Users


               100,000


                 50,000



                       0
                              HP DL585 Dual-Core               HP DL585 Single-Core



       This SAP SD Users benchmark test’s results compare the performance of two almost identically configured 4-P
 HP ProLiant DL585 systems. The systems were identical except that one used four dual-core processors and the
 other used four single-core processors. The dual-core system supported 1772 users and processed 178,000 order
 line items per hour. The dual-core system showed a significant improvement over the single-core system, serving
 755 more users and processing 76,000 more order line items per hour than the single-core server. Those
 differences amount to an almost 75 percent performance improvement.

 TPC-C
 The Transaction Processing Performance Council (TPC) designed the TPC-C benchmark to demonstrate the
 scalability of an online transaction processing (OLTP) database system. The TPC-C benchmark simulates an order
 entry/shipping application in which multiple clients execute transactions against a database. The database
 transactions include entering orders, checking order status, delivering shipments, changing stock levels, and
 recording payments. The performance measurement used in the TPC-C benchmark is the transactions per minute
 (tpmC) score, which measures how many new orders users can enter while the system performs a given workload
 in the background. The TPC-C can also demonstrate the price/performance of a database system by correlating the
 performance scores with the cost of the system tested. Figure D shows the results of the single-core and dual-core
 TPC-C benchmark tests.




                                         Brought to you by AMD and Windows IT Pro eBooks
                                                    Chapter 3 Sixty-Four-Bit Business Intelligence Advantages 35




                                                        Figure D
                                   Single-core and dual-core TPC-C results
                     120,000              109633

                                          144%
                     100,000

                                                                                 76214
                       80,000
                                                                                100%
                       60,000


                       40,000

                                                      95                                   95
                       20,000
                                                     Watts                                Watts

                          0
                  2p Servers             ADM Opteron                            ADM Opteron
                                          Model 280                              Model 254
                                          Dual-Core                              Single-Core


      The TPC-C benchmarks are more I/O-oriented than the previous benchmarks discussed. Therefore, the
difference between single-core and dual-core performance isn’t as pronounced. Even so, the results that Figure D
shows demonstrate a significant performance benefit from moving from single-core to dual-core. The 2-P dual-core
system achieved a total tpmC score of 109,633—a 44 percent improvement over the tpmC posted by the 2-P
single-core server. This 44 percent improvement was achieved solely by upgrading from single-core processors to
dual-core processors. For CPU-bound installations, this type of upgrade could provide a significant increase in
processing power and greatly benefit overall system performance. Note that power consumption doesn’t increase
with the 44 percent performance improvement; the dual-core system used the same 95 watts as the single-core
system.

Multiple-Core
Although the current technology is limited to dual-core processors (two processors on a single die), this ceiling will
certainly be raised in the near future. Multiple-core designs involving four cores per die will be available in the
next year, boosting the system power per footprint to even higher levels. Recent Gartner Group research indicates
that 64-bit processors with multiple cores will dominate the markets by 2007. For more information about the
Gartner Group study, go to http://www.gartner.com/DisplayDocument?doc_cd=127410. Because AMD designed
its Opteron processors with multiple-core technology in mind, no architectural changes are required to increase
the number of cores the processors support.




                                        Brought to you by AMD and Windows IT Pro eBooks
36   Moving SQL Server to the x64 Platform


     Because the BI features that SQL Server 2005 provides are primarily oriented toward midsize and
large businesses, they’re available in the SQL Server 2005 Enterprise and SQL Server 2005 Standard
editions. In fact, the SQL Server 2005 Workgroup and SQL Server 2005 Express editions contain just
two BI features: the SQL Server Integration Services Import/Export Wizard and Reporting Services.
You’ll find Analysis Services, the core SQL Server Integration Services designed for ETL transfers, and
Data Mining only in the 32-bit and 64-bit SQL Server 2005 Enterprise and SQL Server 2005 Standard
editions. Also, you’ll find several advanced BI functions only in the SQL Server 2005 Enterprise
Edition. Those functions include proactive caching, advanced data management (which supports
partitioning cubes), and the ability to scale out Reporting Services servers. The BI features in the x64
editions of SQL Server 2005 are native 64-bit applications fully able to take advantage of the
enhanced x64 platforms available today.

Sixty-Four-Bit BI Benefits
The x64 64-bit platform lets SQL Server 2005’s BI subsystems in the Enterprise and Standard editions
achieve higher levels of scalability than was possible with the previous 32-bit editions of these
features. The increased scalability is possible primarily because of the boost in computing power and
the increase in available memory (up to 1TB using the AMD Opteron processor; up to 64GB using an
Intel EM64T processor-based system). First, I’ll consider some of the limitations found in the 32-bit
implementation of SQL Server 2005 BI components, then consider some of the areas in which these
BI features specifically benefit from the 64-bit computing architecture.

Thirty-Two-Bit BI Limitations
Unlike the 32-bit SQL Server relational database engine, which could use Address Windowing
Extensions (AWE) to boost the internal capacity of its buffer cache and thereby relieve some of the
32-bit memory constraint, Analysis Services and the other BI subsystems (e.g., Integration Services,
Reporting Services) were never AWE-enabled. Therefore, the 32-bit SQL Server BI components ran
under the 32-bit Windows 4GB memory ceiling. By default, this 4GB of memory is split evenly
between the Windows OS and the applications.
     Realizing the constraints that the memory ceiling could place on applications such as the SQL
Server BI stack, Microsoft developed a way to tune Windows’ allocation of the available 4GB of
memory address space. For certain versions of the Windows Server OS, Microsoft added a /3GB
startup parameter that let users shift the balance of RAM in favor of the applications. Microsoft
supports the /3GB switch only on the following OSs:
     • Windows 2003 Datacenter Edition
     • Windows 2003 Enterprise Edition
     • Windows 2003 Standard Edition
     • Windows 2000 Datacenter Server
     • Win2K Advanced Server (AS)

    Using this switch with Windows startup lets Windows devote 3GB to the applications, leaving
1GB for the Windows Server OS. Although this switch can benefit the applications, it’s a doubled-
edged sword—giving memory to the applications but taking it from the OS. Workloads that stress the
Windows kernel face the danger of becoming memory constrained, which can severely degrade


                                   Brought to you by AMD and Windows IT Pro eBooks
                                                Chapter 3 Sixty-Four-Bit Business Intelligence Advantages 37


overall system performance. Even in situations in which this workaround succeeded in improving
overall system performance, it still added only 1GB of memory for application use.
     The AMD Opteron processor’s ability to support 1TB of RAM eliminates the memory constraint
for applications entirely without hindering Windows performance. The OS can also take advantage of
the added memory. The result is significantly improved overall system performance as well as
improved application performance and scalability.

Analysis Services Performance Enhancements
The 64-bit x64 editions of Analysis Services 2005 provide significantly enhanced performance for
aggregation and processing of large sets of OLAP data. The ability to process much larger amounts of
data in memory without the need to perform the much slower I/O required to use temporary disk
files significantly improves performance over the 32-bit editions of Analysis Services. As the data sets
increase in size, the benefits of 64-bit computing grow substantially. The increased memory that the
x64 editions of Analysis Services provides can support larger cubes, improve performance for
processing large dimensions, and process cubes faster.
     Similarly, the greater processing power of the AMD Opteron processors in conjunction with
AMD’s Direct Connect Architecture provides better support for parallel processing operations both for
simultaneously processing cubes and for accessing partitioned cubes. The x64 architecture’s increased
memory support also enables a higher maximum number of concurrent users. And the more
powerful and scalable x64 systems, such as those that run AMD Opteron processors, let you
implement server consolidation to an extent that isn’t possible with 32-bit Analysis Services. Let’s look
at how the x64 architecture benefits each of these areas in more detail.

Support for Very Large Cubes
The x64 platform’s increased memory capacity provides Analysis Services with a much larger working
data cache. The larger data cache enables significant performance benefits for very large cubes. The
extended data cache reduces the need for physical I/O to satisfy queries. The increased memory
capacity is especially useful when you have cubes with many fact tables, partitions, and large
numbers of aggregates.

Huge Dimensions Supported in Memory
Analysis Services caches dimensions in memory. The memory capacity of the x64 platform processors
(e.g., AMD Opteron processors) lets Analysis Services better handle very large dimensions, which
might include millions of members. The increased memory is especially crucial when you use custom
security in Analysis Services. Custom security caches a separate copy of the dimension for each
custom security role assigned. (For 32-bit implementations, the memory required is an extremely
significant concern.) The increased memory that AMD’s 64-bit architecture offers lets it effectively
handle large dimensions and dimensions that use custom security roles .

Faster Query Processing
Much as the SQL Server relational database engine uses its buffer cache to store recently used query
results, Analysis Services’ data cache stores recently used query results. Because of the dynamic
nature of the data cache, older query results are continually being expelled from the cache. When
Analysis Services resolves user queries, it first attempts to use the data in its data cache to respond to

                                    Brought to you by AMD and Windows IT Pro eBooks
38    Moving SQL Server to the x64 Platform


the query. It either retrieves the specific data set from the cache or (if the dataset isn’t available there)
attempts to create the requested data set from other data already present in the cache. If the query
can be addressed using cached data, it’s referred to as a warm cache query. If the query can’t be
satisfied by the cache and I/O is required to resolve the query, it’s referred to as a cold cache query.
The x64 architecture’s expanded memory capacity lets Analysis Services maintain a much larger data
cache; the result is a higher percent of warm cache queries that don’t resort to physical I/O.

Faster Cube Processing
The additional memory capacity of 64-bit computing also enables faster cube processing. Analysis
Services cubes consist of data aggregations; these aggregations don’t take place in real time. Instead,
the cube data must be periodically refreshed by “processing the cube.” The greater memory capacity
of the x64 architecture—for example, of the AMD Opteron processor—reduces the need to write
temporary files to the disk subsystem while processing the cube data. With the reduced need to
perform disk I/O, cube processing can be performed much faster and therefore more often as well.

Faster Parallel Processing of Partitioned Cubes
The Enterprise Edition of Analysis Services 2005 lets you partition cubes into multiple parts; each
cube segment is stored on a different disk partition. This technique can enhance performance
because each partition can be accessed independently. Partitioning decreases the performance impact
of the I/O required to access each partition because the I/O is divided between multiple disks. The
Direct Connect Architecture of the AMD Opteron processor can access multiple partitions efficiently
because of the improved parallel processing capabilities and the reduced memory latencies of its
integrated memory controller.

Faster Parallel Processing and Querying
The Direct Connect Architecture also offers faster simultaneous cube processing and data querying.
The typical data mart or data warehouse contains numerous cubes that must be reprocessed
(refreshed) periodically. In many cases, some cubes must be refreshed while other cubes remain in
active use. The reduced latencies and high-speed processor and memory connections in the Direct
Connect Architecture coupled with the increased memory capabilities of the x64 architecture let the
system more effectively process cubes while simultaneously satisfying ongoing end-user query
requests.

Larger Number of Concurrent Users
Like the relational database engine, the x64 architecture’s larger memory capability lets the server
support more simultaneous user contexts and a higher number of connections. Each user connection
requires a set of data structures that maintain the state of each connection to the server. The larger
addressable memory space that AMD Opteron processors provide, for example, increases the number
of active user connections that a server can support.

Increased Performance and Lower Costs Through Server Consolidation
Although the x64 platform’s larger address space provides clear benefits for several internal areas of
the SQL Server relational database engine and for Analysis Services, one area in which the benefits


                                    Brought to you by AMD and Windows IT Pro eBooks
                                             Chapter 3 Sixty-Four-Bit Business Intelligence Advantages 39


aren’t so obvious is server consolidation. Co-hosting the SQL Server relational database engine and
Analysis Services on the same server typically isn’t recommended because either entity can require
most if not all of the available memory in a 32-bit server. However, the massive increase in headroom
coupled with the greater processing power of the x64 platform make it possible to consolidate your
SQL Server relational database instance with Analysis Services—for instance, on servers running AMD
Opteron processors—as Figure 3.1 shows.

                                                Figure 3.1
                          Consolidating SQL Server and Analysis Services




                                    32-bit                                32-bit
                             SQL Server relational                   Analysis Services
                               database engine




                                                     64-bit
                                         SQL Server relational database
                                          engine and Analysis Services


    Running the SQL Server relational database engine and Analysis Services on the same system lets
you use the Shared Memory provider to connect Analysis Services cubes to the SQL Server relational
databases. Unlike the TCP/IP or Named Pipes client protocols, Shared Memory is an in-memory
provider that doesn’t need to drop down into the system networking layers to make the connection
between Analysis Services and SQL Server. This in-memory capability supports the fastest possible
connection between Analysis Services and SQL Server because they run on the same system. The
connection is many times faster than the networked connection required when Analysis Services and
the SQL Server relational database engine reside on separate systems. The AMD Opteron processor’s
multicore capabilities, its ability to address up to 1TB of physical memory, and its high-speed,
low-latency memory and I/O access provide a high-performance platform for consolidating SQL
Server and Analysis Services.


                                 Brought to you by AMD and Windows IT Pro eBooks
40   Moving SQL Server to the x64 Platform


     Two important side benefits of server consolidation are reduced infrastructure and management
requirements. Consolidating SQL Server relational database engine and Analysis Services reduces both
the system hardware your IT staff manages and your licensing costs (you don’t need the extra SQL
Server license required to run Analysis Services on a separate server). Fewer servers also have
reduced power and cooling requirements. This basic efficiency is enhanced further when you use
servers that are extremely power efficient. The AMD Opteron processor can be moved from
single-core to dual-core without increasing power requirements, making servers running dual-core
AMD Opteron processors excellent candidates for server consolidation.

Integration Services Performance Enhancements
SQL Server Integration Services (SSIS) performs ETL tasks for SQL Server 2005. Like the 32-bit Analysis
Services, the 32-bit SSIS can’t take advantage of AWE and is confined to the 2GB or 3GB address
space that the 32-bit editions of Windows provide. The increased memory that the x64 platform offers
improves overall SSIS performance significantly. The access to increased memory capacities vastly
improves the performance of memory-intensive tasks such as row-by-row calculations, lookups,
aggregation, and sorting. The additional working memory lets SSIS do more work in memory,
reducing the need to perform intermediate disk reads and writes. Additionally, SSIS’s multithreaded
parallel data loading ability lets it use the greater system throughput that the integrated memory
controller of the AMD Opteron processor provides. The ability to work with larger data sets in
memory not only speeds up the specific task but also helps free up server resources to be applied to
other workloads.

Improved Performance for Row-by-Row Calculations
The x64 platform provides improved performance for SSIS tasks that execute operations row by row.
Examples of such tasks include performing lookups, creating calculated columns, and executing data
and character conversions. The enhanced processing power of the AMD Opteron processor supports
performing these operations more rapidly, and the increased memory capacity lets SSIS bring more
rows into working memory, enhancing the query performance for applications that use these row-
by-row functions.

Improved Performance for Aggregations
The increased memory that the x64 platform provides vastly improves performance for tasks that
perform aggregation and sorting. Aggregation and sorting require manipulating large amounts of data
at one time. Such operations perform significantly better with more memory because the data sets
involved can consist of thousands or millions of rows and are usually too large to fit into memory at
one time. To process these tasks, SSIS must use temporary storage to store intermediate results. Using
temporary storage requires disk I/O, which increases the time needed to perform the task. More
memory lets more data be manipulated at one time, reducing the need to perform disk I/O to store
intermediate results.

Faster Loading of Multiple Data Sources
SSIS is a multithreaded application capable of performing parallel processing by simultaneously
loading data from multiple data sources and writing data to multiple targets. The Direct Connect
Architecture of the AMD Opteron processor improves the system’s ability to perform parallel

                                  Brought to you by AMD and Windows IT Pro eBooks
                                              Chapter 3 Sixty-Four-Bit Business Intelligence Advantages 41


processing. The low memory latency combined with the high 8GBps I/O speed that
HyperTransport™ technology provides can support much higher system throughput, which SSIS
parallel operations use effectively.

Reporting Services Performance Enhancements
Reporting Services also benefits from 64-bit computing. Like Analysis Services and Integration
Services, Reporting Services can’t use AWE support. However, the 64-bit SQL Server 2005 Reporting
Services can take advantage of the increased memory available on the x64 architecture to process and
render complex reports.

Faster Report Rendering
SQL Server 2005’s Reporting Services is a native 64-bit .NET application designed to run on the x64
version of the Microsoft Common Language Runtime (CLR). Using the CLR’s dynamic memory
management, the x64 edition of Reporting Services can access all physical memory available to the
CLR, resulting in performance improvements for rendering large and complex reports.

x64 BI Performance
The performance of the x64 architecture for OLAP workloads has been well established. The TPC-H
(the H represents ad hoc) benchmark, developed by the Transaction Processing Performance Council
(TPC), is the industry standard for OLAP and decision-support workloads. At the time of this writing,
the AMD Opteron processor–based systems hold the top performance and price/performance posi-
tions on the TPC-H benchmark for both 100GB and 300GB nonclustered databases running SQL
Server 2005.

TPC-H Benchmarks
The TPC-H is a decision-support processing benchmark that involves executing ad hoc decision-
support queries. The TPC-H benchmark comprises a collection of ad hoc queries plus a number of
concurrent data updates. The decision-support queries and the database against which they run have
been designed for industrywide relevance. The TPC-H benchmark performs queries against a
database of predetermined size. The queries are designed to have a high degree of complexity and to
provide answers to common business questions.
     The TPC-H benchmark uses a performance metric called the Composite Query-per-Hour
Performance Metric (QphH@Size). This measurement’s intent is to reveal a number of different
capabilities in a system’s query processing. Those capabilities include the query processing
performance for a single query and the query processing performance for multiple queries. The
price/performance measure correlates the price of the tested system with the resulting QphH score.

TPC-H 100GB
In the TPC-H 100GB benchmark test, an HP DL585 equipped with four AMD Opteron 880 processors
running at 2.4GHz set new performance and new price/performance records. The system was
equipped with 64GB of memory, 80 36GB 10K external drives, and four 36GB 15K internal drives for
a maximum of 3024GB total disk storage. This system set a new world record for 100GB database
query performance with a score of 12,600 QphH and a price/performance of 7.67 $/QphH. Figure 3.2
shows the top three nonclustered 100GB TPC-H results.

                                  Brought to you by AMD and Windows IT Pro eBooks
42    Moving SQL Server to the x64 Platform



                                                 Figure 3.2
                                          TPC-H 100GB results
                12,600 QphH            10,704 QphH                       8,793 QphH
                7.67 $/QphH            15.88 $/QphH                      8.17 $/QphH
  14,000
  12,000
  10,000
     8,000
                                                                                         100GB TPC-H
     6,000
     4,000
     2,000
        0
             HP ProLiant DL585          PowerEdge                        PowerEdge
                  G1 4-P           6800/64G/3.0GHz/2+               6800/32G/3.0GHz/2+
                                    2MB with MS SQL                  2MB with MS SQL
                                          2005                             2005

     The HP DL585’s top results were almost 20 percent higher than those of the next-closest
system—and the price/performance was less than half that of the same system.

TPC-H 300GB [3]
A quite similarly equipped HP DL585 system also set the world record for the nonclustered 300GB
TPC-H benchmark. In the 300GB tests, the x64 system was an HP DL585 equipped with four
dual-core Opteron 880 processors running at 2.4GHz and 64GB of memory. It had 160 36GB 10K
external drives, two 36GB 15K and two 72GB 15K internal drives—for a maximum of 5976GB of total
disk storage. This system recorded a score of 12,225 QphH and a price/performance of 11.71
$/QphH. Figure 3.3 shows the top three nonclustered 300GB TPC-H results.




                                  Brought to you by AMD and Windows IT Pro eBooks
                                              Chapter 3 Sixty-Four-Bit Business Intelligence Advantages 43



                                                 Figure 3.3
                                          TPC-H 300GB results
             12,225 QphH             11,915 QphH                      9,962 QphH
             11.71 $/QphH            22.78 $/QphH                    58.82 $/QphH
 14,000
 12,000
 10,000
  8,000
                                                                                        300GB TPC-H
  6,000
  4,000
  2,000
      0
          HP ProLiant DL585       HP ProLiant DL585                  SGI Altix 350 8P
               G1 4-P             2.2GHz dual-core


    In the 300GB test, the top results were close but price/performance was about half that of the
next-closest system and about one-fifth that of the third system.

Next: Sixty-Four-Bit Application Development
Chapter 3 has presented some of the advantages that the x64 architecture provides for the BI
components of SQL Server 2005. Chapter 4 will explore 64-bit application development. You’ll see
how to develop code for the 64-bit .NET Framework and how to migrate existing 32-bit applications
to 64-bit applications.




                                  Brought to you by AMD and Windows IT Pro eBooks
 Moving
SQL Server

  x64
  to the



  Platform By Michael Otey


                   sponsored by
iv




Contents
Chapter 4: Sixty-Four-Bit Database Application Development . . . . . . . . . . 44
     Windows 32-Bit and 64-Bit Application Support . . . . . . . . . . . . . . . . . . . . . . . . . 44
         x64 Application Performance Benefits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .            45
     SQL Server 2005 x64 T-SQL Application Development . . . . . . . . . . . . . . . . . . . . 46
     The 64-Bit .NET Framework                 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
         .NET Framework Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .             48
            Sidebar: Virtualization of Clients and Servers . . . . . . . . . . . . . . . . . . . . . . . . . . .             49
         32-bit and 64-bit .NET Framework Compatibility . . . . . . . . . . . . . . . . . . . . . . . . . .                  50
     64-Bit Visual Studio 2005 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
     Developing 64-bit Applications for x64                     . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
         Specifying a Target Platform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .        52
         Developing 64-bit CLR Database Objects for x64 . . . . . . . . . . . . . . . . . . . . . . . . . .                  54
     Next: Migrating from 32-Bit to 64-Bit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
44


Chapter 4:


Sixty-Four-Bit Database
Application Development
In Chapter 2 and Chapter 3, I described some of the benefits that the x64 platform offers to the
Microsoft SQL Server relational database as well as to SQL Server’s Business Intelligence (BI)
components, including Analysis Services, Integration Services, and Reporting Services. The x64
platform also brings with it a number of advantages for database application performance—especially
for n-tiered applications that must service multiple client connections. In this chapter, I’ll discuss the
Microsoft technology that’s available to support the development of 64-bit applications. I’ll dive a bit
deeper into the technology to explore some of the specific benefits that the x64 architecture provides
for database applications. After reviewing the tools that Microsoft provides to build 64-bit applications,
I’ll cover how you can use those tools to develop 64-bit applications. The chapter will conclude by
discussing how to migrate 32-bit applications to the 64-bit platform.

Windows 32-Bit and 64-Bit Application Support
Windows provides support for 32-bit and 64-bit hardware as well as for 32-bit and 64-bit applications.
To do so, Microsoft offers three versions of the Windows Server OS: a native 32-bit edition, an x64
edition, and an Itanium edition. Figure 4.1 presents an overview of Windows support for 32-bit and
64-bit applications.

                                                  Figure 4.1
                           Microsoft’s 32-bit and 64-bit application support

                                 32-Bit Stack             32-Bit/64-Bit Stack          64-Bit Stack

                                                                         32-bit
                Applications         32-bit               32-bit                      x64      Itanium
                                                                                x64


            Windows Server           32-bit               32-bit              x64     x64      Itanium


             Device Drivers          32-bit               32-bit              x64     x64      Itanium



        Windows Hardware             32-bit                x64                x64     x64      Itanium




                                   Brought to you by AMD and Windows IT Pro eBooks
                                           Chapter 4 Sixty-Four-Bit Database Application Development 45


      The complete application stack for both 32-bit and 64-bit applications consists of four hardware
and software layers. The hardware is the most basic level. This level is the server system itself. Device
drivers, a vital but often overlooked portion of the application support stack, make up the next level.
Device drivers are software components that provide the interface between the OS and the
underlying hardware platform. The makers of hardware components, such as motherboards, hard
drives, and video and network cards, create the device drivers.
      To interact with the Windows Server OS, device drivers must conform to specific Windows
standards. In general, 32-bit hardware requires 32-bit device drivers and 64-bit hardware requires
64-bit device drivers. However, the x64 platform is an exception. The 100 percent x86 binary compat-
ibility of the AMD64 CPU architecture, for example, lets it run both 32-bit and 64-bit device drivers.
The Windows Server OS sits on top of the hardware support that the device drivers provide and, in
turn, supports the applications that end users run. The applications form the final layer of the stack.
      As you would expect, each version of Windows provides support for its specific hardware and
applications. In other words, the 32-bit version of Windows requires 32-bit hardware, uses 32-bit
device drivers, and can run 32-bit applications, such as the 32-bit edition of SQL Server 2005. As you
look at the rightmost column of Figure 4.1, you can see that the 64-bit side is a bit more interesting;
two different hardware platforms and two different versions of the Windows Server OS must be
matched. The x64 and Itanium platforms are both 64-bit, but their architectures differ and are
completely incompatible. (For more information about the differences between the x64 and Itanium
hardware platforms and Windows OS support, refer to Chapter 1.) The 64-bit Itanium platform
requires Itanium-specific device drivers, an Itanium-only edition of Windows Server, and applications
that are specifically created for the Itanium x64 platform.
      In contrast, columns two, three, and four in Figure 4.1 illustrate the flexibility that the x64
platform provides. The x64 platform can run as a fast 32-bit system, supporting 32-bit devices drivers,
the 32-bit edition of the Windows Server OS, and 32-bit applications. (Note that because the stack
uses the 32-bit editions of Windows, you have no support for 64-bit applications.) In the middle
column, you can see that when it runs the x64 edition of Windows Server, the x64 platform provides
support to simultaneously run 32-bit and 64-bit applications. In this case, the x64 edition of Windows
Server requires native 64-bit device drivers to interface with the system hardware. The 64-bit
applications run natively on the x64 edition of the Windows Server OS while the Windows on
Windows64 (WOW64) subsystem provides 32-bit support. (For more information about Windows
Server WOW64 support, refer to Chapter 1.)

x64 Application Performance Benefits
The main reason to consider the x64 platform as an application server is increased scalability. I’ve
covered the performance benefits of the 64-bit platform for relational database and BI applications
and offered numerous benchmark results to support them. In earlier chapters, I cited some of the
benchmarks, but you can find many additional examples. For example, some of Microsoft’s early
adopter tests conducted on x64 platforms yielded impressive gains over their 32-bit counterparts.
Table 4.1 gives you an idea of the increased scalability the x64 platform provides.




                                   Brought to you by AMD and Windows IT Pro eBooks
46    Moving SQL Server to the x64 Platform


Table 4.1 x64 Performance Improvements
Workload                                                                      Performance Improvements
x64 SAP Sales and Distribution (SD) Users benchmark (enterprise               18 percent more users supported
resource management—ERP—application)
x64 file serving                                                              111 percent higher user capacity
x64 Active Directory (AD)                                                     2x higher throughput
x64 Terminal Services                                                         170 percent more users supported

     In each case, moving to the x64 platform brought a significant increase in scalability. Microsoft’s
SAP SD Users implementation supported 18 percent more users. The file-serving capabilities increased
drastically, supporting 111 percent more users. Microsoft’s internal AD implementation saw a twofold
increase in performance. But the biggest increase was in Microsoft’s internal Terminal Services
application; the company recorded an increase of 170 percent in the number of users supported. In
each case, the increased scalability came about in large part because of the increased memory the
x64 platform provides.
     The 32-bit x86 platform was limited to 4GB (2GB reserved for applications and 2GB for the OS).
The x64 platform eliminates both limitations—and different applications benefit from the increase in
OS memory and application memory. For example, as a database-centric application, the SAP SD
Users application benefited from the raw increase in available application memory. However, the
Terminal Services application works differently. Because Terminal Services requires OS kernel
memory for each user connection, it was the increased memory the OS could address that was the
primary factor for its increased scalability. Just as importantly, these performance gains don’t come at
the cost of backward compatibility. The x64 platform is completely compatible with today’s 32-bit
business applications. For applications that need additional addressable memory and increased
performance and scalability, upgrading to the native 64-bit x64 applications can be a compelling
solution.

SQL Server 2005 x64 T-SQL Application Development
DBAs must concern themselves with the SQL Server database development side. Common develop-
ment tasks include creating and maintaining database objects (e.g., tables, views, stored procedures,
triggers, user-defined functions). T-SQL scripts perform the vast majority of these tasks. DBAs and
other T-SQL database developers will be relieved to know that for this type of development T-SQL
for 32-bit SQL Server is 100 percent compatible with the native 64-bit x64 platform. Existing T-SQL
scripts function on the x64 editions of SQL Server 2005 exactly as they do on the 32-bit editions.
Microsoft redesigned SQL Server in 1998 knowing that 64-bit processors would be an option for
future releases of SQL Server. The company designed all of SQL Server’s on-disk structures to
accommodate 64-bits. This design ensured that SQL Server would be completely 64-bit compatible,
and it lets you freely move SQL Server database objects between 32-bit and 64-bit systems.
     In addition, the toolset you use to develop and run T-SQL scripts on the 32-bit editions and the
x64 editions of SQL Server 2005 is exactly the same. In both cases, you use SQL Server Management
Studio to manage the server system and the Query Editor to create and execute T-SQL scripts. Note
that the SQL Server 2005 toolset is completely platform agnostic. You can use the 32-bit SQL Server
Management Studio and Query Editor to manage and develop T-SQL scripts for both 32-bit and x64
editions of SQL Server 2005. Likewise, you can use the native x64 version of the SQL Server

                                       Brought to you by AMD and Windows IT Pro eBooks
                                          Chapter 4 Sixty-Four-Bit Database Application Development 47


Management Studio and Query Editor to manage and develop T-SQL scripts for both 32-bit and x64
editions of SQL Server 2005. The capabilities and features in each edition are identical.

The 64-Bit .NET Framework
You’ve seen that for DBAs and T-SQL database developers, 32-bit and x64 database development is
the same. However, T-SQL is usually just a part of the total database development solution. The other
part is the Microsoft .NET Framework. The .NET Framework is used for developing both desktop-
based WinForms applications and Web-based n-tiered applications. In this section, you’ll learn about
the different versions of the .NET Framework and the tools you can use to develop 64-bit .NET
applications.
     Microsoft provides a 32-bit version and a 64-bit x64 version of the .NET Framework 2.0, the
current version. As you might expect, the 32-bit edition of the .NET Framework can run on both the
32-bit edition of Windows Server and the x64 edition of Windows Server using WOW64. Figure 4.2
presents an overview of the relationship between the OSs and the .NET Framework.

                                                 Figure 4.2
                    Windows support for the 32-bit and 64-bit .NET Framework

                            32-bit               32-bit                   64-bit
                          Applications         Applications             Applications

                             32-bit                32-bit                   32-bit
                                                                            64-bit
                              .NET               .NET CLR                    .NET
                              CLR                 WOW64                      CLR

                            32-bit                        Windows x64
                           Windows


                                                    x64

     Like other 32-bit applications, the 32-bit .NET Framework’s maximum addressable memory is
4GB. The x64 edition of the .NET Framework doesn’t share this limitation. Just as the 64-bit x64
edition of SQL Server can take advantage of the increased capacities the x64 platform provides, 64-bit
.NET Framework applications can take advantage of the increased memory ceiling the x64 platform
offers—and can access up to 512GB of memory. Figure 4.3 presents an overview of the maximum
memory addressability of the 32-bit and 64-bit .NET Framework.




                                  Brought to you by AMD and Windows IT Pro eBooks
48   Moving SQL Server to the x64 Platform



                                                Figure 4.3
                    32-bit and 64-bit .NET Framework memory addressability

                        32-bit
                   .NET Framework              4GB
                   Windows Server
                     x86 or xz64

                        64-bit
                   .NET Framework
                   Windows Server                              512GB
                     x86 or xz64


     Although the 64-bit .NET Framework lets the application effectively address much more memory,
the essential language constructs remain the same. In other words, the maximum variable size for
string objects is still 2GB. Likewise, the maximum size for all other variables remains unchanged.
However, though you can’t have bigger variables, the 64-bit platform lets you have many more of
them.

.NET Framework Components
The 64-bit version of the .NET Framework 2.0 has the same components and Foundation Class
Libraries (FCLs) as the 32-bit version. The basic components of the .NET Framework include
     • Common Language Runtime (CLR)
     • Framework Class Libraries (FCL)
     • .NET Framework software development kit (SDK)

     The CLR provides support for running .NET applications on the Windows Server OS and includes
memory management, garbage collections, and a just-in-time (JIT) compiler. The FCL includes the
code that supports WinForms, ADO.NET, ASP.NET, and the hundreds of other .NET class libraries.
The .NET Framework SDK provides command-line compilers, a debugger, and numerous code
examples.
     Developing 64-bit managed code for the x64 environment is virtually the same as developing
applications for the 32-bit .NET environment—except for a few differences that I discuss in the
following section. Microsoft performed many under-the-covers changes to the 64-bit .NET Framework
to enable this support, including to the exception manager, to the JIT compiler, and to stubs, which
call unmanaged code and interface with the debugger. If you work solely with managed code, the
code just runs. If your application makes calls to unmanaged code, you need to be aware of 32-bit
and 64-bit differences.




                                 Brought to you by AMD and Windows IT Pro eBooks
                                                 Chapter 4 Sixty-Four-Bit Database Application Development 49




                           Virtualization of Clients and Servers
Virtualization of both servers and clients is one of the hottest current trends in the computer industry. On the server
side, virtualization can lower IT costs by reducing the number of physical servers to be managed and
simultaneously increasing hardware utilization. In addition to reduced management costs, server consolidation also
results in infrastructure savings by reducing power, space, and cooling
requirements. Although the typical x86 server experiences short-term peaks
and lulls, it usually runs at about 10 percent to 20 percent of capacity—far               Application Application
below the 75 percent to 85 percent utilization common in mainframe-class
systems. This low utilization means that the typical x86 installation has
excess capacity for a large portion of the day and generally underutilized                  Guest OS Guest OS
hardware resources. Client virtualization lets an organization support
legacy applications and older OSs side-by-side with new hardware
technology.                                                                                Virtualization Software
      Virtualization is clearly a useful strategy, but its cost is performance.
Although in many cases running a legacy application on a virtual machine                              Host OS
on a high-performance x64 system can result in better performance than
the application had on older hardware, the fact remains that virtualization                    System Hardware
could be much more efficient. Figure A presents the current state of
virtualization technology.
      The virtualization software typically runs on top of the host OS, with                         Figure A
the software managing the resource between the host and the guest OS.                       Current virtualization
Microsoft’s Virtual Server 2005 Release 2 (R2) and VMware’s Virtual Server                          technology
are examples of this type of product. Some virtualization products, such as
VMware’s ESX server, attempt to reduce this overhead by slimming down the host OS layer. However, even for ESX
Server, a host software layer is still present.
      Future technologies will move much of the support for virtualization into the hardware layer. The result will
                                                       be improved performance for virtual machines. AMD’s
                                                            virtualization technology is an example of such technology.
                                                              Intel supports a similar design in its virtualization
                                                              technology. Figure B presents an overview of how
    Application Application                                   virtualization will change with AMD Virtualization
                                                              (AMD-V™).
                                                                     With AMD-V, the virtualization software support
     Guest OS Guest OS Management                             moves into the host environment through the
                                         Partition
                                                              implementation of a new Hypervisor technology. This
                                                              design reduces the overhead associated with virtualization
                      Hypervisor
                                                              technology and moves a portion of the virtualization
                                                              implementation down into the system hardware. The AMD
                AMD with AMD-V                                Direct Connect Architecture will let the system better
                                                             support this level of hardware virtualization by eliminating
                        Figure B                          the legacy frontside bus bottleneck, vastly increasing
                         AMD-V                            bandwidth and system partitioning efficiency.



                                         Brought to you by AMD and Windows IT Pro eBooks
50   Moving SQL Server to the x64 Platform



32-bit and 64-bit .NET Framework Compatibility
The 32-bit version of the .NET Framework and its components are almost completely compatible with
the 64-bit x64 version. This compatibility ensures that .NET Framework developers can realize the
benefits of 64-bit computing by leveraging their investments in existing 32-bit .NET Framework code.
In most cases, source-code compatibility between the 32-bit and the 64-bit editions of the .NET
Framework is close to 100 percent. However, the following areas are among the few in which the
32-bit and 64-bit editions of the .NET Framework differ:
    • floating-point calculations—The IEEE 754 standard for floating-point arithmetic allows for
        different results for certain floating-point operations on different platforms (e.g., 32-bit and
        64-bit platforms).
    • pointer size—Thirty-two-bit platforms have 32-bit pointers (4 bytes) whereas 64-bit platforms
        have 64-bit pointers (8 bytes).
    • memory alignment—Types are aligned on boundaries of the natural lengths of the data type.
        Thirty-two-bit applications use 1-, 2-, 4-, and 8-byte alignments; 64-bit applications use 1-, 2-,
        4-, 8-, 10-, and 16-byte alignments. If items are longer than 8 bytes, the 64-bit version of the
        .NET Framework aligns them on the next greater boundary.

    Cross 32-bit/64-bit application development involves some additional considerations. For
example, 64-bit .NET processes can’t load 32-bit DLLs or 32-bit COM in-processing (Inproc) servers.
Likewise, 32-bit .NET processes can’t load 64-bit DLLs or 64-bit COM Inproc servers. However, remote
procedure calls (RPCs) between 32-bit and 64-bit processes on the same system are supported.

64-Bit Visual Studio 2005
Although you can develop both 32-bit and 64-bit .NET Framework applications by using a standard
text editor and the command-line SDK tools, Microsoft Visual Studio 2005 is the best tool for .NET
application development. Visual Studio 2005 provides an array of features that make the development
process more productive, including IntelliSense prompting during editing, integrated debugging for
both .NET and T-SQL code, Instant Watch debugging support, built-in database project support, and
advanced database design and query tools. However, Visual Studio 2005 editions range from the free
Visual Studio Express Edition to the enterprise-oriented Visual Studio Team System Edition. The
support for 64-bit development as well as the support for SQL Server database development between
members of the Visual Studio 2005 family varies accordingly. Table 4.2 presents a high-level look at
the different features that each edition of Visual Studio 2005 supports.




                                   Brought to you by AMD and Windows IT Pro eBooks
                                              Chapter 4 Sixty-Four-Bit Database Application Development 51


Table 4.2 Visual Studio 2005 Editions and Features
                   Visual Studio      Visual Studio          Visual Studio              Visual Studio      Visual Studio
Feature            Express Products   Standard Edition       Professional Edition       Tools for Office   Team System
32-bit x86         Yes                Yes                    Yes                        Yes                Yes
64-bit x64         No                 Yes                    Yes                        No                 Yes
64-bit Itanium     No                 No                     No                         No                 Yes
support
Server Explorer    No                 No                     Yes                        Yes                Yes
SQL Server 2005    No                 No                     Yes                        Yes                Yes
integration
Programming        Visual Basic (VB), All                    All                        VB and VC#         All
languages          VC#, VC++, and
included           VJ# (single
                   language)—Visual
                   Web Developer
                   includes VC#
                   and VB
Office development No                 No                     No                         Yes                Yes
support
User experience    Simplified menu    Simplified menu        Full                       Full               Full
                   options and        options and
                   defaults           defaults
Database           Local database     Local and remote       Local and remote           Local and remote   Local and remote
design tools                          databases              databases                  databases          databases
Data Access        VB, VC#, VC++, Local and remote           Local and remote           Local and remote   Local and remote
Designers          VJ# (local         databases              databases                  databases          databases
                   database), Visual
                   Web Developer
                   (local and remote
                   databases)

     As you would expect, all editions of Visual Studio 2005 support the 32-bit x86 platform. How-
ever, to get x64 platform support, you must have the Visual Studio 2005 Standard Edition, Visual
Studio Professional Edition, or the Visual Studio Team System. The lower-end Visual Studio Express
products are restricted to creating 32-bit applications. You’ll find support for creating Itanium-based
64-bit applications only in the Visual Studio Team System Edition.
     If you develop database applications or CLR-based database objects for SQL Server, you’ll want to
have the Visual Studio Professional, Tools for Office, or Team System edition. In addition to x64
platform support, these editions provide several database-oriented features, including the Server
Explorer, with which you can browse and manage databases; database design tools, which let you
create and modify tables and other database objects (e.g., stored procedures); and project templates,
which let you create SQL Server CLR database objects.
     Note that no specific x64 or Itanium-specific editions of Visual Studio 2005 exist. For the x64
platform, the Visual Studio 2005 development environment runs as a 32-bit process. However, you
can install and run Visual Studio 2005 on a 64-bit OS and use it to develop, run, and debug 64-bit



                                      Brought to you by AMD and Windows IT Pro eBooks
52   Moving SQL Server to the x64 Platform


applications. You can use project build settings to target the different platforms. The following section
presents more information about the specific use of Visual Studio 2005 to create x64 applications.

Developing 64-bit Applications for x64
The Professional, Tools for Office, and Team System editions of Visual Studio 2005 all support the
creation of x64 applications. The extended memory x64 architecture lets developers move their 32-bit
applications to pure 64-bit applications at their own pace while experiencing an improvement in
performance even before the applications are moved.
      When you use the default setup for Visual Studio 2005, the process of creating 64-bit applications
is entirely automatic and seamless. If you develop on an x64 machine, the applications you build
with Visual Studio 2005 will automatically be 64-bit x64 applications. Although the Visual Studio 2005
IDE runs as a 32-bit process, Visual Studio 2005 detects the type of OS it’s running on and automati-
cally sets up the build process to output code to match the host OS. In other words, when Visual
Studio 2005 runs on the 32-bit version of Windows, it creates 32-bit .NET applications, and when it
runs on an x64 edition of Windows, it outputs x64 code. What makes this automaticOS matching
possible is the combination of the CLR’s JIT support and the .NET Framework compiler, which
outputs Microsoft Intermediate Language (MSIL) code rather than a directly executable binary image.
The CLR always JIT-compiles the code before it’s executed, and the MSIL code the .NET compiler
produces is essentially the same for the 32-bit and 64-bit compilers.
      Visual Studio 2005 project settings control the code output. Executable programs created on the
.NET Framework 2.0 by using the default target platform setting Any CPU are compatible with both
the 32-bit .NET Framework and the 64-bit .NET Framework. For example, if you create a 32-bit .NET
Framework 2.0 application using the 32-bit version of Windows XP and copy the executable to an
x64 system running the 64-bit version of the .NET Framework, the 64-bit CLR on the x64 system uses
its JIT compiler to automatically recompile the application as a 64-bit application. The first time the
application is executed, a brief delay occurs while the JIT converts the code; thereafter, the
application runs as a 64-bit application. And you can just as easily go the other way, creating 64-bit
applications on the x64 platform and copying and running them on a 32-bit system. The 32-bit CLR
will JIT-compile the code, automatically transforming the application to a 32-bit .NET application.
      For 100 percent managed code—which includes the vast majority of SQL Server 2005 x64
database and CLR applications—the process is seamless and automatic. However, if your .NET code
makes calls to unmanaged code, you must address several considerations, such as pointer size and
memory alignment. For more information about these considerations, see the Microsoft Webcast at
http://msevents.microsoft.com/cui/webcasteventdetails.aspx?eventid=1032259880&eventcategory=
5&culture=en-us&countrycode=us.

Specifying a Target Platform
The Visual Studio 2005 Configuration Manager controls the type of code that the Visual Studio 2005
build process creates. The default platform setting of Any CPU lets Visual Studio 2005 automatically
detect the underlying OS and generate matching code. However, you can also instruct Visual Studio
2005 to output code that’s specific to a given platform. You might want to do so if you know the
code should execute only on a given platform—for example, if segments of the code are platform-
specific.


                                   Brought to you by AMD and Windows IT Pro eBooks
                                          Chapter 4 Sixty-Four-Bit Database Application Development 53


    To explicitly set Visual Studio 2005 to output code for the x64 platform, open a new Visual
Studio project, and from the Build drop-down menu, select Configuration Manager option to start the
Configuration Manager, which Figure 4.4 shows.

                                                 Figure 4.4
                            Visual Studio 2005 Configuration Manager




    Visual Studio 2005 has four supported platform settings:
    • Any CPU—This setting is the default setting. It lets the application support all hardware plat-
       forms. It will run as 32-bit on a 32-bit platform and 64-bit on a 64-bit platform.
    • x86—With this setting, an application will always run as a 32-bit application. If the application
       is executed on an x64 system, it will run as a 32-bit application in the WOW64 subsystem.
    • x64—With this setting, an application will always run as a 64-bit x64 application. If the appli-
       cation is executed on a 32-bit system, it won’t run.
    • Itanium—With this setting, an application will always run as a 64-bit Itanium application. If the
       application is executed on a 32-bit or x64 system, it won’t run.

     To create an x64 platform-specific application with Configuration Manager, from the Active solu-
tion configuration drop-down menu and select the <New> option. Doing so displays the New Solu-
tion Platform dialog box that Figure 4.5 shows.




                                  Brought to you by AMD and Windows IT Pro eBooks
54   Moving SQL Server to the x64 Platform



                                                  Figure 4.5
                              Visual Studio 2005 New Solution Platform




     In the New Solution Platform dialog box, from the Type or select the new platform drop-down
menu, select x64 or the new platform prompt. Keep the default value of Any CPU in the Copy set-
tings from: prompt. Click OK to close the New Solution Platform dialog box and return to Configura-
tion Manager. Once there, use the Active solution platform drop-down menu to select the x64
platform option.


n Note   Remember that this process isn’t required to create a .NET application that runs on an x64
         system. That happens automatically. It’s required only when you want to restrict the
         application to a given platform.


Developing 64-bit CLR Database Objects for x64
Developing 64-bit CLR database objects for the x64 editions of SQL Server 2005 is exactly the same as
developing them for the 32-bit edition of SQL Server. If you use the Visual Studio 2005 Professional
Edition, Tools for Office, or Team Systems edition, simply open a new project and select the SQL
Server Project Template. Add a database reference to your target SQL Server database system so
Visual Studio knows how to deploy the database objects. From the project menu, select the
appropriate Item template for the type of object you want to create. Visual Studio 2005 supports
creating the following database objects:
     • stored procedures
     • user-defined functions
     • triggers
     • user-defined types
     • aggregates




                                   Brought to you by AMD and Windows IT Pro eBooks
                                          Chapter 4 Sixty-Four-Bit Database Application Development 55


     Visual Studio 2005 creates the starter project to which you add your code. When the coding is
finished, you deploy the solution to the target SQL Server system. Remember that no matter which
platform you use for development—whether it’s for a 32-bit or x64 system—the .NET Framework
automatically handles the conversion of the program to the appropriate format for the native platform.
The .NET framework performs the conversion as long as you use the default setting of Any CPU for
the target platform.

Next: Migrating from 32-Bit to 64-Bit
Chapter 4 has presented some of the advantages of moving your applications from the 32-bit platform
to the x64 platform as well as the tools and processes available to create x64 applications. In Chapter
5, I’ll discuss how you can migrate from 32-bit SQL Server 2005 and Analysis Services 2005 to the x64
editions of SQL Server 2005 and Analysis Services 2005. I’ll cover migrating your application code and
explore some server-consolidation strategies.




                                  Brought to you by AMD and Windows IT Pro eBooks
 Moving
SQL Server

  x64
  to the



  Platform By Michael Otey


                   sponsored by
                                                                                                                                  v




Contents
Chapter 5: Migrating to SQL Server x64 . . . . . . . . . . . . . . . . . . . . . . . . . . 56
  Migrating Databases to 64-Bit SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
      Detach/Attach . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .    58
      Backup and Restore . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .       59
  Migrating to 64-Bit Analysis Services                    . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
      Backup and Restore . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .       60
      Analysis Services Deployment Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .              61
  Pricing and Licensing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
  Revisiting the Benefits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
  The 64-Bit Future . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
56


Chapter 5:

Migrating to SQL Server x64
In the previous four chapters, you learned about the server-level benefits of the x64 architecture and
the performance and scalability enhancements that it provides for the Microsoft SQL Server database
platform. Specifically, you saw how the SQL Server relational database engine, Analysis Services OLAP
processing, Integration Services extraction, transformation, and loading (ETL) functions, and Reporting
Services report rendering all derive specific benefits from the increased memory capacity and superior
system throughput the x64 architecture provides. You also saw how you can use Microsoft Visual
Studio 2005 and the Windows .NET Framework 2.0 to seamlessly develop both 32-bit and 64-bit
applications. In this chapter, I discuss how to migrate from the 32-bit SQL Server platform to the
64-bit SQL Server 2005 x64 platform. I go through the steps that let you move SQL Server 2005
relational databases and Analysis Services cubes to the x64 platform. In addition, I review the
licensing considerations.
     Migrating from the 32-bit database platform to the 64-bit x64 platform is a four-step process that
requires changing the system server hardware, the OS, and the database server software. Figure 5.1
presents an overview of the steps involved in migrating from the 32-bit version of Windows Server
and SQL Server to the 64-bit bit editions of Windows Server and SQL Server.

                                                   Figure 5.1
                     Moving from 32-bit SQL Server to 64-bit SQL Server 2005

                         32-Bit                                              64-Bit
                       SQL Server                                        SQL Server x64
                1
                        32-Bit                                                 x64
                       Hardware            New server system                 Hardware

                2                                                        Windows Server
                        32-Bit
                                             OEM license or               2003 R2 x64
                    Windows Server          license exchange                Edition
                3        32-Bit                                              SQL Server
                       SQL Server         New SQL license or                  2005 x64
                                           license exchange                    Edition
                4   32-Bit Database                      64-Bit Database
                      and Cubes     Copy data: identical   and Cubes
                                     on-disk structures




                                    Brought to you by AMD and Windows IT Pro eBooks
                                                                       Chapter 5 Migrating to SQL Server x64 57


     On the server system side, you need a 64-bit x64–compatible system. Your x64-compatible
servers must use either AMD Opteron or Intel EM64T processors. Currently, x64 systems are
already the predominant server system offerings from all of the Tier 1 hardware vendors
(HP, IBM, and Fujitsu Siemens; Dell has announced that it will have Opteron systems available
by the end of the year). For more information, go to
http://news.com.com/dell+opts+for+amds+opteron/2100-1006_3-6074059.html and
http://www.businessweek.com/technology/content/may2006/tc20060519_952193.htm.
     When you select new hardware, it’s vital to remember that one of the primary advantages the
x64 platform offers is its increased memory capacity. It would be a mistake to size your new x64 with
the same memory used in the 32-bit system it’s replacing. Certainly, less than 4GB won’t let you
benefit from one of the main x64 advantages. When you size the new system for database and/or
Business Intelligence (BI) workloads, you can’t go wrong by adding RAM. You should consider
equipping new servers with either the maximum RAM capacity or as much capacity as your
organization can afford.
     The new x64 server must run one of the following x64 editions of the Windows Server 2003 OS:
     • Windows 2003 Release 2 (R2) Standard x64 Edition
     • Windows 2003 Enterprise x64 Edition
     • Windows 2003 Datacenter x64 Edition

     You’ll need to address licensing. The x64 editions of the Windows 2003 OS are priced identically
to the 32-bit editions. Most OEM server vendors offer preinstalled versions of the Windows 2003 x64
OSs. In addition, Microsoft volume licensing customers can freely exchange their existing 32-bit
versions of Windows for x64 editions. (For more about licensing, see the section “Pricing and
Licensing.”)
     After the 64-bit x64 hardware is in place and an x64 edition of the Windows 2003 OS has been
loaded, you must install one of the x64 editions of SQL Server 2005. Doing so provides you with a
full native 64-bit hardware and software stack. Microsoft offers two x64 editions of SQL Server 2005—
SQL Server 2005 Standard x64 Edition and SQL Server 2005 Enterprise x64 Edition.
     Although migrating from the 32-bit version to the 64-bit version of SQL Server involves a number
of steps, migration is a straightforward and safe process. To ensure the correct operation of the new
x64 platform, you can either use two servers to run the 32-bit server with the new 64-bit server or
use a side-by-side style of migration. For side-by-side migration, you can load the 32-bit version of
SQL Server on the x64 hardware and OS so that the 32-bit database instance and the 64-bit database
instance run simultaneously.
     The final step in migrating to the x64 edition of SQL Server is moving your data from the old
32-bit platform to the new 64-bit platform. This task is fairly easy because all of the on-disk structures
the 32-bit server used are the same for the 64-bit editions: No data changes are required. In the next
section of this chapter, I explore in detail moving the data from a 32-bit version of SQL Server to one
of the new 64-bit SQL Server 2005 x64 editions.

Migrating Databases to 64-Bit SQL Server
Migrating from a 32-bit SQL Server relational database installation to a new x64 installation is a direct
and clear-cut process. When Microsoft redesigned SQL Server 7.0 in 1998, the company developed
the system with 64-bit capabilities in mind. All the on-disk structures the 32-bit version of SQL Server

                                   Brought to you by AMD and Windows IT Pro eBooks
58   Moving SQL Server to the x64 Platform


uses are identical to the on-disk structures the 64-bit editions of SQL Server uses. The identical
structures make moving your database from the 32-bit version to the 64-bit version easy. After you
install the new x64 hardware platform, a new x64 edition of the Windows 2003 OS, and the SQL
Server 2005 Standard x64 Edition or the SQL Server 2005 Enterprise x64 Edition, you have two
primary options for migrating your databases from the 32-bit server to the x64 server: detach/attach or
backup and restore.

Detach/Attach
By using the sp_detach_db and sp_attach_db stored procedures, you can move one or more
databases from a 32-bit SQL Server system to a 64-bit SQL Server system. Figure 5.2 presents an
overview of this process.

                                                  Figure 5.2
                                An overview of the detach/attach process



                          SQL Server                                        SQL Server
                                                                              64-Bit


                           sp_detach_db                                       sp_attach_db


                                                        COPY
                         .mdf           .ldf                              .mdf          .ldf

                        Data       Transaction                            Data       Transaction
                        File          Logs                                File          Logs


     In this scenario, you first run the sp_detach_db stored procedure to detach one or more
databases from the 32-bit SQL Server system. Doing so takes the database offline, which lets you
move the database data files. SQL Server databases are stored in OS files that end with the extensions
.mdf (for the primary data file) and .ldf (for the transaction log). Most databases will have one .mdf
and one .ldf file. However, some larger databases use multiple file groups, which consist of multiple
.mdf and .ldf files. After the data and log files are detached, you can copy those files across the
network to the new 64-bit SQL Server system by using a standard file-system management tool such
as Windows Explorer or a command-line tool such as the Xcopy utility.
     After the databases have been copied to the 64-bit system, you can run the sp_attach_db stored
procedure to bring the databases online. No changes are required for the database files. You should
follow this procedure for each user database that you want to migrate to the x64 platform.




                                   Brought to you by AMD and Windows IT Pro eBooks
                                                                        Chapter 5 Migrating to SQL Server x64 59




n Note   Detaching and attaching databases is a great way to move and copy databases between
         different SQL Server systems. For example, to move databases between a development and
         production server, you can detach a database from the development server, copy the data
         (.mdf) and log files (.ldf) to the production server system, then reattach the database to make it
         available on the target system. Either server could be 32-bit or 64-bit.

     You can run both the sp_detach_db and the sp_attach_db stored procedures from the Microsoft
SQL Server Management Studio by using OSQL or Sqlcmd. To detach a database, enter the following
at a command line, substituting the name of your database with for ‘database_name’:
exec sp_detach_db ‘database_name’

    To reattach the database, enter the following at a command line, substituting the name of your
database for ‘database_name’:
exec sp_attach_db’database_name’,
  ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\database_name.mdf’,
  ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\database_name.ldf’


Backup and Restore
The process of using backup and restore to move databases from a 32-bit server to a 64-bit server is
quite similar to the detach/attach process. On the 32-bit SQL Server system, you can back up the
databases by using the T-SQL BACKUP command. Alternatively, you can use the graphical SQL Server
Management Studio’s backup capabilities. Backing up a SQL Server database creates a file-system file
that ends in .bak. You then use a file-system management tool such as Windows Explorer or a
command-line tool such as Xcopy, to copy the resulting .bak file from the 32-bit server to the 64-bit
server. After you copy the .bak file to the 64-bit server, you can restore it by using either the T-SQL
RESTORE command or SQL Server Management Studio.
     You can run the T-SQL BACKUP and RESTORE commands from the SQL Server Management
Studio or by using OSQL or Sqlcmd. To back up a database, enter the following BACKUP command
at a command line, substituting the name of your database for ‘database_name’:
BACKUP DATABASE database_name TO DISK = ‘c:\database_name.bak’

    The BACKUP command creates a file with the extension .bak. In this example, the .bak file will
be created in the c:\ root directory. This file contains all of the data from the selected SQL Server
database. You can then copy it to the target x64 system and restore it by using the following command:
RESTORE DATABASE database_name FROM DISK = ‘c:\database_name’


Migrating to 64-Bit Analysis Services
The process of moving from 32-bit Analysis Services to 64-bit Analysis Services is similar to the
backup and restore scenario for moving SQL Server relational databases. Just like the SQL Server


                                    Brought to you by AMD and Windows IT Pro eBooks
60    Moving SQL Server to the x64 Platform


relational database files, Analysis Services on-disk structures use exactly the same format for 32-bit
Analysis Services that they use for 64-bit Analysis Services. You have two primary options for
migrating your Analysis Services databases from the 32-bit server to the x64 server: You can use
backup and restore or the Analysis Services Deployment Wizard.

Backup and Restore
To move cubes from the 32-bit version of Analysis Services by using the backup and restore
methodology, you first back up the 32-bit database to an Analysis Backup File (which has the
extension .abf), then restore it to the 64-bit system. Figure 5.3 presents an overview of this process.

                                                  Figure 5.3
                       Analysis Services backup and restore migration process



                       Analysis Services                               Analysis Services
                                                                            64-Bit


                           XMLA BACKUP                                     XMLA RESTORE


                                                        COPY
                                 .abf                                                .abf




     To migrate Analysis Services databases from a 32-bit Analysis Services system to a 64-bit Analysis
Services system with the backup and restore method, you can use either the graphical interface the
SQL Server Management Studio provides or XML for Analysis (XMLA) backup and restore statements.
The backup process will create an .abf file on the file system. After you back up the Analysis Services
database, you can copy it to the x64 destination server by using any standard file-copy mechanism
including the graphical Windows Explorer or Xcopy. After the file has been copied to the target
server, you can then use the XMLA restore command or the SQL Server Management Studio to
restore the contents of the .abf file to the x64 Analysis Services.
     To back up an Analysis Services database by using XMLA, you can issue the following example
command, substituting the name of your Analysis Services database for ‘database_name’:
<Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
  <Object>
    <DatabaseID>database_name</DatabaseID>
  </Object>
  <File>c:\database_name.abf</File>
  <ApplyCompression>false</ApplyCompression>
</Backup>


                                   Brought to you by AMD and Windows IT Pro eBooks
                                                                       Chapter 5 Migrating to SQL Server x64 61


     This command will back up the specified Analysis Services database to a file named
database_name.abf in the c:\ root directory.
     To restore the Analysis Services database to the x64 Analysis Services system, you can issue the
following example XMLA restore command, substituting the name of your database for
‘database_name’:
<Restore xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
  <File>c:\ database_name.abf</File>
  <DatabaseName> database_name </DatabaseName>
</Restore>


Analysis Services Deployment Wizard
You can also use the Analysis Services Deployment Wizard to migrate Analysis Services 2005
databases from 32-bit systems to 64-bit systems. The wizard can take the output from an Analysis
Services project and use it to create and process an Analysis Services database on the target server.
However, to use the Analysis Services Deployment Wizard, you must have network connectivity
between the 32-bit source system and the 64-bit target system. To run the Analysis Services
Deployment Wizard, use the Start, All Programs, Microsoft SQL Server 2005, Analysis Services,
Deployment Wizard option. After it displays a Welcome screen, the wizard’s first dialog box prompts
you for the name and location of your Analysis Services project file, as Figure 5.4 shows.

                                                  Figure 5.4
                        Specify Source Analysis Services Database dialog box




     After specifying the source path to your Analysis Services project, clicking Next displays the
Installation Target dialog box that Figure 5.5 shows.




                                   Brought to you by AMD and Windows IT Pro eBooks
62   Moving SQL Server to the x64 Platform



                                                  Figure 5.5
                                    Installation Target dialog box




     The Installation Target dialog box lets you select the x64 target server. You use the Server prompt
to specify the name of the x64 target server. You use the Database drop-down menu to specify the
name of the Analysis Services database that will be created on the target server. Generally, you would
want to use the same name as the source system, but you also have the option to change it. Clicking
Next displays the Specify Options for Partitions and Roles dialog box that Figure 5.6 shows.




                                   Brought to you by AMD and Windows IT Pro eBooks
                                                                      Chapter 5 Migrating to SQL Server x64 63



                                                 Figure 5.6
                         Specify Options for Partitions and Roles dialog box




    You use the Specify Options for Partitions and Roles dialog box to specify how the wizard
should treat database partitions as well as the roles and members in the database. The default settings
deploy the partitions of the original database along with the roles and members that exist. The default
value for the Roles and members prompt adds the roles and members from the source
database to any existing roles and members. This approach is typically what you want during a
migration scenario, but you can also choose to replace all the roles and members or not to deploy
the existing roles and members. Clicking Next displays the Specify Configuration Properties dialog
box that Figure 5.7 shows.




                                  Brought to you by AMD and Windows IT Pro eBooks
64   Moving SQL Server to the x64 Platform



                                                  Figure 5.7
                             Specify Configuration Properties dialog box




     The Specify Configuration Properties dialog box lets you set up the configuration parameters for
the Analysis Services database. By default, these values are all taken from the source database and
transferred to the new x64 target database. The most important of these values is the connection
string that connects to the datasource. In a typical migration scenario, all of the datasource would be
present on the target system, and you would want to keep the default values. Clicking Next displays
the Select Processing Options dialog box that Figure 5.8 shows.




                                   Brought to you by AMD and Windows IT Pro eBooks
                                                                       Chapter 5 Migrating to SQL Server x64 65



                                                  Figure 5.8
                                 Select Processing Options dialog box




    The Select Processing Options dialog box controls how the Analysis Services objects will be
processed (i.e., populated with data) after the database is built on the target server. The Select
Processing Options dialog box is the final wizard dialog box that lets you make any changes to the
deployment process. Clicking Next displays a confirmation dialog box that lets you check all of your
previous selections. You have the option of clicking Back to page back and make any needed
changes. Clicking Next again runs the deployment process and builds the Analysis Service database
on the target x64 system.
    You can also deploy Analysis Services databases to 64-bit servers by using the Visual Studio 2005
project deployment options. However, this option is typically used for creating new Analysis Services
databases.

Pricing and Licensing
Microsoft’s licensing costs for the x64 editions of SQL Server 2005 are identical to those for the 32-bit
editions. No unexpected pricing hurdles bar the adoption of x64 technology. Microsoft also lets
Volume Licensing and Software Assurance customers upgrade from an existing 32-bit SQL Server 2005
license to the 64-bit x64 edition license at no cost.
     In addition, you face no extra charge for using multicore processors, and Microsoft has com-
mitted to supporting this practice (see http://www.microsoft.com/licensing/highlights/multicore.mspx).
Under SQL Server’s per-processor licensing model, you need only license each physical socket.

                                   Brought to you by AMD and Windows IT Pro eBooks
66   Moving SQL Server to the x64 Platform


Neither the number of processor cores used in that socket nor the number of logical processors
changes the licensing cost. For example, if you have a 2-processor (2-P) system running two dual-
core Opteron processors, you have two CPU sockets on the motherboard but four real CPUs in the
system. In this scenario, if you chose to use SQL Server’s per-CPU licensing model, you would need
only a two-CPU license—one for each CPU socket on the motherboard. Likewise, if you were
upgrading to a 4-P system that used four dual-core processors (e.g., the HP DL585), you would need
only a four-CPU license even though the system actually has eight processors. Because dual-core pro-
cessors are on the same die, they’re licensed as a single processor.

Revisiting the Benefits
The x64 technology provides a number of essential advantages to all SQL Server 2005 installations in
terms of scalability, manageability, cost effectiveness, and application compatibility.
    • Scalability—The x64 platform provides great performance, and several world-record
       Transaction Processing Performance Council (TPC)-C and SAP Sales and Distribution (SD)
       Users benchmarks offer clear-cut proof of the platform’s performance advantages. The large
       memory addressability and nearly unlimited virtual memory result in vastly improved
       performance and reduced need for system I/O. In addition, AMD Direct Connect Architecture’s
       integrated memory controller and 8GBps HyperTransport Bus on the CPU, memory, and I/O
       connections provide reduced system latencies, better support for parallel processing, and
       higher data throughput.
    • Manageability—The management tools that the x64 editions of SQL Server 2005 and Analysis
       Services provide are completely “feature compatible” with their 32-bit counterparts. In addition,
       T-SQL code is 100 percent compatible between the 32-bit version of SQL Server and the x64
       edition. .NET Framework 2.0 application code supports seamless 32-bit to 64-bit just-in-time
       (JIT) compilation. Identical on-disk formats make it easy to migrate the 32-bit versions of both
       SQL Server 2005 and Analysis Services 2005 to the x64 platform. The setup process is the
       same for the 32-bit and 64-bit versions of SQL Server 2005, and the Windows Installer
       technology lets you create automated server deployments.
    • Cost effectiveness—The licensing cost of the 32-bit and 64-bit versions of SQL Server 2005 is
       the same. You face no additional licensing charge if you want to take advantage of the higher
       performance multiple-core processor technologies. Running the x64 platform Windows 2003
       and SQL Server 2005 can be a compelling alternative to UNIX and other proprietary solutions.
       The x64 platform’s scalability lets you use it effectively in server consolidation scenarios to
       reduce hardware, infrastructure, and management requirements. Combining the performance
       with the power-efficient characteristics of the AMD Opteron line of processors lets you deploy
       greater computing power in less physical space with lower ongoing energy and cooling costs.
    • Application compatibility—In addition to its higher performance and vastly increased memory
       capabilities, one of the strongest arguments for the x64 architecture is its ability to simultane-
       ously run both 32-bit and 64-bit applications at full speed. The 100-percent binary compatibil-
       ity of the x64 architecture lets organizations move to native 64-bit applications at their own
       pace while continuing to run all of their existing 32-bit applications at performance levels that
       often exceed the performance that the native 32-bit platform provides.




                                   Brought to you by AMD and Windows IT Pro eBooks
                                                                       Chapter 5 Migrating to SQL Server x64 67



The 64-Bit Future
Bob Muglia, Microsoft Vice President of Server and Tools Business, in his address at the Microsoft
Professional Developers Conference (PDC) 2005, summed up Microsoft’s view of the 64-bit x64
technology: “As we move forward . . . I really think of 32-bit as legacy . . . the future is all 64-bit.”
Almost a year later, that statement really rings true.
     Most of the servers that Tier 1 hardware vendors sell are x64 compatible. Microsoft itself has fully
supported the x64 platform and has released native x64 versions of the Windows OS as well as x64
SQL Server 2005 Standard and Enterprise editions. Although a few minor differences between the
32-bit and 64-bit editions of these products exist, the x64 editions are feature complete. Microsoft’s
future commitment to the x64 platform as a server architecture is also evident in the next release of
Exchange 2007—the product is planned to be x64 only.
     The x64 architecture has ushered in the era of 64-bit computing, and SQL Server 2005 is
designed to take full advantage of the benefits the x64 architecture offers. You’ll have the perfor-
mance and scalability that you’ll need for your current and future database application requirements.




                                   Brought to you by AMD and Windows IT Pro eBooks

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:73
posted:11/13/2011
language:English
pages:77