Deleting millions of rows from SQL Server

So, you've found large tabels and now you want to trim them down to size... Not drop them them, just delete most of the rows, using some conditions.

If you run a:

DELETE FROM SOMETABLE
WHERE "SOME_CONDITION"

...you'll be waiting a long time.

As it alleges in the comments at the source:

Any delete operation will causing locking - on the row level. But once your transaction has more than 5000 row-level locks, SQL Server will do a lock escalation and lock the entire table in exclusive mode
—marc_s

So this uses Delete top(@batchsize) ... in a while loop to do the needful.

DECLARE 
    @BATCHSIZE INT, 
    @WAITFORVAL VARCHAR(8), 
    @ITERATION INT, 
    @TOTALROWS INT, 
    @MAXRUNTIME VARCHAR(8), 
    @BSTOPATMAXTIME BIT, 
    @MSG VARCHAR(500),
    @STARTALL DATETIME,
    @STARTBATCH DATETIME,
    @ENDBATCH DATETIME

SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4000;
SET @WAITFORVAL = '00:00:10' -- Delay between deletes -- UPDATE THIS
SET @MAXRUNTIME = '08:00:00' -- 8AM
SET @BSTOPATMAXTIME = 1 -- ENFORCE 8AM STOP TIME
SET @ITERATION = 0 -- LEAVE THIS
SET @TOTALROWS = 0 -- LEAVE THIS

SET @STARTALL = GetDate()

WHILE @BATCHSIZE>0
BEGIN
    -- IF @BSTOPATMAXTIME = 1, THEN WE'LL STOP THE WHOLE JOB AT A SET TIME...
    IF CONVERT(VARCHAR(8),GETDATE(),108) >= @MAXRUNTIME AND @BSTOPATMAXTIME=1
    BEGIN
        RETURN
    END

    SET @STARTBATCH = GetDate()

    /* vv You need to update this!*/
    DELETE TOP(@BATCHSIZE)
    FROM SOMETABLE
    WHERE SOMECONDITION
    /* ^^ You need to update that */

    SET @BATCHSIZE=@@ROWCOUNT
    SET @ENDBATCH = GETDATE()


    SET @ITERATION=@ITERATION+1
    SET @TOTALROWS=@TOTALROWS+@BATCHSIZE
    SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR)
    RAISERROR (@MSG, 0, 1) WITH NOWAIT
    SET @MSG = 'Batch duration: ' + 
            Cast(DATEDIFF(s, @STARTBATCH, @ENDBATCH) as varchar) + 
            ' seconds. Batch Speed ' + CAST(CAST(@BATCHSIZE as float)/(DATEDIFF(s, @STARTBATCH, @ENDBATCH)) AS VARCHAR) + 
            ' rows per seconds. Subtotal elapsed: ' + 
            CAST(DATEDIFF(s, @STARTALL, @ENDBATCH) as varchar) + 
            ' seconds. Cumulative Speed ' + CAST(CAST(@TOTALROWS as float)/(DATEDIFF(s, @STARTALL, @ENDBATCH)) AS VARCHAR) + 
            ' rows per second'
    RAISERROR (@MSG, 0, 1) WITH NOWAIT
    WAITFOR DELAY @WAITFORVAL 
END

SET @MSG = 'TOTAL elapsed: ' + 
  CAST(DATEDIFF(s, @STARTALL, @ENDBATCH) as varchar) + 
  ' seconds. Total rows: ' + CAST(@TOTALROWS AS VARCHAR) + 
  '. Final Speed: ' + CAST(CAST(@TOTALROWS as float)/(DATEDIFF(s, @STARTALL, @ENDBATCH)) AS VARCHAR) + 
  ' rows per second'
RAISERROR (@MSG, 0, 1) WITH NOWAIT

Source

See also

results matching ""

    No results matching ""