sql - DELETE Command is too slow in a Table with Clustered Index

Question

I have a rather big table named FTPLog with around 3 milion record I wanted to add a delete mechanism to delete old logs but delete command takes long time. I found that clustered index deleting takes long time.

DECLARE @MaxFTPLogId as bigint
SELECT @MaxFTPLogId = Max(FTPLogId) FROM FTPLog WHERE LogTime <= DATEADD(day, -10 , GETDATE())
PRINT @MaxFTPLogId
DELETE FROM FTPLog WHERE FTPLogId <= @MaxFTPLogId

I want to know how can I improve performance of deleting?

Answer 1

It might be slow because a large delete generates a big transaction log. Try to delete it in chunks, like:

WHILE 1 = 1
BEGIN
    DELETE TOP (256) FROM FTPLog WHERE FTPLogId <= @MaxFTPLogId
    IF @@ROWCOUNT = 0
        BREAK
END

This generates smaller transactions. And it mitigates locking issues by creating breathing space for other processes.

You might also look into partitioned tables. These potentially allow you to purge old entries by dropping an entire partition.