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:
http://asstoredprocedures.codeplex.com/Wiki/View.aspx?title=FileSystemCache
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:
http://jesseorosz.spaces.live.com/blog/cns!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:
http://blogs.msdn.com/ntdebugging/archive/2009/02/06/microsoft-windows-dynamic-cache-service.aspx
…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:
http://www.microsoft.com/downloads/details.aspx?FamilyID=e24ade0a-5efe-43c8-b9c3-5d0ecb2f39af&displaylang=en
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…
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
hello!
Interesting info
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."
Chris, I’ve done a bit more investigation on the system file cache as it relates to Analysis Services and blogged about my discoveries. The biggest discovery is the standby cache:
http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=19
There’s also a new ASSP release that’s able to clear the standby system file cache in addition to the active system file cache.