Introduction

One of the things I have to deal with in an Analysis Services project is performance improvement: faster query, using minimum resources, with maximum concurrency.

Performance problems can sometimes be tricky to identify. I've set up a bunch of tools and techniques over the years that helped me sorting out such issues (mainly, but not exclusively, around MDX performance).

Tools

• SQL Server Profiler: "Microsoft SQL Server Profiler is a tool that captures Microsoft SQL Server 2005 events from a server. The events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem". An essential tool to understand what's done by Analysis Services when it executes a MDX query.
• Perfmon: SSAS exposes a complete set of performance counters. These counters are explained in SQL Server 2005 Analysis Services Performance Guide.
• MDX Script Performance Analyser: a handy tool for identifying time consuming statements in a complex MDX Script. Source code didn't evolved since April 2007, but this alpha version is fully operational.
• MDX Studio: the dream tool by Mosha Pasumansky for tracking performance issues in MDX queries and MDX Scripts.
• BIDS Helper: a collection of useful tools that integrate with SQL Server Business Intelligence Development Studio.
• SQL Server 2005 Best Practices Analyzer: "The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment". Most of the best practices recommendations are natively included in SSAS 2008.
• VB Script: quick (and dirty) ADO MD programming can sometimes be used to simulate query workload or cache warming.

Process

1. When query performance problems occur, I start by identifying long time running queries (which can be lost in multiple ones). To do so, I use SQL Server Profiler to collect all running queries. SQL Server Profiler has an option to export MDX queries from a trace (see here and here).
2. Once I collected all the queries, I run them through a simple VB Script I developed, which executes MDX queries stored in a file, and output query number and execution time in a CSV format.
3. Then I copy the CSV output into Excel. Excel allows me to quickly sort and highlight long time running queries.
4. Once I have spotted critical queries, I start analyzing them one by one with MDX Studio.
5. Sometimes, a query can rely on complex MDX Script. In such circumstances, I run the query through MDX Performance Analyzer to locate significant MDX Script parts.

Useful links and resources

General

• SQL Server 2005 Analysis Services Performance Guide.
• SQL Server Best Practices Article: Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services.

Architecture

• Scale-Out Querying with Analysis Services.

Designing

• Analysis Services Query Performance Top 10 Best Practices.
• OLAP Design Best Practices for Analysis Services 2005.

Processing

• Analysis Services Processing Best Practices.

MDX

• SQL Server Analysis Services MDX Query performance monitoring.
• Using SQL Profiler for Analysis Services 2005.
• Performance of IIF function in MDX.
• How to warm up the Analysis Services data cache using Create Cache statement.

Conclusion

This list is not exhaustive, so feel free to fulfill it by dropping a comment.

Update (18/02/2008)

Tools

• U2U Analysis Services Best Pratice Analyser: "Analysis Services Best Practise Analyser (SqlAsBpa for short) is a tool which checks your live Microsoft Sql Server Analysis Services 2005 against some important best practises, and reports items which violate these best practises".

Usefull links and resources

• SQL Server 2005 BI evaluate and diagnose Analysis Services database performance.