CREATE TRIGGER занимает более 30 минут на SQL Server 2005
В нашей живой / производственной базе данных я пытаюсь добавить триггер в таблицу, но безуспешно. Я пробовал несколько раз, но на выполнение оператора создания триггера ушло более 30 минут, и я его отменил.
Таблица - это та, которая часто читается / записывается парой разных процессов. Я отключил запланированные задания, которые обновляют таблицу, и пытался иногда, когда в таблице меньше активности, но я не могу остановить все, что обращается к таблице.
Я не верю, что проблема связана с самим оператором создания триггера. Оператор создания триггера был успешным и быстрым в тестовой среде, и триггер работает правильно, когда строки вставляются / обновляются в таблицу. Хотя, когда я создал триггер в тестовой базе данных, таблица не была загружена, и в ней было значительно меньше строк, что отличается от реальной / производственной базы данных (100 против 13000000+).
Вот оператор создания триггера, который я пытаюсь запустить
CREATE TRIGGER [OnItem_Updated]
ON [Item]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF update(State)
BEGIN
/* do some stuff including for each row updated call a stored
procedure that increments a value in table based on the
UserId of the updated row */
END
END
Могут ли возникнуть проблемы с созданием триггера для таблицы во время обновления строк или если в ней много строк?
В SQLServer триггеры по умолчанию создаются включенными. Можно ли сделать триггер отключенным по умолчанию?
Есть другие идеи?
Ответы 5
Это странно. Триггеру AFTER UPDATE не нужно проверять существующие строки в таблице. Я полагаю, возможно, вы не можете получить блокировку таблицы для добавления триггера.
Вы можете попробовать создать триггер, который практически ничего не делает. Если вы не можете его создать, значит, проблема в блокировке. Если можете, то вы можете отключить этот триггер, добавить желаемый код в тело и включить его. (Я не верю, что вы можете отключить триггер во время создания.)
Я считаю, что триггер CREATE попытается заблокировать всю таблицу.
Если у вас много активности на этой таблице, возможно, придется ждать долго, и вы можете создать тупик.
Для любых изменений схемы вы действительно должны получить всех участников базы данных.
Тем не менее, есть соблазн внести «небольшие» изменения с активными соединениями. Вы должны взглянуть на блокировки / соединения, чтобы увидеть, где находится конфликт блокировки.
Проблема может быть не в самой таблице, а в системных таблицах, которые необходимо обновить, чтобы создать триггер. Если вы выполняете какой-либо другой тип DDL в рамках своих обычных процессов, они могут его задержать.
Используйте sp_who, чтобы узнать, откуда исходит блок, а затем исследуйте его.
Частью проблемы может быть и сам триггер. Может ли ваш триггер случайно обновить все строки таблицы? Существует большая разница между 100 строками в тестовой базе данных и 13000000 строк. Очень плохая идея разрабатывать код для такого небольшого набора, когда у вас такой большой набор данных, что вы не можете предсказать производительность. SQL, который отлично работает для 100 записей, может полностью заблокировать систему с миллионами на часы. Вы действительно хотите знать это при разработке, а не при продвижении в продакшн.
Вызов сохраненной процедуры в триггере обычно очень плохой выбор. Это также означает, что вам нужно перебирать записи, что является еще худшим выбором для триггера. Триггеры должны всегда учитывать множественные вставки / обновления или удаления записей. Если кто-то вставляет 100000 строк (что не маловероятно, если у вас 13000000 записей), тогда цикл через хранимую процедуру на основе записей может занять несколько часов, заблокировать всю таблицу и заставить всех пользователей выследить разработчика и убить (или, по крайней мере, искалечить) ему, потому что они не могут выполнить свою работу.
Я бы даже не стал рассматривать этот триггер на продукте, пока вы не протестируете набор записей, аналогичный по размеру продукту.
Мой друг Деннис написал эту статью, в которой показано, почему тестирование небольшого объема информации, когда у вас есть большой объем информации, может создать трудности на prd, которых вы не заметили на dev: http://blogs.lessthandot.com/index.php/DataMgmt/?blog=3&title=your-testbed-has-to-have-the-same-volume&disp=single&more=1&c=1&tb=1&pb=1#c1210
Запустите DISABLE TRIGGER triggername ON tablename перед изменением триггера, затем включите его с помощью ENABLE TRIGGER triggername ON tablename
Выполнение функции sp_who во время выполнения оператора создания триггера помогло мне решить проблему. Был старый сеанс с другого сервера, который был приостановлен, пока у него была блокировка стабильности схемы для таблицы, поэтому я не мог создать триггер.