Jeff Moden (6/12/2008)
Not sure the use of a correlated subquery (a form of hidden RBAR) will speed anything up here... Maybe...The real fact of the matter is the OP says it used to work just fine... and doesn't now... what could be the problem? Parallelism?
True enough Jeff. Looking back at the original post (and paraphrasing), the system is hanging and the factory floor can't work. The issue could be blocking. Pretty sure that the NOLOCK hint is ignored on the delete, and if the DELETE is going to delete 11,000,000 rows, I wouldn't be surprised if SQL puts a table lock on the table.
If it was working before but isn't now, the two things that come to my mind now would be data and disk fragmentation. The OP should check these out.
😎