[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.

TabularQueryTimeByCores

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…

Frog-Blog out

© Alex Whittles, Purple Frog Systems Ltd

7 Responses to “SSAS Tabular – NUMA and CPU Cores Performance”

  • Greg Galloway:

    Very interesting. What SSAS memory limits did you use when affinitized to one NUMA node?

    • Alex:

      HardMemoryLimit: 50
      LowMemoryLimit: 35
      TotalMemoryLimit: 50
      VertipaqMemoryLimit: 45

      I do have another (28Gb) tabular cube on there, so memory is a little tight at the moment when limiting to a single NUMA node – hoping for a bigger server with faster memory soon!

  • Marco Russo:

    Create one SSAS instance per NUMA node and split databases between instances… but the real issue is what happen whether your database is larger than RAM local to a single NUMA node

    • Alex:

      Yeah that’s what we’re seeing. The problem is in this environment we only have a single large database, so we can’t split it – and the hardware was not originally specified for use with the Tabular model so it’s far from ideal.

      Just goes to show that you need to know what you’ll be using it for before buying a server!

  • Henk:

    Hi Alex,
    looking at this graph and was wondering ;
    at some point the CPU power is no longer the bottleneck ? How much data are you scanning in memory ?

    • Alex:

      Hi Henk.

      Both queries were scanning approx 900m records, with 200m distinct customers.

      The two queries show different CPU profiles. One CPU limited, one not. The simple ‘single result’ query, which just asked for the total distinct count across the entire table, kept improving as cores were added (within a single NUMA node). The more complex query split the results up into about 600 cells, split using a number of different attributes from different tables. This performance levelled out at 8-10 cores.

      My assumption at the moment is that this performance is being constrained by the memory speed (only a frustrating 667Mhz on this server). I’m hopefully getting hold of a 1866Mhz RAM server to test this theory.

    • Alex:

      Henk – correction, my comments apply to the later tests (http://www.purplefrogsystems.com/blog/2014/02/ssas-tabular-performance-numa-update/), not these.

      This was a smaller dataset, and the CPU performance tests were flawed, as explained on the update post.

Leave a Reply

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Organising SQLRelay

Submit a session for SQLBits

Frog Blog Out
twitter
rssicon