3

I am trying to delete thousands of records say 35K from a table which consists like 10 million records.

Delete * from Table where ScenarioID=859

I have also tried bulk deletion

DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1

WHILE (@RowsDeleted > 0)
BEGIN
    DELETE top(100) FROM tableWHERE ScenarioID= @ID
    SET @RowsDeleted = @@ROWCOUNT
END

I would like to ask

  1. Generally How much time it would take to delete such no of records?
  2. If there is any Index on that table then is it gonna affect the delete statement?
    Note: I have a non-clustered index on a column ID. Does this have any affect on performance of if i delete records based on that ID
  3. are there any common bottle-necks that may cause delete to take long time than it should take ?

Is there any thing going wrong in this plan ?

enter image description here

PS: My database is hosted in Azure and it takes just below 10 seconds to execute the same command in Azure portal

2
  • Look at the execution plan. It answers all of this.
    – usr
    Dec 26, 2014 at 12:44
  • 1
    Generally, SQL Server will use row-level locks to lock those rows that are being manipulated (and that includes rows being deleted). However, if you manipulate more than 5000 rows in a single transaction, then SQL Server will do a lock escalation and lock the entire table that's being deleted from exclusively - no other operations (not even SELECT) are possible anymore. So you might want to break up your delete operation into chunks of less than 5000 rows each
    – marc_s
    Dec 26, 2014 at 18:23

2 Answers 2

3
  1. Generally How much time it would take to delete such no of records ?

Impossible for us to guess - way too many factors involved here, including performance of your data and log drives (a factor of which level of service you chose), your log growth rate and number of auto-growth events that occur, concurrent activity, number of indexes, existence of foreign keys, indexed views, etc.


  1. If there is any Index on that table then is it gonna affect the delete statement?

Yes. With the exception of filtered indexes, a delete from a table will also delete from each index. This is not free. If you generate an actual plan using the free SentryOne Plan Explorer, it tries to make it very obvious that the cost involves other indexes too:

enter image description here

In Management Studio, there is no visual cue that you're not just deleting from a single (clustered) index, but the information is in the plan XML - so you can also dig for it in the properties:

enter image description here


  1. are there any common bottle-necks that may cause delete to take long time than it should take?

Yes, mentioned above and in the other answer. You need to find the right balance of number of rows to delete, and we can't guess that for you without a lot more information. Please read this post:

One of the key things you'll see there - and I mention it in an answer to another question of yours - is to use transactions to break the looped deletes up into separate atomic operations.

DECLARE @RowsDeleted INT = 1, @counter INT = 1;

BEGIN TRANSACTION;

WHILE (@RowsDeleted > 0)
BEGIN
    DELETE top(100) FROM dbo.table WHERE ScenarioID = @ID;
    SELECT @RowsDeleted = @@ROWCOUNT, @counter += 1;
    IF @counter % 10 = 0
    BEGIN
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
    END
END

COMMIT TRANSACTION;

Again, you'll need to find the right balance here. Maybe it's committing 500 rows at a time, or 1,000, or 5,000. Impossible for us to know.

Another common bottleneck is logging activity. This may be resolved by using smaller transactions but could still be an issue. Unfortunately, in SQL Azure I'm not sure how exactly you would find out if that is the case (don't believe the default trace is an option for you).

If you are deleting the majority of the table, another option is to move the rows you want to keep into a new table, then drop the old table, and rename the new one.

3
  • I have tried that please check my execution plan
    – Vivekh
    Dec 29, 2014 at 10:56
  • @Vivekh you have tried what? If your delete is still slow even deleting a small number of rows, I suspect it's not the execution plan that's the problem, it's blocking. Dec 29, 2014 at 14:05
  • I have tried deleting in Chunks and also rebuild the indexes based on fragmentation. it did improved a bit like a minute but it still takes 3 minutes to delete 50K rows. How to find whether its blocking or not ?
    – Vivekh
    Dec 30, 2014 at 7:08
2

1) Generally How much time it would take to delete such no of records ?

It depends on many factor like

  1. Is there any BEFORE/AFTER DELETE triggers
  2. Too many Indexes
  3. Cascade delete
  4. Number of rows to delete
  5. Deadlocks and blocking

2) If there is any Index on that table then is it gonna affect the delete statement ? Note: I have a non-clustered index on a column ID. Does this have any affect on performance of if i delete records based on that ID

If you have a nonclustered index on the ID column then will help in performance and delete will be faster.

3) are there any common bottle-necks that may cause delete to take long time than it should take ?

I think the answer of it is in the answer to first question itself.

Transaction log grow during these delete operation and if there are big number of records to delete, it's better to do it in off hours in production server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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