Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

My Quick Guide to SSAS Query Tuning Part One

Posted by thomasivarssonmalmo on January 28, 2012

The intention of this blog post is to write a practical introduction to SSAS Query tuning with the SQL Server Profiler tool. I think that SQL Server profiler can be very usefu but it also have its issues with delivering too much information and in a very unclear way. I hope that this post can be helpful and show how Profiler interacts with the SSAS engine.

The first step is to build a modified Adventure Works cube that only use basic measures from the fact table, no MDX scripts(except for the default calculate() and with no measure expressions. With measure expressions you will get more events, in Profiler for a single query, than you need to start learning this tool. I am using SSAS 2008 R2 SP1 and the Adventure Works DW database and cube project available on www.codeplex.com.

I have two measure groups in thiis model.

TheMeasureGroups

The following dimensions are used. They have no attribute relations yet, only flat connections with the dimension key in each dimension. You can use as many as you like. I have included more dimensions than I need for this blog post but I will refer back to this model in later posts.

TheDimensions

Finally, these are the relations between the dimension and measure groups. It is exactly as in the standard Adventure Works model but without the measure expressions.

The relations

There are no partitions in the measure groups and no aggregations in the measure groups. We will add that in a later post and see the impact of that. I will improve this model step-by-step.

I assume that starting SQL Server Profiler is not an issue here. I have a saved template that I reuse for query tuning. The focus in this blog post are some of the following events.

The Profiler Trace Events

Now it is time to start the fun. I assume that you have the SSAS service up and running and that you have started SQL Server Management Studio and connected to SSAS. Otherwise you will se a lot of file load events that I have described in two previous blog posts. Start the profiler just before running each query and after each query stop it and clear the events. What you will see here, at best in this blog post, is storage engine events and nothing in the formula engine since this model do not include MDX scripts yet.

I will start wih this query:

Select {} on 0
From [Adventure Works DW2008R2 Performance];

Here is a subset of the events that you will see in Profiler.

FirstQueryNull

There are no Query Subcube events so no activity has been going on in the storage engine. The query was sent to the server and a result was returned(Serialize Results Current).Resource usage shows zero on all counters. Thanks to Chris Webb research about these counters here

The second query will use a basic measure group measure on the columns axis and nothing more.

Select {[Measures].[Order Quantity]} on 0
from [Adventure Works DW2008R2 Performance]

.

Query2aPartitions

The is one reading from the measure group and partition containing the [Order Quantity] measure.

A new event occurs that was not shown in the first query. The event subclass numbers 2 and 22(query subcube and query subcube verbose means that data was not retrived from the storage engine cache.

Query 2 Query Subcube Verbose

If you look at the Query Subcube Verbose event in the more detailed window that appears if you mark that event you will see this.

Query 2 Subcube Verbose

We did only send a query that requested a measure from the cube but no dimensions, like you can see above. On each dimension you have a line of zeroes meaning that no specific dimension member was asked for in the query. Profiler only shows the dimensions related to the measure in the measure group. The line of zeroes is a mathematical model called vectors and that is how SSAS currently shows its internals.

This is not the main point here but the resource usage counters are the following for this query explained from Chris Webbs blog post above.

Query 2 Resource Usage

The reads tells you about the number of disk read operations for the query and the reads in kb tells you about the physical amount of data that was retrieved.

The number of rows scanned are the number of rows decoded/filtered/aggregated by the storage engine.The number of rows returned are the number of resulting rows from the same query. I will not go into detail about these counters.

The next query will retrieve the same measure as before but add a new measure from the second measure group.

Select {[Measures].[Order Quantity],[Measures].[Order Quantity – Fact Reseller Sales]} on 0
from [Adventure Works DW2008R2 Performance];

For the same measure that we asked for in the pervious query we will get this event in Profiler.

Fråga3Cache

The data above is retrived from the storage engine measure group cache. In the query subcube and subcube verbose you will see both event subclass 1 and 21 showing that the data was requested from the measure group cache.

The second measure goes through the same process as the measure in the previous query.

Query 3 second Measure

The data is not cached since it has never been retrieved before.

Both Query subcube verbose events still show the same zero for all dimensions. This is the query subcube verbose event for the second measure.

Query 3 second measure query subcube verbose

If you like you can run the third query a second time to confirm that both measures will be retrieved from the storage engine cache.

Finally it is time to see what happens in profiler when you run queries for dimension members. I will only use a date dimension to show this.

This is the first query.

Select {[Order Date].[Calendar Year].&[2010]} on 0
From [Adventure Works DW2008R2 Performance];

This is the clear message from the SSAs engine that you have requested a dimension member in the Query Subcube event.

Query Subcube query 4 query subcube

Can you see the 1 appearing at the end?

Query subcube verbose is a little bit more generous.

Query4 verbose

There is a number 2 on the order date dimension. This is the internal SSAS DataId of that member.

Finally if you run this query with many dimension members:

Select {[Order Date].[Calendar Year].&[2005],[Order Date].[Calendar Year].&[2006],[Order Date].[Calendar Year].&[2007]} on 0
From [Adventure Works DW2008R2 Performance];

Query 4 Many dimension members

Can you see the star * on dimension 4? This means that all members was requested from that attribute. 

If you run this query with two dimension members:

Select {[Order Date].[Calendar Year].&[2005],[Order Date].[Calendar Year].&[2006]} on 0
From [Adventure Works DW2008R2 Performance];

Query4 two members

Here you will see the plus sign + . This means that more than one member was requested.

I hope that this first part of the query tuning can explain the storage engine retrieval of data in Profiler and how it describes the retrieval of dimension members.

Advertisements

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

 
%d bloggers like this: