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)