| Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services |
| 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 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 |
