SSAS Cache

tinytimothy2 — Mon, 04 Mar 2024 14:41:01 GMT

Say i have 10 large models all built in SSAS (no other models)

1 model i decide to make has no refreshes on for a week.

Say i have bookmarks in PBI that dictates what is ran (no one will do extra filtering or adhoc)

Say I go through 10 book marks. Each takes 10 seconds to display results initially.

When i go back through result page return immediately.

Shouldn't we expect that for each user for each day the data would return immediately UNTIL there is new refresh?

I am not seeing that as the case. I thought i would be all in cache.

I assume something must happen that evicts it from cache

What tools do we have to see why it isn't in cache?

I guess what i am looking for is a tool that can show me what causes the eviction.

There must be other processes from other models that are happening that are affecting this.

Ideas?

Re: SSAS Cache

Anonymous — Tue, 05 Mar 2024 01:22:44 GMT

Hi tinytimothy2 ,

Analysis Services indeed employs caching to enhance query performance. When you execute a query, it tries to retrieve results from the cache first before hitting the underlying data source.

To understand why data isn’t in cache, consider the following tools:

SQL Server Management Studio (SSMS): Use SSMS to execute the XMLA ClearCache command against specific levels (database, cube, dimension, table, or measure group).
Monitoring Tools: Monitor system performance and resource usage to identify any processes affecting cache eviction.
Profiler Traces: Set up profiler traces to capture query execution details and analyze cache behavior.
Query Execution Plans: Examine query plans to identify bottlenecks or unexpected behavior.
Event Logs: Check for any warnings or errors related to cache eviction.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Re: SSAS Cache in Report Server

SSAS Cache

Re: SSAS Cache