Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 24, 1999

SQL Sam and the Never-Ending Delete - Part 1 - Page 2

By Steve Hontz

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."

What did SQL Sam see? Go to the solution now!

 

Review SQL Sam and the Never-Ending Delete - Part 1

 

Back to SQL Sam Cases


Steve Hontz is President of The Bit Corner, Inc., a Phoenix-based consulting firm specializing in Windows NT, C++, and SQL Server development. You can reach him at steve@bitcorner.com.




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date