I have a 64-bit Windows 2003 server with 48 CPU cores and 128GB of RAM running a single application (SQL 2008 Analysis Services). SSAS is currently using about 50GB of memory.

SSAS relies to a large extent on the Windows File Cache to hold frequently used data (see this article, for example). On my server, the windows file cache is normally in the 10-15GB range, but occasionally it will suddenly drop to 5-6GB (Memory\System Cache Resident Bytes). When this occurs, all the SSAS page reads have to go to disk, and queries start timing out until the file cache repopulates.

I have a second server (only 24 cores, but otherwise near-identical) that doesn't exhibit the same symptoms, even running the exact same SSAS instance, the same queries, and the same load (this is a load-balanced environment).

I've asked a detailed SSAS-focused question on dba.stackexchange.com, but a few questions about the Windows behavior:

  1. Is there a way to know why the SSAS database file is getting flushed out of the cache?

  2. Can I pre-populate the file cache or actively manage it in some way? (I'm not sure that using a RAMDRIVE is an option in our environment)

At one point, we did change the network optimization setting on the server to "Maximize data throughput for file sharing" to match the other box, but that hasn't seemed to make a significant difference.

Edit: Added bounty. If we can't answer "why", then perhaps just a way to better understand what processes are currently using the cache, or what files are in it, or something that might lead us in the right direction.


I don't usually just post a link but since you haven't gotten any help yet, please check this out. It is an extremely detailed article about how the cache manager works.

A process runs every second to evaluate what can be written to disk and flushed from the cache. You asked specifically if there is a way to see what processes are using the cache or what files are in it. My understanding is that all disk reads and writes are cached so all processes that read or write to the disk will "use" the cache. I haven't found any way of peeking in to see what the contents are though.

File data in the system file cache is written to the disk at intervals determined by the operating system, and the memory previously used by that file data is freed—this is referred to as flushing the cache. The policy of delaying the writing of the data to the file and holding it in the cache until the cache is flushed is called lazy writing, and it is triggered by the cache manager at a determinate time interval. The time at which a block of file data is flushed is partially based on the amount of time it has been stored in the cache and the amount of time since the data was last accessed in a read operation. This ensures that file data that is frequently read will stay accessible in the system file cache for the maximum amount of time. details

In Server 2003, if the Cache Manager does not try to write modified file data back to a file, and free memory becomes scarce, the memory manager's modified writer thread writes the unwritten data back to a file. The system does not rely on the memory manager to flush file data back to the disk. Instead, the Cache Manager tries to write the data back to nonvolatile storage in a timely manner by using the "lazy writing" process. As programs modify file data, the Cache Manager keeps track of how much data is modified, or "dirty." The Cache Manager writes back one-eighth of the cache's modified data to disk every second. details

  • Thanks, still not positive this is what's happening, but I think this is at least pointing me in the right direction, to better understand the file cache – BradC Dec 15 '11 at 18:11
  • I'll keep looking for a tool to allow you to actually view the memory. I'm thinking a forensic memory analysis tool might be the best place to start but will require you knowing where to look... If I find something more, I'll update this thread. – Paul Ackerman Dec 15 '11 at 20:05

What is the value for the LimitSystemFileCacheSizeMB setting?

Analysis Services Operations Guide

An interesting utility that will provide you with detailed information about where the server memory is in use is RAMMap. In particular, there may also be memory in use on the StandBy Cache that you are unaware of.

  • LimitSystemFileCacheSizeMB is set to 0, but unless the Memory\System Cache Resident Bytes isn't really accurate, I don't think the file cache is growing out of control and impacting other processes – BradC Dec 15 '11 at 17:26

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.