Live Blogging @PASS - SSAS Consolidation and Virtualisation

Reposted from Chris Webb's blog with the author's permission. 

Here are some notes from the SQLCat team’s session on SSAS consolidation and virtualisation; they’re a bit fragmentary since I’m too busy paying attention to what’s being said! I get asked about these issues by my customers all the time.

• Use Windows System Resource Manager to control how many resources SSAS can use. For more on WSRM see http://technet.microsoft.com/en-us/library/cc755056.aspx
• If SSAS and SQL are on the same server, use the Shared Memory protocol to improve processing performance
• Also since resource usage requirements for SSAS and SQL will be different when processing and when querying, if they’re on the same box you can use WSRM to dynamically change resource allocations at different times.
• Consolidating multiple SSAS databases on the same machine, it can be good to use multiple instances (maybe one per database) on the same machine to give fine control over resource usage, service packs etc.
• Someone asked the question of whether there is an overhead to using multiple instances each with one database rather than using a single instance with multiple databases. Answer: multiple instances would perform better but use slightly more resources; better to start with a single instance and only move to multiple instances when you have a good reason to do so.
• Tests run comparing SSAS running on bare metal and Hyper V – no difference in performance between the two for querying, but for the Storage Engine (processing and SE activity when querying) you use 1.5 times more threads on Hyper V (can modify the default number of threads available for processing, may therefore need to change this for Hyper V).
• Description of a custom-built system for load balancing SSAS developed by the MSSales team inside Microsoft. Code and white paper will be available in a few months.
• There were various issues with Synchronization in SSAS that have been fixed in the late CUs.
• IIS7 performs much better for HTTP access to SSAS – performs as well as a direct connection. I’m sure I also heard somewhere that there were some performance issues for HTTP access that were noticeable over a slow network that have also been fixed in the latest CUs.

The most useful session so far at this conference for me – I learned a lot.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.spaces.live.com/ .