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

Мы столкнулись с проблемой sp.

У нас есть довольно простой sp, содержащий объявленную таблицу и пару внешних объединений, которые в конце возвращают от 20 до 100 строк.

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

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

Мы ничего не поняли и вошли в Management Studio в производственную базу данных и запустили sp, она выполняется менее 1 секунды.

Таким образом, при запуске из нашего приложения он чрезвычайно медленный и вызывает тайм-ауты, когда он запускался из Management Studio, он очень быстр и не занимает больше секунды.

Любой, кто хорошо знает SQL Server 2005, может дать нам подсказку относительно этого?

Ответ 1

Я думаю, что ваша проблема может быть "Параметрическое обнюхивание". Это процесс, когда среда выполнения SQL Server "обнюхивает" значения параметров sp во время компиляции или перекомпилирует, чтобы генерировать более быстрые планы выполнения. Но иногда он получает комбинацию параметров, которые вместе с текущими данными возвращают sp, делает действительно медленным sp.

Есть пара хороших объяснений. Поиск в Stackoverflow. Это одно хорошо: http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

Одним из возможных решений является создание локальных переменных в sp и установка для них значений входящих параметров. Затем используйте только локальные переменные в sp.

CREATE PROCEDURE [dbo].spTest
  @FromDate as DATETIME
AS
BEGIN
  DECLARE @FromDate_local as DATETIME
  SET @FromDate_local = '2009-01-01'

  SET @FromDate_local = @FromDate
  ...
  SELECT * FROM TestTbl WHERE FromDate >= @FromDate_local
END

Ответ 2

Перекомпиляция - это тупой инструмент. Это, скорее всего, параметр sniffing

Посмотрите на этот вопрос: Неисправность хранимой процедуры для конкретного пользователя

Ответ 3

Thanx для ответов, ребята, кажется, что sp_recompile решил проблему, по крайней мере, все было запутано, так как я выполнил ее вчера днем, буду следить за ней и посмотреть, будет ли она быстро.

Не понимайте, однако, что перекомпилировать не удалось, когда я изменил содержимое внутри sp?

Ответ 4

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

Ответ 5

Вы можете быть уверены, что ситуация с тупиком не возникает? Пробег из студии управления будет изолирован, так как от приложения это может быть частью более крупной транзакции.