If you do not configure the cache size to sort all of the data in memory, a warning appears in the session log stating that the Data Integration Service made multiple passes on the source data. The Data Integration Service makes multiple passes on the data when it has to page information to disk to complete the sort. The message specifies the amount of memory required for a single pass, which is when the Data Integration Service reads the data once and performs the sort in memory without paging to disk. To optimize mapping performance, configure the cache size so that the Data Integration Service makes one pass on the data.

If the amount of incoming data is greater than the Sorter cache size, the Data Integration Service temporarily stores data in the Sorter transformation work directory. The Data Integration Service requires disk space of at least twice the amount of incoming data when storing data in the work directory.

For best performance, configure the Sorter cache size with a value less than or equal to the amount of available physical memory on the machine that runs the mapping. To sort data using a Sorter transformation, allocate at least 16 MB (16,777,216 bytes) of physical memory. Sorter cache size is set to Auto by default.