SQL Server Performance

Help required in optimizing the MDX query

Discussion in 'Analysis Services/Data Warehousing' started by madswami, Dec 23, 2008.

  1. madswami New Member

    Hi,
    We are using the following MDX query to query TFS Cube. We are getting time out problem on some TFS servers having huge number of work items. We need help in fine tuning the MDX query.
    Quick points to note –
    • The MDX query seem to be consuming high parsing time
    • The query when executed for 1 day of date on production server it took approx 12 sec to parse
    • After parsing the query, in execution mode the MDQ query consumes lot of time
    • In execution mode, the query takes more than 9 minutes
    • The MDX query is embedded inside a RDL report
    • On production server, SSRS time out is set to 2min
    MDX Query used in the report
    WITH MEMBER Measures.[Remaining Work] AS
    IIF([Measures].[Microsoft_VSTS_Scheduling_RemainingWork]= null or [Measures].[Microsoft_VSTS_Scheduling_RemainingWork]<=0,
    (IIF([Measures].[Microsoft_VSTS_Scheduling_BaselineWork]= null,0,[Measures].[Microsoft_VSTS_Scheduling_BaselineWork])), [Measures].[Microsoft_VSTS_Scheduling_RemainingWork]) SELECT { Measures.[Remaining Work] } ON COLUMNS, non empty { (STRTOMEMBER(@StartDateParam):STRTOMEMBER(@EndDateParam)) *[Work Item].[System_Id].[System_Id] } ON ROWS FROM [Work Item History] WHERE ( { [Work Item].[System_WorkItemType].&[Bug], [Work Item].[System_WorkItemType].&[Requirement], [Work Item].[System_WorkItemType].&[Task] } ,STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]") ,STRTOSET(@IterationPath) StartDateParam and EndDateParm takes values of this type : [Date].[Date].&[2008-03-22T00:00:00].
    So , it’s taking values from Date dimension . Let me know if you need any further details.
    Thanks, Swami
  2. satya Moderator

  3. madswami New Member

    Thanks for responding.
    Parsing of the query itself is consuming lot of time when tried to compile from a SQL editor inside analysis server. After the query parsing is over, the executing time consumes 9 times the parsing time which itself seem to be hogging the entire processing time.
    When the same is executed from an RDL file, it times out as the default timeout on reporting services is set to max of 2min. Since the parsing & execution is taking more than 9 minutes, there is no way the RDL file can wait until 9 min.
    Can you please suggest/modify the provided query and let me know if I could try anything different to enhance the performance of the query.
    Appreciate your help.
    Thanks,
    Swami
  4. satya Moderator

    Did you get a chance to review the articles above?

Share This Page