John Rowan (6/11/2008)

---

Also, your WHERE clause of PO.PODescription LIKE '%00000%' will normally produce a table scan. It's a bit hard to say for sure though as you have not posted an execution plan or your index structure as requested by the other posters.

Matt Miller (6/11/2008)

---

Agreed, although I can't think of how that wouldn't cause a table scan, thanks to the leading %. That kind of forces a table scan (or a clustered index scan), since there's no decent way to use an index to seek those out.

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?

Lynn Pettis (6/12/2008)

---

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.

Jeff Moden (6/12/2008)

---

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.