SSAS Performance Seeker Bag Pack


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


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


  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







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

Update (18/02/2008)


  • 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

This entry was posted in Performance. Bookmark the permalink.

4 Responses to SSAS Performance Seeker Bag Pack

  1. Unknown says: Liteon presario 1700 Adapter Liteon presario 1800 Adapter Liteon satellite 1100 Adapter Liteon travelmate 4200 Adapter Dell nf343 Battery Compaq NX9000 DV9000 19V 4.74A 90W 5.5MM*2.5MM Adapter Hp NC6000 NC8230 19V 4.74A 90W 4.8MM*1.7MM Adapter Hp DV9000 19V 4.74A 90W 5.5MM*2.5MM Adapter Dell 6400 D620 19.5V 3.34A 65W 7.4MM*5.0MM Adapter Dell 19.5V 6.7A 130W 7.4MM*5.0MM Adapter Dell 9100 9200 19.5V 7.7A 150W 7.4MM*5.0MM Adapter Dell B130 1300 19V 3.16A 60W 5.5MM*2.5MM Adapter Dell 20V 3.5A 70W HORSE SHOE Adapter Ibm T40 X30 R30 16V 3.5A 56W 5.5MM*2.5MM Adapter Liteon M320 M680 19V 3.42A 65W 5.5MM*2.5MM Adapter Liteon 1200 1800 19V 3.95A 75W 5.5MM*2.5MM Adapter Liteon TravelMate 4200 19V 4.74A 90W 5.5MM*1.7MM Adapter Liteon Satellite 1100 19V 4.74A 90W 5.5MM*2.5MM Adapter Liteon M3 M5244 19V 6.3A 120W 4 PIN ROUND Adapter Liteon 2200 R3000 19V 6.3A 120W 5.5MM*2.5MM Adapter Liteon 5600P 5620 20V 6A 120W 4 PIN Adapter Liteon 1520 1500 NX9100 20V 6A 120W 5.5MM*2.5MM Adapter Samsung GT8000 GT8600 19V 3.16A 60W 5.5MM*3.4MM Adapter Samsung M50 X60 19V 4.74A 90W 5.5MM*3.0MM Adapter Toshiba PA3283U-1ACA 15V 5A 75W 6.3MM*3.0MM Adapter Toshiba PA2521U PA3201U-1ACA 15V 6A 90W 6.3MM*3.0MM Adapter Toshiba PA3237U 15V 8A 120W SPECIAl 4 HOlE Adapter Toshiba PA3467U PA3396U 19V 3.42A 65W 5.5MM*2.5MM Adapter Toshiba PA3589U 19V 4.74A 90W 5.5MM*2.5MM Adapter Acer aspire one zg5 battery Acer black aspire one zg5 battery

  2. Unknown says:


  3. dfasdf says:


  4. Sam Kane says:

    Here are this and some other articles on SSAS Performance:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s