preparing for the 64-bit platform ron van moorsel, senior dba consultant sql services ltd rob...

Preparing for the 64-bit Platform

Ron van Moorsel, Senior DBA ConsultantSQL Services Ltd

Rob Hawthorne, SQL Server Solution SpecialistMicrosoft NZ Ltd

Wednesday, 26th July 2006

Preparing for the 64-bit Platform

Ron van Moorsel, Senior DBA ConsultantSQL Services Ltd

Rob Hawthorne, SQL Server Solution SpecialistMicrosoft NZ Ltd

Wednesday, 26th July 2006

Agenda 64-bit SQL Server

Introduction Platforms Benefits Challenges and Best Practices Summary and Questions

Introduction Speaker’s Bio

Ron van Moorsel Senior DBA Consultant for SQL Services Ltd SQL Server and Oracle Certified DBA since 1999 Almost 20 years IT experience, 10 years in Europe with

multinationals like Philips and Nissan working with mainframes

Rob Hawthorne Microsoft NZ’s SQL Server Solution Specialist Been working with SQL Server since early version 6.0 Focused on high availability and scalability Author of “SQL Server Database Development from Scratch”

Key Takeaways

64-bit Platforms

Server platforms are now 64-bit ready You have choices about the SQL Server

configuration You even have a choice about the type of 64-bit

to run, IA64 or x64

Properties of Itanium (IA64) Runs 64-bit Windows, drivers and

software specifically compiled for the Itanium instruction set

Runs 32-bit software without being recompiled

Cannot act like an x86 (32-bit) processor or boot 32-bit Windows

Does not run versions of Windows or 64-bit drivers compiled for x64

Intel Itanium - 2 offerings Aimed at the larger processing

environments, for example: Greater than 4 CPUs (sockets) 64 CPUs Massive scalability database target market

i.e. More than 5,000 users (achieved 30,000 concurrent users)

See http://www.tpc.org (Transaction Performance Processing Council)

Future versions of the processor coming, code name “Montvale”

Vendors: HP, Unisys, NEC, Fujitsu, Bull

http://www.tpc.org/

http://www.tpc.org/

Properties of x64 Runs 64-bit Windows, drivers and software

specifically compiled for x64 instruction set Runs 32-bit software without recompilation

Note: Think of a half-way house Does not run Itanium versions of Windows

nor drivers compiled for Itanium Note: Specific x64 drivers required, not all 64-bit

drivers are equal!

x64 Offerings Two chip vendors, same OS required

AMD (AMD64) Intel (EM64T)

X64 was the first to offer dual-core Generally aimed at <= 4-CPUs, however…

Unisys - 32 socket IBM – 16 socket

Multi-Core and Hyper-Threading Multi-Core chips scale very effectively

Dual-core offers >> 50% performance benefit relative to single core

SQL Server operates as though each core is a separate CPU

SQL Server is priced per socket, not per core (lower TCO)

Hyper-Threading does not benefit typical SQL Server workloads

Generally recommend disabling Hyper-Threading

Potentially overloads a single core with multiple concurrent scheduler tasks

Multiple threads can thrash the CPU cache

Windows on Windows (WOW)

WOW allows 32-bit applications to run on 64-bit Operating Systems

SQL Server will NOT support both 32-bit SQL in WOW and native 64-bit SQL co-existing on same server

32-bit OS

32-bit App

32-bit App

x64 Hardware

64-bit OS

64-bit App

64-bit App

x64 Hardware

64-bit OS

32-bit App

32-bit App

x64 Hardware

WOW

32-bit App running on 32-bit OS

64-bit App running on 64-bit OS

x64 Hardware = AMD64 and Intel EM64T

32-bit App running on 64-bit

OS (in WOW)

32-bit App

WOW

64-bit OS

x64 HW

What is 64-bit SQL Server? Same code-base as 32-bit SQL Server

2005 Allows for flat memory addressing Supports both IA64 and x64 Data files fully compatible with 32-bit SQL

Easy Database Migration & Integration Massive scale-up support, example:

64-way HP Integrity >1,000,000 TPC number

8-node fail-over clustering support

64-bit gaps in SQL Server 2005 Visual Studio (VS) not supported on Itanium

Although management tools are supported BIDS not supported on Itanium – affects SSIS

Must develop and debug packages on a 32-bit server and deploy to IA64

Watch out for driver compatibility! x64 BIDS runs in the WOW

SSIS needs 32-bit drivers during development and 64-bit drivers at run time

Trade off between high processor speed vs. flat memory and scale-up (1.6GHz vs. 3.2GHz)

32-bit SQL Server on x64

32-bit SQL Server 2005 (server & tools) supported on x64 running 64-bit Windows

Under WOW64, SQL Server can access FULL 4GB (VAS) of RAM, as well as AWE

Why would you want to run 32-bit OS instead? Drivers Coexistence with other applications or tools that

aren’t WOW-certified Warning:

http://blogs.msdn.com/slavao/archive/2006/03/12/550096.aspx



SQL Server on x64 and Itanium

x64 ItaniumSQL Server

Edition

32-bit OS

64-bit OS

64-bit OS

64-bit OS

64-bit OS

WOW64 WOW64 SQL Server 2000 32-bit

(SP4) Yes Yes No No No

SQL Server 2000 64-bit Itanium No No No No Yes

SQL Server 2005 32-bit Yes Yes No No No

SQL Server 2005 64-bit x64 No No Yes No No

SQL Server 2005 64-bit Itanium No No No No Yes

SQL Server Express 32-bit Yes Yes No No No

SQL Server Mgmt Studio 32-bit Yes Yes No Yes No

SQL Server BI Dev Studio 32-bit Yes Yes No No No

Memory Overview 32-bit applications

Limited to a 4GB Virtual Address Space (VAS) Applications can use 2, 3 or even 4GB

Workaround: AWE Some applications can use more than 4GB SQL Server: can only be used for data cache Imposes some overhead SQL Server applications that are not AWE aware:

SSAS, SSIS, SSRS, CLR

Memory Overview – Continued 64-bit applications like SQL Server 64-bit

Flat, huge virtual address space can use all memory available without AWE

No mapping needed All SQL Server services can use all addressable

memory: SSAS SSRS SSIS SSNS

Oth

er

Lo

cks

Qu

ery

Wo

rksp

ace.

Pla

n C

ach

e

DB

Pag

e C

ach

eD

B P

age

Cac

he

SQL Server Memory (32-bit)SQL Server Memory (32-bit)

Thread stacks, DLLs,CLR, etc

AWE Addressable

Memory

Buffer Pool(8KB buffers)

Where Will 64-bit SQL Server Help?Relational Engine

Memory hungry workloads:

Plan Cache Workspace Memory Connection Memory Lock Memory: Large-scale OLTP Data Buffer Cache

High Concurrency OLTP / Large DW Row versioning: Resolve writers blocking readers

issue Partitions – on one serverFirst, measure memory pressure

Where Will 64-bit SQL Server Help?Analysis Services (SSAS) Large dimensions

SSAS 2005 queries cached in memory Frees up the Relational Engine

Large memory for Process Buffers Large cubes and fast processing

Very large Filesystem Cache Large number of concurrent users

First, measure

Where Will 64-bit SQL Server Help?Integration Services (SSIS) Large-scale transformations:

Sort Aggregate Key Lookups (cached)

Packages failures due to memory constraints

Where Will 64-bit SQL Server Help?Reporting Services (SSRS) Reporting Services cannot use AWE But on 64-bit it can access all available

memory Large and/or complex reports

Where Will 64-bit SQL Server Help?Scale-Up, Performance and Consolidation Itanium: today’s choice for workloads requiring >

8 CPUs Itanium offers excellent scaling

x64 Xeon scaled-up servers are available x64 offers fastest CPU performance today Consolidation of SQL Server Platforms

Reduced cost of administration, licenses, etc Warning: Generally, no virtualisation of the data tier in

production

Agenda 64-bit SQL Server Introduction Platforms Benefits Challenges and Best Practices

Drivers Memory Configuration NUMA Performance

Summary and Questions

Challenges - Drivers 64-bit drivers for Data Access

AS and IS require 64-bit versions of 3rd Party OleDB drivers to support sources such as Oracle, Informix…

No ODBC driver access for AS since OleDB for ODBC is not ported

MS provides 32-bit Oracle OleDB provider – not 64-bit – have to get it from Oracle

64-bit drivers and software for hardware

NZ Horror stories Ask the hard questions of vendors before purchasing HCL (http://www.microsoft.com/hcl): validate

http://www.microsoft.com/hcl

Agenda 64-bit SQL Server Introduction Platforms Benefits Challenges and Best Practices



Challenges – Memory Configuration

Scenarios: Multiple SQL Instances on server Multiple Service components on server,

e.g. SSAS, SQL Relational Engine, SSIS

Note, both 32-bit and 64-bit memory allocation needs to be configured appropriately – nothing new here

64-bit Memory Configuration Integration Services

Cannot impose explicit memory limits Large Sort, Aggregation and Cached

Lookup operations can consume a lot of virtual memory

Hint: Retrieve minimum necessary fields If still too much memory pressure, consider

using DB operations instead, or running IS on a separate hardware partition or server

Memory Configuration Alternatives Integration Services Use separate hardware partitions (NUMA)

or separate servers for solutions that combine Relational Engine, SSAS and SSIS

Eliminates risks of cross-application memory contention

But this may underutilise CPU resources

OR Manage memory use explicitly for each

component running on a single serverNote: What isn’t an option?

64-bit Memory ConfigurationRelational Engine If multiple SQL Server instances share the

server

Or If multiple applications (SSAS, SSIS) coexist

on the server:Best Practice: place memory ceiling on

each SQL instanceOr even use a fixed memory size

Lock pages in memory to prevent OS paging of SQL under memory pressure

64-bit Memory ConfigurationAnalysis ServicesConsumes memory in two ways

Virtual Memory for SSAS ServiceOS Memory used as Filesystem cache

Potentially unbounded, may starve OS of memory

If SSAS is running on a server with other critical services (SQL or SSIS):

Best Practice: establish a maximum memory size for Analysis Services process and limit growth of Filesystem Cache

Agenda

Introduction Platforms Benefits Challenges and Best Practices



NUMA Considerations All 64-bit servers that scale-up are NUMA

platforms NUMA = Non Uniform Memory Access Longer latencies for accessing memory on remote nodes

CPU

RAM

CPU CPU CPU CPU

RAM

CPU CPU CPU CPU

RAM

CPU CPU CPU

Pod/Node Pod/Node Pod/Node

Bus

Server

SQL Server 2005 and NUMA NUMA memory configuration is the preferred

choice on SQL 2005 servers with > 4 cores Many NUMA Enhancements in Relational Engine

Improved placement of objects in local cache Per-node lazywriter, free list and checkpointing Per-node resource management (threads) Per-node IO completion port

A SQL connection will remain on the same NUMA node for its lifetime

Potential for leveraging local cache for all of its tasks Can direct specific connections to specific nodes

Some NUMA ‘gotchas’ SSAS and SSIS are not NUMA aware

No guarantees of evenly distributed memory allocation OS will tend to fill up a node’s CPUs with work before

using other CPUs 1 node may be busy while the remainder are idle until

the node saturates Consider starting up SQL Server and warming

cache before launching SSAS May be effective in very large cube environments Avoids SSAS potentially grabbing all local memory for

one node Avoids filesystem cache from stealing pages

disproportionately from a single node – which could create a SQL node starved of local pages

Will code run faster on 64-bit? Maybe… Depends on a lot of factors

Is memory a bottleneck? Is the application CPU-bound? Are there pointers in the working set data? Is it instruction cache bound? Is it floating point intensive?

Result: Apps that are not memory constrained on 32-bit may run ~10% less efficiently using 64-bit edition than 32-bit edition on the same machine

You may see the CPUs busier on 64-bit than on 32-bit to perform equivalent workload

Performance Considerations If you are not under memory pressure, and do not

need to scale up, and you want the absolutely fastest single-thread performance

Use x64 instead of IA64 Consider using 32-bit SQL in the WOW or on 32-bit OS Intel’s (EM64T) larger L3 cache may have a noticeable

impact relative to AMD’s (AMD64) If you need to scale up for greater throughput

Use IA64 with large number of CPUs

or Consider newest scaled-up x64 servers

64-bit ‘gotchas’

Driver availability Developer tools support Multiple components competing for

memory (nothing new here) Potential NUMA effects Most applications will run faster, but …

Summary – Takeaways

64-bit SQL Server 2005 can leverage the power of x64 and IA64 platforms Allows SQL Server to fully address memory available

Plan carefully – think before you buy Understand the benefits and gotchas

SSAS, SSIS, SSRS can really benefit from 64-bit Recommended for high volume workloads, consolidation May not always be faster, beware of drivers Allows more effective use of row versioning giving

higher volume transactional throughput Assistance is available 64-bit will become the standard

Thank YouQuestions?

preparing for the 64-bit platform ron van moorsel, senior dba consultant sql services ltd rob...

Documents