I've been evangelizing the use of SQL Profiler to define and identify SQL Server performance problems since the tool was first released with SQL Server 7.0. I've always felt that SQL Profiler--and the underlying SQL Trace technology--is the single most valuable tool in a DBA's arsenal for identifying most performance problems. SQL Profiler is still a somewhat underutilized tool, but over the years, a growing amount of literature and best practices have been published so that DBA's now have a fighting chance of using SQL Profiler effectively for relational engine analysis. However, SQL Profiler's support for MDX and SQL Server Analysis Services wasn't available until SQL Server 2005. Even now, two years after the release of SQL Server 2005, my sense is that SQL Profiler usage in the OLAP/MDX space is minuscule. To date, there hasn't been a lot of content focused on how to use SQL Profiler to performance tune MDX queries. However, Carl Rabeler and Eric Jacobsen from Microsoft's SQL Server Customer Advisory Best Practices Team released a white paper called Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services that's focused on just that.
According to the white paper "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."
The white paper has almost 60 pages of high-quality content about how to tune MDX queries. But what really caught my eye was the substantial amount of information about how to interpret SQL Profiler events related to MDX and Analysis Services. The white paper includes many queries that reference an AdventureWorks sample data warehouse-oriented database. The white paper works through running the queries, interpreting the SQL Profiler data, and helping you understand how to solve the performance problems presented by the query in question. I think this white paper is a great approach for learning how to tune queries, and the SQL Profiler fanatic within me was thrilled to see Microsoft taking an active interest in helping customers learn how to apply this valuable tool to the MDX and Analysis Services world. Nice work Carl and Eric.
