SSAS Performance Seeker Bag Pack

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

Architecture

Designing

Processing

MDX

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

Advertisements
This entry was posted in Performance. Bookmark the permalink.

4 Responses to SSAS Performance Seeker Bag Pack

  1. Unknown says:

    http://www.batteryfast.com.au/laptop-ac-adapter/liteon/presario-1700.htm Liteon presario 1700 Adapter http://www.batteryfast.com.au/laptop-ac-adapter/liteon/presario-1800.htm Liteon presario 1800 Adapter http://www.batteryfast.com.au/laptop-ac-adapter/liteon/satellite-1100.htm Liteon satellite 1100 Adapter http://www.batteryfast.com.au/laptop-ac-adapter/liteon/travelmate-4200.htm Liteon travelmate 4200 Adapter http://www.batteryfast.com.au/dell/nf343.htm Dell nf343 Battery http://www.batteryfast.com.au/laptop-ac-adapter/compaq/compaq-19V-4.74A-90w-5.5mm-2.5mm.php Compaq NX9000 DV9000 19V 4.74A 90W 5.5MM*2.5MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/hp/hp-19V-4.74A-90w-4.8mm-1.7mm.php Hp NC6000 NC8230 19V 4.74A 90W 4.8MM*1.7MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/hp/hp-19V-4.74A-90w-5.5mm-2.5mm.php Hp DV9000 19V 4.74A 90W 5.5MM*2.5MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/dell/dell-19.5V-3.34A-65w-7.4mm-5.0mm-with-pin-pa-12.php Dell 6400 D620 19.5V 3.34A 65W 7.4MM*5.0MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/dell/dell-19.5V-6.7A-130w-7.4mm-5.0mm-with-pin-pa-13.php Dell 19.5V 6.7A 130W 7.4MM*5.0MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/dell/dell-19.5V-7.7A-150w-7.4mm-5.0mm-with-pin-pa-15.php Dell 9100 9200 19.5V 7.7A 150W 7.4MM*5.0MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/dell/dell-19V-3.16A-60w-5.5mm-2.5mm.php Dell B130 1300 19V 3.16A 60W 5.5MM*2.5MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/dell/dell-20V-3.5A-70w-horseshoe-style-special-for-dell-pa-6.php Dell 20V 3.5A 70W HORSE SHOE Adapter http://www.batteryfast.com.au/laptop-ac-adapter/ibm/ibm-16V-4.5A-60w-5.5mm-2.5mm.php Ibm T40 X30 R30 16V 3.5A 56W 5.5MM*2.5MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/liteon/liteon-19V-3.42A-65w-5.5mm-2.5mm-white.php Liteon M320 M680 19V 3.42A 65W 5.5MM*2.5MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/liteon/liteon-19V-3.95A-75w-5.5mm-2.5mm.php Liteon 1200 1800 19V 3.95A 75W 5.5MM*2.5MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/liteon/liteon-19V-4.74A-90w-5.5mm-1.7mm.php Liteon TravelMate 4200 19V 4.74A 90W 5.5MM*1.7MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/liteon/liteon-19V-4.74A-90w-5.5mm-2.5mm.php Liteon Satellite 1100 19V 4.74A 90W 5.5MM*2.5MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/liteon/liteon-19V-6.3A-120w-4pin-round.php Liteon M3 M5244 19V 6.3A 120W 4 PIN ROUND Adapter http://www.batteryfast.com.au/laptop-ac-adapter/liteon/liteon-19V-6.3A-120w-5.5mm-2.5mm.php Liteon 2200 R3000 19V 6.3A 120W 5.5MM*2.5MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/liteon/liteon-20V-6A-120w-4-pin.php Liteon 5600P 5620 20V 6A 120W 4 PIN Adapter http://www.batteryfast.com.au/laptop-ac-adapter/liteon/liteon-20V-6A-120w-5.5mm-2.5mm.php Liteon 1520 1500 NX9100 20V 6A 120W 5.5MM*2.5MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/samsung/samsung-19V-3.16A-60w-5.5mm-3.4mm-pin-inside.php Samsung GT8000 GT8600 19V 3.16A 60W 5.5MM*3.4MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/samsung/samsung-19V-4.74A-90w-5.5mm-3.0mm.php Samsung M50 X60 19V 4.74A 90W 5.5MM*3.0MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/toshiba/toshiba-15V-5A-75w-6.3mm-3.0mm.php Toshiba PA3283U-1ACA 15V 5A 75W 6.3MM*3.0MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/toshiba/toshiba-15V-6A-90w-6.3mm-3.0mm.php Toshiba PA2521U PA3201U-1ACA 15V 6A 90W 6.3MM*3.0MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/toshiba/toshiba-15V-8A-120w-special-4-hole.php Toshiba PA3237U 15V 8A 120W SPECIAl 4 HOlE Adapter http://www.batteryfast.com.au/laptop-ac-adapter/toshiba/toshiba-19V-3.42A-65w-5.5mm-2.5mm.php Toshiba PA3467U PA3396U 19V 3.42A 65W 5.5MM*2.5MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/toshiba/toshiba-19V-4.74A-90w-5.5mm-2.5mm.php Toshiba PA3589U 19V 4.74A 90W 5.5MM*2.5MM Adapter http://www.batteryfast.com.au/acer/aspire-one-zg5.htm Acer aspire one zg5 battery http://www.batteryfast.com.au/acer/black-aspire-one-zg5.htm Acer black aspire one zg5 battery

  2. Unknown says:

    http://www.batteries-shop.nethttp://www.batterieslaptop.nethttp://www.uk-laptopbattery.comhttp://www.wt-batteries.comhttp://www.cheapteastore.comhttp://www.laptopbatterystore.co.ukhttp://www.batterieslaptop.nethttp://www.batteries-supply.comhttp://www.powertoolsbatteries.co.ukhttp://www.batterygrip.orghttp://www.uk-batteries.co.ukhttp://www.ukbatterystore.co.ukhttp://www.topbatteries.co.ukhttp://www.batteries-shop.net/acer-laptop-battery-c-2.htmlhttp://www.batteries-shop.net/apple-laptop-battery-c-3.htmlhttp://www.batteries-shop.net/asus-laptop-battery-c-4.htmlhttp://www.batteries-shop.net/compaq-laptop-battery-c-5.htmlhttp://www.batteries-shop.net/dell-laptop-battery-c-6.htmlhttp://www.batteries-shop.net/fujitsu-laptop-battery-c-7.htmlhttp://www.batteries-shop.net/gateway-laptop-battery-c-8.htmlhttp://www.batteries-shop.net/hp-laptop-battery-c-9.htmlhttp://www.batteries-shop.net/hp-compaq-laptop-battery-c-10.htmlhttp://www.batteries-shop.net/ibm-laptop-battery-c-11.htmlhttp://www.batteries-shop.net/lenovo-laptop-battery-c-12.htmlhttp://www.batteries-shop.net/lg-laptop-battery-c-13.htmlhttp://www.batteries-shop.net/panasonic-laptop-battery-c-14.htmlhttp://www.batteries-shop.net/samsung-laptop-battery-c-15.htmlhttp://www.batteries-shop.net/sony-laptop-battery-c-16.htmlhttp://www.batteries-shop.net/toshiba-laptop-battery-c-17.htmlhttp://www.uk-laptopbattery.com/acer-laptop-battery-c-2.htmlhttp://www.uk-laptopbattery.com/apple-laptop-battery-c-3.htmlhttp://www.uk-laptopbattery.com/asus-laptop-battery-c-4.htmlhttp://www.uk-laptopbattery.com/compaq-laptop-battery-c-5.htmlhttp://www.uk-laptopbattery.com/dell-laptop-battery-c-6.htmlhttp://www.uk-laptopbattery.com/fujitsu-laptop-battery-c-7.htmlhttp://www.uk-laptopbattery.com/gateway-laptop-battery-c-8.htmlhttp://www.uk-laptopbattery.com/hp-laptop-battery-c-9.htmlhttp://www.uk-laptopbattery.com/hp-compaq-laptop-battery-c-10.htmlhttp://www.uk-laptopbattery.com/ibm-laptop-battery-c-11.htmlhttp://www.uk-laptopbattery.com/lenovo-laptop-battery-c-12.htmlhttp://www.uk-laptopbattery.com/lg-laptop-battery-c-13.htmlhttp://www.uk-laptopbattery.com/panasonic-laptop-battery-c-14.htmlhttp://www.uk-laptopbattery.com/samsung-laptop-battery-c-15.htmlhttp://www.uk-laptopbattery.com/sony-laptop-battery-c-16.htmlhttp://www.uk-laptopbattery.com/toshiba-laptop-battery-c-17.htmlhttp://www.topbatteries.co.uk/acer-laptop-battery-c-1.htmlhttp://www.topbatteries.co.uk/hp-laptop-batteries-c-2.htmlhttp://www.topbatteries.co.uk/sony-laptop-battery-c-3.htmlhttp://www.topbatteries.co.uk/dell-laptop-battery-c-4.htmlhttp://www.topbatteries.co.uk/ibm-laptop-battery-c-5.htmlhttp://www.topbatteries.co.uk/compaq-laptop-battery-c-6.htmlhttp://www.topbatteries.co.uk/toshiba-laptop-battery-c-7.htmlhttp://www.topbatteries.co.uk/fujitsu-laptop-battery-c-8.htmlhttp://www.topbatteries.co.uk/apple-laptop-battery-c-9.htmlhttp://www.topbatteries.co.uk/samsung-laptop-battery-c-10.htmlhttp://www.cheapteastore.com/chinese-black-tea-c-3.htmlhttp://www.cheapteastore.com/chinese-herbal-tea-c-7.htmlhttp://www.cheapteastore.com/chinese-oolong-tea-c-2.htmlhttp://www.cheapteastore.com/chinese-puerh-tea-c-4.htmlhttp://www.cheapteastore.com/chinese-white-tea-c-5.htmlhttp://www.cheapteastore.com/chinese-yellow-tea-c-6.htmlhttp://www.cheapteastore.com/tie-guan-yin-c-8.html

  3. dfasdf says:

    http://www.batteries-shop.nethttp://www.batterieslaptop.nethttp://www.uk-laptopbattery.comhttp://www.wt-batteries.comhttp://www.replicawatchesstore.nethttp://www.cheapteastore.comhttp://www.laptopbatterystore.co.ukhttp://www.batterieslaptop.nethttp://www.batteries-supply.comhttp://www.powertoolsbatteries.co.ukhttp://www.batterygrip.orghttp://www.uk-batteries.co.ukhttp://www.ukbatterystore.co.ukhttp://www.topbatteries.co.ukhttp://www.batteries-shop.net/acer-laptop-battery-c-2.htmlhttp://www.batteries-shop.net/apple-laptop-battery-c-3.htmlhttp://www.batteries-shop.net/asus-laptop-battery-c-4.htmlhttp://www.batteries-shop.net/compaq-laptop-battery-c-5.htmlhttp://www.batteries-shop.net/dell-laptop-battery-c-6.htmlhttp://www.batteries-shop.net/fujitsu-laptop-battery-c-7.htmlhttp://www.batteries-shop.net/gateway-laptop-battery-c-8.htmlhttp://www.batteries-shop.net/hp-laptop-battery-c-9.htmlhttp://www.batteries-shop.net/hp-compaq-laptop-battery-c-10.htmlhttp://www.batteries-shop.net/ibm-laptop-battery-c-11.htmlhttp://www.batteries-shop.net/lenovo-laptop-battery-c-12.htmlhttp://www.batteries-shop.net/lg-laptop-battery-c-13.htmlhttp://www.batteries-shop.net/panasonic-laptop-battery-c-14.htmlhttp://www.batteries-shop.net/samsung-laptop-battery-c-15.htmlhttp://www.batteries-shop.net/sony-laptop-battery-c-16.htmlhttp://www.batteries-shop.net/toshiba-laptop-battery-c-17.htmlhttp://www.uk-laptopbattery.com/acer-laptop-battery-c-2.htmlhttp://www.uk-laptopbattery.com/apple-laptop-battery-c-3.htmlhttp://www.uk-laptopbattery.com/asus-laptop-battery-c-4.htmlhttp://www.uk-laptopbattery.com/compaq-laptop-battery-c-5.htmlhttp://www.uk-laptopbattery.com/dell-laptop-battery-c-6.htmlhttp://www.uk-laptopbattery.com/fujitsu-laptop-battery-c-7.htmlhttp://www.uk-laptopbattery.com/gateway-laptop-battery-c-8.htmlhttp://www.uk-laptopbattery.com/hp-laptop-battery-c-9.htmlhttp://www.uk-laptopbattery.com/hp-compaq-laptop-battery-c-10.htmlhttp://www.uk-laptopbattery.com/ibm-laptop-battery-c-11.htmlhttp://www.uk-laptopbattery.com/lenovo-laptop-battery-c-12.htmlhttp://www.uk-laptopbattery.com/lg-laptop-battery-c-13.htmlhttp://www.uk-laptopbattery.com/panasonic-laptop-battery-c-14.htmlhttp://www.uk-laptopbattery.com/samsung-laptop-battery-c-15.htmlhttp://www.uk-laptopbattery.com/sony-laptop-battery-c-16.htmlhttp://www.uk-laptopbattery.com/toshiba-laptop-battery-c-17.htmlhttp://www.topbatteries.co.uk/acer-laptop-battery-c-1.htmlhttp://www.topbatteries.co.uk/hp-laptop-batteries-c-2.htmlhttp://www.topbatteries.co.uk/sony-laptop-battery-c-3.htmlhttp://www.topbatteries.co.uk/dell-laptop-battery-c-4.htmlhttp://www.topbatteries.co.uk/ibm-laptop-battery-c-5.htmlhttp://www.topbatteries.co.uk/compaq-laptop-battery-c-6.htmlhttp://www.topbatteries.co.uk/toshiba-laptop-battery-c-7.htmlhttp://www.topbatteries.co.uk/fujitsu-laptop-battery-c-8.htmlhttp://www.topbatteries.co.uk/apple-laptop-battery-c-9.htmlhttp://www.topbatteries.co.uk/samsung-laptop-battery-c-10.htmlhttp://www.cheapteastore.com/chinese-black-tea-c-3.htmlhttp://www.cheapteastore.com/chinese-herbal-tea-c-7.htmlhttp://www.cheapteastore.com/chinese-oolong-tea-c-2.htmlhttp://www.cheapteastore.com/chinese-puerh-tea-c-4.htmlhttp://www.cheapteastore.com/chinese-white-tea-c-5.htmlhttp://www.cheapteastore.com/chinese-yellow-tea-c-6.htmlhttp://www.cheapteastore.com/tie-guan-yin-c-8.html

  4. Sam Kane says:

    Here are this and some other articles on SSAS Performance:

    http://ssas-wiki.com/w/Articles#Performance

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s