Using SQL Server 2012 Standard - I'm running a delete on a table based on the contents of another table. It's taking rather a long time (5 hours) and doesn't seem to be optimal to me, would appreciate some input optimising the statement:

delete from [dbo].[tbl1]
where exists (
    select *
    from [dbo].[tbl2] t 
    where [dbo].[tbl1].[col1] = t.[col1]
    and [dbo].[tbl1].[col2] = t.[col2]
    and [dbo].[tbl1].[col3] = t.[col3]  

The columns are as follows:

tbl1.col1 varchar(10)
tbl1.col2 datetime
tbl1.col3 varchar(60)
tbl2.col1 varchar(10)
tbl2.col2 datetime
tbl2.col3 varchar(30)

I realise that the datatype on col3 differs, I know this is bad, but would this mean the index cannot be used?

There is a non-unique clustered index on each table (not covered by this query) and a non-clustered index on both, covering all three columns included in the where clause.

tbl1 contains ~1.2 billion rows, tbl2 contains ~30 million rows. I'm expecting around 30 million rows to be deleted from tbl1.

Any help appreciated!

EDIT: FYI, tbl1 and tbl2 are on differing filegroups, but on the same disk (SAN). Also, here is the execution plan:

execution plan

  • Try DELETE t1 from tbl1 t1 inner join tbl2 t2 on tl1.col1= t2.co1 and t1.col2=t2.col2 and t1.col3 = t2.col3 – Spörri Oct 7 '15 at 12:03
  • Try the statement posted by Sporri but before that update statistics. That should work. – Anuj Tripathi Oct 7 '15 at 12:24
  • I actually changed the statement from using a join initially, the updated version was a good bit quicker actually. – dwjv Oct 7 '15 at 13:03
  • Would you be able to drop the indexes, delete the data, and then rebuild the indexes? On delete operations, the indexes have to be changed too so this is just a thought. Not sure if that'd be feasible or you'd be able to test to see if that'd work in your environment, but it's just a thought of something you may or may not be able to easily test win minimal impact of your DBs and system. – Pimp Juice IT Oct 7 '15 at 13:38
  • I had considered this, but dropping then creating indexes on a 1.2 billion row table... – dwjv Oct 7 '15 at 13:39

I've run into this kind of performance issue before, and breaking the delete up into discrete steps of a fixed size is what ended up doing the trick. It allows SQL Server to commit changes more frequently, which is generally easier on the transaction log.

SELECT 'Begin Delete'; --gives @@ROWCOUNT a value
    delete top (100000) from [dbo].[tbl1]
    where exists (
        select *
        from [dbo].[tbl2] t 
        where [dbo].[tbl1].[col1] = t.[col1]
        and [dbo].[tbl1].[col2] = t.[col2]
        and [dbo].[tbl1].[col3] = t.[col3]  

The optimal batch size just depends on your server and what it can handle. I've found 100,000 to be a safe size for me, and we've not got anything particularly powerful, so you may be able to get away with more.


Looks like the delete itself is what is taking the time.
You should test disable, delete, and then rebuild the indexes.

I know you said you tried a join but this would be a quick test

delete top (1000) tbl1Del 
from [dbo].[tbl1] and tbl1Del 
join [dbo].[tbl2] t 
  on tbl1Del.[col1] = t.[col1]
 and tbl1Del.[col2] = t.[col2]
 and tbl1Del.[col3] = t.[col3]; 
ALTER INDEX ALL ON [dbo].[tbl1] REBUILD;     

Run this select and you will know if determining which rows to delete is the problem.

select count(*) 
  from [dbo].[tbl1] and tbl1Del 
  join [dbo].[tbl2] t 
    on tbl1Del.[col1] = t.[col1]
   and tbl1Del.[col2] = t.[col2]
   and tbl1Del.[col3] = t.[col3] 
  • Thanks for this but that rebuild will take hours on the 1.2 billion row table, far from a quick test sadly. I'm not sure I'm going to be able to squeeze any more out of this and will just have to live with it, or get some SSDs :-) – dwjv Oct 8 '15 at 10:37
  • It has been running for 5 hours. Are you sure a rebuild would not be faster? Not squeezing very hard if you you are not willing to give it a try. – paparazzo Oct 8 '15 at 11:03
  • I don't remember exactly, but the initial index creation took around 10 hours. I believe this delete will only take place once a month - it might be a good opportunity to rebuild the index anyway as it'll certainly be a bit fragmented afterwards. – dwjv Oct 8 '15 at 11:05
  • Copy 1 million rows to another table and test both ways. I have some inserts on 100 million row tables and disable and rebuild is way faster. – paparazzo Oct 8 '15 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.