SSAS-Info.com - Microsoft SQL Server Analysis Services (SSAS) 2005/2008 resource hub
SSAS Papers Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services
Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services
User Rating: / 1
PoorBest 
Written by Carl Rabeler, Eric Jacobsen   
Tuesday, 28 August 2007 17:29

Summary: To improve the performance of an individual MDX query that is performing poorly, you must first identify the source(s) of the performance bottlenecks in the execution of the query. This requires that you understand how to use Windows and SQL Server 2005 Analysis Services troubleshooting tools. This best practices article provides information about available troubleshooting tools and demonstrates how to use the most common of these tools to identify and resolve MDX query performance bottlenecks.

Content

Introduction
Methodology for Identifying MDX Query Performance Bottlenecks
Query Execution Analysis Tools
 SQL Server Profiler
 System Monitor
  MSAS 2005:MDX
  MSAS 2005:Storage Engine Queries
 Windows Task Manager
 Kernrate and Kernrate Viewer
 Process Monitor
 MDX Script Performance Analyser
Analyzing Query Execution in Sample Queries
 Getting Started with Query Execution Analysis
  Cache Matching, Filtering, and Prefetching
  Prefetching and Request Reordering
 Storage Engine Bottlenecks and Solutions
  Reducing Disk I/O
  Manually Warming the Cache
  Using Partitions and Aggregations
  Using the Create Cache Statement or With Cache Clause
 Formula Engine Bottlenecks and Solutions
  Ranking Over Sets
  Case, IIF, and Non_Empty_Behavior
Conclusion
Appendix A
Appendix B
Appendix C 

Read more...