[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.]
Question:
My MDX query is running very slow. I need to identify the root cause of this issue.
Answer:
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
http://www.microsoft.com/downloads/details.aspx?FamilyId=975C5BB2-8207-4B4E-BE7C-06AC86E24C13&displaylang=en
SQL Server 2008 White Paper: Analysis Services Performance Guide
http://www.microsoft.com/downloads/details.aspx?FamilyID=3be0488d-e7aa-4078-a050-ae39912d2e43&displaylang=en
Best practices for SQL Server 2005/2008 OLAP cube design and MDX querying
http://technet.microsoft.com/en-us/library/dd285464.aspx
Please remember to mark the replies as answers if they help and unmark them if they provide no help
