3
\$\begingroup\$

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.

enter image description here

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      
\$\endgroup\$
  • \$\begingroup\$ Are you retaining more data than you delete, and which is the ratio of retained/deleted? \$\endgroup\$ – dfhwze Aug 6 at 18:56
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ – 200_success Aug 6 at 19:07
  • 3
    \$\begingroup\$ 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. \$\endgroup\$ – 200_success Aug 6 at 19:07
  • 1
    \$\begingroup\$ @200_success - I have updated the code. The code is designed to delete any record older than 24 hours on daily basis. \$\endgroup\$ – Andy Aug 6 at 19:19
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Mast Aug 7 at 5:44
1
\$\begingroup\$

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

\$\endgroup\$
  • 1
    \$\begingroup\$ The solution seems perfect to me! All I need is to find out best possible time slot when the Production is not in use. \$\endgroup\$ – Andy Aug 6 at 19:44
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Dannnno Aug 22 at 14:55
  • \$\begingroup\$ @Dannnno Can I include your suggestion in my answer as an alternative option? Or you want to make an answer yourself? \$\endgroup\$ – dfhwze Aug 22 at 14:56
  • \$\begingroup\$ Feel free to include it yourself; it doesn't add a whole lot of additional information \$\endgroup\$ – Dannnno Aug 22 at 15:01
  • 1
    \$\begingroup\$ @Dannnno I added it because comments can get removed, thanks for sharing your views. \$\endgroup\$ – dfhwze Aug 22 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.