Last week I had time to test if SP2 fixed SSAS database processing problem. On the databases that had a lot of partitions processing task sometimes would get “stuck” – there would be no CPU activity and processing would never get completed. Unfortunatelly I have to report that SP2 did not fix this problem – for me processing routine failed from the first attempt. But the good news is that Microsoft workaround for this problem on SP1 works on SP2. You have to change SSAS server parameter ThreadPool\Process\MaxThreads from default value of 64 to 150. I have tested my processing routines with value 150 and so far everything worked as expected.
Found blog entry from PSS SQL Server Engineers that describes this problem and how to solve it:
I am finding this issue on majority of servers I am dealing with. But the number MaxThreds could be different between machines depending on the configuration. I found from my experience that value 150 is OK for servers with up to 8 CPU cores. But when server has 16 CPU cores, I had to use value 200. In any way, I recommend that you follow advice from PSS SQL Engineers and sum values of Threads:Processing Pool Idle Threads, Threads:Processing Pool Busy Threads, and Threads:Processing Pool Job Queue Length performance counters and then add 10. You might need to do that a few times, but eventually you will come up with number good for your specific machine. In my experience, after setting new MaxThreds number I had to restart Analsysis Services server, it did not wake up by itself as described in their article.