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!