Freigeben über

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

  • Artikel

 

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.