SSIS Performance
Phenomenon
If you were looking carefully when SSIS was executing, you may find the data volume of data flow processing increase by about 1W records per time(definitely it is not always); also if you were capturing information using Profiler when SSIS was executing, you may found there are more than one bulk insert even you use Fast Load; if you ever set Error Redirection for OLE DB Destination(without transaction required!), you may found there are more records missed in the destination table when just one row goes wrong. That's why? The 'Culprit' is Buffer and its configuration.
Explore
Buffer is the most important concept and structure in SSIS, and also from my own understanding it is the foundation of SSIS, so why? From SSIS point of view, Buffer is the place where SSIS use to store the data pulled from source, then do transformation base on it and then load data from them into the destination; from physical point of view, it is area of memory which data flow task must use even the data flow just contains the simplest Souece -> Destination processing.
As you may know, SSIS has two different engine: Run-time Engine and Data Flow Engine. The former one is responsible to coordinate the executing of executable; the latter is used to do the data processing which is triggered by the specified task – Data Flow. So we can say, Data Flow is the one which do the real data pulling, data transformation, data processing and data loading.
Let's take a look at the example in real world. Just like the following we need to move the goods from one place to another place by the truck. What we need to do are:
in the previous emample, the employee is the Data Flow Engine who is responsible for the whole process of moving goods from source to destination; the truck is the Buffer using which the employee store the goods in process of the moving, as truck has its capacity, buffer also has this concept named Buffer Size which is a very important configuration in SSIS.
[Note] Only Data Flow has this option and also the setting of one Data Flow will not affect the setting of another Data Flow.
Now let's shift to the SSIS world, I have a very simple data flow flow, get data(about 6W records) from [Internet Sales] table and load them into a target table. Screenshot is as following:
In the right side, I show the default configuration for this Data Flow Buffer. I bet you get it, why sometimes data volume increase by 1W records per times
.
DefaultBufferMaxRows means how many rows we want in the buffer at the same time;
DefaultBufferSize means the buffer volume which has minimum value 64KB and maxmum value 100M.
Then let run and capture the information using Profiler, there are 7 INSERT BULK commands and about 1W records per batch as expect:
[Note] Actually how many rows are host in the buffer at the same time is not the same with what we set in the DefaultBufferMaxRows, its calculation rule are:
Then what's the impact on SSIS performance? Simply say, we should use as large buffer volume as possile and less buffer counts where possible, but also should consider the RAM limitation and Transaction Log issue(if require transaction). For exmaple, if SSIS run together with other application in the same server and buffer size is too large, SSIS maybe need to do a lot of swap in and out which reduce the performance.
Fortunatelly we can monitor how SSIS Pipeline use resources by PerfMon tool, SSIS install a set of performance counters:
Rows read – number of rows read from all the data sources in total.
Rows written – number of rows written to all the data destination in total.
Private buffers in use (A private buffer is created by a transform (not by the pipeline) and used as a workspace to assist with the processing of transformation use for temporary work only. For example, the Aggregation transformation uses private
buffers) – number of private transformation buffers in use throughout the pipeline.
Flat buffers in use (A flat buffer is a special type of private buffer used when a transform needs a chunk of memory, but doesn’t need it divided into rowscolumns. It is used as “chunk” memory so that transforms can still have their memory
tracked and managed by the buffer manager. An example is the Aggregate Transform which might use this to allocate space for a hash table.) – number of flat buffers in use throughout the pipeline.
Buffers spooled – if SSIS runs out of physical memory during package execution, or the process executing the package runs out of virtual memory, SSIS begins to spool buffers to files. It has an initial value of 0. When it goes above 0, it indicates
that the engine has started memory swapping. Once this occurs it indicates that the computer has insufficient memory, performance will degrade significantly. It is good practice to monitor this setting and ensure that enough memory for operations is available.
Buffers in use – number of pipeline buffers in use throughout the pipeline.