4

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
10

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.

9
  • Of course you run the risk of losing records inserted after the start of the first query with this method. – jcaron Feb 4 at 23:18
  • @Jarcon Writes block writes (generally speaking), otherwise that can be accounted for as well. The converse problem exists with doing batch deletes by date iteratively too, where data of an older date is added to the Table after the iterator has already passed that date, resulting in extra records that should've theoretically been deleted. – J.D. Feb 5 at 1:25
  • I'm not used to SQL Server but to other database systems, but usually writes in a table or on a row blocks write on the same table or row. Here you read from a table and write into another. While you do that, you could have new writes in the old table which won't be taken into account in your copy (as they didn't exist at the start of the operation), but will be deleted when you drop the table. When deleting old data from a log table, you can't have new data added that matches the "older than X" condition. – jcaron Feb 5 at 15:21
  • @jcaron Sorry I misspoke, I meant reads block writes generally speaking (depending on the lock taken), and you can ensure this by using the appropriate locks if you want. To my other point, deleting data iteratively by date doesn't stop someone from adding data to the Table of an older date that was already iterated past. For example, if the loop is now on dateField = '2021-01-04' nothing stops anyone from inserting into the same table a record with dateField = '2021-01-03', especially because the table will not be locked between iterations, so there's a higher chance of data change. – J.D. Feb 5 at 17:55
  • I’m not sure where you have seen that reads block writes, but unless you specify it explicitly (not sure how that is done in SQL server but in others that could be SELECT ... FOR UPDATE), that doesn’t happen, and it definitely won’t prevent new inserts. I agree that at the database level rows with older timestamps could be inserted, but a) when the timestamp matches even roughly the insertion time this is not an issue and b) you may have more data than you would like – which can be fixed at any time – but with your method you are actually losing data which is definitively lost. – jcaron Feb 5 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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