T-SQL Оптимизировать DELETE из многих записей

Ответ 1

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

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

Есть две вещи, которые вы можете сделать в зависимости от concurrency пользователей и времени удаления

• Если вы можете гарантировать, что никто не будет читать или писать, когда вы удаляете, вы можете заблокировать таблицу в эксклюзивном режиме и удалить (требуется только одна блокировка от движка) и отпустить блокировку
• Вы можете использовать пакетные удаления, вы должны сделать script с помощью курсора, который предоставляет строки, которые вы хотите удалить, и вы начинаете трансакцию и фиксируете каждый X-записей (в идеале 5000), поэтому вы можете хранить короткие шорты транзакций и не принимать много замков

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

Ответ 2

Чтобы расширить предложение о выпуске пакета, я предлагаю вам делать это гораздо чаще (каждые 20 секунд, возможно) - пакетные удаления просты:

WHILE 1 = 1 
    BEGIN 
        DELETE TOP ( 4000 )
        FROM    YOURTABLE
        WHERE   YourIndexedDateColumn < DATEADD(MINUTE, -20, GETDATE()) 
        IF @@ROWCOUNT = 0 
            BREAK    
    END

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

Что касается вашей таблицы, тем не менее, таблица с таким большим количеством трафика, который я ожидал бы увидеть на очень быстром массиве RAID-массива 10/, возможно, даже в секционированном виде, - это ваши диски? Являются ли ваши журналы транзакций на разных дисках в ваших файлах данных? - они должны быть

РЕДАКТИРОВАТЬ 1 - Ответ на ваш комментарий

Добавить базу данных в SIMPLE-восстановление:

ALTER DATABASE Database Name SET RECOVERY='SIMPLE'

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

Если в вашей базе данных есть таблицы, в которых вы не можете потерять данные, вам нужно оставить свою базу данных в режиме полного восстановления (т.е. любая транзакция будет регистрироваться (и, надеюсь, покрашена в *.trn файлы по планам обслуживания серверов) Как я уже сказал в моем вопросе, нет ничего, что остановило бы вас иметь две базы данных: 1 в FULL и 1 в SIMPLE. База данных FULL была бы передними таблицами, в которых вы не можете потерять какие-либо данные (т.е. Вы могли бы применять журналы транзакций к восстановить данные в определенное время), а база данных SIMPLE будет использоваться для этих массивных таблиц с высоким трафиком, которые вы можете позволить потерям данных в случае сбоя.

Все это имеет значение, предполагая, что вы создаете полные файлы (*.bak) каждую ночь и сбрасываете свои файлы журналов в файлы *.trn каждые полчаса или около того).

Что касается вашего вопроса о индексе, обязательно проиндексируйте свой столбец даты, если вы проверите свой план выполнения и увидите любой "ТАБЛИЧНЫЙ СКАНИ" - это будет индикатором недостающего индекса.

Ваш столбец даты, который я предполагаю, является DATETIME с ограничением, устанавливающим значение DEFAULT для getdate()?

Вы можете обнаружить, что вы получаете лучшую производительность, заменив это на BIGINT YYYYMMDDHHMMSS, а затем примените индекс CLUSTERED к этому столбцу - обратите внимание, однако, что вы можете иметь только один кластерный индекс для каждой таблицы, поэтому, если в этой таблице уже есть один, Вам нужно будет использовать некластеризованный индекс. (в случае, если вы этого не знали, кластеризованный индекс в основном сообщает SQL хранить информацию в этом порядке, а это означает, что при удалении строк > 20 минут SQL может буквально удалять материал последовательно, а не перескакивать со страницы на страницу.

Ответ 3

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