Report Portal

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

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...

Tags: mdx, non empty, performance, must read, paper

 

2007-2015 VidasSoft Systems Inc.