Set BLOBTempStoragePath and BufferTempStoragePath to Fast Drives
BLOBTempStoragePath and BufferTempStoragePath are two properties on Data Flow Task. They define where on the hard disk(s) to page buffer data to. BLOBs (text, next, image) are written to BLOBTempStoragePath. All other buffer data will be swapped to BufferTempStoragePath. See the figure below for where these two properties are exposed in designer.
Now what could cause a buffer to swap? There are two possible causes. The first one is when a memory allocation fails. The second one is when Windows signals the low memory resource notification event. Both will trigger SSIS to reduce its working set. SSIS does so by moving buffer data to disk.
When BLOBTempStoragePath is not set, the paths as defined by the system variables TEMP and TMP will be used. The same rule applies to BufferTempStoragePath.
For better performance, we recommend both BLOBTempStoragePath and BufferTempStoragePath point to fast drives. We also suggest that the drives for BufferTempStoragePath and BlobTempStoragePath be on separate spindles in order to maximize I/O throughput.
- Runying Mao
