Processing our large dimensions on our production 64-bit hardware has worked fine. Our dev environment is 32-bit and has SQL, SSRS, SSIS, and SSAS on the same box. The /3GB switch seems to make lots of things unstable so we're only dealing with 2GB that SSAS could possibly use, and half of that is consumed by other processes. So I'm having trouble getting the cube to process due to low memory availability and large dimensions. I've changed MaxParallel to 1 so that it won't do multiple dimensions or partitions in parallel. That usually prevents it from running out of memory and failing. I'm still having sporatic problems.
Specifically, last night we saw processing hang for over 12 hours right in the middle of processing one of the largest dimensions then suddenly pick up and continue on. There were no other workloads going on that box, so I'm looking for an explanation of what SSAS was doing all that time? Before it kicks off the next bit of processing, I know it looks to see if there are enough resources. If there are not enough resources, what does it do? Does it sleep then check again after a while? What's it waiting for, and what can I do to make it happy so it continues when it gets into a wait state like that?
Here are my log files from this oddity last night:
6/4/2007 6:39:50 PM (ProgressReportBegin, WriteDecode): Started processing the 'Account Number' hierarchy.
6/4/2007 6:39:50 PM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.Account Number): 0
6/4/2007 6:40:14 PM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.Account Number): 1
6/4/2007 6:40:14 PM (ProgressReportEnd, WriteDecode): Finished processing the 'Account Number' hierarchy.
6/5/2007 8:54:31 AM (ProgressReportBegin, WriteDecode): Started processing the 'First Name' hierarchy.
6/5/2007 8:54:31 AM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.First Name): 0
6/5/2007 8:54:32 AM (ProgressReportCurrent, WriteDecode, MYSERVERNAME.MYCUBE.Account.First Name): 1
6/5/2007 8:54:32 AM (ProgressReportEnd, WriteDecode): Finished processing the 'First Name' hierarchy.
Hi Greg,
I had some problems with SSAS 2005 getting stuck during processing. For me it was allways during cube processing (vs dimension processing). We had one suggestion from Microsoft, that actually sometimes worked. We were told to execute simple MDX query to SSAS to wake it up. Unfortunatelly that did not work consistently.
What worked for us is changing SSAS server parameter ThreadPool\Process\MaxThreads from default value of 64 to 150.
As in your case your dimension started around 9:00am (somebody in the office), there is very good chance that somebody submitted some query to server and wake it up.
More information about problem like mine is here:
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
Regards,
Vidas Matelis
|||
I'll try that tip about submitting an MDX query and report back if that works. I had read Wayne's post but had completely forgotten about the "submit a simple MDX query" tip. Thanks Vidas.
As for raising the # of threads, I'll play with that, too. Since we've got MaxParallel=1 to prevent it from doing so much in parallel it runs out of memory (whether that's the best idea, I don't know) I certainly don't think it's short on threads. But I can easily check in perfmon.
|||
The suggestion of "run an MDX query and that will wake up the processing" does seem to do the trick. One problem, though, is when your cube isn't processed, you can't run an MDX query to wake it up. The best solution I've found is to run a Clear Cache statement:
<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache>
