Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a monthly ETL process that's too complicated to explain here, but basically we get a large amount of customer data, bulk load it into various tables, then run a series of build processes.

The volume of total data is large, we add 25-30m records monthly to the largest table, which has around 700m records total. There are support tables from 2m-300m records each as well. Total data for this set is around 3.5TB.

This month, some processes are taking exponentially longer than usual. One example is a proc that ordinarily completes in 30 minutes took around 40 hours before I killed it (it still hadn't completed).

Not all processes are affected, and some run even faster than usual.

Obviously the root cause is beyond the scope of something I can ask here, but there is something that bothers me:

We have actual query plans showing in the millions of % for individual operators.

Like a clustered index scan for 791,358,704%, a merge join for 75,566,494%, etc. This occurs in multiple query plans, and these are plans taken from sys.dm_exec_query_plan.

Are these query plans indicative of some other larger issue?

I know with a certainty the problem is not out of date stats - we ran fullscans on all the major tables, and the biggest table and DB I manually rebuilt with pre-sizing to eliminate fragmentation.

share|improve this question
up vote 4 down vote accepted


You meant something like this?

This is an estimated execution plan that's generated based on the statistics by the client tools and note that its NOT an engine issue. If you want MSFT to fix this issue then please vote for this connect item item.

Since you mentioned SQL Server 2008, I would encourage you to look at the WAIT STATS information when the job is being run. Look at why the execution is NOT moving along and figure out the bottleneck.

Also, look at the sys.virtualfilestats information and see if there are significant IO lags and as well as memory usage.


share|improve this answer
Perfect, thanks for the info and links! – JNK Jun 17 '11 at 15:28

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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