SQL Server Integration Services (SSIS) provides a performance object called "SSIS Pipeline".
Here's a bit of info about what's in there.
There are 3 kinds of memory buffers that can be monitored. They are called:
- "Buffers"
- "Private Buffers"
- "Flat Buffers"
A "Buffer" is what the data-flow engine uses to store the data as it flows through.
A "Private Buffer" is owned by a component and is used by that component to store data it needs to do its job. Private buffers have column structure just like regular buffers. e.g. The Sort component has a private buffer to store the data it will pass back out to the data-flow because this is different to the data that is passed in. This is a structured buffer hence a private buffer.
A "Flat buffer" is a chunk of memory available to a component to do with as it pleases. It is accessed byte-by-byte and does not have a defined column structure so the component can use it any wy it likes. e.g. The Lookup component uses a flat buffer to store lookup data hash tables if it has been configured to do so (that's another blog post)
In practice you should be monitoring the "Buffers In Use" perfmon counter as the best indicator of how much memory is being used by your data-flow.
Here is where I got most of this information from.
Thanks to Matt David and Michael Entin of Microsoft for this info.
-Jamie