The cube on my project has been hitting some apparent concurrency issues, so I’ve been hunting for advice on how to tune the hardware (model tuning has already gone a long way). Unfortunately Microsoft don’t have any reference architectures – and their only other advice was to try and use an appliance in Direct Query mode – which was not practical in our circumstances any way.
As usual, the gents at SQLBI had something useful to say on the subject based on a customer case study, which is detailed in this white paper. While well worth a read, I’ll summarise the key findings:
- Standard Server CPU’s don’t perform well enough, and you will need to look at faster CPU’s with a large cache
- Faster CPU’s are better than more CPU’s in terms of return on investment for perfromance
- Fast RAM is a must
- For NUMA aware servers you need to set the Node Affinity to a single node, preferably using a Hyper-V host for your tabular server
Setting aside the last point, which is a bit deep in server config and requires more explanation, the key thing is to look for fast CPU. They found that Workstation Blades were generally better than Server Blades, and some of the best performance they got was out of one of their Dev’s gaming rigs!
We’ll be trying some of this out and hopefully I can keep you posted with results. I have more stuff on monitoring tabular in the pipeline now I’ve finished my PowerPivot book (to be published soon).
Also, don’t forget my upcoming DW Starter Training on Nov 24/25 in Sydney