Home > Analysis Services, Performance, Processing > The anatomy of a process update – Part 2

The anatomy of a process update – Part 2

February 21, 2013 Leave a comment Go to comments

In part 1 of this 2 part post I shared an overview of the procedure to optimise process update. I will now walk through the optimisation process on one of our multi terabyte systems.

Part 1 shows a visualisation of process update and i have captured timings in profiler for all the stages shown and I now present my starting point for tuning below.

image

We have 3 stages that can be optimised.

  • Dimension build – This comes in at 92 seconds which is acceptable so i won’t be discussing this further but you can refer to part 1 for tuning options.
  • Partition index processing – Almost 25 minutes is spent here so tuning is required.
  • Commits – Almost 7 agonising minutes are spent waiting for the phase 2 commit with the server lock in place.

Tuning the partition index build

As shown in the process diagram, the unit of parallelism is Measure Group and as such we need to reduce the runtime of the measure groups so i drilled down and started investigating. Below is the most startling discovery I found as i drilled down.

Measure Group Size GB Processing (s) Index size gb
Group A 950,000 1463 90
Group B 200,500 1450 90

Up until this particular moment I had always believed that index size was directly proportional to the size of the fact data but the data i collected above showed otherwise so i dug further.

Below is data collected from an individual partition from each measure group and “distinct attrib a” is the number of unique members present for the attribute with the largest index.

Measure Group Num Rows Distinct Attrib A Data Size (mb) Index Size (mb)
Group A 123,643,449 315169 8800 710
Group B 14,764,905 348921 207 202

The interesting data here is that group B indexes are almost the size as the actual data where as group A indexes are <10% of data.

At this point i reached for my favourite SSAS internals book SQL Server 2008 Analysis Services Unleashed for an answer. The book did not disappoint and yielded the answer, internally SSAS breaks fact data files into segments and index files contain an index entry for each attribute member in a segment at a cost of 4 bytes per member. Group B has an exceptionally high number of attribute members per segment compared to Group A which is why we see such a huge disparity in size. To quote the book.

Analysis services builds a bitmap index for each attribute member represented in a segment. Because each segment has up to 65,536 records, it cannot have bitmap indexes for more than 64K members of the attribute. In the worst-case scenario, each attribute in the segment is used in one page and therefore has a 4-byte bitmask (2 bytes for the block map and 2 bytes for the page map). This means that the index of that segment of members of a single attribute would contain 262,144 bytes (65,536 members * 4 bytes for the bitmask). This is, however, the worst-case scenario. Generally speaking, the index is much smaller.

Once i understood how the indexes were built i realised that i could reduce their size and subsequently improve performance. In this case, 95% of the index size belongs to a few attribute indexes from one dimension. By introducing a sort in the source by just the key attribute of the dimension containing the largest indexes the number of distinct attribute members per segment will drop significantly.

Introducing the sorting gave me a 95% reduction in index size and a ~10% increase in fact size for group B whilst group A yielded a 99% reduction in index sizes and a 1% increase in fact size!

Armed with this knowledge we worked through the rest of our large measure groups introducing the sorts and reprocessing our data. this yielded the following results.

      • Partition index processing time was reduced from ~25 mins to ~5 mins.
      • SSAS indexes have been reduced by 435gb! Down from 560gb to 125gb.
      • We have typically seen a ~10% increase in partition processing on larger partitions due to reduced index sizes.
        Process update now looks like this

image

So, before i move on to the next section there are a few points i have not covered.

We were able to introduce the sort because all of our partitions reference a view and no view would currently ever return > 2bn rows. To achieve the sort we introduced a SELECT TOP 2147483647 and an ORDER BY <Attributeid> into the views and we took into account the following considerations

·An ordered select will be slower than one that is not ordered.

oIn our case SSAS is the bottleneck because it can’t consume the data as fast as SQL can throw it out. This continues to be the case.

oIn some cases we now see SQL using a parallel plan.

oWe had more than enough CPU power to handle this and no significant increase was measured

oWe currently control the DOP of all SSAS queries with the resource governor and If necessary we can easily adjust.

·Memory impact of SORTS

oWe observed some queries taking more memory but we have the capacity to handle the demands.

oDuring normally processing we don’t spill to tempdb.

oWhen under heavy memory pressure SQL will spill the sorts to TEMPDB and enough space is required to handle this situation.

Tuning the commits

This phase has always been the thorn in our side and the server level lock that is held during the phase 2 commit is a killer as nobody can connect to the server during this time.

You will notice from the previous section that although partition index process time reduced there was no impact on the commit phase which is expected as its a single threaded metadata operation.

With the commit at 395 seconds with have around 250,000 metadata objects that have to be worked through so on a regular basis we merge our partitions as this reduces the number of indexes and therefore the number of metadata objects. Once we do the the merge our process update now looks like this.

image

The merging brought us down to around 125,000 metadata objects and as you can see almost halved our server lock!

Its still way to long for us but we have to balance the management overhead and the potential performance impact of being too aggressive with our merging. We have looked at turning off attribute indexes that are not in use and unfortunately there are no more we can turn off.

The only other option to reduce the commit time is a move to SSD which we know will solve the problem but as i have said previously I feel that a SSAS design change is required to make the process multi threaded.

Conclusion

I have walked you through my performance tuning process where I reduced the process update run time from ~39 mins to ~11 mins, massively reduced index sizes and delivered an overall increase in partition processing as well!

Please remember that most cubes behave differently and the results you may get from sorting may be very different so as usual testing is vital! I hope you found this post useful.

Advertisements
  1. Szymon Wantuch
    February 22, 2013 at 12:21

    Hi.
    I’ve been waiting for this post as I have exactly the problems with processupdate performance. I have a question: how did you came up with the data and index size statistics? Especially for attributes. Do you have some tool that generates this automatically? The mere thought of going manually through more than 1000 partitions to collect them is a bit terryfying.

    • February 22, 2013 at 13:10

      Hi,

      The size statistics was a pain but i used Treesize Pro to get what i needed (simply because i had a copy to hand). I have no doubt that i could have got the necessary data dump through a WMI or powershell query of some sort though.

      One other “tool” i used for a birds eye view of what was going on was my storage cube that i developed some time ago that captures cube storage stats and allows me to browse through the SSAS database to the partition level amongst other things… Its something i may blog in the future.

  2. Jesse O
    March 3, 2013 at 01:28

    Great post…however one thing is missing: the rebuilding of flexible aggregations.

    To me that’s the largest overhead in a process update of a dimension.

  3. March 3, 2013 at 10:49

    Hi Jesse,

    Flexible agregations are deleted during a process update if they are invalidated and not rebuilt. The rebuild is done when you issue a process index after the process update so i’ve not included the flexible aggregation rebuild as its not part of the process update.

    However, since the process index rebuilds indexes and flexible aggs you might find that if its possible to make your indexes smaller that performance improves and much of what i have covered should be relevant to tuning process index.

    Andrew

  4. CI
    March 8, 2013 at 18:52

    Trying to wrap my head around this…

    Where did you introduce the sort? The fact or dimensional view?

    • March 8, 2013 at 22:33

      Hi,

      In the fact. Any other questions just shout.

      A

  5. JC
    July 10, 2015 at 09:01

    Hi, we also face the same problem as you describe. Few questions, did the sort cause any performance problems when processing the measure group? Also with regards to putting the sort on the fact data, I have about 5index map files that are the same size, so would you just randomly pick one?

    • July 10, 2015 at 10:13

      Hi,

      Your relational database will take a hit. In our case we are using SQL Server and the sorting on huge fact tables did hit tempdb so we did increased tempdb to accomodate. If your doing the entire measure group then depending on your max connections in your data source your even more likely to hit tempdb so you need to size it accordingly.

      What to pick is down to your data, i started with the key attribute and that happened to be the one we stuck with. When picking the attribute(s) its all about get the data to group together to maximise compression and yes you might do better sorting by more than one.

      Andrew

  1. February 21, 2013 at 23:40

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: