10

we have a quite big MS SQL database with millions of rows. I created a simple script to delete rows older than 1 month, but this seems to lock the table and creates trouble for the application.

The table has an indexed "ID" PK, and also a "date" column which I will use for this task.

What is the best way to do this without causing the lock? I was considering partitioning, but not sure if its the best way to go. Thanks in advance.

0

3 Answers 3

19

You need to delete rows in batches to avoid lock escalation. Lock escalation happens when SQL Server switches from row or page locks to locking the entire table. Lock escalation conserves memory when SQL Server detects a large number of row or page locks have been taken, and more are needed to complete the operation. This may be why you're having blocking issues. If I recall correctly, row locks escalate to table locks when more than 5,000 rows are involved.

If the ID column is also the clustered index you might benefit from using the ID column to perform the delete instead of using the date column. To do that, obtain the ID value for the date you want to work with, then loop to delete fewer than 5,000 rows per batch, where the ID value is less than or equal to the desired ID. This is only applicable assuming rows are inserted in date order, such that incrementing ID values match incrementing date values.

You can determine how many rows were affected by the DELETE by checking the @@rowcount variable, and you can set a maximum number of rows to be affected using SET ROWCOUNT, then loop until your DELETE no longer removes rows:

SET ROWCOUNT 4500;
DECLARE @i int;
SELECT @i = 1;
    
WHILE @i > 0
BEGIN
    Delete from dbo.my_table Where ID < 4356;
    SELECT @i = @@rowcount;
END

An alternate method that eliminates the use of the deprecated SET ROWCOUNT statement is to add a TOP clause to the DELETE query, as in:

DECLARE @i int = 1;
DECLARE @id int;
SET @id = COALESCE((
    SELECT TOP(1) mt.ID
    FROM dbo.my_table mt
    WHERE mt.date < '2021-01-04T00:00:00.000'
    ORDER BY mt.date DESC
    ), 0);
WHILE @i > 0
BEGIN
    DELETE TOP(5000) mt
    FROM dbo.my_table mt
    WHERE mt.ID <= @ID;
    SET @i = @@ROWCOUNT;
END

In the code above, I've added a query to obtain the maximum ID value for rows on or before January 4th, 2021, simply to illustrate how this works.

Both methods above result in the same outcome, all rows prior to the given @ID value are deleted from the table. Locking should be reduced to using row-level locks, thereby preventing a table lock from causing blocking on other concurrent processes.

As an alternative, you could look at Read Committed Snapshot Isolation (RCSI), otherwise known as row-versioning, at the database level. RCSI prevents writers from block readers, however there are many possible impacts to using row versioning. You should thoroughly test the applications that use the database if you decide to go this route. Read up carefully and consider the other impacts on your application before blindly enabling RCSI.

0
2

Another way you can handle this that would minimize downtime (assuming the majority of the data is being deleted) is you can INSERT everything within the last month into a new Table. DROP the old Table, and use sp_rename to instantly rename your new Table to the old Table's name.

For example:

SELECT *
INTO NewTable
FROM OldTable
WHERE OldTable.[date] >= '2021-01-04';

DROP TABLE OldTable;

EXEC sp_rename 'NewTable', 'OldTable'; -- Renames the new table with 1 month of data to the old table's name

This trick used to be really helpful for me when I had to remove billions of records from a single Table. Hopefully you already have an index on the date column, otherwise you can do batch INSERTS into the new table until you have your 1 month of data (similar to what Mo64's answer suggests, for this piece).

By the way, I stumbled on this relevant article by Aaron Bertrand coincidentally, while researching something else. It has some good information on mass deletion performance tuning, and ironically one solution is the one I mentioned above.

0
-2
Declare @counter INT  
Set @counter = 10 -- (you can always obtain the number of rows to be deleted and set the counter to that value)  
While @Counter > 0  
Begin  
Delete TOP (4000) from <Tablename> where ID in (Select ID from <sametablename> with (NOLOCK) where DateField < '2021-01-04') -- or opt for GetDate() -1  
Set @Counter = @Counter -1 -- or set @counter = @counter - 4000 if you know number of rows to be deleted.  
End

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.