SSIS: Understanding BufferTempStoragePath

Recently while teaching the SSIS Master’s class for Pragmatic Works, one of my students asked about the buffertempstoragepath property in the data flow and how it could be used to assist in performance tuning. Since there doesn’t seem to be many articles on this property, I thought a good article on it and what it does might be helpful for some others out there who haven’t used it before.

The buffertempstoragepath property signals to SSIS where it should store buffers on disk if a memory allocation fails. This statement tells us right away that in theory, this is a property that should never ever need to be used. One of the things that makes SSIS so quick is its ability to cache data in memory and move it from place to place. This property is allocating locations on disk that can store these buffers temporarily if SSIS runs out of memory. We can watch SSIS memory usage by monitoring the BuffersInUse and Buffers Spooled counters in perfmon, which are available under the SQL Server: SSIS Pipeline XX.X(your version number here) grouping.

The buffers in use perfmon property is the number of buffers that SSIS is currently using. The Buffers spooled counter is how many buffers SSIS couldn’t process due to memory issues and spooled it to disk. How does this tie into the buffertempstoragepath property? You can think of it as when buffers spooled > 1, then the buffertempstoragepath property tells ssis where to put it. If you do run into a scenario where you may encounter a memory issue (i.e. your application has millions of rows to move and the box you’re working with is a machine with 8 GB RAM), specifying this property can help you, a little. If you do need to specify this property, then make sure you set it to a drive that has REALLY REALLY fast storage (the fastest you can get in {on?} the {USS} Enterprise). Solid states are ideal, but if that’s not possible, get the fastest you can.

There is an excellent article that Arshad Ali wrote on SSIS buffers here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s