MS SQL Server 2005 — самопроизвольно прерывается хранимая процедура

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

WITH RECOMPILE, вероятно, будет самым быстрым решением - используйте SET STATISTICS TIME ON, чтобы узнать, какова на самом деле стоимость перекомпиляции, прежде чем отбрасывать ее из-под контроля.

Если это все еще неприемлемое решение, возможно, лучший вариант — попытаться реорганизовать оператор вставки.

Вы не говорите, используете ли вы UNION или UNION ALL в своем операторе вставки. Я видел, как INSERT INTO с UNION создавали странные планы запросов, особенно в версиях SQL 2005 до SP2.

• Предложение Раджа удалить и воссоздать целевую таблицу с помощью SELECT INTO — один из способов.

• Вы также можете попробовать выбрать каждый из трех исходных запросов в свою собственную временную таблицу, а затем UNION эти временные таблицы вместе во вставке.

• В качестве альтернативы вы можете попробовать комбинацию этих предложений - поместить результаты объединения во временную таблицу с SELECT INTO, а затем вставить из нее в целевую таблицу.

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

Это след перехвата параметров. Да, первый шаг - попробовать RECOMPILE, хотя это не всегда работает так, как вы хотите, в 2005.

Обновление: в любом случае я бы попробовал выполнить перекомпиляцию на уровне оператора для INSERT, так как это может быть проблемой статистики (о да, проверьте, включено ли автоматическое обновление статистики).

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

Я полностью согласен с диагнозом обнюхивания параметров. Если у вас есть входные параметры для SP, которые меняются (или даже если они не меняются), обязательно замаскируйте их локальной переменной и используйте локальную переменную в SP.

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

В SQL Server 2008 вы можете использовать функцию OPTIMIZE FOR UNKNOWN.

Кроме того, если ваш процесс включает в себя заполнение таблицы и последующее использование этой таблицы в другой операции, я рекомендую разбить процесс на отдельные SP и называть их по отдельности WITH RECOMPILE. Я думаю, что планы, сгенерированные в начале процесса, иногда могут быть очень плохими (настолько плохими, что их нельзя завершить), когда вы заполняете таблицу, а затем используете результаты этой таблицы для выполнения операции. Потому что во время первоначального плана таблица сильно отличалась от той, что была после первоначальной вставки.

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

Попробуйте зарегистрировать ход вашего SP, как описано здесь или здесь.

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

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

Когда ваш SP заблокируется, можете ли вы выполнить вставку в table_y?

У вас есть работа по обслуживанию индексов?

Актуальна ли ваша статистика? Один из способов узнать это — проверить предполагаемые и фактические планы запросов на наличие больших вариаций.

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

Мое лучшее предположение:

Вы захотите убедиться, что table_y можно удалить полностью и быстро.

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

Еще одно замечание: попробуйте использовать truncate, если это возможно. он использует меньше ресурсов, чем удаление без предложения where:

truncate table table_y

Кроме того, если в вашей СОБСТВЕННОЙ транзакции произойдет ошибка, это приведет к тому, что все последующие вызовы (очевидно, каждые 5 минут) будут "зависать", если вы не обработаете свою ошибку:

begin tran
begin try
 -- do normal stuff
end try
begin catch
 rollback
end catch
commit

Самая первая ошибка - это то, что даст вам информацию о фактической ошибке. Видеть, как он зависает в ваших собственных последующих тестах, — это просто вторичный эффект.

Если вы делаете эти шаги:

DELETE table_y
INSERT INTO table_y <3 selects unioned together>

Возможно, вы захотите попробовать это вместо этого.

DROP TABLE table_y
SELECT INTO table_y <3 selects unioned together>