SQL Server Technical Article
Authors: Denny Lee, Kay Unkroth
Contributor: James Podgorski
Technical Reviewers: Akshai Mirchandani, Murshed Zaman, T.K. Anand
Published: June 2010
Applies to: SQL Server 2008, 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
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, please download the whitepaper Scale-Out Querying for Analysis Services with Read-Only Databases
Pingback from Twitter Trackbacks for Scale-Out Querying for Analysis Services with Read-Only Databases - Whitepapers [sqlcat.com] on Topsy.com
Pingback from Scale-Out Querying for Analysis Services with Read-Only Databases … Debt on Me
Pingback from Using Trace to Monitor and Audit Analysis Services | MarkTab Data Mining
Excellent White Paper!
I’m in the startup phase of an enterprise SSAS project and considerate an architecture with Scale-Out. There is two target-groups for the cube - (1) external users who connect using a web-application and (2) internal users that connect using excel and management studio. Let’s assume that i setup four query servers - is it possible to make a setup, so the internal users only make query’s to one of the query servers? It’s very important to insure good performance to the external users.
Pingback from Resource Monitoring | MarkTab Data Mining
For a seutp where you have internal users and external users going to different servers - I would probably just have your internal users go to one server and external users go to the NLB against the other three servers. If you need them all to go to the same NLB, it depends on different configurations, but NLBs should be able to identify internal vs. external users - and then send internal users to one set of servers.
Pingback from Az Analysis Services oldalra skálázása - K??v??ri Attila Szakmai blogja - TechNetKlub
Pingback from SSAS White Paper list - reading for "SSAS Maestro" program | Vidas Matelis Analysis Services Blog
by Denny Lee As many of you know from the various blogs, whitepapers, and conferences from SQLCAT, there
Pingback from Why the obsession with random I/O within the context of SSAS? - MSDN Blogs
Vidas Matelis összeállított egy egész jó listát azokról a dokumentumokról, amelyek szükségesek lehetnek
Here are this and some other articles on SSAS Performance:
Pingback from SSAS Maestro program – my experience so far | Vidas Matelis Analysis Services Blog
thanks! SQL really helps improve our database. I've used this before and it really helps me a lot.. Thanks
i find it so helpful. I love the information you've just provided. amazing.