Loading in 2 Seconds...
Loading in 2 Seconds...
SESSION CODE: BIE306. Best Practices in Fast Track, Parallel Data Warehouse, and Traditional Data Warehouse Design. Jeff Spiller ESS Performance and Solutions Engineering COE (Center of Excellence) BI ATC (Business Intelligence - Advanced Technology Center).
ESS Performance and Solutions Engineering COE (Center of Excellence)
BI ATC (Business Intelligence - Advanced Technology Center)
Fast Track, Microsoft SQL Server 2008 Parallel Data Warehouse and Traditional Data Warehouse Design
BI Best Practices and Tuning for Scaling SQL Server 2008
RDBMSThe Continuum to SQL Server 2008 R2 (scale-up) Parallel Data Warehouse (PDW)
SSASThe Continuum to SQL Server 2008 R2 (scale-up) Parallel Data Warehouse (PDW)
Traditional MD design
Fast Track reference configurations
SQL Server Parallel Data Warehouse (PDW)
Traditional DW design reference configurations
Mmm, what will my logical & physical DB design look like ?
Lower hardware costs
Different logical and physical DB design philosophies
X-Ray view at the physical disk level
First let’s look at a traditional DW…..How To Gain Speed While Reducing CostsLet’s lake a closer look under the hood to understand BI performance
3rd day load
6th day load
5th day load
2nd day load
Data is stored wherever it happens to land
Fast Track & PDW
Eliminating indexes and storing data sequentially will provide the fastest disk throughput rates
Fast Track & PDW
Fastest sequential scan rates
Index-lite is faster because there is less disk head movement
Traditional DW design with indexes & summary tables
Disk throughput is slower with indexes, aggregates and summary tables
Example: Average disk Seek time is typically about 4ms; Full stroke is about 7.5ms.
At 15K RPM = 250 revolutions/sec. = 4ms for a full revolution = Average latency is about 2ms.
Fast Track & PDW are designed to stream large blocks of data sequentially which is even faster than “average latency” because disk heads are directly over the streaming data.
Why does PDW and Fast Track want data to be stored sequentially ?Why Does PDW and Fast Track Want Data to be Stored Sequentially ?
Track of data
No seek time
Disk drive manufacturers sometimes quote average seek time and max/full seek time numbers
Seek time is typically 2 - 4x longer than average latency. By eliminating seek time you can have approximately 2 – 4x fewer disk drives in order to maintain a given throughput level.
Fast Track & PDW are designed to stream large blocks of data sequentially!
In addition, HP and SQL Server PDW uses Massively Parallel Processing (MPP) to expand Fast Track concepts in a BI “appliance”
Fast scan rates
Fast Track and PDW get it’s speed from FAST scan rates !
Fast Track or PDW
Traditional DB design
Basic6 – 12TBDL38x w/MSA2000
Mainstream12 – 24TBDL585 G6 w/MSA2000
Mainstream16 – 32 TB DL580 G5 w/MSA2000 G2
Premium24 – 48 TBDL785 G6 w/MSA2000 G2
Without HP Factory Express
With HP Factory Express
Faster time to solution
Free up valuable IT resources
Maximize your IT investment
Buffers are allocated via Execution Trees
Each of these Numbered Steps represents a new Execution Tree
Spawning multiple copies of the package with a horizontal partition of data will create more process space and execution trees
Complete an evaluation on CommNet and enter to win!
You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year
© 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.