SSIS and multiple data flows

Just yesterday someone asked:
"If you have a large data set in SSIS 2005 to process. Which way is faster to process the dataset in one data flow or if you can segregate the data flow in to 2 or 3 branches?"

What you are basically talking about is multi-threading.

SQL 2008 does a much better job of multi-threading than 2005. You really need to read this so that you understand what 2005 is really doing when you “think” you are spinning off multiple threads.

If you can bulk insert into an empty table for fast load then you might be better off taking this route with one data flow. The gains there are tremendous. Make sure you understand what fast load means and the requirements before you check the box and assume you will be doing fast loads.

In short there are possibilities that can speed up your load. Having plenty of CPU, IO, and memory (basically no hardware bottlenecks) is essential to see any performance gains with multi-threading.

Comments

SQL Server ETL for Data Lineage

What is data lineage? Ok let us suppose you built a wonderful database with loads of data coming from source files from your vendors, your own AS400, and different departments of your own organization. All of this data has been imported into 200 tables in this database producing a plethera of information that is used for reporting purposes. One day a department head comes to you and says, "I think this number here on this report is wrong. Where did you get it from?" Do you escort that person to the computer room and show him/her your server? No, I didn't think so. So how do you come up with where the information came from? Likely you find a data load expert in your IT department and have him/her spend the next several minutes/hours/days rummaging through stored procedures, ssis packages, dts packages, custom applications, etc trying to find this information. However, if you had this: you could simply pull up the history of how that data came to be loaded and point. How us...

Why fragmentation occurs and how to avoid/fix it.

Let's suppose you have this table: We have made last name the primary key in this table Now lets import some rows into the table and check the fragmentation Notice the fragmentation at 96.48% and all we did was one import of 16426 records. So now we have several questions: How bad is this? Fragmentation causes SQL Server to skip around to read data from your tables. For a one time read on a small table this is virtually meaningless. In a job that may need to read millions of records, this could tear apart your performance. Ok this is bad. How did it happen? The primary key on a table is clustered. This means the data will be stored according to the primary key. In our case, last name. When data doesn't come in the same way you store it (i.e. Customer names are not coming in alphabetically, but rather randomly) SQL Server must constantly split pages to store the data correctly. This causes data to be "fragmented" into multiple areas of the disk instead of one continuou...

SSRS Chart Scaling issue

I had a tough problem to figure out yesterday and no amount of hunting on the internet revealed a solution so here it is: The problem was that the range chart I was using do display data had a time bar on the x axis. The line on the chart did not line up with the time on the x axis. Example below. Looking at Italy notice a tooltip of 20:07:47 but the x axis is lined up almost exactly on 21:00:00. The solution turned out to be on how the chart series is setup. Both the Vertical and Horizontal axis were set to primary. Changing the Vertical axis to secondary solved the problem. Notice now how Italy lines up correctly now. I hope this helps you!

SQL Server Cruiser

Search This Blog