REAL PRACTICES: Performance Scaling Microsoft SQL Server 2008 Analysis Services at Microsoft adCenter

Authors: Michael Anderson, Denny Lee, Dennis Dewitt

Contributors:Bilal Obeidat, John Montgomery

Technical Reviewers: John Sirmon, Chuck Heinzelman, Steve Howard, Txomin Barturen, Ken Taylor, Mike Mcghee, Justin Kennedy, Thomas Kejser, and Nicholas Dritsas

This was a joint effort between EMC and Microsoft

Published:  December 2010

Applies to: SQL Server 2008 R2 Analysis Services

Abstract:
This white paper explains how Microsoft® adCenter implemented a Microsoft SQL Server® 2008 Analysis Services Scalable Shared Database on EMC® Symmetrix VMAX™ storage.  Leveraging TimeFinder® clones and Enterprise Flash Drives with the read-only feature of SQL Server 2008 Analysis Services allowed adCenter to dramatically scale out OLAP while maintaining SLAs and decreasing system outages.

Executive Summary
Great information technology departments leverage business intelligence to provide a competitive advantage.  Some of the world's largest enterprise users run Microsoft® SQL Server® 2008 Analysis Services to provide critical data analytics. Top-end Analysis Services systems deliver massive processor power and memory capacity for the most complex, multi-terabyte data warehouses to handle business-critical workloads with record-setting performance, as well as high reliability, availability, and serviceability.

A single server running Analysis Services cannot support an unlimited number of concurrent users or queries.  Although an optimized cube design and efficient multidimensional expressions (MDX) can help to maximize performance, concurrent queries depleting server resources eventually require a scale-out approach.
In a storage area network (SAN) environment, it is possible to leverage the read-only database feature of SQL Server 2008 Analysis Services to separate data processing from reporting functions.  Implementing a read-only reporting volume enables multiple query servers to access the same database concurrently, which can help to save terabytes of storage space without complicated configurations.  The shared database volume only has to be able to sustain the combined input/output (I/O) workloads that the parallel query servers might generate.  Microsoft calls this technology Scalable Shared Database.

The EMC® Symmetrix VMAX™ array utilizes TimeFinder® clones to facilitate rapid data copy and performance isolation with minimal impact to processing and reporting servers.  Implementing Enterprise Flash Drives (EFDs) within the Symmetrix® VMAX array has created a new ultra-high-performance storage tier that removes previous performance limitations imposed by rotating disk drives.  The significantly lower latency of EFDs dramatically improves the performance of latency-sensitive applications.  For real-world workloads, EFDs feature high sustained performance with very low response times.
 

To continue reading, please download the whitepaper REAL PRACTICES: Performance Scaling Microsoft SQL Server 2008 Analysis Services at Microsoft adCenter