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


go


 


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


Output:


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


 Output:


SE_TIME


96203


 


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