Cube processing very slow - Profiler shows Discover XML metadata

Question

Cube processing very slow - Profiler shows Discover XML metadata

venus.pvr

SSCarpal Tunnel

Points: 4813
More actions
November 24, 2014 at 7:02 pm

#151390

Hi,
My cube processing has finished processing all the measures. And it shows "Finished building aggregations and indexes for the 'xxxx' partition'. After that it is stuck and runing for a long time.
SSAS profiler showed Discover_xml metadata and now it shows no activity for the last 5 minutes. Please let me know what might be happening. Please let me know if I need to give more information.
Thanks,
veena

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17463 More actions · Answer 1

More information would be good, yeah 😀

What is the size of your cube? How many aggregations do you have? Server architecture? Anything else happening on the box? So, so many things can cause a slow process.

I would generally ignore that "master" message you get when processing from Visual Studio/SSMS as it can be misleading. When it get's "stuck" at the point you describe have a look at all of the nodes that display in the process progress box (under the "Command" node), which one still has a little working "cog" next to it - that's what's taking time.

Have a read of the following best practices article and see that you are following the advice for your environment http://technet.microsoft.com/en-gb/library/cc966525.aspx

I'm on LinkedIn

venus.pvr SSCarpal Tunnel Points: 4813 More actions · Answer 2

the cube size is about 70 GB. it was running fine a few days ago and it is very slow now. We don't have a lot of aggregations. But it seems like it hangs at some point and just doesn't show any activity than discover_xml_metadata..

</PropertyList>

not sure what is going in the background. If the look at memory and CPU SSAS consumes at this point, Memory remains constant and is acceptable(we have 64 GB available and usage ie much lower). CPU shows 0.

Not sure what could be blocking.

Thanks.

PB_BI SSCoach Points: 17463 More actions · Answer 3

Have a read of the following: download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc

Pay attention to the Processing and Server Tuning parts.

Have you tried a trace on your source SQL instance to see if it could be getting blocked there?

I'm on LinkedIn

venus.pvr SSCarpal Tunnel Points: 4813 More actions · Answer 4

Thank you for the resources. Actually I checked the profile and it was the query issue. The query was running forever on the oracle side and never returned the dataset. Once that was tunes, everything else was fixed.