Take the 2-minute tour ×

Have a quick general question. I have a table I'm trying to purge a table. I'm deleting using a WHILE loop with WAITFOR DELAY time of 50ms between batches and 2000 records per batch. The real issue is that as time progresses the number of records deleted drops over time. Refer to the following:

Minute Number | Number of Records Deleted:

            1 | 162,000 
            2 | 116,000 
            3 |  80,000 
            4 |  72,000 
            5 |  62,000 
            6 |  38,000 
            7 |  38,000 
            8 |  34,000 
            9 |  20,000

Wondering if there is something fundamental about using WHILE loop batches for deleting records that causes the performance to degrade with each iteration of the loop. we have been manually watching the performance and then stopping the proc as the performance begins to drop drastically around the 5th to 6th minute and then restarting the deployment again. We don't think its a locking issue directly because as we play with the batch size the performance is always dropping at around the 5 to 6 minute mark.

share|improve this question
What is the table definition and the delete query? –  Martin Smith Jun 6 '13 at 23:41
Agreed with @MartinSmith, we need to see the code. It's just guessing until then. –  Thomas Stringer Jun 7 '13 at 0:37
Please provide the query - very likely you can add an index on your deletion criteria to speed it up dramatically. –  JNK Jun 7 '13 at 13:25
It may be much faster to copy the rows you want to keep into a new table, drop the old one, rename the new one. –  A-K Jun 9 '13 at 0:40

3 Answers 3

You say that performance degrades with the number of executions and that "restarting the deployment" fixes it.

I'm unclear what you mean by that particular phrase but presuming that it involves stopping the loop and then restarting it after some period then one possibility is ghost records.

I created a table as below (with one row per page for easier maths)

     Y CHAR(8000)

FROM   master..spt_values v1,
       master..spt_values v2

And assumed your DELETE process is clearing out older rows as identified by some ascending column.


WHILE @i < 10
      SET @i+=1

      RAISERROR('Processing %d',0,1, @i) WITH NOWAIT

      DELETE TOP (2000) FROM T
      WHERE  X <= 50000

      /*50 MS Delay*/
      WAITFOR DELAY '00:00:00:050'

      SELECT ghost_record_count,
      FROM   sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('T'), NULL, NULL, 'DETAILED')
      WHERE  index_level = 0


| logical reads | ghost_record_count | version_ghost_record_count |
|          2018 |               2000 |                          0 |
|          4025 |               4000 |                          0 |
|          6033 |               6000 |                          0 |
|          8026 |               7965 |                          0 |
|         10004 |               9944 |                          0 |
|         11989 |              11920 |                          0 |
|         13977 |              13902 |                          0 |
|         15963 |              15882 |                          0 |
|         17954 |              17864 |                          0 |
|         19943 |              19849 |                          0 |

Records aren't deleted straight away. They are marked as ghosts to begin with then cleared up by a background task. In the test above very few of them were cleaned up in the 10 iterations meaning that the tenth DELETE did 10 times as many reads as the first one.

Possibly in your environment the DELETEs are occurring at a greater rate than the ghost cleanup task processes them leading to the continual degradation of performance.

share|improve this answer

I've seen this dozens of times. It was always because the statistics for the table became progressively out of date. If this is your case, updating the statistics or reindexing the table should bring performance back up. It's also possible that getting your batch to recompile, by whatever method, would help.

share|improve this answer

There are many ways you can do optimize deletion of rows from the table.

  • By creating a view on the top of table and then running delete from table_name.

    create view v1 as (select top (10000) * from t1 order by a)
    delete from v1

Here is an excellent article from SQL CAT team describing how it will reduce I/O : Fast ordered delete

  • By deleting in small increments using TOP() command.

    SET @TotalRows = 0
    SET @Rows = 0
    WHILE 1=1
    DELETE TOP 100 FROM table_name Where --some condition --
    SET @Rows = @@ROWCOUNT
    SET @TotalRows += @Rows
    IF(@Rows < 100)
    SET @Rows = 0;
    SELECT @TotalRows AS [Rows_Deleted_Total]
  • Also, you need to take into account the activity going on the server, the recovery model of the database simple/bulk-logged/full. Take frequent transaction log backups when running this process if you're using full or bulk logged recovery models.

  • If you are running enterprise edition then partition the table. This will allow you to switch partitions in and out - called sliding window.

Worth mentioning Aaron Bertrand's article - Break large delete operations into chunks

share|improve this answer

Your Answer


