SQL Sam and the Never-Ending Delete - Part 2
Environment: SQL Server 6.5, SP5a
"Hmmm," said Sam. There's only one process in your accounts database, and it's not blocked. But there is something interesting here- the command shows up as "INSERT", not "DELETE".
"Could be a bug in sp_who2," suggested Nully.
"Maybe," said Sam, "but I doubt it. I think there's more going on here than meets the eye. Is it okay if we stop this delete, Chuck?"
"Sure, Sam. We're not in production now. I just want to get to the bottom of this."
SQL Sam killed the process and then waited for the DELETE operation to rollback. It took a long time because of the large number of records involved.
"Okay, it's time to look at the query plan," said Sam. Sam brought up a query window and entered the following:
set rowcount 1 DELETE FROM customers WHERE cust_num LIKE '%8009______'
"What's the 'set rowcount 1' for?" asked Chuck.
"That's so we can delete just one row and see what happens," said Sam. He then turned on the Show Query Plan option in the Query Options dialog and ran the query. This is what he saw:
STEP 1 The type of query is DELETE The update mode is deferred FROM TABLE customers Nested iteration Table Scan TO TABLE customers STEP 1 The type of query is COND STEP 1 The type of query is SELECT FROM TABLE dbo.customers DELETED Nested iteration Using Dynamic Index STEP 1 The type of query is COND STEP 1 The type of query is INSERT The update mode is deferred FROM TABLE dbo.customers deleted Nested iteration Using Dynamic Index NOT EXISTS : nested iteration FROM TABLE Updated_Customers Nested iteration Table Scan TO TABLE Updated_Customers
"Okay," said Sam. "I can now see that your simple DELETE isn't so simple. I can see several reasons why this delete runs so long."