Transcript Moving to 64-bit SQL Server 2005

Meeting Date: October 16, 2007
Topic: The 64-bit Question by Rick Heiges
www.Charlotte-SQL.org
The 64-bit Question:
Which Platform?
Presented by
Rick Heiges, SQL MVP
Sr. Solutions Consultant
[email protected]
SQL245
About the Presenter
Rick Heiges – Sr. Consultant – Scalability Experts
SQL Server MVP
MCP, MCSE, MCDBA, MCTS: SQL2005, MCITP: DBA
MBA, MS-MIS, BSCS
PASS – Director of Event Operations
Founder – Triad SQL Server User Group
Presented at PASS, local user groups, SQL Server Road
Shows, DevTeach (Top 5 SQL Track), SQL Connections
BLOG on sqlblog.com and sqljunkies.com
Articles in SQL Server Standard, Windows IT Pro
Webcasts - SQL 2005, 64-bit Computing, Consolidation
Ambassador at TechEd
Check out www.ricksql.com for more content!
Agenda
Key Differences in 32-bit v 64-bit
systems
Windows / SQL Versions
X64 or IA64 and Performance /
Scalability
Which Components Benefit
Migration to 64-bit SQL Server
Poll: SQL 2005 Live in your
environment?
No – All production servers are
SQL 2000 or earlier
Yes – We have 1 or 2 systems
“Live”
Yes – The Majority of our
production systems are SQL 2005
Yes – We are completely switched
to SQL 2005
Poll: 64-bit in your environment
Yes – We have at least one
production system using SQL
Server 2000/2005 64-bit
Yes – We have a majority of our
systems on SQL Server 2000/2005
on a 64-bit platform
No – That’s why I am here
Before we start - No Magic Bullet
Not Every work load will benefit
x64 and Itanium
Fundamental differences
Abbreviations – IA32, x86, x64, IA64,
IPF
OS / SQL mix
x86 on x86 OS on x64 Hardware
x86 on x64 OS on x64 Hardware
x64 on x64 OS on x64 Hardware
The 32-bit Challenge
Today, customers are experiencing unprecedented
challenges in their computing environment
Increased complexity in mixed IT environments
Ever increasing user counts
Power, space and heat restrictions
It is becoming increasingly difficult to sustain MissionCritical databases on 32-bit architectures
Difficulty in scaling to handle increasing database sizes
Difficulty in managing large concurrent user counts
There is a limited future for 32-bit processors, 32-bit
Operating Systems, and 32 bit databases
All current and future hardware and software
development is oriented towards 64-bit platforms
32-bit Addressing
32-bit processors have address registers that
are 32-bits wide (can address up to 4GB RAM)
Each individual process gets its own virtual
address space that can be up to 4GB
This virtual address space is divided – system
and user space, by default 2 GB each
The user space can be extended to 3 GB with
the 4 GB Tuning feature
/3GB flag = 4GB Tuning Feature – reduces system
space to 1GB, allowing 3GB for user space – also
/USERVA
Add to boot.ini file and reboot to take effect
32-bit Systems With PAE And AWE
PAE - Physical Address Extension
Allows 36-bit memory addressing
Allows the x86 processor to address up to 64GB of
physical RAM
Add /PAE switch in the boot.ini file and reboot server
AWE – Address Windowing Extension
AWE – a set of APIs that allow programs to address
memory locations outside of their 4GB virtual addressing
range
With AWE programs reserve memory as non-paged
(will not page out)
These methods add a translation layer to memory
access – slower than direct memory access
Performance Implications Of 32-bit Memory
Memory split into two parts is not as efficient as
memory in one segment
Overhead is introduced, memory access is substantially
slower
32-bit systems with 8 GB of RAM may not perform much
better than systems with 4 GB of RAM
Memory above 4 GB can only be used for Buffer
cache
Buffer cache does not support user connections;
all user connections must be supported from low
memory (<= 3GB)
AWE overhead may take up an additional 1 GB of low
memory, leaving only 1-2 GB for managing user
connections
SQL Server connections are lightweight, so 4,000+
connections are possible, even with limited memory
32-bit Memory Areas
Data Pages Only
32GB RAM
OS VAS
Additional App VAS
Application VAS
Route 64 – Where Business is Going
x
The 64-bit Solution
Microsoft has solved these problems by extending
their platforms to provide a robust Operating
System, Windows Server 2003 R2, for advanced
64-bit architectures. Note: In Windows 2003 64bit, Kernel and User mode split 16TB
Multi-core x64 processor-based systems offers
hybrid 32-bit/64-bit capabilities that make the
transition to 64-bit processing easy
Intel Itanium® 2 processor-based systems
(Currently Dual-Core) offers high-end performance
capabilities for the most demanding and missioncritical corporate databases
Microsoft offers Windows Server 2003 editions for
both the x64 and Itanium® 2 architectures
Agenda
Key Differences in 32-bit v 64-bit
systems
Windows / SQL Versions
X64 or IA64 and Performance /
Scalability
Which Components Benefit
Migration to 64-bit SQL Server
The 64-bit Solution – SQL Server
SQL Server 2005 offers 64-bit versions for both the
x64 and Itanium® 2 architectures
Compared to 32-bit database versions the 64-bit
Windows versions of SQL Server 2005 provide
outstanding performance
More Workloads per “u” of rackspace, Up to 50
Instances
All Components able to take advantage of Large
Virtual Address Space in SQL 2005
Windows Server 2003 Family
32-bit
x86
64-bit
x64
64-bit
Itanium
Up to 32way,
64 GB RAM
Up to 64-way,
1 TB RAM
Up to 64way,
1 TB RAM
Up to 8-way,
64 GB RAM
Up to 8-way,
1 TB RAM
Up to 8-way,
1 TB RAM
Up to 4-way,
4 GB RAM
Up to 4-way,
32 GB RAM
Up to 2-way,
2 GB RAM
n/a
Up to 4-way,
32 GB RAM
n/a
Poll: Windows in your environment?
At lease one server is Windows Server 2003
Enterprise Edition is running in Production
A majority of our production servers are
Enterprise Edition of Windows Server 2003
A majority of our production servers are
Standard Edition of Windows Server 2003
We only run Windows Server 2003 Standard
Edition in our production Environment
We are still on Windows 2000 or earlier
SQL Server Versions and Platforms
Version
Platform
32-bit (x86)
64-bit (x64)
64-bit (Itanium
Platform
Family)
SQL Server 7.0
X
(All Editions)
N/A
N/A
SQL Server 2000
X
(All Editions)
(sp4 using WOW –
x86 version)
X
Enterprise Edition
SQL Server 2005
X
(All Editions)
X
Standard/Enterprise
Editions
X
Enterprise Edition
SQL Server 2005 64-bit Editions
Standard
Complete data
management & analysis
platform for medium
businesses and large
departments
4 CPU
Unlimited RAM
(64-bit)
Enterprise
Fully integrated data
management and
analysis platform for
business critical
enterprise applications
Unlimited Scale
+ Partitioning
Database Mirroring
Adv. DB mirroring,
Complete online &
parallel operations,
OLAP Server
DB snapshot
Reporting Server
Advanced Analysis
Tools including full
OLAP & Data
Mining
New Integration
Services
Data Mining
Full Replication &
SSB Publishing
$6K per proc or
$2,799 (Server + 10 users)
Customized & High
Scale Reporting
Adv SSIS
$25K per proc or
$13.5K (Server + 25 users)
Higher Editions include
same functionality as the
edition below it.
New for SQL Server 2005
Note: 2-node clustering
in SQL Server 2005
Standard Edition
requires Windows
Server 2003 R2
Enterprise Edition
Additional 64-bit Windows Server
Advantages: Thread Model
Thread model benefits
Faster context switches
Decreased memory usage
Quicker connection times
Perceived as a more “native”
implementation since it uses threads
versus processes
No changes required for client
applications
Can perform faster than Process model
for CPU-intensive operations
Additional 64-bit Windows Server
Advantages: Thread Tuning
Both CPU affinities and thread priorities
can be set and manipulated via registry
parameters on a thread-by-thread basis
User-thread support
Users may now run the database in userthread mode, which employs databasescheduled fibers instead of O/S scheduled
threads
For CPU intensive apps, this will provide a
performance boost and reduce CPU utilization
What is 64-bit SQL Server?
Same codebase as 32-bit SQL Server
Flat memory addressing (no need for AWE)
2 64-bit versions for SQL 2005 - IA64 and X64
Data file compatibility with 32-bit SQL
Easy Database Migration & Integration
Detach/Attach, Log shipping, Replication, DB Mirroring etc.
No changes in client apps when connecting to a 64-bit
backend and a 32-bit backend
Massive Scale-up support – up to 64-way
8-node Fail-over clustering support
Agenda
Key Differences in 32-bit v 64-bit
systems
Windows / SQL Versions
X64 or IA64 and Performance /
Scalability
Which Components Benefit
Migration to 64-bit SQL Server
x64 or Itanium™?
• x64 for mainstream / commodity
• Competes against existing high-end x86 platforms
• Targets servers using up to 4p/8core
• Pricing not significantly greater than traditional x86 based
systems
• Based on 64-bit Extension to the x86 instruction set
• Itanium for scale-up
• Optimized for most demanding DB & LOB workloads
• Targets servers using 4 -128* processors
• Scalable up to 128* processors and 2 terabyte of physical
memory
• Pricing more competitive to x64 and x86 systems, and
price/performance comparable to x86 and x64
• Based on EPIC (Explicitly Parallel Instruction Computing)
Overall Advantages of 64-bit Processors
Much larger direct-addressable
memory space (AWE not used)
Better on-chip cache management
Improved multiprocessor
performance
Enhanced on-processor parallelism
Increased bus and I/O bandwidth
for faster and wider throughput
x64 Benefits
Faster Clock Speeds
Longer History with multi-cores
More Versatile
able to run 32-bit apps
ease of transition
More Affordable
x64 Server Options
x64 hardware supports multiple configurations
Extends hardware investment by allowing gradual migration to
64-bit computing
Provides superior 32-bit performance and very good 64-bit
performance
32-bit Stack
Hybrid Stack
32-bit
64-bit Stack
Applications
32-bit
Windows Servers
32-bit
x64
x64
Device Drivers
32-bit
x64
x64
x64
x64
x64
Server Hardware
x64
x64
64-bit Platform Positioning
Breadth of Applications
Mainstream
Versatile
32-bit x86
64-bit x64
Most
Scalable
64-bit
Itanium
Scalability
64-bit Architecture (x64) - Offerings
AMD
Opteron
Athlon 64
Intel
Xeon with EM64T
Pentium IV with EM64T
First dual-core offering
Generally aimed at 4 sockets or less
Unisys – 32 sockets – ES7000
IBM – 16 sockets
Other main vendors
HP and Dell (AMD and Intel)
Itanium Scales Better with dual-core
Source: Windows IT Pro: Essentials
Guide to “Solving SQL Server Sprawl”
– September, 2006
Itanium Benefits For Databases
Larger cache size
Fast Itanium® 2 on-die L3 cache up to 24 MB
Less cache latency (fewer clock cycles needed)
Databases benefit greatly with larger caches closer to the
processor
Allows for “bigger” systems
Better Parallelism
Better Instruction level parallelism (11 issues ports)
More registers (264 registers supporting parallel instructions
and reduce need for memory access)
Improved Scalability of System Bus allowing for bigger
systems
Improved Micro Architecture
Predication removes delays caused by mispredicated
branches
64-bit Architecture (IA64) - Offerings
Intel chip architectures
Itanium - Merced, released in 2001
Itanium2-McKinley, then Madison, now Montecito
Montecito – first dual-core
Tukwila - multi-core - due in 2008
Vendors – Bull, Fujitsu, Fujitsu-Siemens
Computers, Hitachi, HP, NEC, Silicon Graphics and
Unisys
Up to 64-sockets
Up to 1.6 GHz
Up to 24 MB L3 cache
Agenda
Key Differences in 32-bit v 64-bit
systems
Windows / SQL Versions
X64 or IA64 and Performance /
Scalability
Which Components Benefit
Migration to 64-bit SQL Server
Poll: Do you currently use SQL Server for BI?
Yes
No
Not Sure
Which SQL Server 2005 Components
Benefit from 64-bit?
Relational Engine
Snapshot Isolation
Table / Index Partitioning
Failover Clustering
Integration Services
Analysis Services
Reporting Services
Also, IT Operations Benefit from
SQL Server Consolidation
Why the Relational Engine Benefits
from 64-bit ?
Small transactions are more thread
intensive
64-bit provides the faster thread execution
mode
Faster I/O – Windows Multi-path I/O
Lower Context Switching with lower CPU
queues
Logical reads are faster as more data can
be cached
Avoid page faults
Statistics Refresh will use parallelism
Integration Services
No explicit way to limit memory utilization ceiling
Specific tasks to watch:
Sorts
Aggregations
Lookups
Be sure to minimize the columns that are carried
through the pipeline
Rewrite lookup queries to only pull key and lookup
columns
Or set the ceiling within the task – performance trade-off
Packages can fail on 32-bit when the 3 GB VAS
limit is reached
Analysis Services 2000
Memory Utilization
Query cache
Connections
Processing Buffers
Read ahead
Shadow Dimensions
Replica Dimensions (Dimension Security)
Dimensions and Member Properties
Limited to
4 GB !
Reporting Services
Does not benefit from AWE
Large or complex reports can require
lots of memory
CLR based Application LOVES Memory
SQL Server Consolidation
Scenario 1:
DB Consolidation
Single OS, Single SQL,
Many DB
Maximum scale up and
resource utilization
Scenario 2:
SQL Instance Consolidation
Scenario 3:
Server Consolidation
Single OS, Multiple SQL Instances
Multiple OS, Multiple SQL
- Greater flexibility and control over
CPU/Memory resource allocation and
maintenance schedules (per instance)
- Same model as stand-alone
servers, with option to re-partition
H/W resources as needs change
Utilization
Isolation
Windows 2003 Server (24P)
Payroll Server
Windows 2003 Server (64P)
Windows 2003 Server (64P)
SQL 2005 Server
Manufacturing Instance
•Manufacturing DB
•Marketing DB
•Payroll DB
Enterprise
Storage &
Backup
Windows (8P)
Marketing
Server
Marketing Instance
Windows 2003 Server (32P)
Payroll Instance
Manufacturing Server
Poll: Do you have many “small/medium” SQL
Servers in your production environment?
Yes
No
Not Sure
Agenda
Key Differences in 32-bit v 64-bit
systems
Windows / SQL Versions
X64 or IA64 Performance /
Scalability
Which Components Benefit
Migration to 64-bit SQL Server
Business Benefits of 64-bit Migration
Meet increasing business demands
Allow more complex workloads
Serve greater number of customers
Improve response times
Increase cost effectiveness
Do more work on the same budget
Enable server and database consolidation
Make more efficient use of Datacenter resources
“Future proof” your database investment
All development resources are being directed towards 64-bit
platforms
The “tipping-point” for moving 64-bit processing into the
mainstream has been reached
Ease of Migration
Minimal Migration hurdles
Same on-disk format
Detach database from 32-bit server
Attach database to 64-bit server
Interoperability with other SQL Server
installations
Scripting languages will “just work” on SQL
Server 64 Bit Edition
Same Price as 32-bit!!!
How to Migrate
Already on 32-bit SQL 2005?
Test!!!
Easy: Backup/Restore or
Detach/Attach
Still on 32-bit SQL 2000?
Follow Guidelines for Migration
Run Upgrade Advisor / Upgrade Assistant
Test!!!
Backup / Restore or Detach / Attach
Things to know before moving to 64-bit
Research impact of 3rd party software
(Itanium)
Virus protection
Specialty backup programs
Database drivers
Hardware drivers
Management tool support
Shared memory across components
Potential NUMA affects (SP2 includes
enhancements)
Don’t move if you don’t have to
Minimize memory utilization using
previous tips
Hardware gets better and cheaper
with time
Establish your resource bottleneck(s)
before assuming 64-bit will solve your
problems
If you need to move to 64-bit
Move to x64 for moderate-sized
workloads
Pros
Easier migration path – 32-bit can run in WOW
Outstanding price/performance
Cons
Maximum of 4 CPUs (Unisys and IBM exceptions)
Maximum of 1 TB of RAM
If you need to move to 64-bit
Move to Itanium for very large or
complex workloads
Pros
Support for many CPUs – currently up to
512
High parallel
Very large memory support – up to 1000
TB
Cons
Cost
Driver compatibility
No BIDs – no SSIS debugging
Poll: Has this session helped to shed light on
the benefits of 64-bit technology?
Yes
No
Not Sure
Poll: Which 64-bit Platform?
x64
Itanium
Neither – I am sticking with 32-bit
Not Sure
Route 64 – Exit Now!
x
Summary
64-bit provides many benefits over
32-bit systems
There are two platform choices for
64-bit – each with its own pros/cons
64-bit enables new heights of
performance and scalability for SQL
2005
Migration is not complicated
64-bit is the future
Thank You!!!
Don’t forget to check out
www.ricksql.com for more free
content!
Resources
http://www.microsoft.com/windowsserver2003/enterprise/defaul
t.mspx
http://www.microsoft.com/sql/editions/64bit/overview.mspx
http://www.microsoft.com/windowsserversystem/64bit/bulletin.
mspx
http://msdn2.microsoft.com/en-us/library/ms141766.aspx
http://searchsqlserver.techtarget.com/qna/0,289202,sid87_gci1
161294,00.html?bucket=NEWS&topic=301334
http://www.intel.com/business/bss/products/server/sqlserver2005.htm
http://www.sqlmag.com/essential/index.cfm?fuseaction=show&g
uid=f919979c-967a-4d33-bcca-78a826c3f22e
http://www.scalabilityexperts.com/default.asp?action=article&ID
=320
http://www.ricksql.com