Sql-server – Will a non-clustered index on a column with covering index on all the columns of table affect the Delete statement Records)

azure-sql-databasenonclustered-indexsql server

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

Best Answer

  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.

Related Question