Scale-Out Querying for Analysis Services with Read-Only Databases
SQL Server Best Practice Article
Writer: Denny Lee, Kay Unkroth
Contributors: James Podgorski
Technical Reviewer: Akshai Mirchandani, Murshed Zaman, T.K. Anand
Published: June 2010
Applies to: SQL Server 2008 and SQL Server 2008 R2
Summary
This white paper describes recommended design techniques and methodologies to maximize the performance and scalability of SQL Server 2008 Analysis Services deployments by using read-only query servers.
Introduction
Some of the world's largest enterprise servers run Microsoft® SQL Server™ 2008 Analysis Services. Top-end systems, such as HP Integrity Superdome, Unisys ES7000, and IBM x3950 M2 deliver massive processor power and memory capacity for even the most complex, multi-terabyte data warehouses to handle their business-critical workloads with record-setting performance, as well as high reliability, availability, and serviceability.
It makes sense to use top-end server models in a large Analysis Services environment. With all cube cells loaded into memory Online Analytical Processing (OLAP) queries perform most efficiently, query optimizers tend to generate more efficient query plans, communication between worker threads is extremely fast, and latencies are minimal. Yet, Analysis Services cannot use more than 64 logical processors. Only the relational engine of SQL Server 2008 R2 can fully exploit an Integrity Superdome with 64 Intel Itanium CPUs, 256 logical processors, and 2 terabytes of memory running Microsoft Windows Server® 2008 R2.
With a hard limit of 64 logical processors, an Analysis Services server 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. A common technique is to distribute the workload across multiple dedicated query servers, which has many advantages, yet the downside is an inefficient use of expensive storage resources given that each query server requires a separate database copy.
In a Storage Area Network (SAN) environment, it is possible to mitigate redundancies to some degree. Virtual copy snapshots provide an option to present the same database to each query server by means of a separate logical unit number (LUN). In this way, multiple query servers can read data from a single underlying database folder on a SAN array, yet the query servers still require read/write access to their database snapshots, so each query server’s LUN must still be writeable. Read/write snapshots complicate the SAN design and not all SAN systems support this feature, but the read-only database feature of SQL Server 2008 Analysis Services eliminates this requirement. It 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 LUN only has to be able to sustain the combined input/output (I/O) workloads that the parallel query servers might generate.
SQL Server Customer Advisory Team (SQLCAT) performance tests prove that read-only databases can be a viable option for scale-out querying, both, for formula-engine-heavy queries that primarily process data already in memory as well as storage-engine-heavy queries that involve a scan of the files on disk to perform a calculation. The key in both cases is to optimize the SAN environment for the anticipated levels of random I/O operations.
This white paper contains information for data warehouse architects, database administrators, and storage engineers who are planning to deploy read-only query servers in a SAN-based environment for Analysis Services scalability. This paper assumes the audience is already familiar with the concepts of Storage Area Networks, Windows Server, SQL Server, and SQL Server Analysis Services. A high-level understanding of Analysis Services optimization techniques for cube processing and query performance is also helpful. Detailed information is available in the SQL Server 2008 Analysis Services Performance Guide at http://sqlcat.com/whitepapers/archive/2009/02/15/the-analysis-services-2008-performance-guide.aspx.
To continue reading, we offer this document as a downloadable Microsoft Word document.