I'm very new to MDX, but I'm investigating a performance issue and I'm hoping someone can help point me in the right direction. An MDX query is reading from a partitioned measure group. In the profiler trace, I can see "starting reading data... finished reading data" for the various partitions. But for a few of them, they can take hours to return data. The service is running, but there is nothing at all in the trace for a very long time. Which partition takes a long time to return and which one is quick seems to change from run to run.
Sometimes, the full set of partitions are eventually read and the query finishes. But on a few occasions, it appears to freeze. No one can connect via SSMS. The service is running, but unresponsive. We've gone as long as 4 hours before restarting services.
I've looked at basically all the SSAS performance counters and there is very little data there that seems useful. The memory used is well below the low-memory-limit parameter
Does anyone have a suggestion on how to analyze this? What would cause such long reads of partitions, even leading to a service restart? There doesn't seem to be a clear path on how to tackle this issue, so I'm hoping there is an idea or two out there. thanks!