SSAS Tabular – NUMA and CPU Cores Performance
[UPDATE] After further investigation, I found that the tests in this post were inacurate and the results unreliable. Updated NUMA test results here
In my last post (SSAS Tabular Performance – DefaultSegmentRowCount) I presented some analysis of the query performance impact of changing the DefaultSegmentRowCount setting. This post describes the next tests that I ran on the same system, investigating the impact of restricting SSAS to just 1 NUMA node instead of the 2 avaiable on the server.
It’s well known that SSAS Tabular is not NUMA aware, so it’s common to see advice recommending affiliating SSAS to a single NUMA node to improve performance.
From what I’d read, I was expecting that by affiliating SSAS to a single NUMA node that the query performance would improve slightly, maybe 10-30%.
Recap of the setup:
- 7.6Gb SSAS tabular cube, running on a 2 x CPU 32 core (Xeon E5-2650 2Ghz, 2 x NUMA nodes) server with 144Gb RAM
- SQL Server 2012 SP1 CU7 Enterprise
- 167m rows of data in primary fact
- 80m distinct CustomerKey values in primary fact
- No cube partitioning
- DefaultSegmentRowCount: 2097152
- ProcessingTimeboxSecPerMRow: 0
- CPU core affinity configured using Windows System Resource Manager (see John Sirman’s great guide to using WSRM with SSAS)
I ran profiler, checking the ‘Query End’ duration on a simple distinct count of CustomerKey, with no other filters or attributes involved.
You can see that dropping from 32 cores across 2 NUMA nodes down to 16 cores on a single node had almost no impact at all.
Within a single NUMA node, the performance dramatically improved as the number of cores increased, but as soon as a second NUMA node is added, the performance flat lines, with no further significant improvement no matter how many cores are added.
As per my last post – I’m sure there are other things afoot with this server, so this behaviour may not be representative of other setups, however it again reinforces advice you will have already seen elsewhere, that with SSAS Tabular – avoid NUMA hardware…