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)>@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
