How to speed up delete from table with composite clustered index but delete predicate on second column of the clustered index

Question

ALTER TABLE dbo.TableA ADD  CONSTRAINT [PK_composite] PRIMARY KEY CLUSTERED 
(
    col1 ASC,
    col2 ASC,
    col3 ASC,
    col4 ASC,
    
)WITH (PAD_INDEX = OFF, 
       STATISTICS_NORECOMPUTE = OFF, 
       SORT_IN_TEMPDB = OFF, 
       IGNORE_DUP_KEY = OFF, 
       ONLINE = OFF, 
       ALLOW_ROW_LOCKS = ON, 
       ALLOW_PAGE_LOCKS = ON) 
GO

Sample table data

col1 col2 col3 col4 col5 col6 col7 
       1
       1
       2
       2
       3
       4
       5
       5
       5
   

TableA contains over 1.2 billion rows . we have to delete all values older than say col2 <7700 ( which is more than half of the rows in the table) . On average to delete 5 million rows it takes one hour. is there a way this can be made run faster . The only index is on the table is clustered index and the table is in a database an AG .

showing here part of the code where col2=5 matches 5 million rows and the delete takes 1 hour and 4 minutes

 DELETE TOP (@BATCHSIZE)
   FROM dbo.TableA
  WHERE col2=5  --example

Answer 1

You could follow the approach Michael J Swart describes in Take Care When Scripting Batches, as originally suggested by Erik Darling in a comment.

The first few batches complete quickly, but later batches gradually get slower as it takes longer and longer to scan the index to find rows to delete. By the time the script gets to the last batch, SQL Server has to delete rows near the very end of the clustered index and to find them, SQL Server has to scan the entire table.

But I know something about the indexes on this table. I can make use of this knowledge by keeping track of my progress through the clustered index so that I can continue where I left off

You could also create a nonclustered index on the col2 column to assist with that.

Answer 2

You could partition the table on col2. This would limit the scan, or even permit truncating whole partitions.

Answer 3

You could create a new table with identical schema & clustered index and do a INSERT/SELECT to copy the rows you want to keep. Drop the original table & use sp_rename to change the name of the new table back to match the original table, then add the non clustered indexes