Analysis Services suffers incremental performance degradation during session

Analysis Services suffers incremental performance degradation during session by David Beavonn

Active

Type:	Suggestion
ID:	778090
Opened:	1/31/2013 2:15:14 PM
Access Restriction:	Public

Workaround(s)

view

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.

Details (expand)

English

Analysis Services

Optimize the ability to add a series of calculated members.

Improved Performance

0 attachments

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):
http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/87b0d98f-2345-44d5-81d7-ca8078c49bfb

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.

Thanks,
Karen

Posted by David Beavonn on 1/31/2013 at 2:19 PM

One possible workaround is to use an undocumented feature of the CREATE MEMBER statement that allows multiple session members to be created at the same time. This is similar to the way that "WITH MEMBER ... ... ... SELECT ..." allows more than one member to be defined for a query.