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.
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)>@HourRetained SET @DELETEATATIME = 20000 WHILE @DELETECOUNT > 0 BEGIN DELETE TOP(@DELETEATATIME) FROM tblcalldatastore WITH (ROWLOCK) WHERE IsTestCase=0 and datediff(hour,receiveddate,@CutOffDate)>@HourRetained SET @DELETECOUNT = @DELETECOUNT - @DELETEATATIME END end try BEGIN CATCH SELECT 'FAILED - ' + ERROR_MESSAGE() AS ErrorMessage END CATCH END