Biggest question is why there is so much difference between the estimated rows and the actual rows for temp tables

Mac 2014-05-29 10:43:32

This query does pull in a ton of data and then aggregates many of the fields together.

We've had to comment out a fourth of the aggregation because it takes so long for it to run. It used to run for 1 hr to 1 hr 10 minutes. After commenting out the one part in both stored procedures (there's about 3 being used here) and rebuilding some indexes and updating some statistics, we've been able to bring the time down to 30 minutes. And we run this for a day at a time, so 1 day will take anywhere from 30 minutes to 40 minutes to run.

I've thought about making a filtered index based on what we're grouping by or at least making an index based on the main field being used, but where we seem to have the biggest disconnect of estimated rows and actual rows is when we insert them into the table where they will be housed for the rest of time.

And the most IO intensive portion is when these rows get inserted, they also get inserted into the index. That part takes a toll and is the most expensive portion of this query.

Dan Holmes 2014-05-29 11:21:42
Can you be more specific? "This query" doesn't help much when there are 30+ in the plan. Which statement in this plan are you asking about?
Mac 2014-05-29 11:59:01
Sorry about that, Statement 7 and Statement 35 are the bigger concerns.
Ranga 2014-05-29 18:51:45
just shooting in the dark….can the indexes be dropped before the insert and rebuilt after insert ?
Mac 2014-05-29 19:34:31
We tried something like that. It doubled the time it took to execute.
Aaron Bertrand 2014-05-31 17:01:10
Quite difficult to make any analysis or recommendations here with such a large number of statements and complex individual plans, but without the query text or the ability to understand the different objects (due to anonymization).