Сохраненная процедура медленна при вызове из Интернета, быстро из 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 секунды, и операторы в строке с одинаковыми значениями были немедленно возвращены.
![executionplan]()
После некоторого поиска в 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
он снова работал как шарм.