Which Engine (Storage / Formula) causing MDX Performance Issue?


Follow below mentioned steps:

-Set Profiler with Query Begin and Query End Event, Run Profiler Trace and Execute MDX which you need to tune.

-Stop Profiler trace and save trace to table in RDBM

-What you need to do is run the following SQL query against the table in which you had saved Profiler Trace

Select * from <table_name> where eventclass=9 or eventclass=10 order by connectionid, currenttime



Once you've got that and have identified the long running query or queries, run the following:


Select sum(duration) as SE_TIME from <table_name> where eventclass=11 and connectionid = <Target_connectionid> and rownumber > <rownumber_for_eventclass=9> and rownumber < <rownumber_for_eventclass=10>


Compare the SE_TIME value with the duration value for Eventclass=10 on the long running query. If it's greater than 30% of the duration, the bottleneck is in SE otherwise it's in FE.


For Example:

Select * from master.dbo.cxtable where eventclass=9 or eventclass=10 order by connectionid, currenttime


RowNumber      EventClass          Duration              ConnectionID

1                              9                              NULL                     36

5055                       10                           216516                  36



Select SUM(duration)as SE_TIME from master.dbo.cxtable where eventclass=11 and connectionid = 36 and rownumber>1 and rownumber<5055





if setime >30%of eventid 10 Duration, then culprit is SE

30% of event id 10 (30% of 216516) = 64954.8

96203 > 64954


In this case culprit Storage Engine.


Comments (0)

Skip to main content