Cube processing hangs when CoordinatorExecutionMode set to anything other than 1

Question

We have a hyper-v vm running sql server analysis services.  We started with 1 cpu allocated to the vm, and then scaled up to 4 cpus allocated to the vm.  When we scaled up to 4, we began to experience a problem with the nightly build of the ssas cube.  It would peg the cpu at 100% and never complete.  Killing the process and restarting would work for the most part, but whenever it was included as a part of the typical nightly etl it would hang.  

I checked the CoordinatorExecutionMode at that time and it was set to -4.  I set this parameter to 1 and have had consistent success however the cube build is long running and it seems silly that with 4 processors (albeit virtual) I cannot utilize multi-threading for this cube build.  I have tried setting this parameter to -1, to 2, to 4, and all create this situation with the cube build hanging. 

Anyone have any clues or is this a bug when using virtual machines with multiple virtual processors?  The other aspects of ETL, the SSIS packages and Sql Server pieces all had drastic performance improvements with faster run times when we added the processors, ssas seems to be the only component with the problem that began hanging and actually finishing slower.

 

Answer 1

It could be a number of things. I would try the following ideas, and if you can still reproduce the problem, open a support case.

First, especially if it's AS2005, it could be that a longrunning query is still running and preventing cube processing from committing. If there are no users querying your cube in the middle of the night, I setup a job to restart the SSAS service right before nightly cube processing. I'd recommend you consider this.

Second, there's a known problem when SSAS runs out of threads during processing and hangs. See this article. I would bump up ThreadPool\Process\MaxThreads to 128 or more and see if you can still reproduce this problem.

http://blogs.msdn.com/b/psssql/archive/2007/01/16/processing-appears-to-stall-or-become-sluggish-on-multi-processor-machines-running-analysis-services-2005.aspx

I would also run Profiler while you're doing cube processing one night and see if when it "hangs" it's still running, but very slowly.

Hope some of that helps.

---

http://artisconsulting.com/Blogs/GregGalloway

Answer 2

Thank you very much for your response.  We are on SSAS 2008 build 10.0.2757.0.

Interesting suggestion to restart the SSAS service before the build.  I may go for that if it consistently works.  I have set the max threads to 128 and 256 previously, it seems to speed up the process when coordinatorexecutormode was set to 1 but did not prevent this hanging cpu issue if coordinatorexecutormode was set to something other than 1.  I dont think its running slowly when it hangs, but I will run the profiler.   I see the cpu pegging at 100% for as long as i let it run, longest was almost 3 hours for a cube process that typically takes around 45 minutes.

As I stated it seems to be something in the way SSIS is triggering the cube build after the nightly ETL and the way the VM handles the multiple CPU resources assigned to it.   Not sure but it feels like a bug.