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.

Answer 2

Since it's a log table, there is no need to make is clustered.

It's unlikely that you will search it on Id.

Alter your PRIMARY KEY so that it's unclustered. This will use HEAP storage method which is faster on DML:

ALTER TABLE FTPLog DROP CONSTRAINT Primary_Key_Name
ALTER TABLE FTPLog ADD CONSTRAINT Primary_Key_Name PRIMARY KEY NONCLUSTERED (FTPLogId)

, and just issue:

SELECT @MaxFTPLogTime = DATEADD(day, -10 , GETDATE())
PRINT @MaxFTPLogId
DELETE FROM FTPLog WHERE LogTime <= @MaxFTPLogTime

Answer 3

Check the density of your table (use command DBCC showcontig to check density) Scan Density [Best Count:Actual Count] this parameter should be closer to 100% and Logical Scan Fragmentation parameter should be closer to 0% for best performance of your table. If it is not, re-index and refragment the index of that table to improve performance of your query execution.