Tuesday, August 2, 2016

SSAS Multidimensional Cube Processing - Shrinking the unfamiliar window

Last week I posted some of my perfmon graphs from an SSAS server.  I want to model the work happening on an SSAS server during multidimensional cubes processing - both dimension and fact processing.

That's here:
SSAS Multidimensional Cube Processing - What Kind of Work is Happening?
http://sql-sasquatch.blogspot.com/2016/07/ssas-multidimensional-cube-processing.html

There was a window at the end of the observation period where data was still flowing across the network, CPU utilization was still high enough to indicate activity, but the counters for rows read/converted/written/created per second were all zero.  The index rows/sec counter was also zero.



A colleague read my post, looked at my data... and pointed out:
"One of your graphs shows what was happening there!"

Sure enough, he was right.  Even though I time-shifted my graphs to somewhat obscure the data, he noticed what activity filled the gaps.  I'm gonna hafta work hard to make sure these guys don't lap me soon. :-)

  
In-memory file work, mainly for dimensions. Well, that shrinks the mystery window for me :-)

Let's look at the very tail end of the observation.

At the very tail end of the observation period, data stops coming over the network.  But CPU utilization increases. And CPU utilization drops off as "In-Memory Other File KB" drops off.

So this "other file" was somewhat expensive to handle, even though only for a limited time.

Wonder what that file was, since it wasn't a Dimension Index, Property, or String file?

By now you're probably wondering - why don't you just use profiler already? :-)

I'll get there - soon, probably.  But I'm trying to lay the groundwork for understanding system activity independent of knowing the individual cubes being processed.  My colleagues and I will likely be reviewing many perfmon captures across many cubes servers.  Some of the captures will almost certainly be started after cubes processing has begun.

So I'm hoping to get as far as possible from just the perfmon files, before returning for another source of information.

But certainly - being able to tie out system activity to process step beginning and end from profiler is preferable if in a position to influence cube design and development from the front end :-)   




2 comments:

  1. Please explain what kind of the relationship between "SSAS Multidimensional processing" (from the title of this article) and "SSAS Tabular processing" (from perfmon counters "In-memory..."). These are two different subsystems in no way related to each other.

    ReplyDelete
    Replies
    1. Yes, tabular and MDM are separate. But the perfmon counters are not separately aligned to Tabular and MDM as you've imagined.

      Delete