Partition Caching

Question

Partition Caching

ktflash

Default port

Points: 1400
More actions
December 3, 2019 at 9:50 am

#3703729
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 4 years, 11 months ago by ktflash.
I want to be the very best
Like no one ever was
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

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

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

Martin Schoombee SSCoach Points: 19128 More actions · Answer 1

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 Default port Points: 1400 More actions · Answer 2

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 want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Martin Schoombee SSCoach Points: 19128 More actions · Answer 3

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.