I have a database where I load files into a staging table, from this staging table i have 1-2 joins to resolve some foreign keys and then insert this rows into the final table (which has one partition per month). I have around 3.4 billion rows for three months of data.

What is the fastest way to get these rows from staging into the final table ? SSIS Data Flow Task (that uses a view as source and has fast load active) or an Insert INTO SELECT .... command ? I tried the Data Flow Task and can get around 1 billion rows in around 5 hours (8 cores / 192 GB RAM on the server) which feels very slow to me.

  • 1
    Are the partitions on separate filegroups (and are on those filegroups on different physical disks)? Commented Feb 19, 2014 at 14:44
  • 3
    A really good resource The Data Loading Performance Guide. This addresses a lot of performance optimization you can do e.g Enabling TF610, Using BCP OUT/IN, SSIS etc. You just have to follow the recommendations and test it out in your environment.
    – Kin Shah
    Commented Feb 19, 2014 at 15:17
  • @Aaron yes, per month one filegroup, 12 san lun's attached so all jan go on one lun etc. Not sure how many disks per lun but should be plenty.
    – nojetlag
    Commented Feb 19, 2014 at 16:30
  • Yeah I really meant "sets of disks" and probably could have mentioned controllers too, which can get saturated. Commented Feb 19, 2014 at 16:32
  • @Kin had a look at the guide but it seems outdated, "The SQL Server destination is the fastest way to bulk load data from an Integration Services data flow to SQL Server. This destination supports all the bulk load options of SQL Server – except ROWS_PER_BATCH." and in SSIS 2012 they recommend the OLE DB destination for better performance.
    – nojetlag
    Commented Feb 19, 2014 at 16:34

2 Answers 2


One common approach:

  1. Disable / drop indexes / constraints on target table.
  2. INSERT dbo.[Target] WITH (TABLOCKX) SELECT ...
  3. With credit to JNK of course, you can do the above in batches of n rows, which can reduce the strain on the transaction log, and of course means that if some batch fails, you only have to-start from that batch. I blogged about this (while in reference to deletes, the same basic concepts apply) here: http://www.sqlperformance.com/2013/03/io-subsystem/chunk-deletes
  4. Re-enable / re-create indexes / constraints on target table (and perhaps you can defer some of those, if they are not necessary for all operations, and it is more important to get the base data online quickly).

If your partitions are physical and not just logical, you may gain some time by having different processes populate different partitions simultaneously (of course this means you can't use TABLOCK/TABLOCKX). This assumes that the source is also suitable for multiple processes selecting without overlapping / locking etc., and making that side of the operation even slower (hint: create a clustered index on the source that suits the partitioning scheme on the destination).

You may also consider something a lot more primitive, like BCP OUT / BCP IN.

I don't know that I would jump to SSIS to help with this. There are probably some efficiencies there, but I don't know that the effort justifies the savings.

  • 3
    Do not blindly drop indexes (esp. clustered index) if your data is not sorted. Dropping the index and expecting to recreate a clustered index can be a huge mistake because it can cost both huge disk space plus huge amount of time. I am not the first one to experience such mistake. Look at the "Plan B" description in this article sqlmag.com/t-sql/…. The author had the same issue.
    – jyao
    Commented Apr 11, 2016 at 23:06

Looking at your problem from an SSIS perspective I feel the reason this may have taken so long is that you didn't have batching on. This can lead to too many rows filling the SSIS pipeline and can hinder your SSIS performance as a result. What you need to do is alter your rows per batch setting and possibly your maximum insert commit size. Now what you set this too will depend on the amount of memory available to your SSIS server? What the disk speed of your SQL Server instance is? The best way to do this is test. Lets for example use 10,000. This will send a batch to the server 10,000 at time thus keeping your pipeline from overfilling and will help run this process faster. These settings are set in your OLEDB destination.

OLEDB Destination

If it is an issue you can also add an execute SQL task before and after to do as @AaronBertrand suggests and remove/re add any indexes or constraints to the table.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.