Журнал SQL Server заполнен из-за активной транзакции
Я пытаюсь обновить столбец в таблице и получаю следующую ошибку:
The transaction log for database 'STAGING' is full due to 'ACTIVE_TRANSACTION'.
Я пытаюсь запустить следующую инструкцию:
UPDATE [STAGING].[dbo].[Stg_Encounter_Alias]
SET
[valid_flag] = 1
FROM [Stg_Encounter_Alias] Stg_ea
where [ACTIVE_IND] = 1
and [END_EFFECTIVE_DT_TM] > convert(date,GETDATE())
В моей таблице около 18 миллионов строк. И приведенное выше обновление изменит все строки. Размер таблицы 2,5 ГБ. Также БД находится в режиме простого восстановления.
Это то, что я буду делать очень часто на разных столах. Как я могу с этим справиться?
Размер моей базы данных указан ниже
Ниже приведены свойства базы данных !!! Я попытался изменить размер журнала на неограниченный, но он вернулся к значению по умолчанию.
Может ли кто-нибудь сказать мне эффективный способ справиться с этим сценарием?
Если я запускаю партиями:
begin
DECLARE @COUNT INT
SET @COUNT = 0
SET NOCOUNT ON;
DECLARE @Rows INT,
@BatchSize INT; -- keep below 5000 to be safe
SET @BatchSize = 2000;
SET @Rows = @BatchSize; -- initialize just to enter the loop
WHILE (@Rows = @BatchSize)
BEGIN
UPDATE TOP (@BatchSize) [STAGING].[dbo].[Stg_Encounter_Alias]
SET
[valid_flag] = 1
FROM [Stg_Encounter_Alias] Stg_ea
where [ACTIVE_IND] = 1
and [END_EFFECTIVE_DT_TM] > convert(date,GETDATE())
SET @Rows = @@ROWCOUNT;
END;
end
Нет индекса для END_EFFECTIVE_DT_TM. Попробую запустить партиями. На самом деле я выполнял пакетные усечения для одной и той же таблицы, и они отлично работали для того же количества строк. Но как-то не работают обновления !! И я сделал то же обновление в другой БД с той же конфигурацией, и это сработало.
Ответы 2
Вы выполняете обновление в одной транзакции, и это приводит к тому, что журнал транзакций становится очень большим.
Вместо этого выполняйте обновления партиями, скажем, по 50–100 тысяч за раз.
У вас есть указатель по END_EFFECTIVE_DT_TM, который включает ACTIVE_IND и valid_flag? Это улучшило бы производительность.
CREATE INDEX NC_Stg_Encounter_Alias_END_EFFECTIVE_DT_TM_I_
ON [dbo].[Stg_Encounter_Alias](END_EFFECTIVE_DT_TM)
INCLUDE (valid_flag)
WHERE ([ACTIVE_IND] = 1);
Еще одна вещь, которая может значительно повысить производительность, если вы используете Enterprise Edition ИЛИ SQL Server 2016 SP1 или новее (любой выпуск), - это включение data_compression = page для таблицы и ее индексов.
Я обновил код в вопросе выше, и он работает чертовски медленно, если я запускаю его партиями !!! У него была отличная скорость в другой БД, когда я выполнял тот же запрос в другой базе данных !!!
размер вашей партии составляет 2 КБ, что намного меньше, чем я предлагал. Запуск в пакетном режиме должен быть только немного медленнее .... Вы создали индекс? Другая БД может быть на сервере с другой спецификацией оборудования ?? вы изменили запрос, добавив ТОП ... у вас есть первичный ключ?
Фактически это была промежуточная база данных без индексов. Я добавил индекс на end_effective_dt_tm, и это не помогло со скоростью !!! Другая БД имеет точно такую же конфигурацию, как эта. Не уверен, что происходит !! Я также пробовал с 100k, и запрос выполнялся уже 15 минут. без признаков того, что закончить раньше !!!
Вы предварительно измерили свой TLog? Файл TLog находится в быстром хранилище? Есть ли у таблицы первичный ключ? Это куча или кластерный индекс?
У меня есть предложенный вами индекс, и размер пакета, который я использовал, составлял 100000. Запрос выполнялся в течение 15 часов, и я вручную остановил его. Я использовал все настройки по умолчанию. Я не уверен, как проверить быстрое хранилище или предварительно изменить размер Tlog.
Единственное, что я могу предложить, это опубликовать расчетную плоскость запроса без пакетного и пакетного запроса. brentozar.com/pastetheplan
Спасибо за помощь, Митч. Я фактически увеличил размер журнала транзакций и удалил все индексы как промежуточную БД. И его обновление 17 миллионов строк за 24 секунды :)
Хотя я не очень доволен решением, но из-за нехватки времени я иду дальше. Над эффективностью надо работать дальше.
Спасибо, Митч !! Можете ли вы предложить какие-либо изменения памяти, которые я могу сделать, чтобы он мог обрабатывать больше обновлений / вставок