Analysis Services suffers incremental performance degradation during session - by David Beavonn

Status : 


Sign in
to vote
ID 778090 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 1/31/2013 2:15:14 PM
Access Restriction Public


In an Analysis Services session, there is a cumulative performance degradation that is caused by the engine re-evaluating previous commands, in addition to the new command.

Internally, each time an MDX command containing a WITH or CREATE is executed in the personalization extension it is added to a collection of commands for the session.  This progressive accumulation of commands in the collection and the fact that the entire collection is re-evaluated each time a new execution occurs for a MDX CREATE or WITH statement on the session level results in the progressively increasing execution time.
Sign in to post a comment.
Posted by David Beavonn on 3/7/2013 at 6:19 AM
As part of an unrelated support case with SSAS, a team member confirmed that each time a command is executed, all previously created session members need to be re-evaluated on some level (Case 112112010023227).

I was keeping this thread up to date as I worked through the issue (before and after my tech support case):

There is sample code. Note that my theory and conclusions were a bit different so please read to the end. In particular, it was the CREATE MEMBER (calculated members) that were more responsible for performance problems than the CREATE SET.
Posted by Microsoft on 3/6/2013 at 11:18 AM
Hi David,

Could you provide the repro for the issue? How do you add each MDX Command? Do you add them through AMO, AML/A, UI, etc.?

The model with SQL trace events would be very helpful.