Хранимая процедура висит, казалось бы, без объяснения причин

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

замечания:

  • Копирование кода в окно запроса дает результат запроса в 1 секунду
  • SP занимает > 2,5 минуты, пока я не отменил его
  • Монитор активности показывает, что он не блокируется ничем, он просто выполняет SELECT.
  • Запуск sp_recompile на SP не помогает
  • Отбрасывание и повторное создание SP не помогает
  • Установка LOCK_TIMEOUT до 1 секунды не помогает

Что еще может быть?

UPDATE. Я предполагаю, что это связано с параметризацией. Я использовал процедуру Адама Маханика, чтобы узнать, какой подзапрос висит. Я нашел что-то неправильно с планом запроса благодаря намеку Мартина Смита. Я узнал о EXEC ... WITH RECOMPILE, OPTION(RECOMPILE) для подзапросов в SP и OPTION (OPTIMIZE FOR (@parameter = 1)), чтобы атаковать параметр sniffing. Я до сих пор не знаю, что было не так в этом конкретном случае, но я вышел из этой битвы, приправленный и гораздо лучше вооруженный. Я знаю, что делать в следующий раз. Итак, здесь точки!

Ответы

Ответ 2

Я думаю, что это связано с параметризацией и необходимостью параметризации ваших входных параметров локальным параметрам в SP. Добавление с повторной компиляцией приводит к тому, что план выполнения должен быть воссоздан и устраняет большую часть преимуществ наличия SP. Мы использовали "Перекомпилировать" по многим отчетам, пытаясь устранить эту проблему с зависанием, и это иногда приводило к зависанию SP, который мог быть связан с другими блокировками и/или транзакциями, обращающимися к тем же таблицам одновременно. См. Эту ссылку для получения более подробной информации Параметр Sniffing (или Spoofing) в SQL Server и измените свой SP на следующее, чтобы исправить это:

СОЗДАТЬ ПРОЦЕДУЮ [dbo]. [SPNAME] @p1 int, @p2 int AS

DECLARE @localp1 int, @localp2 int

SET @localp1 = @p1 SET @localp2 = @p2

Ответ 3

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

DBCC DROPCLEANBUFFERS 

DBCC FREEPROCCACHE

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

msdn.microsoft.com

Ответ 4

Спасибо за все комментарии.

Я все еще не нашел ответа, но я опубликую здесь прогресс.

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

  • В обычном окне запроса (зависание, идентифицированное с помощью sp_whoisactive) выполняется зависание части запроса (3 секунды)
  • Нет блокировок, в соответствии с Activity Monitor SPID делает SELECT
  • Сохраненная процедура работает более 6 часов без ответа
  • Параметры, переданные SP и переменные, объявленные в окне, одинаковы

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

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

Ответ 5

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

Ответ 6

Первое, что нужно сначала.

Пожалуйста, проверьте, есть ли какие-либо незафиксированные транзакции. Начальная транзакция без "COMMIT TRANSACTION"