How to prevent SSAS to unload from RAM, how to speed up first request

Question

Hi,

I am fairly new to SSAS and SQL Server but Google didn't help. I am grateful for pointers to the right documentation.

I have setup a cube and I query it from Excel using pivot tables.

The very first update/request I run takes around 10 secs to run, however the following request complete in less then a second.

When I wait a while without querying (10 minutes or so) the first update takes 10 seconds again.

How can I prevent this behavior and speed up the first update?

I imagine, that SQL Server unloads the cube from RAM after a while. Is there a way to mark the cube as "keep in RAM".

Thanks,

Cilvic

Answer 1

The most likely reason that it gets removed from cache is SQL Server memory pressure. How much RAM does the server have?

If adding more memory is not an option, you could perhaps schedule a periodic (hourly say) cache warm-up: How to warm up the Analysis Services data cache using Create Cache statement?:

For example, ascmd.exe could be used every hour to execute all queries in a directory keeping the cache ready for subsequent user queries. The other approach, which has been used, is to create a cache query for each user query. This is feasible if the MDX query is part of a report, then one simply adds another query that has the side effect of populating the cache, thereby speeding up the next query.

Also: Build Your Own Analysis Services Cache-Warmer in Integration Services

There are several SSAS performance counters you can monitor:

• MSAS 2008:Memory Cleaner Current Price Current price of memory, $/byte/time, normalized to 1000.

• MSAS 2008:Memory Cleaner Balance/sec Rate of balance+shrink operations.

• MSAS 2008:Memory Cleaner Memory shrunk KB/sec Rate of shrinking, in KB/sec.

• MSAS 2008:Memory Cleaner Memory shrinkable KB Amount of memory, in KB, subject to purging by the background cleaner.

• MSAS 2008:Memory Cleaner Memory nonshrinkable KB Amount of memory, in KB, not subject to purging by the background cleaner.

• MSAS 2008:Memory Cleaner Memory KB Amount of memory, in KB, known to the background cleaner. (Cleaner memory shrinkable + Cleaner memory nonshrinkable.)