Partition Caching

  • ktflash

    Ten Centuries

    Points: 1093

    Ahoi,

    from what an external consultant once told me, the cube cache is cleared once a partition is processed. This results in queries executed from these measures/partitions being slower. This is problematic since we daily process the partitions of the current year, which leads to the cache being cleared every morning.

    This is what i could also observe and confirm, if a report was just run and than rerun immediatly after it is obviously running faster since the results has already been cached. My users also reported: over the day, times are going down since more and more data has been requested and is in cache already.

    When i try to find stuff about caching in SSAS I stumble across Proactive Caching, which sounds exactly what i am looking for, but from what i am reading it is something totally different from what i am expecting. I do not care about changes being made in my relational database in the background.

    https://docs.microsoft.com/en-us/analysis-services/multidimensional-models-olap-logical-cube-objects/partitions-proactive-caching

     

    What i am looking for is an option to somehow pre cache certain partitions after they have been freshly processed, so that once a user requests data from it, the cache is not totally empty and it must all be taken from "scratch".

     

    EDIT:

    Something like this seems to exist for SSRS

    https://docs.microsoft.com/de-de/sql/reporting-services/report-server/preload-the-cache-report-manager?view=sql-server-ver15

    I also found something in this slideshare mentioning cube aggregations, which i should take a look into?

    https://de.slideshare.net/LuisGoldster/how-analysis-services-caching-works-61715847

    • This topic was modified 1 week, 3 days ago by  ktflash.

    I wanna be the very best
    Like no one ever was

  • Martin Schoombee

    SSCoach

    Points: 19026

    Aggregations will help with performance and is definitely something worth looking at. You're right, proactive caching isn't really what you want to do here...you just want to warm the cache to improve query performance.

    Assuming that you know what the most common queries are (and if you don't you should capture that in the logs), I'd recommend executing a few of those queries in a scheduled job and as part of your cube refresh process. This will warm the cache and give you the additional performance boost you're looking for.

  • ktflash

    Ten Centuries

    Points: 1093

    Martin Schoombee wrote:

    Aggregations will help with performance and is definitely something worth looking at. You're right, proactive caching isn't really what you want to do here...you just want to warm the cache to improve query performance.

    Assuming that you know what the most common queries are (and if you don't you should capture that in the logs), I'd recommend executing a few of those queries in a scheduled job and as part of your cube refresh process. This will warm the cache and give you the additional performance boost you're looking for.

     

    Ok, i am going to do 2 things:

    • Asks users to run reports of slow reports get MDX Queries
    • Run a Trace on a Day and Save it to a table

    The question is though, is there a way to do this scheduled and automated?

    Run Trace in a timespan and save the results into a table?

     

    I wanna be the very best
    Like no one ever was

  • Martin Schoombee

    SSCoach

    Points: 19026

    Scheduled I'm not sure about, but you can use the SSAS flight recorder to collect query execution info either to a table or log file. Look at the instance properties to set it up.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply