RSS Feed
SSAS database processing sometimes gets stuck
April 22nd, 2007 by Vidas MatelisLast 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.
Note:
Found blog entry from PSS SQL Server Engineers that describes this problem and how to solve it:
http://blogs.msdn.com/psssql/archive/2007/01/16/processing-appears-to-stall-or-become-sluggish-on-multi-processor-machines-running-analysis-services-2005.aspx
Note 2008-Nov-07:
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.
Posted in SSAS | 8 Comments »
May 1st, 2007 at 3:20 pm
Found blog entry from PSS SQL Server Engineers that describes this problem and how to solve it:
http://blogs.msdn.com/psssql/archive/2007/01/16/processing-appears-to-stall-or-become-sluggish-on-multi-processor-machines-running-analysis-services-2005.aspx
November 25th, 2008 at 10:45 am
Hi Vidas!
We’re facing the same problem with most of the x64 boxes with 16 or more cpus and 40gb ram. We are processing dozens of small partitions within the same transaction.
When MaxParallel is not specified, SSAS became very optimistic and job scheduler decides to process about 40 partitions in parallel.
Knowing that each partition consumes three threads and adding the fact that SSAS uses up to CoordinatorBuildMaxThreads value (4) for multiple segments within partition; it results in 3 * 40 * 4 = 480 threads used.
So we solved partially by setting a MaxParallel value of 4 in xmla processing commands.
It gave us a well balanced improvement between time and resources consumed.
November 25th, 2008 at 11:00 am
Leandro,
I also played with MaxParallel and planning just in a few days publish PowerShell script to process partitions with MaxParallel setting. I also found that MaxParallel works. But I also found that it is a bit harder to get correct MaxParallel value. I found that when I am not using MaxParallel – my server CPU utilization during processing is 100% and I am getting fastest cube processing. Limiting MaxParallel value usually increases processing time and my CPU utilization goes bellow 100%.
Did you measure how much slower is your cube processing with MaxParallel?
November 25th, 2008 at 11:28 am
Hi Vidas,
I agree that finding the exact maxparallel value is just difficult and it depends on each implementation.
I use resource usage thresholds to find it. I mean that I get care of memory usage and processing queues as the most usual.
When not using maxparallel we saw that it is more likely that Processing Threads Queue Length rise up to 30 or more, causing processing jobs to wait for free threads.
One thing that I’ve not said is that the most of our cubes have small or even lack of aggregations.
I believe that CPU is used more intensively during index and aggregation build, not during reading incoming data, so I guess you must have a great number of aggregations jobs executing at the same time.
Does it happen during full process ?.
November 25th, 2008 at 4:23 pm
Leandro,
Most of the measure groups in production have usage based aggregations. As you said aggregations and building indexes on larger partitions uses lots of CPU. Also because of the business reasons, we have some measure groups that are not straight star schemas and require relational joins (and extra CPU). Most of the issues we notice during full re-process , that might take many hours.
November 26th, 2008 at 2:44 am
Hi Vidas,
If partition are quite large and MaxParallel option is used, then increasing the CoordinatorBuildMaxThreads parameter to a value of 10 or more could read more partition segments in parallel during index and aggregation creation.
Another workaround could be separating data processing from index and aggregation.
You could try both approaches.
Our processing times did not suffer when using MaxParallel as cubes had to be largely partitioned which allowed SSAS to schedule processing jobs more efficently. I’m talking about 300mill monthly records.
November 26th, 2008 at 11:16 pm
Leandro,
Thanks for sharing your experience and the tip on CoordinatorBuildMaxThreads. My understanding is that increasing this value will try to use more memory. I did not explain that I have clients where SQL Server and SSAS are on the same machine (for different reasons). I am assuming that you have proper configuration and your SQL Server database is on the different machine than SSAS. That is probably why we see different CPU usage patterns.
I’ll keep in mind that behaviour is different on different configurations, and I’ll try to change CoordinatorBuildMaxThreads value at some point in the future.
December 14th, 2008 at 8:58 pm
[…] my post “SSAS database processing sometimes gets stuck” comments Leandro Tubia posted that reducing the MaxParallel value helps with the “SSAS […]