wolfkillj (3/13/2013)
What's going on in the rest of the script? Are you certain the waits are related to this portion of the code?As was previously stated, this is a majorly inefficient way of deleting multiple rows from a table. I would question why your vendor is sending you this script - it suggests a real lack of depth of understanding of T-SQL, SQL Server, and set-based operations.
The rest of the script is essentially a repetition of the block I sent, with some exceptions where additional child-table records are deleted. I watched the page resource waits, and getting the info via DBCC page(), the pages are on the same table that's been purged.
As to the vendor logic, I'm in full agreement there. At the minimum I'm looking at turning IMPLICIT_TRANSACTIONS on before executing the script.