none
MDX Query performance "Query subcube Verbose" duration is longer after a big dimension is added

    Question

  • Hi,

    we've added a big dimension (about 2G, 90-100 million rows) (it is actually a fact dimension) and a new measure group (based on the same table) into the cube to fine tune some MDX performance which contains certain measure, yes queries have that measure performance is much better now.

    but I've seen some queries which do not really use the new measure group or the new dimension become slower (e.g. one of them 1.5 minutes to 2 minutes).

    when I checked sql profiler "Query subcube Verbose" (EventSubclass is 22-Non-cache data), I found the duration for the event changed from 720 or so to 4500 or so. and there are 4 other events having similar increase doubled the time).

    Should I accept the fact that it is natural the query will be slower even though it does not have the new dimension or measure group in the query, or should I start digging something fishy here?

    Thank you.



    -- Let us help each other to continuously improve.

    Friday, January 11, 2013 7:38 AM

Answers

  • Hi,

    Are you doing proper testing and not comparing apples to oranges? Cube query performance heavily depends on the cache - was the 720 response also on cold cache?

    There are functions (distinct()) that will scan all measure groups trying to calculate NonEmpty().

    It's hard to answer based on this little info, but I would first make sure that you have a proper test sample, and starting points. Test both on cold cache, warm cache. Lots of info is available in the performance guide.

    Regards,

    Hrvoje Piasevoli



    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli

    Wednesday, January 16, 2013 2:43 AM

All replies

  • Hi Minnie,

    For the large dimension, you can refer to Jesse's tips in the blog below:
    Tips: Processing Large Dimensions: http://jesseorosz.wordpress.com/2008/09/25/tips-processing-large-dimensions/

    SQL Server Analysis Services query performace caused by many reasons. In this case, you concern about the query performance. I recommand you refer to the following articles:
    Analysis Services Query Performance Top 10 Best Practices: http://technet.microsoft.com/en-us/library/cc966527.aspx
    Analysis Services 2008 R2 Performance Guide: http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/10/10/analysis-services-2008-r2-performance-guide.aspx

    Hope this can help you to tune the performance issue.

    Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, January 15, 2013 6:53 AM
  • Hi,

    Are you doing proper testing and not comparing apples to oranges? Cube query performance heavily depends on the cache - was the 720 response also on cold cache?

    There are functions (distinct()) that will scan all measure groups trying to calculate NonEmpty().

    It's hard to answer based on this little info, but I would first make sure that you have a proper test sample, and starting points. Test both on cold cache, warm cache. Lots of info is available in the performance guide.

    Regards,

    Hrvoje Piasevoli



    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli

    Wednesday, January 16, 2013 2:43 AM
  • Hi Elvis Long,

    thanks for the response, yes we are trying hard to follow the best practise guide mentioned.

    Hi HrvojePiasevoli,

    Yes, you are right. I was not comparing it fairly. one version had 2 months less data, hence faster.

    All tests are cleared cache and established session.

    We also discovered after pre-fetch (unexpected partitions scan) also slowed down some individual queries after adding more partition, which we could not really control much as setting up "Disable Prefetch Facts=True, Cache ratio=1" made some queries slower and some faster (and http://mdxdax.blogspot.com.au/2011/11/avoid-pitfalls-of-fact-data-prefetching.html was referenced too).

    Regards,

    Min


    -- Let us help each other to continuously improve.


    Wednesday, January 16, 2013 7:29 AM