I appreciate conditional joins aren't the best. Apparently it's a legacy of buying a new system that bears no resemblance to the original system, and then trying to run them...

here's the select sqlplan

I've checked with changing the maxdop to 1 for the existing script

original (maxdop 1) 666433 (subrow cost)

original (maxdop 12) 688366

@table 98225

So the @table still appears to be...

Thinking myself, one difference of course is that @tables don't allow parallelism, whereas #tables do

As the whole issue appears to be an issue of SQL getting confused with many parallelised...

have two plans. Sanitized (good little product that!)

Obviously the names should speak for themselves.

Many thanks

I can. Only problem with that of course is it's completely unsanitized. Even the table/column names can give away a lot of information.

pete

In case anyone's still interested (!) I've pretty much worked out what the issue was; after fully dissecting the code (as I say - there's a lot; 65 steps, with...

funnily enough I've been thinking along the same lines, but the joins are so complex, with the tables at the bottom of the script joining four or five others further...

Good idea - I'll set up an Xevent today - see if it can tell me something.

Course, bound to be speedy tonight.

pete

I've written a few scripts - tempdb is obviously being hit quite heavily, with a massive IO spike across all 8 datafiles at that particular point. The top queries at...

I have measured for Allocation, and there are (what I'd class as) a lot of pagelatch_ups.

There are issues elsewhere; it's a single job, with 65 different steps. As I say,...

sorry - my bad. Meant there was no extended jobs. I'll have a read on Extended events (I've come from a SQL 2005 environment, so still not 100% au fait...

Oh really? Damn

There are no extended events - I've written a nice Gantt chart of all processes throughout the night, and there's never any overlap

Could be server side issues; funnily...

Thanks all

To be honest, I've no idea at the moment why sometimes it takes so long and uses so much Tempdb.

Most of the time the step takes 20 mins,...

And the process - it's a single step in the massive job - that takes it up to 90GB would prefer it this way, over all in one lot.

I'm guessing...

No space constraints. DB about 50gb free, and the log about 40gb. I monitor the log size every ten minutes and there was no issue. No issue on tempdb either....