What You Need to Know About SSAS Processor Affinity

I’ve been looking into using processor affinity on our SSAS instances to help control the workload on our servers. Sometimes a process can run which will grab as much CPU resource as it can, which means that queries on the cubes cannot run, grinding the front end to a halt. Unlike the db engine, there is no resource governor for Analysis Services. Sure you can set the min/max memory simple enough, but setting limits on IO and CPU is a more complex task.

This post is a good intro to the configuration settings when setting processor affinity. I recommend you also read up on the linked post on that article. However, it’s not just a case of all SSAS related activities running only on the defined processors. There’s multiple thread pool references that you can set custom affinities for (look for the properties with affinity in the name). If you’ve read that list you’re probably wondering what group affinities you should configure to fix the problem you are having, if indeed you are having an issue. This is hard to determine without testing. For me, in our scenario, I set the IOProcess and Process Group Affinity to the same processors and left the others blank. This removed out problems of the CPU maxxing out, but for you it may be different. If you are not having any issues w/r/t CPU, then I would advise you to leave these settings as they are.

So as you have to enter hexidecimal you have to convert your binary. I used this binary-to-hex calculator to convert the binary values. One thing I discovered that I had to do to get the settings to take effect was to prefix all hex values with “0x” for the values to take affect. Yes it is case sensitive, so that needs to be a lower case “x”.

You can also alter the PerNumaNode setting. By default it is set to -1 (this is the same in SQL Server 2014 as it was in 2012), which is recommended. Using -1 allows SSAS to adjust the number of thread pools and their thread affinity based on node count. If the system has fewer than 4 nodes, SSAS implements the behaviors described by PerNumaNode=0, whereas PerNumaNode=1 is used on systems having 4 or more nodes. You can read more about those behaviours in the MSDN article PerNumaNode.

If you are gong to alter this setting and you are using VM’s then you should turn on Virtual NUMA. This is available in the latest version of Hyper-V. You cannot use dynamic memory with the VMs, so they have to be static. You also need to be aware of NUMA spanning, but unless the VM’s you have are very large, or the total memory on the hosts very small, this will not be an issue. If you do not have access to your hosts, you can confirm whether or not V-NUMA is configured for your VM by checking via task manager. Viewing processors via NUMA will be greyed out if it is not available. Box below has not had V-NUMA enabled.

numa_greyed_out

This virtual machine does have V-NUMA turned on. We can see it is virtualising a 2 socket machine, but the great thing about V-NUMA is the flexibility it provides us: we can configure it to have more or less NUMA nodes, or alter the size of memory in each NUMA node simply by changing the settings, which is not possible in a physical machine.numa_node_enabled

Once you have altered the hex values you must restart your SSAS instance for the processor affinity to take affect. You can verify whether your changes took effect by checking the properties of the SSAS box and the log file.

In the properties window, you can see the current setting of the Processor Affinity; if you altered this and restarted the box, and the current value is blank, then you know for some reason they did not take affect.

processor_affinity

In the log file, before I altered the Process Affinity settings this was the thread settings:

6/6/2014 3:29:04 PM) Message: The IOProcessing thread subpool with affinity 0x000000000000ffff now has 1 minimum threads, 160 maximum threads, and a concurrency of 20.

Post change the settings were different:

(6/10/2014 7:33:07 AM) Message: The IOProcessing thread subpool with affinity 0x0000000000000077 now has 1 minimum threads, 64 maximum threads, and a concurrency of 12.

For further reading I refer you to this post. I echo the warning that you need to test this as much as possible before changing production. Happy tinkering!

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

1 thought on “What You Need to Know About SSAS Processor Affinity”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s