This is a query I have used on a table of 240,000,000 records where I wanted to delete one month out of eighty, based on ExecutionMonth

that had 3,500,000 records.

The secret is to create a small view based on the table and keep deleting the view. 

 

alter VIEW dbo.ROWS_ToBeDeleted AS

 

    SELECT TOP 1000  *

           —note the index on the where field

    FROM [DWH].[ZZZ] with (INDEX (idx_zzz))

            WHERE ExecutionMonth=202207

 

GO

 

 

WHILE EXISTS(SELECT top 1000 * FROM [DWH].[ZZZ]  WHERE ExecutionMonth=202207)

BEGIN

DELETE dbo.ROWS_ToBeDeleted

END

 

If you have question or inputs, let me know!