Улучшить производительность удаления sql и уменьшить размер файла журнала и tempDB?

Question

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

Delete from EmailList(nolock) 
WHERE EmailId IN ( 
    SELECT EmailId 
    FROM Emails 
    WHERE EmailDate < DATEADD([days], -60, GETDATE())
)

Это работает, но проблема в том, что это занимает много времени и файл журнала становится очень большим из-за этого. Размер файла журнала увеличивается более чем на 100 ГБ каждый день.

Я думаю, что мы можем изменить это на

Delete from EmailList(nolock) 
WHERE EXISTS ( 
    SELECT EmailId 
    FROM Emails 
    WHERE (Emails.EmailId = EmailList.EmailId) AND 
        (EmailDate < DATEADD([days], -60, GETDATE()))
)

Но кроме этого, есть ли что-то, что мы можем сделать, чтобы улучшить производительность. больше всего уменьшить размер файла журнала?

• EmailId проиндексирован.

Answer 1

Я видел

GetDate()-60

синтаксис стиля выполняет НАМНОГО лучше, чем

DATEADD([days], -60, GETDATE()))

особенно если в столбце даты есть индекс. Несколько коллег-администраторов и я потратили немало времени, пытаясь понять, ПОЧЕМУ он будет работать лучше, но результат был в пудинге.

Еще одна вещь, которую вы, возможно, захотите рассмотреть, учитывая объем записей, которые, я полагаю, вам нужно удалить, - это разбиение удалений на группы, скажем, 1000 или 10000 записей. Это, вероятно, ускорит процесс удаления.

Answer 2

[РЕДАКТИРОВАТЬ]:

относительно комментария @TomTom: если у вас есть доступная редакция SQL Server Enterprise, вам следует использовать разбиение таблиц.

Если это не так, мой оригинальный пост может быть полезным:

---

[ОРИГИНАЛЬНАЯ ПОЧТА]

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

В зависимости от ваших требований это не сработает, но, возможно, вы сможете извлечь из этого некоторые идеи.

Вместо использования 1 таблицы используйте 2 таблицы с одинаковой схемой. Создайте синоним (я предполагаю, что вы используете сервер MS SQL), который указывает на "активную таблицу из 2 таблиц (означает, что это активная таблица, в которую вы сейчас записываете). Используйте этот синоним для вставок в приложении или вместо использования синонима приложение может просто изменять таблицу каждые x дней, в которые оно пишет.

Каждые x дней вы можете обрезать старую / неактивную таблицу, а затем воссоздать синоним, чтобы указать на усеченную таблицу (если вы используете решение synonnym), так что вы эффективно разделяете данные за раз.

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

Если изменение кода вашего приложения не является вариантом, рассмотрите возможность использования того же принципа, но вместо записи в синоним, с которым вы могли бы создать представление вместо триггеров (операция вставки вставила бы в "активный" раздел). Код триггера должен был бы syhcnronize, используя что-то вроде Applock, как упомянуто выше (так что запись во время работы процесса переключения).

Мое решение немного сложнее, поэтому в настоящее время я не могу опубликовать здесь код, но он работает без проблем для приложений с высокой нагрузкой, и процесс swithcingt/cleanup полностью автоматизирован.

Answer 3

Вы пробовали разделение по дате, тогда вы можете просто отказаться от настольных версий в те дни, которые вас больше не интересуют. Имея "хью" базу данных, вы наверняка запускаете корпоративную версию SQL Server (в конце концов, хью больше, чем очень большой), и она имеет секционирование таблиц.