Как ускорить удаление из большой таблицы базы данных?

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

Эта статья поддержки MS может быть интересна: Как решить проблемы с блокировкой, вызванные эскалацией блокировок в SQL Server :

delete_more:
     DELETE TOP(500) FROM LogMessages WHERE LogDate < '2/1/2002'
IF @@ROWCOUNT > 0 GOTO delete_more

Вы можете достичь того же результата, используя SET ROWCOUNT, как предлагает Митч, но согласно MSDN он не будет поддерживаться для DELETE и некоторых других операций в будущих версиях SQL Server:

Использование SET ROWCOUNT не повлияет на операторы DELETE, INSERT и UPDATE в будущих выпусках SQL Server. Избегайте использования SET ROWCOUNT с операторами DELETE, INSERT и UPDATE в новых разработках и запланируйте изменение приложений, которые в настоящее время его используют. Для аналогичного поведения используйте синтаксис TOP. Дополнительные сведения см. В разделе TOP (Transact-SQL).

Вы можете создать новые файлы, скопировать все, кроме «удаленных» строк, а затем поменять местами имена в таблицах. Наконец, отбросьте старые таблицы. Если вы удаляете большой процент записей, это может быть быстрее.

Другое предложение - переименовать таблицу и добавить столбец статуса. Когда status = 1 (удален), вы не хотите, чтобы он отображался. Таким образом, вы затем создаете представление с тем же именем, что и исходная таблица, которая выбирает из таблицы, когда статус равен нулю или = 0 (в зависимости от того, как вы его реализуете). Удаление отображается немедленно для пользователя, и каждые пятнадцать минут может выполняться фоновое задание, удаляющее записи, которое выполняется без ведома никого, кроме dbas.

Если вы используете SQL 2005 или 2008, возможно, вам поможет «изоляция моментальных снимков». Он позволяет данным оставаться видимыми для пользователей во время обработки базовой операции обновления данных, а затем показывает данные сразу после их фиксации. Даже если удаление занимает 30 минут, ваши приложения все это время останутся в сети.

Вот краткое руководство по блокировке снимков:

https://www.mssqltips.com/tip.asp?tip=1081

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

Вы можете удалять небольшие партии, используя цикл while, примерно так:

DELETE TOP (10000) FROM LogMessages WHERE LogDate < '2/1/2002'
WHILE @@ROWCOUNT > 0
BEGIN
    DELETE TOP (10000) FROM LogMessages WHERE LogDate < '2/1/2002'
END

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

DELETE FROM TABLE WHERE ROW_TO_DELETE = 'OK';
GO

-->

INSERT INTO #TABLE WHERE NOT ROW_TO_DELETE = 'OK';
TRUNCATE TABLE;
INSERT INTO TABLE (SELECT * FROM #TABLE);
GO

вот решение вашей проблемы.

DECLARE @RC AS INT
SET @RC = -1

WHILE @RC <> 0
BEGIN
    DELETE TOP(1000000) FROM [Archive_CBO_ODS].[CBO].[AckItem] WHERE [AckItemId] >= 300
    SET @RC = @@ROWCOUNT
    --SET @RC = 0
END