The indexes will reduce the probability of a deadlock happening (by a huge factor), because they will speed up transactions so there is less chance of a temporal overlap, but also possibly...

Also notice that the  plan for the (paralellised) slow update doesn't use the clustered index from the target table in performing the join. I've never heard of this, but could...

Sorry, forgot to ask for the plan for the non-parallelised (maxdop 0) version of the problem update, too.

OK so the staging table sorts it out - correct? That means it's not the performance of the joins alone, and it's not the UPDATE itself or its side-effects. It seems...

Yes that is basically it - there may be something about the complexity of the query or the repeated instances of the table that is contributing to perfomance problems. So...

DBCC INPUTBUFFER( <spid> ) tells you the last command issued.

Yes - try creating a table to hold teh output of the SELECT, then do the UPDATE from that table (the staging table). And yes, the other thing to try...

Have you tried without the clustered index on the target table? Tried selecting the records into a staging table before inserting? I think the status is set based on the...

Run the SELECT part only. Does that have the same performance problem? If not then perhaps the problem could be your clustered index rebuild. Or have you tried removing all...

This one will fetch a single field value at a time and sort out the row numbering in the process. So as long as none of your data...

--I don't know the full rules, but here's some simple SQL code

--which does at least some of it.

--It could be used in a where clause or case...

Your second query

is (in relevant respects) of the form:

--Title...

Try this - it's a bit like a dynamic SQL solution, but CONTAINSTABLE takes a string param so it's perhaps acceptable. It might give...

Bit less straightforward with PATINDEX() - no startpos parameter. Here's a pretty rough bit of example code, which might form the basis of a solution. Don't know what performance would be...

Or:

exec sp_configure 'remote query timeout', 300 --5 mins

go

reconfigure

go

I prefer to use TSQL whenever possible, and always save the script.