slow outer join after delete rows

5 pts.
Tags:
SQL Server 2005
SQL Server errors
Hi, I've tried this on two installations of SQL Server 2005 service pack 2. If I delete rows of a table, an outer join on the empty table is extremely slow...if I truncate it's ok, if I delete with the tablock hint it's ok. Any ideas why? The code to reproduce this follows, the outer join takes <1 second after truncate or using tablock but 64 seconds if using just delete. Thanks. CREATE TABLE [testtab1]( [col1] [bigint] NULL, [col2] [bigint] NULL ) ON [PRIMARY] CREATE TABLE [testtab2]( [col1] [bigint] NULL, [col2] [bigint] NULL ) ON [PRIMARY] declare @i int set @i = 1 while @i <= 20000 begin insert into testtab1 values(@i,floor(10*rand())) set @i = @i + 1 end DECLARE @date datetime SET @date = getdate() INSERT INTO testtab2 (col1, col2) SELECT b.col1,b.col2 FROM testtab1 b LEFT OUTER JOIN testtab2 a ON a.col1 = b.col1 AND a.col2 = b.col2 WHERE a.col1 IS NULL OR a.col2 IS NULL print 'Elapsed time for join/insert after table creation:'+CONVERT(CHAR(8), GETDATE()-@date, 108) truncate table testtab2 SET @date = getdate() INSERT INTO testtab2 (col1, col2) SELECT b.col1,b.col2 FROM testtab1 b LEFT OUTER JOIN testtab2 a ON a.col1 = b.col1 AND a.col2 = b.col2 WHERE a.col1 IS NULL OR a.col2 IS NULL print 'Elapsed time for join/insert after truncate table:'+CONVERT(CHAR(8), GETDATE()-@date, 108) delete from testtab2 -- with (tablock) SET @date = getdate() INSERT INTO testtab2 (col1, col2) SELECT b.col1,b.col2 FROM testtab1 b LEFT OUTER JOIN testtab2 a ON a.col1 = b.col1 AND a.col2 = b.col2 WHERE a.col1 IS NULL OR a.col2 IS NULL print 'Elapsed time for join/insert after delete:'+CONVERT(CHAR(8), GETDATE()-@date, 108) Output: (20000 row(s) affected) Elapsed time for join/insert after table creation:00:00:00 (20000 row(s) affected) Elapsed time for join/insert after truncate table:00:00:00 (20000 row(s) affected) (20000 row(s) affected) Elapsed time for join/insert after delete:00:01:04 Output if tablock hint is uncommented (20000 row(s) affected) Elapsed time for join/insert after table creation:00:00:00 (20000 row(s) affected) Elapsed time for join/insert after truncate table:00:00:00 (20000 row(s) affected) (20000 row(s) affected) Elapsed time for join/insert after delete:00:00:00

Answer Wiki

Thanks. We'll let you know when a new response is added.

By running <a href=”http://itknowledgeexchange.techtarget.com/sql-server/upgrade-for-sp_who2-called-sp_who3/”>sp_who3</a> on the spid running the above code I see that my processed is being paused with a waittype of SOS_SCHEDULER_YIELD.

This basically means that the scheduler that you are trying to use is busy doing something else, possibly flushing the delete to disk?

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following