The first thing you are going to is to figure out is if you even need a dedicated analysis services server.
Some of our smaller customers just go for a mini “appliance” server which has Analysis Services/SharePoint and sometimes even a small data warehouse and PowerPivot installed on a single virtual machine with 16+ GB of RAM. For under 50 users, with only maybe 10 concurrent users, a cube size under 10GB or so and light usage this may work well, and is certainly a good starting block for an SME, proof of concept or small department.
Larger customers may want to “co-habit” the data warehouse and the analysis services installation. This is an ideal option if the data warehouse is populated at night and the analysis services is queried mainly during the day. Memory allocation is the problem here as in both SQL Server and Analysis Services you will need to adjust memory settings. we have worked for 4-5 financial institutions who do this.
As the user count creeps into the hundreds, cube sizes start to go over 100GB, or queries become more intense, the need for a dedicated server emerges.
While there are some great reference architectures for data warehouses like Fast Track, there is no such equivalent for Analysis Services. Here are my thoughts based on what we see in our customer sites, our lab and load testing:
1. How much Memory do I need ?
well many of our customers with cubes under 50GB in size just make sure the memory is larger than the cube “to be sure”, but as you scale to 100GB+ this becomes in-practical. So you need to plan for all the dimensions and a good sizable chunk of measure groups begin cacheable. If you already have the cube and a load test harness, the “best” way to know how much memory is needed is simply to measure it! The [MSAS2008:Memory\Memory Usage Kb] performance counter will show the working set and memory should be sized as say 40% higher than this to allow for OS and growth. Once you know how much memory you need you’ll know if you can buy a single socket server(s) or if you need to buy a dual socket server and only fit one socket (see below)
2. How many Sockets or CPU’s do I need?
Two things we really need to be aware of is:
- Analysis services 2008R2 does NOT support greater than 64 processors, so your four socket, 80 core server is a no no until SQL 2012.
- For many workloads the cost of NUMA is going to result in very poor performance gains and in cases where the workload is memory bound, potentially slower performance.
So the largest server that we recommend by default for Analysis Services is a SINGLE numa node. If you need more throughput than a single node, you need to scale out using load balancing.
Now we are not saying that you can’t scale analysis services beyond two sockets, but for most customers we have worked with in under 500GB or so size range, performance has not scaled as well as you would hope for with NUMA. Below is a load test we ran on a complex report where the single NUMA node using 10 cores actually performed more throughout in terms of queries over a fixed time on the same server with 20 cores. Let me repeat that – you may find out that you can make your Analysis Services server service more users by simply removing some CPU’s!
The challenge here is you may not be able to buy a single socket server as these are often limited to only 32GB of RAM, so you may end up buying a dual socket server and only fitting one socket just to fit enough memory. Some of our customers even buy dual socket servers and “throw” one of the CPUs in a cupboard, it depends how flexible your supplier is!
3. What type of CPU and Memory clock speed do I need ?
This depends on your workload and how much you care about performance
The formula engine in Analysis Services is single threaded, so in many cases, 1 good CPU will be faster than 64 slower ones.
If you queries which are returning large cell sets then memory clock speed will be critical.
Here is the selection we would make buying a dell R410. Note that with the licensing changes in SQL 2012 a four core configuration may become practical.
We have customers who use IBM or HP Servers with the 10 core Intel Xeon E7 2870 chipset with 10 cores and 20 threads (hyper threaded). These have scaled to 20 concurrent connections running complex queries, with a 10-1 concurrency ratio of actual users to physical connections this could be a user base of say 200 users.
As I mentioned before be aware of licensing changes where one license will be required per four cores. This makes denser environments less appealing then less cores with higher specification.
4. How many nodes do I need for Scale Out ?
The answer to this is quite simple. How many users can a single node support and how many users do I want to plan for, accounting for the fact that we may need N-1 if we are implementing high availability.
As a rough rule of thumb, maybe 1 node per 50 physical connections for smaller environment. If you have larger queries this may come down to say one node per 20 physical connections or lower. Bear in mind that 50 actual connections may be a user base of hundreds of concurrent users due to think time, etc.
5. Can I virtualise Analysis Services ?
Absolutely, certainly more so than with the SQL Server DB Engine. Most hypervisors do not like to cross NUMA boundaries and Analysis Services does not like to cross NUMA boundaries so from this perspective they are good partners.
However there is one huge conflict. In Analysis Services we want fast CPU’s and memory clock speed. With virtualisation people typically use much denser environments with much slower CPU’s and lots of cores to increase efficiency. You will pay your performance costs not because of the virtualisation itself, but more due to the generic nature of the hardware used.
6. How can I prove I have the right hardware and it is configured correctly?
A load test is actually much easier than you think. Come and see my session at SQL Bits 10 which may be recorded 😉
http://sqlbits.com/Sessions/Event10/Load_Testing_Analysis_Services
Alternatively, I love nothing better than helping customers load testing Analysis Services!
