Операция Delete в SQL очень медленная

Ответ 1

Вещи, которые могут привести к замедлению удаления:

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

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

Ответ 2

• Отключить CONSTRAINT

  ALTER TABLE [TableName] NOCHECK CONSTRAINT ALL;

• Отключить индекс

  ALTER INDEX ALL ON [TableName] DISABLE;

• Индекс перестроек

  ALTER INDEX ALL ON [TableName] REBUILD;

• Включить CONSTRAINT

  ALTER TABLE [TableName] CHECK CONSTRAINT ALL;

• Удалить снова

Ответ 3

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

delete top (10) YourTable where col in ('1','2','3','4')
while @@rowcount > 0
    begin
    delete top (10) YourTable where col in ('1','2','3','4')
    end

Ответ 4

Профилактическое действие

Проверьте с помощью SQL Profiler основную причину этой проблемы. Может быть Triggers вызывать задержку в выполнении. Это может быть что угодно. Не забудьте выбрать Database Name и Object Name при запуске Trace, чтобы исключить ненужные запросы сканирования...

Фильтрация имени базы данных

Фильтрация имен таблиц/хранимых процедур/триггеров

Корректирующее действие

Как вы сказали, ваша таблица содержит 260 000 записей... и IN Predicate содержит шесть значений. Теперь каждая запись находится в поиске 260 000 раз для каждого значения в IN Predicate. Вместо этого это должно быть Inner Join, как показано ниже.

Delete K From YourTable1 K
Inner Join YourTable2 T on T.id = K.id

Вставьте значения IN Predicate в Temporary Table или Local Variable

Ответ 5

Если таблица, которую вы удаляете, имеет триггеры BEFORE/AFTER DELETE, что-то там может вызвать задержку.

Кроме того, если у вас есть внешние ключи, ссылающиеся на эту таблицу, могут возникать дополнительные UPDATE или DELETE.

Ответ 6

Возможно, что в других таблицах есть ограничение FK для вашей [таблицы]. Поэтому для проверки ссылочной целостности БД необходимо проверить эти таблицы. Даже если у вас есть все необходимые индексы, соответствующие этим FK, проверьте их количество.

У меня была ситуация, когда NHibernate неправильно создал дублированный FK s в тех же столбцах, но с разными именами (что разрешено SQL Server). Это резко сократило работу оператора DELETE.

Ответ 7

В моем случае статистика базы данных стала поврежденной. Заявление

delete from tablename where col1 = 'v1' 

занимало 30 секунд, хотя не было ни одной соответствующей записи, но

delete from tablename where col1 = 'rubbish'

побежал мгновенно

работает

update statistics tablename

исправил проблему

Ответ 8

Действительно ли [COL] является символьным полем, в котором хранятся числа, или вы можете избавиться от одиночных кавычек вокруг значений? @Alex прав, что IN медленнее, чем =, поэтому, если вы можете это сделать, вам будет лучше:

DELETE FROM [table] WHERE [COL] = '1'

Но лучше все-таки использовать числа, а не строки, чтобы найти строки (числа, похожие на sql):

 DELETE FROM [table] WHERE [COL] = 1

Возможно, попробуйте:

 DELETE FROM [table] WHERE CAST([COL] AS INT) = 1

В любом случае убедитесь, что у вас есть индекс в столбце [COL], чтобы ускорить сканирование таблицы.

Ответ 9

Проверить план выполнения этого оператора удаления. Посмотрите, используется ли поиск индекса. Кроме того, что такое тип данных col?

Если вы используете неправильный тип данных, измените инструкцию обновления (например, от "1" до "1" или "N1" ).

Если используется сканирование индексов, воспользуйтесь подсказкой .

Ответ 10

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

https://support.microsoft.com/en-us/kb/224453

это случай waitresource КЛЮЧ: 16: 72057595075231744 (ab74b4daaf17)

-- First SQL Provider to find the SPID (Session ID)

-- Second Identify problem, check Status, Open_tran, Lastwaittype, waittype, and waittime
-- iMPORTANT Waitresource select * from sys.sysprocesses where spid = 57

select * from sys.databases where database_id=16

-- with Waitresource check this to obtain object id 
select * from sys.partitions where hobt_id=72057595075231744

select * from sys.objects where object_id=2105058535

Ответ 11

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

Ответ 12

После проверки пакета служб SSIS (из-за того, что SQL Server выполнял команды очень медленно), который был установлен на нашем клиенте примерно за 5-4 года до того, как я написал это, я обнаружил, что существуют следующие задачи: 1) вставить данные из файла XML в таблицу с именем [Importbarcdes].

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

3) "удалить из [Importbarcodes]", чтобы очистить таблицу строки, которая была вставлена после того, как файл XML был прочитан задачей пакета служб SSIS.

После быстрой проверки всех операторов (SELECT, UPDATE, DELETE и т.д.) В таблице ImportBarcodes, имеющей только 1 строку, выполнение заняло около 2 минут.

Расширенные события показали много PAGEIOLATCH_EX уведомлений об ожидании.

В таблице отсутствуют индексы и триггеры не зарегистрированы.

После тщательного изучения свойств таблицы на вкладке "Хранилище" и в общем разделе в поле "Пространство данных" было показано более 6 ГБА пространства, выделенного в страницах.

Что случилось:

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

Таким образом, это была основная причина событий ожидания, которые были захвачены сеансом расширенных событий, и медленно выполняемых команд над таблицей.

Запуск ALTER TABLE ImportBarcodes REBUILD устранил проблему, освободив все неиспользуемое пространство. TRUNCATE TABLE ImportBarcodes сделал то же самое, с той лишь разницей, что удалил все файлы подкачки и данные.

Ответ 13

откройте CMD и выполните эти команды

NET STOP MSSQLSERVER
NET START MSSQLSERVER

это перезапустит экземпляр SQL Server. повторите попытку после команды удаления

У меня есть эта команда в пакетном скрипте и запускаю ее время от времени, если я сталкиваюсь с подобными проблемами. Обычный перезапуск ПК не будет таким же, поэтому перезапуск экземпляра является наиболее эффективным способом, если вы столкнулись с некоторыми проблемами с вашим сервером sql.