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.
