Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
6  
What is the table definition and the delete query? –  Martin Smith Jun 6 '13 at 23:41
1  
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)

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.

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.

    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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.