Оптимизация удаления на SQL Server

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

до сих пор:

убедитесь, что внешние ключи имеют индексы
убедитесь, что условия индексируются
использование WITH ROWLOCK
уничтожить неиспользуемые индексы, удалить, перестроить индексы

теперь, ваш ход.

Ответ 1

Следующая статья, Быстрое упорядоченное удаление операций может вас заинтересовать.

Выполнение быстрых операций удаления SQL Server

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

Ответ 2

У меня гораздо больше опыта работы с Oracle, но, скорее всего, это относится и к SQL Server:

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

Ответ 3

Интересно, пришло ли время для сборщиков мусора? Вы отмечаете строку для удаления, и сервер удаляет ее позже во время развертки. Вы не захотите этого для каждого удаления - потому что иногда строка должна идти сейчас, но это было бы удобно иногда.

Ответ 4

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

Мои предложения:

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

Ответ 5

Резюме ответов до 2014-11-05

Этот ответ помечен как вики сообщества, так как это постоянно развивающаяся тема с множеством нюансов, но очень мало возможных ответов.

Первая проблема заключается в том, что вы должны спросить себя, какой сценарий вы оптимизируете? Как правило, это производительность с одним пользователем на db или масштабированием со многими пользователями на db. Иногда ответы полностью противоположны.

Для оптимизации одного пользователя

Подсказка a TABLELOCK
Удалить индексы, не используемые в удалении, а затем перестроить их позже
Пакет с использованием чего-то типа SET ROWCOUNT 20000 (или любого другого, в зависимости от пространства журнала) и цикла (возможно, с WAITFOR DELAY), пока вы не избавитесь от всего этого (@@ROWCOUNT = 0)
При удалении большого% таблицы просто создайте новую и удалите старую таблицу
Разделите строки для удаления, а затем отпустите парирование. [Подробнее...]

Для оптимизации нескольких пользователей

Блокировки строк подсказки
Используйте кластеризованный индекс
Конструировать кластеризованный индекс, чтобы минимизировать реорганизацию страницы, если удалены большие блоки.
Обновить столбец "is_deleted", затем выполнить фактическое удаление позже в окне обслуживания

Для общей оптимизации

Убедитесь, что FK имеют индексы в своих исходных таблицах.
Обязательно WHERE имеет индексы
Определите строки для удаления в предложении WHERE с таблицей вида или производной таблицы, а не напрямую ссылаясь на таблицу. [Подробнее...]

Ответ 6

(если индексы "не используются", почему они вообще существуют?)

Один из вариантов, который я использовал в прошлом, состоит в том, чтобы выполнять работу в пакетах. Грубым способом было бы использовать SET ROWCOUNT 20000 (или что-то еще) и цикл (возможно, с WAITFOR DELAY), пока вы не избавитесь от всего этого (@@ROWCOUNT = 0).

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

Ответ 7

Проблема в том, что вы недостаточно определили свои условия. То есть что именно вы оптимизируете?

Например, система недоступна для ночного обслуживания и пользователей нет в системе? И вы удаляете большую часть базы данных?

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

Если вы подключены к сети, какая вероятность того, что ваши удаления конфликтуют с действиями пользователя (и является ли пользовательская активность преобладающе прочитанной, обновленной или какой)? Или вы пытаетесь оптимизировать работу пользователя или скорость выполнения вашего запроса? Если вы удаляетесь из таблицы, которая часто обновляется другими пользователями, вам необходимо выполнить пакетную обработку, но с меньшими размерами партии. Даже если вы делаете что-то вроде блокировки таблицы для обеспечения изоляции, это не очень хорошо, если инструкция delete занимает час.

Когда вы определяете свои условия лучше, вы можете выбрать один из других ответов здесь. Мне нравится ссылка в сообщении Роба Сандерса для того, чтобы дозировать вещи.

Ответ 8

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

Удалить партиями.

Если у вас есть таблицы внешнего ключа, которые больше не используются (вы будете удивлены, как часто производственные базы данных заканчиваются старыми таблицами, от которых никто не избавится), избавиться от них или хотя бы разбить соединение FK/PK, Нет смысла печатать таблицу для записей, если она не используется.

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

Ответ 9

Я добавлю еще одно:

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

Ответ 10

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

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

Ответ 11

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

Ответ 12

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

Ответ 13

Если это правда, что UPDATES быстрее DELETES, вы можете добавить столбец статуса DELETED и фильтровать его в своих вариантах. Затем запустите proc по ночам, который действительно удаляет.

Ответ 14

У вас есть внешние ключи с включенной ссылочной целостностью? У вас активированы триггеры?

Ответ 15

Упростите любое использование функций в вашем предложении WHERE! Пример:

DELETE FROM Claims
WHERE dbo.YearMonthGet(DataFileYearMonth) = dbo.YearMonthGet(@DataFileYearMonth)

Эта форма предложения WHERE потребовала 8 минут, чтобы удалить 125 837 записей.

Функция YearMonthGet составила дату с годом и месяцем с даты ввода и установила day = 1. Это было сделано для того, чтобы мы удалили записи по годам и месяцам, но не день месяца.

Я переписал предложение WHERE:

WHERE YEAR(DataFileYearMonth) = YEAR(@DataFileYearMonth)
AND MONTH(DataFileYearMonth) = MONTH(@DataFileYearMonth)

Результат: для удаления данных из 125 837 записей требуется около 38-44 секунд!