How do I troubleshoot the slow MDX query performance? RRS feed

  • General discussion

  • [This is just for sharing information for those common asked questions collected from forums. If you have any better way or feedback, please directly reply in this thread so that community users can benefit from your answers.]

    My MDX query is running very slow. I need to identify the root cause of this issue.
    For MDX query performance, it is usually caused by the following factors:
    1. Bottlenecks from SSAS Formula engines.
    2. Bottlenecks from SSAS Storage engines.
    3. Bottlenecks from system resources.
    You can use SQL Server Profiler and System Performance Monitor to track the cause of the issue. For formula engines, you need to check the following events:
    Query SubCube
    Query Begin
    Query End
    You can determine how much time the storage engine consumes by checking the Query SubCube event's Duration value. Compare it with the Duration value in Query End event. If it is larger than 30%*(Duration  in Query End event), your SSAS storage engine may have performance bottlenecks. You can add
    Query SubCube Verbos event for more detailed information on the TextData column. When this issue happens, you can consider refreshing your cache or using partitions to improve performance.
    If the subtraction between the total time of query and the time spent on Query SubCube is the query cost spent on formula engine. If it is larger than  30%*(Duration  in Query End event), your SSAS formula engine may have performance bottlenecks. You need to narrow down the cost of the query by adding more events under Query Processing.
    For bottlenecks from system resources, you need to use System Performance Monitor to monitor your server and Analysis services instance to check if there is any memory, I/O, CPU, and network  bottlenecks. You can read the following articles for detailed information and MDX optimization:
    SQL Server Best Practices Article: Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services
    SQL Server 2008 White Paper: Analysis Services Performance Guide
    Best practices for SQL Server 2005/2008 OLAP cube design and MDX querying


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Friday, June 25, 2010 3:59 AM