I have a process that loads about 500,000 records on a daily basis and normally takes somewhere in the neighborhood of 5-10 minutes to complete. First I use a SSIS Package to load the data into a staging table, and then call a Stored Procedure that determines which weekly table the data belongs in, and executes dynamic SQL to move the data from the stage table to the final table.

It's working pretty well except that the Monday load (Sunday's data) is taking around 10 hours to run, instead of the normal 10 minutes. The only difference I see is that when it is copying data from the stage table, Monday's load will be adding the first rows to the week's partition.

I have one clustered index on the table, and data from the staging table should be in the same order. There is also a Non-Clustered index on the ID field that is added as part of the insert.

Does anyone have any suggestions for things that I can look at? It makes no sense to me, why an empty table would be 60 times slower...