SQL Server Performance

DELETE is running very slow

Discussion in 'T-SQL Performance Tuning for Developers' started by exBK, Nov 15, 2005.

  1. exBK New Member

    I have a table which has about 80,000 records. Table has a composite key of quest_id, quest_country and quest_year. When I run a delete statement:

    DELETE FROM tableX WHERE quest_country='US' and quest_year=2004

    It is taking for ever to execute. This statement should delete about 2000 rows. I also added an index for quest_country, quest_year and that didn't help. I am not sure where to look.

    Appreciate any tips. Thanks.
  2. joechang New Member

    if you are deleting a large number of rows, and the execution plan shows a nonclustered index seek, you might try forcing a table scan
    WITH (INDEX(0))
    this could be faster than the index seek plan, see my paper on large data ops for the reason why
  3. exBK New Member

    So what will the delete statement look like?
    I tried:
    DELETE FROM tableX WHERE quest_country='US' and quest_year=2004 WITH (INDEX(0))

    and it didn't work ... sorry I have not used WITH (INDEX(0)) before. Thanks for your time.
  4. Luis Martin Moderator

    Delete from Table with (index(0)) ......


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  5. druer New Member

    Be sure you show the execution plan in the Query Optimizer. If it is doing a Table Scan already to determine the rows to select the index you added to help this out will actually slow your performance. Because not only does it have to remove the data, and remove entries in the previously existing indexes, it will have to remove the entry from your new index, which might not have even been used.

    Before you run your command you could check in the Enterprise Manager to see how many processes have locks on the table you are using to see if perhaps there is just a lot of locking going on which the delete will have to wait and honor or something. Or you can open a second query analyzer window before you start the command, and issue the command "select * from master..sysprocesses where blocked > 0", then you can periodically execute that query while your command is running to see if it is being blocked and who is blocking it.

Share This Page