baabhu (4/17/2013)

---

ChrisM@Work (4/15/2013)

---

That looks ok to me, other than the joins to the temp table not being optimized. You don't really need the temp table anyway:

SET @RowsDeleted = 1000;

WHILE @RowsDeleted > 0

BEGIN

BEGIN TRAN;

DELETE TOP(1000) s

FROM [Experimental].[dbo].[SampleData] s

WHERE xyzmasterCode BETWEEN '0' AND 'Z'

AND filterDate <= @filterDate;

SET @RowsDeleted = @@ROWCOUNT;

COMMIT TRAN;

CHECKPOINT;

BEGIN TRAN;

DELETE TOP(1000) s

FROM [Experimental].[dbo].[SampleData] s

WHERE xyzmasterCode BETWEEN '0' AND 'Z';

SET @RowsDeleted = @RowsDeleted + @@ROWCOUNT;

COMMIT TRAN;

CHECKPOINT;

END

You could run it as one transaction spanning both deletes or as two as shown -I'd try both.

Add a waitfor delay statement after the commit. This will help to reduce the blocking.