Analysis Services scalability

Question

Analysis Services scalability

Jamie Thomson

SSChampion

Points: 11805
More actions
June 24, 2004 at 3:06 am

#108612

Hello,
I was asked the following question lately:
What are Analysis Services’ key weaknesses in terms of scalability?

and whilst I consider myself a fairly proficient AS2k pro I couldn't come up with a decent reasoned answer and waffled something about clustering.

Anyone fancy a stab?

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
June 28, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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

Larry Stein Default port Points: 1447 More actions · Answer 1

My answer depends upon what you mean by "scalability." If you mean number of users, I have not seen a problem directly as a result of number of users since we went to 64-bit. My related problem is users capable of generating MDX that can bring the system to its knees. The more users we get, the more that want to do unreasonable queries (in my selfish opinion).

If you mean volume of cubes/data/query size, I have 2 current issues with AS2K.

1) Loss of usage-based statistics in cases where it might have been avoidedwith more MS development effort (e.g. addition of a simple new measure). Having to do without the stats more than a week or two to reaccumulate the same usage info is difficult to explain to users.

2) Difficulty diagnosing AS performance problems. I hate having to bounce AS because users are stacking up and AS is either not responding at all (gaining no CPU ans data retrieval is not the issue) or is pegging the CPU and we can't tell which MDX is the offender. Either way, users are getting timed out, I can't tell what is happening, and we have to wait 15 minutes for AS to finish restarting (yes, lots of cubes, lots of large dimensions). In fact, I am about to post on a diagnostics issue...

Larry

Jamie Thomson SSChampion Points: 11805 More actions · Answer 2

Thanks Larry, thats really interesting. it turns out the answer they weere looking for wsa something along the lines of:

1) The way it handles multiple large dimensions (dimensions are crossjoined and all members pushed to the client for resolution against the facts hence a cube can perform very poorly and tie up the desktop CPU even if there are a trivial number of rows in the fact table).

2) Memory limitation. 3GB is the maximum available memory for A/S…and all dimensions are loaded into memory when the A/S Service is started. Adding custom security roles creates “shadow” dimensions which are also loaded into memory on startup. Hence, there isn’t much memory available to start with and A/S will quite happily consume all of it just getting up and running.

cheers

Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Hey Jamie,

re: point 1, You can force the processing to occur on the server for nearly every type of query, but in general, I would say 'hear hear' that the AS-PTS architecture doesn't perform as well as was intended. Bring on Yukon and the native xmla i/f.

re: point 2, you could go 64bit for more memory, (i think thats even a msft recommendation from one of Dave Wickerts papers/preso's), but at the moment we've got general performance issues rather than mem limitations that would be included in my answer on scalability. One of my pet peeves is zero suppresion, nonemptycrossjoin works a treat for 'natural' members but can't be used at all for calculated members (whether session or cube based), and so far, beta 2 of 2005 is proving to have the same limitation.

Just out of interest, did you client/bosses decide for or against AS based on the limitiations you supplied?

Cheers,

Steve

Steve.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

Larry, I am searching to remember where I saw it, but I thought Dave Wickert (in a preso) gave a possible hack/cludgey (3rd party process viewer??) way to see which users are currently using AS, not sure if it provided the MDX though.

If you look on MSDN for a presentation (webcast, cut in 2 pieces) by Dave Wickert re: 'Top 10 Tips for Operating and Maintaing an AS Database', in part two, he tells and then demos the use of tcpview.exe by sysinternals to see who is connected, and kill their session I think I may have even posted a link to this preso on a previous postsome months ago.

Also, are your users using a 3rd party viewing tool, or typing their own mdx? Most/some 3rd party tools allow for the logging of the mdx query, which when used in conjunction with the results of tcpview, you might be able to pinpoint culprits?

cheers,

steve

Steve.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

Jamie,

Not the preso I was referring to, and probably all too late, but if you look for Dave Wickert in the list of presenters at http://www.microsoft.com/usa/presentations/search.asp?sKeywords=&sDistrict=South%20Central&sortby=Presenter&SortOrder=ascending you'll find one of his presentations regarding scalability. He points to the ops and perf management docs on MSDN a fair amount, so worth downloading them also.

Steve.