Blog Post

Introduction to Using Profiler with SSAS 2008

,

There are many ways to optimize your analysis services cubes, but the real trouble is how do you know where you’re making progress and where you’re not? Well, how would you do this in your T-SQL environment ? You’d check out a profiler trace while you were running the queries and see what you could improve. We can do the same thing in Analysis Services. See Below for some instructions and insights:

Figure 1

clip_image001

Figure 1 Shows us connecting to an Analysis Services Instance in SQL Profiler

Figure 2

clip_image002

Figure 2 shows us configuring a standard trace file using the standard template (we will customize this of course, because no one uses the standard :) )

Figure 3

clip_image003

clip_image004

Figure 3 shows us selecting only the most important events for basic troubleshooting. This is where Profiler proves it’s worth. The Query SubCube Verbose event and the get data from cache and aggregation events are showing you if the data is being retrieved from cache (i.e. your queries are using efficient scoping) or if they are being read directly from the partitions underneath. This could mean improper aggregations, etc..

Query SubCube Verbose is the full data about the query (similar to an XML Showplan in SQL Server, it is delivering the full query and the timings based on the profiler trace)

This trace is of me processing my cube, I’m looking for certain events that tend to run longer than others . Including building indexes, etc.. So far so good!

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating