If a session contains Aggregator, Rank, or Joiner transformations, examine each
_writetodisk counter. To view the session performance details while the session runs, right-click the session in the Workflow Monitor and choose Properties. Click the Properties tab in the details dialog box.
If these counters display any number other than zero, you can increase the cache sizes to improve session performance. The Integration Service uses the index cache to store group information and the data cache to store transformed data, which is typically larger. Therefore, although both the index cache and data cache sizes affect performance, you may need to increase the data cache size more than the index cache size. However, if the volume of data processed is greater than the memory available you can increase the index cache size to improve performance.
For example, the Integration Service uses 100 MB to store the index cache and 500 MB to store the data cache. With 200 randomly distributed accesses on each of the index and data caches, you can configure the cache in the following ways:
To optimize performance, allocate 100 MB to the index cache and 200 MB to the data cache. The Integration Service accesses 100 percent of the data from the index cache and 40 percent of the data from the data cache. The Integration Service always accesses the index cache, and does not access the data cache 120 times. Therefore, the percentage of data that gets accessed is 70 percent.
Allocate 50 MB to the index cache and 250 MB to the data cache. The Integration Service accesses 50 percent of the data from the index cache and 50 percent of the data from the data cache. The Integration Service does not access both index and data caches a 100 times each. Therefore, the percentage of data that gets accessed is 50 percent.
If the session performs incremental aggregation, the Integration Service reads historical aggregate data from the local disk during the session and writes to disk when saving historical data. As a result, the Aggregator_readtodisk and Aggregator_writetodisk counters display numbers besides zero.
However, since the Integration Service writes the historical data to a file at the end of the session, you can still evaluate the counters during the session. If the counters show numbers other than zero during the session run, you can tune the cache sizes to increase performance. However, there is a cost associated with allocating or deallocating memory, so refrain from increasing the cache sizes to accommodate more data volume if you know what volume of data the Integration Service will process.