Команда DELETE слишком медленная в таблице с кластеризованным индексом

У меня довольно большая таблица с именем FTPLog со значением около 3 milion. Я хотел добавить механизм удаления для удаления старых журналов, но команда delete занимает много времени. Я обнаружил, что удаление кластеризованного индекса занимает много времени.

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

Я хочу знать, как повысить эффективность удаления?

Ответ 1

Это может быть медленным, потому что большое удаление создает большой журнал транзакций. Попробуйте удалить его в кусках, например:

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

Это создает меньшие транзакции. И это смягчает проблемы блокировки, создавая пространство для дыхания для других процессов.

Вы также можете заглянуть в разделы секционированные таблицы. Это потенциально позволяет вам очистить старые записи, отбросив весь раздел.

Ответ 2

Так как это таблица журналов, нет необходимости делать кластеризацию.

Вряд ли вы будете искать его на Id.

Измените свой PRIMARY KEY, чтобы он не был включен. Это будет использовать метод хранения HEAP, который быстрее работает на DML:

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

и просто введите:

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

Ответ 3

Проверьте плотность вашей таблицы (используйте команду DBCC showcontig для проверки плотности) Плотность сканирования [Наилучший счет: фактический подсчет] Этот параметр должен быть ближе к 100%, а параметр Локальное сканирование должен быть ближе к 0% для лучшей производительности вашей таблицы. Если это не так, переиндексируйте и отредактируйте индекс этой таблицы, чтобы повысить производительность выполнения запроса.

Ответ 4

Я предполагаю, что не только эта таблица огромна по количеству строк, но также и то, что она действительно сильно используется для регистрации новых записей, когда вы пытаетесь ее очистить.

Предложение Andomar должно помочь, но я попытаюсь очистить его, когда нет вставок.

Альтернатива:, когда вы пишете журналы, вы, вероятно, не заботитесь об изоляции транзакций. Поэтому я бы изменил уровень изоляции транзакций для кода/процессов, которые записывают записи журнала, так что вы можете избежать создания огромного tempdb (кстати, проверьте, растет ли tempdb во время этой операции DELETE)

Кроме того, я думаю, что удаление из кластерного индекса не должно быть действительно медленнее, чем из некластеризованного: вы все еще занимаете физическое удаление строк. Однако восстановление этого индекса может занять некоторое время.