Set BLOBTempStoragePath and BufferTempStoragePath to Fast Drives

  • Article

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

Comments

  • Anonymous
    May 07, 2007
    Runying Mao and Len Wyatt did a great study of SSIS performance, and posted some results: Getting Optimal

  • Anonymous
    September 21, 2012
    Thx! Works perfectly.

  • Anonymous
    January 04, 2016
    Great - helped in getting the issue sorted