Vidas Matelis Analysis Services Blog

Microsoft just released new best practice paper “Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services” written by Carl Rabeler and Eric Jacobsen. I just started to read it, but I am very impressed. It describes how to use SQL Server Profiler, System Monitor, Windows Task Manager, Kernrate, Process Monitor and MDX Script Performance Analyzer to analyze MDX queries. For SQL Server Profiler this document lists what events to track and what values actually mean. For System Monitor it describes counters that could be useful for MDX query analysis. Document also describes how to establish if bottlenecks are in storage or formula engine.

Then document shows examples on how actually perform analysis on sample queries, explaining all results and provides recommendations on how to improve MDX query performance.

Amongst other examples there are suggestions on how to do Ranking over sets and demonstrates performance difference between CASE and IIF statements.

I just started to read this document, but already saw a lot of very useful information. You can find this paper here.