Running a DELETE stored procedure but finding degradation in performance in WHILE loop as iterations increase

Question

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.

Answer 1

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)

CREATE TABLE T
  (
     X INT PRIMARY KEY,
     Y CHAR(8000)
  )

INSERT INTO T
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
                  'Y'
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.

SET STATISTICS IO ON;

DECLARE @i INT = 0;
WHILE @i < 10
  BEGIN
      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,
             version_ghost_record_count
      FROM   sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('T'), NULL, NULL, 'DETAILED')
      WHERE  index_level = 0
  END 

Results

+---------------+--------------------+----------------------------+
| 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.

Answer 2

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.

Answer 3

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.

   DECLARE @TotalRows BIGINT
   DECLARE @Rows INT
   SET @TotalRows = 0
   SET @Rows = 0
   WHILE 1=1
   BEGIN
   DELETE TOP 100 FROM table_name Where --some condition --
   SET @Rows = @@ROWCOUNT
   SET @TotalRows += @Rows
   IF(@Rows < 100)
   BEGIN
   BREAK;
   END
   SET @Rows = 0;
   END
   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