[ACCEPTED]-DELETE Command is too slow in a Table with Clustered Index-sql-server-2005

Accepted answer

Score: 16

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

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

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

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

Score: 7

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

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

Alter your PRIMARY KEY so that 2 it's unclustered. This will use HEAP storage 1 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

Score: 1

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

Score: 0

I assume that not only this table is huge 17 in terms of number of rows, but also that 16 it is really heavily used for logging new 15 entries while you try to clean it up.

Suggestion 14 of Andomar should help, but I would try 13 to clean it up when there are no inserts 12 going on.

Alternative: when you write logs, you probably 11 do not care about the transaction isolation 10 so much. Therefore I would change transaction 9 isolation level for the code/processes that 8 write the log entries so that you may avoid 7 creating huge tempdb (by the way, check if tempdb 6 grows a lot during this DELETE operation)

Also, I 5 think that deletions from the clustered 4 index should not be really slower then from 3 non-clustered one: you are still psysically deleting 2 rows. Rebuilding this index afterward may 1 take time though.

Source: stackoverflow.com

[ACCEPTED]-DELETE Command is too slow in a Table with Clustered Index-sql-server-2005

Stored Procedures to .sql files

SQL Script to alter ALL Foreign Keys to add ON DELETE CASCADE

IF/ELSE Stored Procedure

Conditional Operator in SQL Where Clause

SQL Server 2005 Get First and Last date for any Month in any Year

Set all the 0 values to NULL

How do I properly SELECT INTO @TempTable Multiple times to return a single resultset?

Using bcp utility to export SQL queries to a text file

Cannot call methods on char

select top 1000, but know how many rows are there?