Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services
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
IntroductionMethodology 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
Tags: mdx, non empty, performance, must read, paper