Transcript Microsoft Microsoft

SQL Server 64 bit
Vu Tuyet Trinh
[email protected]
Hanoi University of Technology
1
Outline





Overview of database performance and scalability
Compatibility
Advantages of 64-bit environment
Consideration for choosing SQL Server 64-bit
Deployment consideration
Microsoft
2
Database Performance and Scalability



Many applications running on 32-bit platforms today are
approaching or have exceeded the limits of the platform.
Advanced DBMS capabilities, data is growing numbers of business
users.
Organizations are consolidating servers to simplify critical data
center operations. Consolidation can reduce management
complexity and cost, while reducing physical space requirements in
the data center.
Microsoft
SQL Server 64 bit
Scalability

Optimized for Windows Server 2003 and Itanium

Great performance



Manageability

T-SQL code-compatibility with SQL Server 2000

8 node clustering support

Same on-disk format as 32-bit for easy migration
One setup for database & OLAP based on Windows
Installer technology
Compelling alternative to expensive Unix solutions

Cost Savings
Large memory addressability (up to 32 TB)
Nearly unlimited virtual memory (up to 8 TB)
I/O savings due to larger memory buffer pools

The highly scalable database platform for memory
intensive, performance-critical business applications
Microsoft
32bit vs. 64 bit
SQL Server 2000 – 32bit
running on a Xeon Server
AWE Memory
(up to 32GB)
3GB Virtual
Address space
available to
SQL Server
Microsoft
•Buffer pool pages
can be mapped /
unmapped
•As pages are
needed, they can
be mapped into
the virtual address
space
•Some structure
stay in virtual
address memory
including plan
cache, cursor
structures and
user connection
context
•Cost associated
with mapping /
unmapping
Virtual
Address
Space
(currently
tested up to
512 GB)
SQL Server 2000 – 64 bit
running on an Itanium 2
Server
•Virtual address space
available to all
structures
•Direct access – no
map cost
•Access to large
amounts of physical
memory in virtual
address space
•Current and future
hardware
The primary differences between the SQL
64-bit and SQL 32-bit




The 64-bit architecture offers a larger directly-addressable memory space. SQL
Server 2005 (64-bit) is not bound by the memory limits of 32-bit systems. Therefore,
more memory is available for performing complex queries and supporting essential
database operations.
The 64-bit processor provides enhanced parallelism, thereby providing more linear
scalability and support for up to 64 processors, and yielding stronger returns per
processor as compared to 32-bit systems.
The improved bus architecture enhances performance by moving more data between
cache and processors in shorter periods.
A larger on-die cache allows for faster completion of user requests and more efficient
use of processor time.
Microsoft
Advantages of 64-bit environment



Memory addressability
Larger numbers of processors and more linear scalability
per processor
Enhanced bus architecture
Microsoft
Memory addressability

A 32-bit system can directly address only a 4-GB address space.
Additional memory is indirectly accessible by using Address
Windowing Extensions (AWE) on 32-bit platforms, as described in a
later section. Windows Server 2003 SP1 running on the Intel Itanium
64-bit architecture supports up to 1,024 gigabytes of both physical
and addressable memory.
Microsoft
Larger numbers of processors and more
linear scalability per processor


Improvements in parallel processing and bus architectures enable 64-bit
platforms to support larger numbers of processors (up to 64) while providing
close to linear scalability with each additional processor. With a larger
number of processors, SQL Server can support more processes,
applications, and users in a single system.
Server platforms that offer more than 32 CPUs are available exclusively on
64-bit architecture. The highest TPC-C benchmark figures for SQL Server
have been achieved on 64-bit systems because these systems leverage
both large amounts of memory and the superior scaling of 64-bit processors
on the 64-bit architecture.
Microsoft
Enhanced bus architecture

The bus architecture on current 64-bit chipsets is faster
and wider than earlier generations. More data is passed
to the cache and processor; this is somewhat analogous
to the improvement that broadband connections offer
over dial-up connections.
Microsoft
Industry-Leading Features








Analysis Server – Gartner claimed Microsoft Analysis Server best of breed
Auto Configuration, Tuning
Loading Data – Bulk Insert rates of 70 MB/Sec+
Replication – transactional replication – 2000 cmds/sec
Management Tools – Set the bar
Reliability -- testing unmatched in the industry
Utilities – Fast Backup, Best re-org and very fast Load
SQL CE, Query Processor, Storage Engine, Developer talent, XML, etc.
Microsoft
Example Terabyte-Scale Deployments

Large-Scale OLTP and Data Warehousing








Scientific / New Generation



Microsoft
Call Center Mgmt / Telecom Billing – Verizon (19 TB)
Credit Scoring and Collections – Fair Isaac (13 TB)
Retail Analytics / BI – Edgars Consolidated (4 TB)
Retail Banking Analytics – Royal Bank of Scotland (3 TB)
SAP – Pilgrim’s Pride (3 TB)
Brokerage Front and Back-Office – CSS Software (1+ TB)
Loss Prevention: (US Retail Chain) – Rite Aid (1+ TB)
Bioinformatics: Rosetta Genomics – (10 TB)
GeoSpatial: Terraserver – (3+ TB)
Astronomy: SkyServer – (~1TB  5TB)
What is 64-bit SQL Server?


Same codebase as SQL Server
All pointer types are now 64 bit



Flat memory addressing to 512GB of RAM


64-way HP, 32-way NEC, 16-way Unisys
Until very recently MSFT held fastest TPC number
Easy Database Migration & Integration


Does NOT require AWE support, massive cache useable by all components of
SQL: Stored proc, OLAP, sort area… etc.
Massive Scale-up support



Good news: Lots of virtual memory
Bad news: Control structures bigger
Detach/Attach, Log shipping, Replication etc.
8-node Fail-over clustering support
Microsoft
Compatibility

Maintains T-SQL code-compatibility with SQL Server 32-bit releases




Same SQL Server 2000 code base plus service pack fixes and
performance enhancements
No changes in database file format
No changes in client applications when connecting to a 64-bit
backend versus a 32-bit backend
Supported Clients

Microsoft
SQL Server 2005 (64-bit) is optimized to run on servers using Intel
Itanium 2, AMD Opteron, AMD Athlon 64, Intel Xeon with EM64T
support, or Intel Pentium IV with EM64T support processors
64-bit gaps

Some non-engine SQL components are not currently
supported on 64-bit:







Graphical Admin Tools
Data Transformation Services
Reporting Services
Notification Services
SQL Mail
English query
These require a 32-bit supporting instance to host
Microsoft
64-Bit Winning Scenarios

Workloads stressing Virtual Memory


Limited to 3GB on 32-bit, regardless of AWE
Applications that stress





Examples:




Microsoft
Procedure cache and cursor memory
Sort, index and hash table memory
Connection memory
Analysis Services memory
Server Consolidation
High-Volume, diverse OLTP
Large-Scale OLAP
Simultaneous queries with large table joins
64-Bit Winning Scenarios - 2

Massive Scale-up Scenarios



More linear scalability beyond 8-way, and especially beyond 16way
Server Consolidation
Workloads leveraging large SQL Buffer Cache



Microsoft
OLTP with large working sets
Relational Data Warehouses
64-bit eliminates performance overhead of AWE, the 32-bit largememory technology
SQL Server (64-bit)
64-bit address space


Flat address space
Applies to all memory
structures


Procedure cache
Data structures


Buffer pool



Microsoft
Locks
Sorts, joins
User connections
DB Page Buffers need not
undergo AWE map/unmap
Example: Server Consolidation / Larger
Procedure Cache



Internal test of a 500-database consolidation; accounting
application
Scarce resource on 32-bit is Procedure Cache
Migrating to 64-bit and eliminating the bottleneck reduces
CPU and enables greater throughput
Microsoft
Consolidation / Proc Cache
500 Pace database
Tpm
10,000
5,000
TPM versus think time
14133
15,000
Tpm (64)
Tpm (32)
7059
4770
2379 2318
0
30sec
10sec
5sec
Thinktime
500 Pace databases
CPU%, SQL
comp/sec
120
100
81.2
80
60
40
20
0
51.4
36.7
Microsoft
SQL comp/sec
(64)
CPU% (64)
25.3
9.0
30sec
10sec
Thinktime
SQL comp/sec
(32)
CPU% (32)
5sec
CPU% and SQL
compilations/sec
versus think time
Example: Large-Scale OLTP Migration

Large US Escrow / Title company






Microsoft
Document management, title and tax processing
High-volume, complex transactions and searches
Refinance boom had stressed their 32-way 32-bit servers to over
70% CPU at peak
Migrated OLTP from 32-way 32-bit to 32-way 64-bit
Reduced CPU from 70% to 30%
Indexing time improved by 30%
Example: Scale Up
Bulk Insert Throughput
16-way Unisys Cassin (Itanium II – 1Ghz) and16-way Unisys
ES7000 (1.6Ghz Xeon) both with 64GB of RAM
32-bit vs. 64-bit BULK INSERT Throughput
Bulk Load Throughput
(MB/sec)
80
70
60
50
32-bit
ES7000
64-bit
CASSIN
40
30
20
10
0
0
Microsoft
2
4
6
8
10
12
14
Number of BULK INSERT streams
16
18
Example: Eliminate AWE
16-way Unisys Cassin (Itanium II – 1Ghz) and 16-way Unisys
ES7000 (1.6Ghz Xeon) both with 64GB of RAM (16GB to SQL)
Table
Tot Rows
Data Size(MB)
Index Size (MB)
48,223,600
6,188.32
2,781.53
CUSTOMER
121,303
14.18
4.14
PAYMENT
130,414
11.52
3.21
48,475,317
6,214.02
2,788.88
ORDERS
TOTAL
Re-select data which is already memory-resident
64 bit
CPU’s
CPU Time
64-bit Faster by
Elapsed Time
CPU Time
32 bit
Elapsed Time
CPU Time
Elapsed Time
16-way
36,120
2,994
2.07
1.87
74,905
5,601
4-way
30,470
7,931
1.85
1.99
56,313
15,819
Query: select sum(Qty) from ORDERS
Microsoft
Advantages Of Analysis Services (64-Bit)

Not constrained by 3GB memory
limit



Dimension memory + Shadow
dimensions + Buffer space
(partitions)
Cache fills
remaining space
Huge dimensions supported in
memory

Microsoft
MOLAP dimensions are extremely
large, but provide best query
performance
Dimension
Memory
Shadow
Dimensions
Processing
Buffers
Available
Cache
Advantages Of Analysis Services (64-Bit)

More parallelism in partition processing is possible due to memory
advantages



Analysis Services can make use of huge memory available for
query cache and cube processing
Faster aggregation processing


Each partition needs “working” memory
Aggregations are built in memory during
cube processing, avoiding Temp Files
Very large filesystem cache speeds cube query operations
Microsoft
Example: Analysis Services
OLAP Large Customer Dimension




Transaction tracking to customer level – 3 Million key
customers
Migrated to 64-bit allowed support of the full customer
dimension
Memory also speeded data load and aggregation time by
factor of 10
Expanded size of customer dimension to 4.5 M
Microsoft
Good SQL Server 64-Bit Candidates

Large working set size (BP) – 6-8GB+




Large scale OLAP



Large number of dimension members
Large-scale parallel processing
Complex queries




I/O intensive
Save on processing i/o interrupts, BP thrashing (critical sections)
I.e., TPC-C like
Sorts, joins, workspace that can spill to disk
ERP (including Supply Chain), CRM, Financial Apps
Relational Analytics – i.e., PSFT EPM, SAP BW
Virtual Memory



Many open cursors
Procedure cache (not an excuse for bad design)
ODBC, OLEDB parm caching
SQL Server 64bit can significantly improve overall dbms performance and
throughput
Microsoft

Customer Examples / Deployed

Leading Express Package Shipper



Customer-level OLAP for profitability analysis / route cost
structures
1+ TB source data
Clalit Health Services



Microsoft
Largest Healthcare provider in Israel
Combined Relational and OLAP
1+ TB
Customer Examples / Under
Development

Large brokerage network



Massive customer-level OLAP solution for assets and trading
activity
Millions of products and Millions of accounts
Property & Casualty Insurance provider


Microsoft
Multi-TB relational analytics workload
Initial workload benchmarks show 2.5x query improvement of
1.5Ghz Itanium II vs 1Ghz Xeon
64-bit Caveats

Non-winners:

Workloads on 4-way servers or smaller that are not under
memory pressure
E.g. 3+ Ghz Xeon architectures will often
outperform Itanium II at 1.5 Mhz when virtual
memory is not under pressure
Beware the oversell:




Microsoft
64 Bit ≠ 2x performance of 32 bit
4-way Itanium II ≠ 8-way Xeon
64-bit Caveats




Extended stored procedures and AS UDFs must be
recompiled for 64-bit
ODBC Drivers for 3rd party databases
Device drivers for SAN
Management and monitoring tools
Microsoft