sql server 64 bit vu tuyet trinh [email protected] hanoi university of technology 1
TRANSCRIPT
MicrosoftMicrosoft
Outline
Overview of database performance and scalability Compatibility Advantages of 64-bit environment Consideration for choosing SQL Server 64-bit Deployment consideration
2
MicrosoftMicrosoft
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.
MicrosoftMicrosoft
SQL Server 64 bit
Cost Savings
Scalability
The highly scalable database platform for memory intensive, performance-critical business applications
Optimized for Windows Server 2003 and ItaniumOptimized for Windows Server 2003 and Itanium
Great performanceGreat performance Large memory addressability (up to 32 TB) Large memory addressability (up to 32 TB) Nearly unlimited virtual memory (up to 8 TB)Nearly unlimited virtual memory (up to 8 TB) I/O savings due to larger memory buffer poolsI/O savings due to larger memory buffer pools
T-SQL code-compatibility with SQL Server 2000T-SQL code-compatibility with SQL Server 2000
8 node clustering support8 node clustering support
Same on-disk format as 32-bit for easy migrationSame on-disk format as 32-bit for easy migration One setup for database & OLAP based on Windows One setup for database & OLAP based on Windows
Installer technologyInstaller technology Compelling alternative to expensive Unix solutionsCompelling alternative to expensive Unix solutions
Manageability
MicrosoftMicrosoft
32bit vs. 64 bit
AWE Memory AWE Memory (up to 32GB)(up to 32GB)
3GB Virtual 3GB Virtual Address space Address space
available to available to SQL ServerSQL Server
SQL Server 2000 – 32bit SQL Server 2000 – 32bit running on a Xeon Serverrunning on a Xeon Server
Virtual Virtual Address Address Space Space
(currently (currently tested up to tested up to
512 GB)512 GB)
SQL Server 2000 – 64 bit SQL Server 2000 – 64 bit running on an Itanium 2 running on an Itanium 2 ServerServer
•Buffer pool Buffer pool pages can be pages can be mapped / mapped / unmappedunmapped
•As pages are As pages are needed, they can needed, they can be mapped into be mapped into the virtual the virtual address spaceaddress space
•Some structure Some structure stay in virtual stay in virtual address memory address memory including plan including plan cache, cursor cache, cursor structures and structures and user connection user connection contextcontext
•Cost associated Cost associated with mapping / with mapping / unmappingunmapping
•Virtual address space Virtual address space available to all available to all structuresstructures
•Direct access – no Direct access – no map costmap cost
•Access to large Access to large amounts of physical amounts of physical memory in virtual memory in virtual address spaceaddress space
•Current and future Current and future hardwarehardware
MicrosoftMicrosoft
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.
MicrosoftMicrosoft
Advantages of 64-bit environment
Memory addressability Larger numbers of processors and more linear scalability
per processor Enhanced bus architecture
MicrosoftMicrosoft
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.
MicrosoftMicrosoft
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.
MicrosoftMicrosoft
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.
MicrosoftMicrosoft
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.
MicrosoftMicrosoft
Example Terabyte-Scale Deployments
Large-Scale OLTP and Data Warehousing
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)
Scientific / New Generation
Bioinformatics: Rosetta Genomics – (10 TB) GeoSpatial: Terraserver – (3+ TB) Astronomy: SkyServer – (~1TB 5TB)
MicrosoftMicrosoft
What is 64-bit SQL Server?
Same codebase as SQL Server All pointer types are now 64 bit
Good news: Lots of virtual memory Bad news: Control structures bigger
Flat memory addressing to 512GB of RAM Does NOT require AWE support, massive cache useable by all components of
SQL: Stored proc, OLAP, sort area… etc. Massive Scale-up support
64-way HP, 32-way NEC, 16-way Unisys Until very recently MSFT held fastest TPC number
Easy Database Migration & Integration Detach/Attach, Log shipping, Replication etc.
8-node Fail-over clustering support
MicrosoftMicrosoft
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 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
MicrosoftMicrosoft
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
MicrosoftMicrosoft
64-Bit Winning Scenarios Workloads stressing Virtual Memory
Limited to 3GB on 32-bit, regardless of AWE Applications that stress
Procedure cache and cursor memory Sort, index and hash table memory Connection memory Analysis Services memory
Examples: Server Consolidation High-Volume, diverse OLTP Large-Scale OLAP Simultaneous queries with large table joins
MicrosoftMicrosoft
64-Bit Winning Scenarios - 2
Massive Scale-up Scenarios More linear scalability beyond 8-way, and especially beyond 16-
way Server Consolidation
Workloads leveraging large SQL Buffer Cache OLTP with large working sets Relational Data Warehouses 64-bit eliminates performance overhead of AWE, the 32-bit large-
memory technology
MicrosoftMicrosoft
SQL Server (64-bit) 64-bit address space
Flat address space Applies to all memory
structures Procedure cache Data structures
Locks Buffer pool
Sorts, joins User connections
DB Page Buffers need not undergo AWE map/unmap
MicrosoftMicrosoft
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
MicrosoftMicrosoft
Consolidation / Proc Cache500 Pace database
2379
7059
14133
2318
4770
0
5,000
10,000
15,000
30sec 10sec 5sec
Thinktime
Tpm Tpm (64)
Tpm (32)
500 Pace databases
36.7
81.2
9.0
25.3
51.4
0
20
40
60
80
100
120
30sec 10sec 5sec
Thinktime
CPU
%,
SQ
Lco
mp/s
ec SQL comp/sec(32)CPU% (32)
SQL comp/sec(64)CPU% (64)
TPM versus think time
CPU% and SQL compilations/sec versus think time
MicrosoftMicrosoft
Example: Large-Scale OLTP Migration
Large US Escrow / Title company 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%
MicrosoftMicrosoft
Example: Scale UpBulk Insert Throughput16-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
0
10
20
30
40
50
60
70
80
0 2 4 6 8 10 12 14 16 18
Number of BULK INSERT streams
Bu
lk L
oad
Th
rou
gh
pu
t (M
B/sec)
32-bitES7000
64-bitCASSIN
MicrosoftMicrosoft
Example: Eliminate AWE
Table Tot Rows Data Size(MB) Index Size (MB)
ORDERS 48,223,600 6,188.32 2,781.53
CUSTOMER 121,303 14.18 4.14
PAYMENT 130,414 11.52 3.21
TOTAL 48,475,317 6,214.02 2,788.88
CPU’s
64 bit 64-bit Faster by 32 bit
CPU Time Elapsed Time CPU Time 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
Re-select data which is already memory-resident
Query: select sum(Qty) from ORDERS
16-way Unisys Cassin (Itanium II – 1Ghz) and 16-way Unisys ES7000 (1.6Ghz Xeon) both with 64GB of RAM (16GB to SQL)
MicrosoftMicrosoft
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 MOLAP dimensions are extremely
large, but provide best query performance
DimensionDimensionMemoryMemory
ShadowShadowDimensionsDimensions
AvailableAvailableCacheCache
ProcessingProcessingBuffersBuffers
MicrosoftMicrosoft
Advantages Of Analysis Services (64-Bit)
More parallelism in partition processing is possible due to memory advantages Each partition needs “working” memory
Analysis Services can make use of huge memory available for query cache and cube processing
Faster aggregation processing Aggregations are built in memory during
cube processing, avoiding Temp Files Very large filesystem cache speeds cube query operations
MicrosoftMicrosoft
Example: Analysis ServicesOLAP 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
MicrosoftMicrosoft
Good SQL Server 64-Bit Candidates
Large working set size (BP) – 6-8GB+ I/O intensive Save on processing i/o interrupts, BP thrashing (critical sections) I.e., TPC-C like
Large scale OLAP Large number of dimension members Large-scale parallel processing
Complex queries 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
MicrosoftMicrosoft
Customer Examples / Deployed
Leading Express Package Shipper Customer-level OLAP for profitability analysis / route cost
structures 1+ TB source data
Clalit Health Services Largest Healthcare provider in Israel Combined Relational and OLAP 1+ TB
MicrosoftMicrosoft
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 Multi-TB relational analytics workload Initial workload benchmarks show 2.5x query improvement of
1.5Ghz Itanium II vs 1Ghz Xeon
MicrosoftMicrosoft
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: 64 Bit ≠ 2x performance of 32 bit 4-way Itanium II ≠ 8-way Xeon
MicrosoftMicrosoft
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