210 likes | 390 Views
DBI407. Best Practices for Building Tier 1 Enterprise Business Intelligence Solutions with Microsoft SQL Server Analysis Services. Adam Jorgensen President Pragmatic Works. What do Tier 1 Solutions have in Common?. Design and Query Considerations High Concurrent User Count
E N D
DBI407 Best Practices for Building Tier 1 Enterprise Business Intelligence Solutions with Microsoft SQL Server Analysis Services Adam Jorgensen President Pragmatic Works
What do Tier 1 Solutions have in Common? • Design and Query Considerations • High Concurrent User Count • Increased Hardware Requirements • IO Hungry! • Real Time Implications • Proven Strategies
Design and Query Considerations • Simplicity is Key • Parent child use • Many to Many • Cell by Cell vs. Block Mode • Data Types • Partitioning and Locking • Threading • Sizing • Distinct Count Performance • Hashing and Locking Pools
Managing Multi User Concurrency • Scaling Out • Synchronization • Read-Only DB’s • SAN Snapshots and Cones • System Engineering • Heap/Virtual Memory
Increasing Hardware Requirements • Using SSD’s • Block Size • Short Stroking • OPAS and Latency • ROLAP (Preview)
Improving IO • Partition Alignment • Storage Enging Performance • ROLAP • Transparent aggregations • No Sub-queries – must use table binding • Disabling the count of rows measure due to subquery • Indexed Views with individual tables • Best Practices Chart
Real Time Implications • Techniques for Real Time • Proactive caching • Rolap • Incremental Processing Molap/Rolap
Proven Strategies – Case Studies • Highlights from SQLCAT (Examples of big installations) • AdCenter • Xbox Live • Yahoo!
Case Study - AdCenter • EMC DMX V-Max to handle the I/O • V-Max are dedicated to the application • Hundreds of disks and spindles devoted to this process • Short-stroking the disks to ensure fastest I/O • Works closely with EMC directly (present at EMC World regularly) • Testing with EMC EFDs (Enterprise Flash Drives) • Systems Engineering team dedicated to DW process • Work closely with various vendors (EMC, HDS, etc.) • References • Accelerating Microsoft adCenter with Microsoft SQL Server 2008 Analysis Services. • REAL PRACTICES: Performance Scaling Microsoft adCenter with Microsoft SQL Server 2008 Analysis Services on EMC VMAX
Yahoo – Massive Data for Intense Scale Oracle 10g SSAS Cube Builder CDF Partition 1 Partition 1 1.2TB/day Partition 2 Partition 2 12TB cube Partition N Partition N NAS 50GB/hr SSAS Query Servers File1 File2 FileN HW NLB
Summarizing (1/2) • You HAVE to get the design right if you want to scale • Partitioning is absolute crucial • Partition for processing speed • Partition for data latency (real time vs. Stale) • Partition for archival of old data • Secondary concern to above: partition for query speed • Hardware really matters for large cubes • NAND devices are match made in heaven. • Tests show two core Nehalem CPU can often support hundreds of users • Carefully balance IOPS vs. Memory, consider hot portion of cube
Summarizing (2/2) • Locking is your enemy • Consider processing server with sync vs. Individual servers processing • Consider how to flip • For super low latency, ROLAP is the way • Multi User Settings • Just run them as per perf guide • Consider adjusting CoordinatorBalancingFactor • Keep it Simple • Remember the premise: AS is a ”send me any query” database. • You really have to get this right at scale
What’s Next? • Follow, Tweet and Enter to win an Xbox Kinect Bundle • GAME ON! Join us at the top of every hour at the BI booth to compete in the Crescent Puzzle Challenge and Win Prizes • Sign up to be notified when the next CTP is available at: microsoft.com/sqlserver Join the Conversation @MicrosoftBI /MicrosoftBI
Resources • Connect. Share. Discuss. http://northamerica.msteched.com Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers http://microsoft.com/technet http://microsoft.com/msdn