Alberto Ferrari : SSIS

SSIS: living in a parallel world? Not yet...

AlbertoFerrari — Sun, 14 Sep 2008 19:57:00 GMT

Today I come into a nice bug (misfeature?) of SSIS 2008 and I'd like to share it with you. Let's start from the beginning:

One of the keys to make something speedy is to parallelize it. In TableDifference I read from two inputs, compare them and produce several outputs for new, deleted, updated rows and so on. It is the classical "produce/consumer" paradigm that works fine in parallel, creating a consumer thread that compares rows and different threads to produce them.

During the porting of the component in SQL 2008 I discovered that my code does not work as expected. It reads all the lines, then terminates abnormally without producing a single row of output. It took me a couple of hours and several coffes to discover the change in the way SSIS handles asyncronous components causing my bug.

In order to make the problem easier to understand (for microsoft connect too), I produced the same behaviour with a much simpler script component. Imagine to have a script transformation that needs to make some very complex work on each row. You will end up with something like this:

    public override void Input0_ProcessInputRow(Input0Buffer Row) {
        Output0Buffer.AddRow();
        Output0Buffer.LineID = VeryComplexFunction(Row);
        if (Row.EndOfRowset ()) {
           OutputBuffer.SetEndOfRowset ();
        }
    }

But, if you have a 64 CPU computer. You can imagine to detach many threads, each one handling just one very complex function, in this way you will produce a massive parallel computation. Wow! The solution to get maximum speed would be this:

    public override void Input0_ProcessInputRow(Input0Buffer Row) {
        Thread T = new Thread(SendAsyncRow);
        T.Start(Row.EndOfRowset());
    }

    public void SendAsyncRow(Object O) {
        Output0Buffer.AddRow();
        Output0Buffer.LineID = VeryComplexFunction();
        if ((bool)O == true) {
            Output0Buffer.SetEndOfRowset();
        }
    }

You detach a separate thread for each row, the thread will compute the very complex function and send it to the output buffer as soon as it is ready. In order to make SSIS understand when everything is finished, you signal SetEndOfRowset in the last thread.

Don't start crying, this code will never work, the SetEndOfRowset is called on the last read line and not on the last produced one, but this is just pseudocode to understand the topic, don't blame me for inconsistencies. :) Let's go on with the main topic.

Under SSIS 2005 this code (in VB, of course) would work fine as the engine detects the end of processing when it receives the EndOfRowset on the outputs. In SSIS 2008 the engine detects the end of the processing as soon as the last call to ProcessInputRow has finished. So, in our case, after the last thread has been detached SSIS believes that the processing is finished and calls the cleaning up of the component, completely ignoring both the presence of separate threads running and our williness to not terminate the component because we know that we need some more time.

The result? As with any other parallel bug you will sometime get a crash, on the same inputs sometime finish corectly, sometime get an error... the nightmare of any programmer.

If you want to try it by yourself, cut and paste this code to a transformation script in a data flow that process some lines ( rows 10/20 lines are enough):

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Threading;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent {

    bool useThreads = false;

    /// <summary>
    /// 
    /// Processes input rows
    /// 
    /// </summary>
    /// <param name="Row"></param>
    public override void Input0_ProcessInputRow(Input0Buffer Row) {
        Thread T = new Thread(SendAsyncRow);

        if (useThreads) {
            T.Start(Row.EndOfRowset());
        } else {
            SendRow();
        }
    }

    /// <summary>
    /// 
    /// Sends a row in sync mode
    /// 
    /// </summary>
    private void SendRow() {
        // Thread.Sleep(100);
        Output0Buffer.AddRow();
        Output0Buffer.LineID = VeryComplexFunction();
    }

    /// <summary>
    /// 
    /// Sends a row in async mode
    /// 
    /// </summary>
    /// <param name="O"></param>
    public void SendAsyncRow(Object O) {
        Thread.Sleep(100);
        Output0Buffer.AddRow();
        Output0Buffer.LineID = VeryComplexFunction();
        if ((bool)O == true) {
            Output0Buffer.SetEndOfRowset();
        }
    }

    /// <summary>
    /// 
    /// Not very complex... but it's a demo!
    /// 
    /// </summary>
    /// <returns></returns>
    private int VeryComplexFunction() {
        return 10;
    }
}

using Serial mode (useThreads se to false) everything works fine. Using parallel mode (useThreads set to true) SSIS will crash and produce no output at all. The bug in TableDifference was very similar to that, I needed to handle the wait for termination of the consumer process during the ProcessInput function.

It is not the first time I discover inconsistencies in the way SSIS handles parallelism, in order to try to make it easier to write code (using 2008 mode there is no need to call SetEndOfRowset and your program will run fine if you forget it) the architects of SSIS introduce a behavior that will make better and correct code crash. If you believe this is a problem, vote here, anyway... be aware of it, it mighe be useful in the future. :)

Table Difference 2.0 - Call for beta

AlbertoFerrari — Mon, 17 Dec 2007 11:53:37 GMT

The new version 2.0 of SSIS SCD handling component TableDifference is in beta, I am searching for beta testers and some help in writing an installer for it. You will find all the informations at www.sqlbi.eu.

The final release of the component will be as usually freely available with sources as soon as it has been tested enough.

SSIS Multicast and Trash or Separate and Union?

AlbertoFerrari — Thu, 15 Nov 2007 12:15:22 GMT

Today I was optimizing a package that handles a very common topic: process some rows and send all them to a flow but, for some of them, make additional processing.

A simple Multicast and a Conditional Split solve the problem, the picture explains it better than my words:

Now, I was wondering why we should duplicate one million rows to two millions and then trash half of them away. I thought a better solution was to separate the rows before and duplicate only half a million, bringing them together again with the other half million from the other flow. So I tried this solution, thas does exactly the same but does not trash anything. We are not in the age of consumerism and we do not like to trash anything, don't we?

Well, even if I thought it would be a better solution, it is not. The first one (with one million rows of 1K each) takes approximately 13 seconds to run, the second one with the same data 16 seconds, that is 23% slower than before.

So, the conclusion is that SSIS still lives in the age of consumerism, the process of UNION ALL takes a very long time to process and makes trashing a better solution. There are good explanations for this (UNION ALL does a lot more work than Multicast does) but I did not think that a 23% degradation would occur.

The lesson is: it is best to trash then try to recycle, at least with data with SSIS. :)

Table Difference 1.2

AlbertoFerrari — Mon, 23 Jul 2007 08:16:29 GMT

TableDifference has been updated to version 1.2. For those who want to read the main description of the component can be found here but take a look at the full story, this component and the documentaton has been updated several times.

You will find complete documentation about the changes at www.sqlbi.eu and all the sources and binaries in the download section.

Have fun with your SCD handling, any comment will be very welcome.

Mantaining order in SSIS flow, problems with Merge

AlbertoFerrari — Tue, 10 Apr 2007 06:48:26 GMT

In a previous post I spoke about the advantages of having sorted flows in SSIS to greatly speed up data insertion using fastload. The need to have a sorted flow brings some severe problems to the SSIS programmer that he need to be aware of and that IMHO Microsoft should address with a future implementation of SQL Server.

Let’s have a look at the problem.

In the image you can see a very typical SSIS data flow where you need to manage the error flow of a lookup component and go on with the processing. Even if you can’t see it from the picture, think that Sample Source will produce a sorted output of several millions rows and we want to insert into TestTable with the same sorting.

This task works fine but it has a big problem: the Union All component will lose the sorting of the flow as it will handle data from both its input in an unordered way. This is not a bug, Union All has its behaviour by design. Still we have a problem and we know that sorting several millions rows after the Union All component is not an option for memory consumption.

In SSIS you have another component, Merge, that will kindly maintain the order of its inputs, so you can change your package this way:

Everything will work fine until you have a package that should handle several millions rows where only a very few of them (say 1.000) will enter the lookup error output flow.

In such a situation Merge will start caching ALL the rows from its first input (the row that correctly matched lookup) until something will come from its second input (rows coming from the Derived Column task). The problem is that SSIS will NOT call the ProcessInput method of the Derived Column task until its buffer reaches a certain amount of rows (normally 10.000 or a number like it) and this will not happen because only 1.000 rows will enter the Derived Column path. In such a situation Merge will start consuming memory and will fill up all the available memory really fast leading to crashes and/or very poor performances.

Even this behavior of both Merge and SSIS is “by design”, so we cannot complain it. But it could be easily solved setting the max number of rows of the buffer in the Derived column to 1 in order to call ProcessInput immediately (if I know what kind of data I will read I can easily set up values that make my SSIS package run faster). Unfortunately there is no such kind of option in SSIS so, at present, the problem cannot be solved.

Needless to say, even if this is a problem, you can try to make your sorted package run without any merge component and you will be able to maintain the sort of the flow but this is a really big limitation in expressivity of your ETL algorithm so awareness of the problem is mandatory to successfully complete your ETL process.

Making Fast Load really fast on clustered indexed tables with SSIS

AlbertoFerrari — Sun, 08 Apr 2007 06:48:26 GMT

I have been so used to use int identity fields as primary key for any table and to believe it’s the fastest way to define a clustered primary key that I never thought this can cause serious performance problems to SSIS. Until I tested it and discovered that int identity primary keys are among the slowest way to insert huge amount of data with SSIS. In the post I’ll describe the technique that – from my tests – is the fastest way to insert data into tables with clustered index using SSIS.

As a test case I used a very simple table with only two fields:

CREATE TABLE [dbo].[FastLoadTests](
      [Id] [int] Identity NOT NULL,
      [TestString] [varchar](2048) NOT NULL
) ON [PRIMARY]

I filled it with a very simple task that generates one million of monotonically increasing Id and TestStrings of exactly 512 bytes each, throwing them into an OleDb destination adapter that uses FastLoad to fill the table.

If the table has no index at all the package runs in 18.5 seconds, pretty nice. Now, I created an index on the table like this:

CREATE UNIQUE CLUSTERED INDEX [FastLoadTests_ClusteredIndex] ON [dbo].[FastLoadTests] ([Id] ASC) ON [PRIMARY]

And run the package once more. The execution time is now 1.08.5 (one minute and eight seconds). It is more or less 4 times slower. Clearly, as SQL has to sort one million rows, I was expecting poor performances. The first trial has been that of reducing the number of rows to sort.

Looking at the various options in the OleDb destination adapter it is easy to find that it has a parameter (Maximun Insert Commit Size) that defaults to 0.

Setting it to 10.000 reduced execution time to 44.5 seconds, a better result but still more than two times slower than the table with no clustered index. Is this the best result? Not at all! Let’s try something different.

Reading (carefully) the MS documentation about the OleDB destination adapter (http://msdn2.microsoft.com/en-us/library/ms141237.aspx) you’ll discover that you can gain performance if the input data is sorted accordingly to the clustered index on the table, specifying the ORDER option with the advanced editor.

After some trials I managed to set it with the advanced editor:

It has been surprising that, running the package, I got this error:

[Test Table [44]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Could not bulk load. The sorted column 'Id' is not valid. The ORDER hint is ignored.".

So, it seems that setting this option on an INT IDENTITTY field causes FastLoad to fail. Really surprising because it seems that SQL Server does not know that the keys generated by itself will be sorted! Anyway, removing the Identity setting from the table and using the package generated ID resulted in execution time of 46.23 seconds. No performance gain at all. Anyway as it seemed an interesting way to search into, I went more on trials.

The last and resolving trial test has been that of REMOVING the Maximum Insert Commit Size parameter from the destination adapter. Running the package again I got a result of 19.07 seconds execution time, more or less the same time as inserting the data with no clustered index at all. Got it! Now I have a really fast way to insert data.

The results are summarized in this table

	No Index 0 MICS	Index 10.000 MICS	Index 0 MICS
Int Identity key	18.85	41.15	1.08.57
SSIS generated key	18.59	44.54	1.10.03
SSIS key and ORDER option	18.85	46.23	19.07

*MICS = Maximum Insert Commit Size

You can easily see that using the ORDER option gives a dramatic speed improvement when you have a clustered index but ONLY if you are able to generate the sorting key in the SSIS package and DO NOT USE the MICS parameter.

I think that the documentation for this behavior should have been made far more accessible and that ORDER parameter is so important that should have been shown in the first page of the standard editor for OleDb destination, hope it will be done in the next release of Sql Server.

Moreover, as using MICS parameter leads to a dramatic performance degradation, I think that the OleDb destination adapter should issue a warning if both ORDER and MICS parameter are set so to make the programmer think twice before using them together.

Data Flow Components, constructors and SSIS

AlbertoFerrari — Mon, 31 Jul 2006 06:48:26 GMT

Yesterday I had to fix a simple still subtle bug in TableDifference, I think sharing the experience will be useful for everybody involved in custom component creation.

The component has some fields that are initialized in the component constructor, at the end of the work the component does not clear them as I would expect the object to be destroyed by the SSIS engine. Everything works fine until you run the component in a data flow task contained in a foreach loop then... bang! the component crash.

The problem is that the component was not fresh-built but contained properties with the same data that were there at the end of its first execution. It seems to me that SSIS does not destroy components after a data flow terminates but reuses them during the subsequent runs of the same data flow task recalling their pre-execute method. I solved the problem clearing variable values in the pre-execute method, the problem is solved but I don't like the way the SSIS engine works, I would really like an object to be destroyed and then recreated when the container data flow task finishes execution.

If confirmed (is anybody from Microsoft listening?) this behaviour is - in my opinion - very interesting but wrong. You can decide to use this behaviour to implement some sort of state management in a component but in the same time you are prone to very nasty bugs if you do not clear all your properties in the pre-execute method and then initialize them to a meaningful value.

I did not found useful documentation about this behaviour, has anybody done any kind of investigation about it?

Threads and custom components: FlowSync 1.0

AlbertoFerrari — Thu, 06 Jul 2006 06:48:26 GMT

Several people downloaded TableDifference to handle SCD faster, some of them, especially using it on huge table (more than 10 millions rows) noticed memory problems. The problem is that of a flow running too fast and making TableDifference cache data, we know of it and now we decided to solve it creating a new component called "FlowSync". You can find all the details and source code here.

In the article there is a brief discussion about how SSIS handles the ProcessInput method of a component with more than one input, here is an extract:

As you may already know ProcessInput is called once for every buffer and, in the case of a component with two or more inputs like TableDifference or Union All, this method is called once for each buffer of each input, so the inputs are mixed together and handled by the same method. A solution to the problem of syncronizing input, before deciding to develop FlowSync, has been that of using semaphores to stop the faster input inside the ProcessInput method. It would have been a nicer solution BUT ProcessInput is called in only ONE thread, even if it has two input flows. So, if ProcessInput is stopped then all the inputs of the components are stopped and the system will be in a deadlock state.

This is very strange because each flow runs in a separate thread but it seems that the two thread synchronize on a single one when they need to pass data to the component. So the solution has been that of inserting the sync technique where we still have separate threads, hence directly on the flows with a transformation component: FlowSync.

I would really like to see in the next version of SSIS the ability to decide if – when developing a component – we want ProcessInput to be called in a multithreaded environment or not, my personal opinion is that – using threads – programs become easier to write and maintain, TableDifference may be a good candidate to demonstrate this statement.