Lynn Pettis (6/12/2008)
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.
😎
Correct, WITH (NOLOCK) only affects SELECTs.
I just can believe the delete of 11 million rows didn't get caught blocking before. Somethings not quite right here. I think Lynn is on the right track... I've seen it where the undocumented ability to DELETE alias has bitten folks before. Gotta follow the rules and delete from a table name, instead.
--Jeff Moden
Change is inevitable... Change for the better is not.