Сохраненная процедура медленна при вызове из Интернета, быстро из Management Studio

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

Я запустил Sql Profiler и проследил за этим тайм-аутом и, наконец, узнал об этом:

При выполнении операторов из MS SQL Management Studio с теми же аргументами (на самом деле, я скопировал вызов процедуры из профиля профиля sql и запустил его): он заканчивается через 5-6 секунд.
Но при вызове из веб-приложения он занимает более 30 секунд (в трассировке), поэтому моя веб-страница фактически истекает к тому времени.

Помимо того, что у моего веб-приложения есть свой собственный пользователь, каждая вещь такая же (та же база данных, соединение, сервер и т.д.), Я также попробовал запустить запрос непосредственно в студии с пользователем веб-приложения и не занимает больше 6 секунд.

Как узнать, что происходит?

Я предполагаю, что это не имеет никакого отношения к тому, что мы используем BLL > DAL-уровни или Table-адаптеры, поскольку трассировка ясно показывает, что задержка находится в фактической процедуре. Это все, что я могу придумать.

EDIT Я обнаружил в эту ссылку, что ADO.NET устанавливает ARITHABORT в true - это хорошо в течение большей части времени, но иногда это происходит, и предлагаемая работа заключается в добавлении опции with recompile к сохраненному процессу. В моем случае это не работает, но я подозреваю, что это очень похоже на это. Кто-нибудь знает, что еще делает ADO.NET или где я могу найти спецификацию?

Ответ 1

У меня была аналогичная проблема, возникшая в прошлом, поэтому я очень хочу увидеть решение этого вопроса. Aaron Bertrand комментирует OP, вызвав время запроса при выполнении из Интернета, но сверхбыстро, когда выполняется из SSMS, и хотя вопрос не является дубликатом, ответ могут очень хорошо относиться к вашей ситуации.

По сути, похоже, что SQL Server может иметь поврежденный план выполнения кэширования. Вы нажимаете плохой план на своем веб-сервере, но SSMS приземляется по другому плану, поскольку на флагом ARITHABORT есть другая настройка (которая иначе не повлияет на ваш конкретный запрос/сохраненный процесс).

См. ADO.NET, вызывающий хранимую процедуру T-SQL, вызывает исключение SqlTimeoutException для другого примера с более полным объяснением и разрешением.

Ответ 2

Я также испытываю, что запросы медленно запускались из Интернета и быстро работали в SSMS, и я в конце концов узнал, что проблема была что-то, называемое параметром sniffing.

Исправление для меня заключалось в том, чтобы изменить все параметры, которые используются в sproc для локальных переменных.

например. изменить:

ALTER PROCEDURE [dbo].[sproc] 
    @param1 int,
AS
SELECT * FROM Table WHERE ID = @param1

ALTER PROCEDURE [dbo].[sproc] 
    @param1 int,
AS
DECLARE @param1a int
SET @param1a = @param1
SELECT * FROM Table WHERE ID = @param1a

Кажется странным, но он исправил мою проблему.

Ответ 3

Может ли быть, что некоторые другие вызовы БД, сделанные до того, как веб-приложение вызывает SP, удерживают транзакцию открытой? Это может послужить причиной того, что этот SP будет ждать при вызове веб-приложения. Я говорю, что изолировать вызов в веб-приложении (поставить его на новую страницу), чтобы убедиться, что некоторые предыдущие действия в веб-приложении вызывают эту проблему.

Ответ 4

Не спам, но, как мы надеемся, полезное решение для других, наша система увидела высокую таймауты.

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

В конечном счете было большее количество SP, которые были тайм-аутом, многие из которых никогда не делали этого раньше, используя DBCC DROPCLEANBUFFERS и DBBC FREEPROCCACHE, частота инцидентов таймаутов резко упала - все еще есть отдельные случаи, некоторые, где я подозреваю, что регенерация плана занимает какое-то время, а некоторые, где СП являются действительно недостаточно эффективными и нуждаются в повторной оценке.

Ответ 5

Просто перекомпиляция хранимой процедуры (функция таблицы в моем случае) работала для меня

Ответ 6

как @Zane сказал, что это может быть связано с параметризацией. Я испытал такое же поведение, и я взглянул на план выполнения процедуры и все операторы sp в строке (скопировал все инструкции из процедуры, объявил параметры как переменные и приписал те же значения для переменной, что и параметры были). Однако план исполнения выглядел совершенно иначе. Выполнение sp выполнялось 3-4 секунды, и операторы в строке с одинаковыми значениями были немедленно возвращены.

После некоторого поиска в Google я нашел интересным прочитать об этом поведении: Медленное приложение, быстрое в SSMS?

При компиляции процедуры SQL Server не знает, что значение @fromdate изменяется, но компилирует процедуру в предположении, что @fromdate имеет значение NULL. Поскольку все сравнения с NULL дают UNKNOWN, запрос не может возвращать любые строки вообще, если @fromdate все еще имеет это значение во время выполнения. Если SQL Server примет входное значение как окончательную истину, он может построить план с только константным сканированием, который вообще не обращается к таблице (запустите запрос SELECT * FROM Orders WHERE OrderDate > NULL, чтобы увидеть пример этого), Но SQL Server должен генерировать план, который возвращает правильный результат независимо от того, какое значение @fromdate имеет во время выполнения. С другой стороны, нет никаких обязательств по составлению плана, который является лучшим для всех ценностей. Таким образом, поскольку предположение состоит в том, что никакие строки не будут возвращены, SQL Server устанавливает для поиска индекса.

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

create procedure dbo.procedure
    @dateTo datetime = null
begin
    if (@dateTo is null)
    begin
        select @dateTo  = GETUTCDATE()
    end

    select foo
    from dbo.table
    where createdDate < @dateTo
end

После того, как я изменил его на

create procedure dbo.procedure
    @dateTo datetime = null
begin
    declare @to datetime = coalesce(@dateTo, getutcdate())

    select foo
    from dbo.table
    where createdDate < @to
end

он снова работал как шарм.