Deletes on large table

I had to help a collegue out today. He had to delete about 12 million rows on a 13 billion row table. And his statement (delete from [table name] where spec_id = 1) never got anywhere.

First thing i tried was to encapsulate the statement in a transaction. This transaction was put within a while loop. The script looked like this:

SET NOCOUNT ON

DECLARE @R INT;

SET @R = 1;

WHILE @R > 0
    BEGIN

    BEGIN TRANSACTION
    DELETE FROM [table name]
    WHERE SPEC_ID = 1
    OPTION (MAXDOP 1)

    SET @R = @@ROWCOUNT;

    COMMIT TRANSACTION

END

Now this looked like a smart plan, but still no progress. The maxdop hint was introduced because the delete statement was locking itself.

Now, what i realized some time later was that the table was partitioned. This meant that the query without the partition key was running around like a wild dog, but effectively doing nothing at all. So, i adjusted the query with the partitioning key:

 
SET NOCOUNT ON

DECLARE @R INT;
DECLARE @DATEID INT;

SET @R = 1;
SET @DATEID = 20170101

WHILE @DATEID < 20190117
    BEGIN

    BEGIN TRANSACTION
    DELETE FROM [table name]
    WHERE SPEC_ID = 1
    OPTION (MAXDOP 1)

    SET @R = @@ROWCOUNT;
    SET @DATEID = @DATEID + 1

    COMMIT TRANSACTION

END 

Now, this helped! The query finished in around 15 minutes clearing out the 10 miljoen records. Still very slow but at least it finished.

Now, i still don’t get why deletes are almost always very slow on SQL Server. Any thoughts on that? Let me know!

Thanks for reading 🙂