Query Session no longer respond

Question

I'm trying to execute the following T-SQL Statement:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION

DECLARE @nRows INT = 1
DECLARE @DataCancellazione DATE = DATEADD(DAY, -7, GETDATE())
CREATE TABLE #IDToDel (ID BIGINT)

WHILE @nRows > 0
BEGIN
  INSERT INTO #IDToDel
  SELECT TOP 5000 LogID
  FROM MioDB.Test
  WHERE CAST(ReceivedDate AS date) < @DataCancellazione

  SELECT @nRows = @@ROWCOUNT

  DELETE RM WITH (PAGLOCK)
  FROM MioDB.Test  RM WITH (PAGLOCK)
  INNER JOIN #IDToDel TBD ON RM.LogID  = TBD.ID

  TRUNCATE TABLE #IDToDel
END
ROLLBACK 

When I launch the execution the query window seems to no longer respond and without having particular increase of CPUTime and DiskIO on the process. Can anyone help me thanks.

Answer 1

Honestly, I think you're overly complicating the problem. SQL Server can easily handle processing millions of rows in one go, and I suspect that you could likely do this in a few 1M row batches. If you have at least 4,000,000 rows you want to delete, then at 5,000 a batch that will take 800 iterations.

There is also no need for the temporary table, a DELETE can make use of a TOP, so you can just delete that many rows each cycle. I define this with a variable, and then pass that (1,000,000 rows). This would mean everything is deleted in 4 iterations, not 800. You may want to reduce the size a little, but I would suggest that 500,000 is easy pickings for instance.

This gives you the following more succinct batch:

SET NOCOUNT ON;
--The following transaction level seems like a terrible idea when you're performing DDL statements. Don't, just don't.
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

DECLARE @BatchSize int = 1000000,
        @DataCancellazione date = DATEADD(DAY, -7, GETDATE());
SELECT 1; --Dataset with 1 row

WHILE @@ROWCOUNT > 0
    DELETE TOP (@BatchSize)
    FROM MioDB.Test --A Schema called "MioDB" is a little confusing
    WHERE ReceivedDate < @DataCancellazione; --Casting ReceivedDate would have had no change to the query
                                             --And could well have slowed it down.