mardi 30 décembre 2014

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


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





Aucun commentaire:

Enregistrer un commentaire