locked
aggregation design wizard is unstable? RRS feed

  • Question

  • hi all,

    i lauched the aggregation design wizard, on the Set Aggregation Options page, i chose the 'I Click Stop' option,

    and clicked Start, it ended up with a performance gain of 30% and 100 aggregations,

    i then clicked Reset and then Start, however, this time it ended up with a performance gain of 40% and 122 aggregations.

    curiously, i clicked Reset and then Start, and i found it always ended up with different results each run.

    is aggregation design wizard designed to behave in this way?

    seems the underlying aggregation design algorithm is NOT stable.

     

    any suggestions will be appreciated.

     

     

    Andrew


    Andrew
    BI, Data Mining, Analytical CRM
    Wednesday, November 17, 2010 1:50 AM

Answers

All replies

  • Hi Andrew,

    There are four options for aggregation design: ‘Estimated storage reaches’, ‘Performance gain reaches’, ‘I click stop’ and ‘Do not design aggregations’. Which one are you using?

     

    Hope this helps,

    Raymond

    Thursday, November 18, 2010 7:47 AM
  • Hi Raymond,

    thanks for ur response.

    I used the 'I Click Stop' option.

     

    thanks

     


    Andrew
    BI, Data Mining, Analytical CRM
    Thursday, November 18, 2010 9:37 AM
  • I used the 'I Click Stop' option.


    Then it can be explained now. If click Start and then click Stop immediately, then you will see zero… I mean the number of aggregation depends on the time between click and stop. I would suggest you read this article before buiding the aggregations:

    http://www.packtpub.com/article/query-performance-tuning-microsoft-analysis-services-part1?utm_source=sl_expertcube_abr4_0709&utm_medium=content&utm_campaign=sanjiv

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Friday, November 19, 2010 12:12 PM
  • Hi Raymond,

    it's very kind of u. thanks.

    steps to reproduce the issue:

    1. choose the 'I Click  Stop' option.

    2. press the 'Start' button. As the wizard designs aggregations, just watch its progress in the graph, do NOT click the 'STOP' button.

    3. wait until aggregation design is complete, and then write down the number of aggregations, optimization level and the amount of disk space consumed.

    4. click the 'Reset' button.

    5. goto step 2.

    after a few loops, u may notice that  each time, it always has a different outcome——here i mean the number of aggregations, optimization level and the amount of disk space consumed .

     

    Thanks


    Andrew
    BI, Data Mining, Analytical CRM
    Friday, November 19, 2010 2:51 PM
  • Hi Andrew,

    Yes, you may get the different value of disk space consumed. However, the results are very close, right? Generally, you cannot create every possible aggregation, and the Performance gain is not a stable value:

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/a0cbfbdb-d84b-41f7-a9a0-0da7bf7f7915

     

    I mean it make sense that you may get different value each time. And you can also test the aggregation design with a small partition, for example: the first partition of Internet Sales in sample database Adventure Works. And you will find each time you will get the same number of Aggregation, however, value of disk space consumed is still unstable, just like running same T-SQL statement but you will get different CPU time.

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Sunday, November 21, 2010 10:09 AM
  • Hi Raymond,

    when the partition is not small, even the numbers of generated ggregations can be quit different.

    so the aggregation wizard works in a somewhat random and heuristic way?

     

    thanks


    Andrew
    BI, Data Mining, Analytical CRM
    Wednesday, November 24, 2010 2:25 PM