You configure the cache size to specify the amount of memory allocated to process a transformation. The amount of memory you configure depends on how much memory cache and disk cache you want to use. If you configure the cache size and it is not enough to process the transformation in memory, the Integration Service processes some of the transformation in memory and pages information to cache files to process the rest of the transformation. Each time the Integration Service pages to a cache file, performance slows.
You can examine the performance details of a session to determine when the Integration Service pages to a cache file. The
_writetodisk counters for any Aggregator, Rank, or Joiner transformation indicate the number of times the Integration Service pages to disk to process the transformation.
If the session contains a transformation that uses a cache and you run the session on a machine with sufficient memory, increase the cache sizes to process the transformation in memory.