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:

SELECT MONTH(RDM.[DATE]) as 'PartitionID',
FROM   [RAW].[DataMine] RDM
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)

  • High tempdb usage could indicate a lot of sorts. Do you normally disable indexes before the insert? – JNK Dec 1 '11 at 21:05
  • Run a trace on profiler and capture the query plan. That might give you a clue as to what it's getting up to. – ConcernedOfTunbridgeWells Dec 1 '11 at 21:09
  • Are you certain you need granularity of a millisecond? That seems like complete overkill to me. – datagod Dec 1 '11 at 21:25
  • Can you post the execution plans for the good and time performing times? I'll take the bad if that is all you have. – mrdenny Dec 1 '11 at 21:41
  • How many records does the subquery return? The IN clause can be inefficient for large numbers of values. – Jared Beck Dec 14 '11 at 21:59

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).


What are your indexes? Are they also partitioned? There are alot of unknowns here, but here are what I see as room for improvement:

An INSERT..SELECT can easily be split up and tuned in 2 parts. The read (SELECT) and the write (INSERT).

Starting off with the read: Your fact table is partitioned, yet you are not using partition elimination in the not exists query. Work that PartitionID into the where clause of the not exists subquery and you are bound to see an improved plan on 13 billion records.

The write: Is there any concern for concurrency on the fact table when you are doing the insert? If so, can you move the insert to an off peak time? Additionally, setting lock escalation to auto can allow partition level (HOBT) locking, allowing a minimally logged bulk insert to 1 partition while the rest are free to be read. I've used this in the DW with much success. If no concurrency concerns, look into what you can do to minimize logging (simple or bulk recovery, trace flag 610 if not a heap, and throw a table lock on the insert). Logging is a common bottleneck. Data can be lazy written but the log can't. Logging is great for OLTP, but this is a fact table. Fully logging fact table inserts = slow.

Additionally, correct use of an ssis data flow task can allow you to obtain a bulk update lock, which is better than an exclusive (which you'd get from adding a table lock on your current query) because not only are the reads multithreaded but, so are the writes.


The performance drop is probably becase in the 1 day of data, the database engine may be doing a sort 1000* on 1000 pices of data, but the month is sorting 1000* for 1000 pices of data/day * 30 days (where 1000 * 1000 << 1000 * 1000 * 30 ). It may be faster to run the query 30 times for 30 days of data rather than once for 30 days of data beacuse the amount of data that the query has to loop through is less.

As others have explaned, you need to analyse your query and try to

1> reduce the amount of data the query looks at by excluding as much as possible

2> Choose indexes so that the data that the query has to search through is optimised. Maybe change the idex type (if the engine has different types)

3> change the order that the joins take place, may change the exection order that the planner chosses.

4> maybe load some of the "where in (" data into a temp table as a seperate query before the main query or create a string if there arnt may values and dynamically create the query (so rather than "... where in (select .. " use "... where in ( 1,2,3...)").

As others have pointed out look at the query plan and see which parts take the longest time - they will be probably be joins and scans (DISTINCT clause) and they to optimise these by making sure there are indexes to assist and maybe restructuring the data.

You should have an index on [FACT].[DataMine].PartitionID, but it may make a difference to drop that before the insert and then re-create it, as sometimes this is faster. Possibly try a bulk insert as well. You would need to load the results of this query into a temp table first though, otherwise the sub queies would have no index on this field when they are selecting, which would make the situation worse.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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