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

Question

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

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

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

Я хочу знать, как я могу улучшить производительность удаления?

12

sql sql-server-2005

Источник

user112986 27 май '09 в 08:39

4 ответа

Другие вопросы по тегам sql sql-server-2005

user50552 27 май '09 в 08:50 2009-05-27 08:50 · Answer 1 · 2009-05-27 08:50

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

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

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

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

user55159 27 май '09 в 08:48 2009-05-27 08:48 · Answer 2 · 2009-05-27 08:48

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

Маловероятно, что вы будете искать его на 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

user29515 27 май '09 в 08:49 2009-05-27 08:49 · Answer 3 · 2009-05-27 08:49

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

user99594 27 май '09 в 09:05 2009-05-27 09:05 · Answer 4 · 2009-05-27 09:05

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

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

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

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