# 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

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

• Are you retaining more data than you delete, and which is the ratio of retained/deleted? Aug 6 '19 at 18:56
• Please ensure that your code is posted with the intended formatting. There seems to be a lot of code here that isn't correctly commented out. Aug 6 '19 at 19:07
• Also tell us more about exactly what this code is designed to accomplish, and what the schema looks like. It's hard to help you without that background information. See How to Ask. Aug 6 '19 at 19:07
• @200_success - I have updated the code. The code is designed to delete any record older than 24 hours on daily basis.
– Andy
Aug 6 '19 at 19:19
• I'd suggest creating a smaller database to test the exact workings of your query on and note the inefficiencies. This will allow you to test alternative implementations a lot easier and faster.
– Mast
Aug 7 '19 at 5:44

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.