2

I am using SSAS for reporting statistics on a big amount of data.

Recently I began learning best practices for tuning and I am interesting in how SSAS caches the data.

I have done my research and I've concluded that there are three types of cache that SSAS is using in order to avoid the direct disk IO hit.

  1. The Formula Engine Cache
  2. The Storage Engine Cache
  3. The Operating System Cache

So, the question is, when a cold query is taking place, will all the three levels store some data? If that is what is happening, is it possible that the same data is cached at different levels? If it is, then why is there such a high usage of cache, as this may consume almost entire RAM?

For example, is it possible that the FE Cache stores some of cellset or rowset data and at the same time the SE Cache stores some subparts of cellset or rowset data and also the OS Cache stores some data?

0

Yes, it happens that all 3 layers are caching the same data.

You shouldn't be worried about SSAS consuming all the memory, because it's there to be used. The SQL Server Database Engine does the same thing.

If SSAS comes under memory pressure, it manages its own caches automatically, removing entries from both front end and storage engine caches as needed. If you have some other services running on the same host competing for memory, then you might want to limit memory usage from SSAS by changing its memory properties settings.

The Operating System cache you refer to is the filesystem cache. Windows manages it automatically as well when it comes under memory pressure. In the task manager, most of the "Cached" figure is due to the filesystem cache. You can evict the file system cache using Sysinternals' RAMMap utility, with its Empty Standby List option.

Chris Webb wrote a couple of blog posts on doing SSAS cache warming, which you might find interesting. The posts are from 2009, but the details are still relevant for newer versions of SSAS multidimensional.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.