Loading a FACT table per day I have a database with 13 billion rows, per day I have around 20-30 mio rows. On top of this I have one cube, one of its dimensions is DateTime that goes down to milliseconds. To load the fact table I use the following Query as T-SQL Task within SSIS:

INSERT INTO [FACT].[DataMine]
SELECT MONTH(RDM.[DATE]) as 'PartitionID',
       DateTime_Key,
       Price,
       Amount,
FROM   [RAW].[DataMine] RDM
INNER JOIN [DIM].[DateTime] DDT
ON RDM.DateTime_Key = DDT.DateTime_Key
WHERE DM.Date BETWEEN '2011-11-28' AND '2011-11-28' AND
      DateTime_Key NOT IN (SELECT DISTINCT DateTime_Key
                           FROM [FACT].[DataMine] DM
                           INNER JOIN [DIM].[DateTime] DT
                           DM.DateTime_Key = DT.DateTime_Key
                           WHERE [DATE] BETWEEN '2011-11-28' AND '2011-11-28')


PartitionID is used because I partition the FACT table by Month. I have to be able to run the load over a certain date range and should not worry about double rows, therefore it looks first if the rows are already loaded or not. 

From the performance this runs not bad, I need around 7-8 minutes for one day of data, but suddenly this goes up like a rocket and then takes > 1 hour for one day of data. What puzzles me is the fact that the load time doesn't go up gradually. Looking at the sql server i see that it is busy in the temp database and I see quiet some disk i/o (eventthough the sql server has around 140 GB RAM still free for him to grab).

Index are all up todate, no fragmentation, statistics are also looking good.

What am I missing to understand where this sudden performance drop comes from ? 

Machine is:
(SQL 2008 R2 64bit / 8 cores / 192 GB RAM / SAN Disks / 10GbE)
The execution plan is likely to be changing.

Grab a copy of a fast plan and a slow plan and compare.

By using a plan guide you may be able to force the query to use the one plan for all occasions (after testing of course).