Analysis Services and the System File Cache

Earlier this week Greg Galloway sent me an email about some new code he’d added to the Analysis Services Stored Procedure Project to clear the Windows system file cache:

I thought this was quite interesting: several times when I’ve been doing performance tuning I’ve noticed that the same query, running on a cold Analysis Services cache, runs much slower when the cube has just been processed. This I put down to some form of caching happening at the OS level or below that; I also vaguely knew that it was a good idea to limit the system file cache, having seen the following on Jesse Orosz’s blog:!E322FD91218E57CF!295.entry

Anyway, doing some more research on this subject I came across the following blog entry that discusses the problem of excessive caching in more detail:
…and announces a new tool called the Microsoft Windows Dynamic Cache Service that aims to provide a better way of managing the system file cache:

Has anyone got any experience with this? From what I can see, installing the Dynamic Cache Service on a 64-bit SSAS box with a big cube on looks like a good idea – has anyone tried it? If you have, or are willing to, can you let me know how you get on? Comments are welcome…

7 responses

  1. I ran into an issue with the file cache growing to the point where it was consuming most of the available memory to the point where AS ran out of memory itself and crashed. What made this issue particularly hard to detect was that AS did not report that it had run out of memory, it simply dumped its memory and then carried on but with much lower throughput. The only way to resolve the issue was to restart the service.In order to stop this happening I implemented cache trimming and limited AS to 5Gb (it was a 32Gb RAM system). This had the beneficial effect on: a) stopping the cache growing out of control b) improved overall performance as AS was not wasting too much time looking through the cache.File Cache is limited using <LimitSystemFileCacheSizeMB> in the AS config file and it requires that the service account has the appropriate security policy of \’Adjust Memory for Process\’.David

  2. Recently had a cachine issue with analysis services and Microsoft gave the following feedback when i asked about this new service."The SSAS implemention via “LimitSystemFileCacheSizeMB” property uses the same API functions “GetSystemFileCacheSize” and “SetSystemFileCacheSize” for file cache triming.Thus I can’t see any benefit in introducing a file cache management utility when the same functionality is already part of SSAS."

  3. Pingback: SSAS queries time out when Windows File System Cache drops | Q&A System

  4. Pingback: Build a Performance monitoring tool for SSAS using Power BI – Shabnam Watson's Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: