Sql-server – Performance tuning of Purging table Data in SQL Server 2016

sql serversql-server-2016

I have to Automate the Purging huge volume of data from main table to Archive.

Steps I am following :

  1. I am inserting the data from main table to archive table.

  2. After cross verifying the count in both tables , deleting the data from main table.

Risk: Delete statement may go in hung state and DB will go in recovery mode.

Note: we can't perform any DDL operation on main table as it's live transaction table. We can go with DML.

  • Database: SQL Server 2016
  • OS: Windows and IIS

Best Answer

DDL is one of the main ways of speeding up the deletion of records, but seeing that DDL is not an option and you're facing 'hung states' for delete statements, perhaps I suggest looking into deleting records in batches?

Please see link, and here's a query example based on link:

DECLARE @Batch INT = 1000
WHILE 1 = 1
BEGIN
    DELETE TOP (@Batch)
    FROM MyTable
    WHERE Date < ‘20200401’';
    IF @@ROWCOUNT < @Batch BREAK
END

In this case, when we're deleting by Date, make sure you've got an index on Date so that SQL Server can quickly grab just those rows. You'll want to avoid table-level locking.

Related Question