Daily SQL job to delete records older than 24 hours Below is a delete job to delete 1M+ records daily and takes 13 hours to complete and sometimes more than that. I need to optimize this.


  The table tblcalldatastore is being inserted 24*7 through a stored
  procedure and is supposed to have no records older than 24 hours.




Execution plan is below.

https://www.brentozar.com/pastetheplan/?id=ryYi2Sv7B

BEGIN      

SET NOCOUNT ON;      

DECLARE @DELETECOUNT int      
DECLARE @DELETEATATIME int      

DECLARE @CutOffDate datetime      
DECLARE @HourRetained smallint      
set @HourRetained = 24      
begin Try      


set @CutOffDate = getutcdate()      

select  @DELETECOUNT = count(*) from [tblcalldatastore]      
where  istestcase=0      
and datediff(hour,receiveddate,@CutOffDate)&gt;@HourRetained      

SET @DELETEATATIME = 20000      





WHILE @DELETECOUNT &gt; 0      

BEGIN      

DELETE TOP(@DELETEATATIME) FROM tblcalldatastore WITH (ROWLOCK)  WHERE IsTestCase=0 and datediff(hour,receiveddate,@CutOffDate)&gt;@HourRetained       


SET @DELETECOUNT = @DELETECOUNT - @DELETEATATIME       


END      




end try      


BEGIN CATCH      

SELECT 'FAILED - ' + ERROR_MESSAGE() AS ErrorMessage      

END CATCH      

END
From Comments

Have you tested performance of copying data of a single day to a temporary table, truncating the target table, and re-inserting the copied data back to the target table?


  Yes, it's not taking much time to copy or insert, it is taking time to delete and select. One of the column has huge XML string.


Proposed Solution

Since inserting and copying data does not seem to yield a performance penalty, I would suggest to:


copy to data of today to a staging table
truncate the existing table (much faster than delete, no transaction logs)
copy staged data back to existing table


Or alternatively, as Dannnno suggests:

Potentially faster than copying the data twice would be to copy data to a staging table, truncate the existing table, then rename them both to swap places. This minimizes latches required for DDL, and also only requires a single copy. Snapshot isolation would also help this be as non-invasive as possible for anyone hitting the database.

Links


Similar problem: non-clustered index bottleneck on delete
Enlists a couple of possible solutions