Implementing the new Fast Track 3.0 Data Warehouse Reference Architecture

1. Implementing the new Fast Track 3.0 Data Warehouse Reference Architecture Tom PizzatoDW Technology SpecialistMicrosoft Corporation Jim HautalaSr. Solutions EngineerHewlett Packard

2. Agenda

3. Understanding Data Warehousing workloads

4. Workload types Day-to-day business Analysis over recorded data

5. Data Warehouse Workload Characteristics SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY, AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER FROM LINEITEM GROUP BY L_RETURNFLAG, L_LINESTATUS ORDER BY L_RETURNFLAG, L_LINESTATUS Scan Intensive Hash Joins Aggregations

6. How people implement their warehouses

7. Some SQL Data Warehouses today Big SAN Big SMP Server Connected together What’s wrong with this picture?

8. Answer: system out of balance • This server might be able to consume 16 GB/Sec of IO, but the SAN might only deliver 2 GB/Sec • Even when the SAN is dedicated to the SQL Data Warehouse, which it often isn’t • Lots of disks for Random IOPS BUT • Limited controllers  Limited IO bandwidth • System is typically IO bound • Queries are slow Result: significant investment, not delivering performance

9. Potential Performance Bottlenecks A A B B FC SWITCH SERVER CPU CORES WINDOWS SQL SERVER CACHE FC HBA FC HBA STORAGE CONTROLLER CACHE A A B B A B CPU Feed Rate SQL Server Read Ahead Rate HBA Port Rate Switch Port Rate SP Port Rate LUN Read Rate Disk Feed Rate DISK DISK DISK DISK LUN LUN

10. The Alternative: A Balanced System

11. Microsoft’s Data Warehouse Solutions

12. Microsoft Data Warehousing Offerings Tier 1 Offerings Enterprise Data Center Fast Track Data Warehouse Parallel Data Warehouse Today’s session focuses here Tier 1 Services and Support

13. SQL Server Fast Track Data Warehouse Solution to help customers and partners accelerate their data warehouse deployments • A method for designing a cost-effective, balanced system for Data Warehouse workloads • Reference hardware configurations developed in conjunction with hardware partners using this method • Best practices for data layout, loading and management Relational Database Only – Not SSAS, IS, RS

14. Fast Track Data Warehouse Components • Software: • SQL Server 2008 R2 Enterprise • Windows Server 2008 R2 • Configuration guidelines: • Physical table structures • Indexes • Compression • SQL Server settings • Windows Server settings • Loading • Hardware: • Tight specifications for servers, storage and networking • ‘Per core’ building block

15. Fast Track Data Warehouse Guidance

16. Key Items to consider for a successful Fast Track Data Warehouse Implementation • Get the data on disk correctly • Don’t Over-Index • Size your system right • Remember that a Fast Track Data Warehouse is designed to be used for scan-centric read mostly workloads. • It is not designed for OLTP or ODS workloads

17. Get the data on disk correctly

18. IO Patterns All databases contain both scans and seeks among with other types of reads and writes, DW workload indicate that the vast majority of reads are sequential – not all

19. Sequential Data Layout Techniques for creating sequential data layout and managing fragmentation are documented in the Fast Track Data Warehouse 3.0 Reference Guide

20. Use compression and an ‘Index-Light’ approach to your designRemember, indexes introduce Random I/O

21. Sizing your system correctly

22. Sizing Your System RightCore Evaluation Metrics • These metrics are used to both validate and position Fast Track Reference Architectures • Maximum Consumption Rate – Ability of SQL Server to process data for a specific CPU and Server combination and a standard SQL query. • Benchmark Consumption Rate – Ability of SQL Server to process data for a specific CPU and Server combination and a user workload or query. • User Data Capacity – Maximum available SQL Server storage for a specific Fast Track RA assuming 2.5:1 page compression factor. These metrics and how to determine them are documented in the Fast Track Data Warehouse 3.0 Reference Guide

23. SQL Server Fast Track Data WarehouseSystem Sizing Tool (Core Calculator)

24. What’s new in Fast Track 3.0 Data Warehouse?

25. What is new in Fast Track Data Warehouse 3.0? • New: Fast Track 3.0 enables Partners to build and test Reference Architectures while Microsoft validates • Customers and Partners get new and enhanced benefits from Fast Track 3.0: • Customers Partners • Faster performance – new configurations offer high IO throughput of up to 15GB/s More control and flexibility to build and test their own configurations • Increased scale – new Reference Architectures offer up to 80 TB! Opportunity to differentiate configurations • Greater Choice with new Hardware Partners

26. Technical Changes in Fast Track 3.0 Data Warehouse

27. HP Fast Track 3.0 Reference Configurations

28. What are Fast Track reference Architectures? • BI/DW reference architecture offerings designed to help customer to accelerate their DW deployments • Repeatable architectural approach for implementing a scalable model for a symmetric multiprocessor (SMP)-based Microsoft SQL Server 2008 DW warehouse • Targeting query workloads patterned for large sequential data sets • Optimizing rapid data reads and query aggregations • Provides “out of box” scalable performance • Certified performance

29. SQL Server Fast Track Data Warehouse for HP • SQL Server® Fast Track Data Warehouse for HP servers, storage and networking products • HP and MSFT has developed 5 hardware reference architectures that use best practicesand have been pre-tested to provide balanced performanceto reduce costs, save time and reduce risk • Accelerates the sales cycle, significant cost savings, protects investments. • The configurations target small, medium and large data warehouses which will provide a path to “Parallel Data Warehouse*” • Complete Bill of Materials for each configuration * Parallel Data Warehouse is MSFT’s next generation data warehouse available in H1CY2010

30. HP SQL Server Fast Track Data Warehouse Key benefits: • Accelerate customer Data warehouse roadmap with pre-tested hardware configurations • Virtually eliminate hardware testing and reduce tuning with better Data Warehouse performance out-of-the-box • Offerslinear scale out performance • Scale from 3 up to80 terabytes using compression capabilities in SQL Server 2008 Enterprise • Provide a lower cost of ownership through better price performance, rapid deployment and industry-standard hardware • Choose the right performance, scalability and pricing to suit your business needs • Sold and Serviced through Microsoft and HP • Balanced best price/ performance for small to mid-sized Data Warehouse

31. HP and Microsoft SQL Server Data Warehousing Continuum: Fast Track, HP Enterprise Data Warehouse Appliance • Balanced hardware approach ideal for data marts - EDW with scan-centric workloads • Fast Track Reference Architectures: 4– 80TB • Parallel Data Warehouse: 56TB+ • Leading price/performance metrics • HP provides configurations, tested performance guidance and • best practices for deploying • HP Sizer for Microsoft SQL Server Fast Track Data Warehouse • Packaged and custom support

32. New Entry Level Fast Track Architecture Base Configuration - Up to 6TB of Storage - Internal Storage - 6K Per Terabyte

33. New Entry Level Fast Track Architecture Expanded Configuration - Up to 16TB of Storage - External D2700 Storage Array - 1.8GB/sec Scan Rate 6K Per Terabyte

34. Existing Fast Track Reference Architecture Refresh 6K Per Terabyte! - DL380G7 - P2000 G3 8GB FC Storage with SFF Drives - Optimized for 20TB Data Warehouse - New 600GB 6GB Small Form Factor Drives - 3GB/sec Scan Rate

35. HP Fast Track 3.0 Architecture Changes

36. Fast Track LUN Configuration • --RAID10 Data LUNs • - RAID10 Log LUNs • - 6 Disk Secondary Stage • Primary Data, TempDB, Primary Stage striped on Data LUNs

37. HP Fast Track Architecture Changes • RAID10 Arrays vs RAID1 • Reduce Management Complexity • 8GB Storage Arrays • New Load Procedures • Bulk Insert To Staging Tables • Insert/Select to Production/Staging Table • Switch to Final DB Partition • Use highest clock speed for best performance • Use default settings on P2000 Arrays

38. Performance Testing and Best Practices • Better Performance with Increased Memory • Guidance changed from 4GB/Core. No longer tied to # of cores. • Queue Depths Optimized at 48 or 64 • RAID10 Arrays reduce Management Complexity • Power Settings in BIOS and Control Panel • MPIO Complexity is High – Use Explicit mappings or Failover Only Policy

39. Performance Testing and Best Practices (cont’d) • Resource Governor plays a bigger role • Changes for large configurations • MAXDOP Settings • MAXDOP 0 not Optimal

40. DL980 G7 • 8 Socket, 64 core server • 512GB RAM • Current maximum size exceeds practical limit of single Fast Track Database • Server tested using multiple fast track databases simultaneously • 14GB/sec logical scan rate using less than 200 disk drives

41. DL980 G7 Findings • Resource Governor Critical • Standard Alloc of memory/process too large at 512GB • MPIO Failover only Critical • 64+ paths per LUN • MAXDOP Settings have large impact • Best Performance DOP16 • PCI bus limitations surface

42. Methodologies and Examples

43. Fast Track Testing • New methodology provides more stringent testing • HP works with Microsoft to perform testing • Test Harness uses simulated workloads from 5,10,20 and 40 concurrent queries • Formal signoff once HP and Microsoft have optimally tuned configuration • New Performance Metrics Gathered • CPU MCR Calculations • Logical and Physical Throughput Specs

44. Reference Configuration Example Bill of Materials Storage Metrics Performance

45. HP Fast Track Sizing Tool Developed by Test Engineers Uses Real World Data Currently being updated for FT 3.0 Complements other BI and DW Sizers Ensures that customers choose configuration based on several factors, not just total DW size

46. HP Fast Track Futures • Fast Track HA Solution • Direct Attached Storage • HP Bladesystem • Solid State Storage • Other BI Systems • OLTP • SSIS

47. Fast Track Case Studies

48. In Summary SQL Server Fast Track 3.0 Data Warehouse Reference Architectures provide performance at a low TCO HP has the reference configurations to ensure your hardware choice is the right one Reference Configurations are tested and validated with real world workloads HP and Pragmatic Works can provide the services and training to help you put it all together

49. HP Fast Track Reference Configurations and Whitepapers • All current Fast Track reference configurations at HP Active Answers: http://h20338.www2.hp.com/ActiveAnswers/cache/70729-0-0-0-121.html • Whitepapers currently published: • HP Configuration and Load Guide for Microsoft SQL Server 2008 Fast Track Data Warehouse • Performance Guide for Microsoft SQL Server 2008 Fast Track Data Warehouse on HP ProLiant DL385 • Fast Track 3.0 Reference Guide • http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-

50. Next Steps • Learn More: • Visit the Microsoft Data Warehousing portal • Visit the Fast Track web page • Get the Sizing Tool • Read the Configuration Guide • http://www.microsoft.com/sqlserver/en/us/solutions-technologies/data-warehousing/fast-track.aspx • Visit the SQL Server DW Portal on TechNet