SP занимает 15 минут, но тот же запрос при выполнении возвращает результаты через 1-2 минуты
Так что в основном у меня есть эта относительно длинная хранимая процедура Основной поток выполнения состоит в том, что это SELECTS INTO некоторые данные во временных таблицах объявлены с помощью он # подписать, а затем пропустить курсор через эти таблицы и сгенерировать "промежуточную сумму" в третью временную таблицу, которая создается с использованием CREATE, Затем эта результирующая временная таблица объединяется с другими таблицами в БД, чтобы сгенерировать результат после некоторой группировки и т. Д. Проблема в том, что этот SP работал нормально до сих пор, возвращая результаты через 1-2 минуты. И теперь внезапно это занимает 12-15 минут. Если я извлекаю запрос из SP и выполняю его в студии управления вручную, устанавливая те же параметры вручную, он возвращает результаты через 1-2 минуты, но SP занимает очень много времени. Любая идея, что может случиться. Я попытался сгенерировать планы фактического выполнения как запроса, так и SP, но он не смог сгенерировать его из-за курсора. Есть идеи, почему SP занимает так много времени, а запрос - нет?
10 ответов
Это след от прослушивания параметров. Смотрите здесь для другого обсуждения об этом; SQL плохая производительность плана выполнения хранимых процедур - сниффинг параметров
Существует несколько возможных исправлений, в том числе добавление WITH RECOMPILE к вашей хранимой процедуре, которое работает примерно половину времени.
Рекомендуемое исправление для большинства ситуаций (хотя это зависит от структуры вашего запроса и sproc) - НЕ использовать ваши параметры непосредственно в ваших запросах, а хранить их в локальных переменных, а затем использовать эти переменные в ваших запросах.
Это связано с параметром сниффинг. сначала объявите временную переменную и установите значение входящей переменной равной temp, и используйте переменную temp во всем приложении. Вот пример ниже.
ALTER PROCEDURE [dbo].[Sp_GetAllCustomerRecords]
@customerId INT
AS
declare @customerIdTemp INT
set @customerIdTemp = @customerId
BEGIN
SELECT *
FROM Customers e Where
CustomerId = @customerIdTemp
End
попробуй этот подход
Попробуйте перекомпилировать sproc, чтобы отбросить любой сохраненный план запроса.
exec sp_recompile 'YourSproc'
Затем запустите ваш sproc, стараясь использовать разумные параметры.
Также сравните фактические планы выполнения между двумя методами выполнения запроса.
Возможно, стоит пересчитать любую статистику.
Я бы также посмотрел на анализ параметров. Может быть, процесс должен по-разному обрабатывать параметры.
Обычно я начинаю устранять подобные проблемы, используя "print getdate() + ' - step '". Это помогает мне сузить то, что занимает больше всего времени. Вы можете сравнить, где вы запускаете его из анализатора запросов, и определить причину проблемы.
Для начала это не похоже на то, что SQL все равно будет работать слишком хорошо, основываясь на использовании ряда временных таблиц (может храниться в памяти или сохраняться в базе данных tempdb - что бы SQL Server не счел нужным), и использование курсоров.
Я бы посоветовал посмотреть, сможете ли вы переписать sproc как запрос на основе набора вместо курсорного подхода, который даст лучшую производительность и будет намного проще настраивать и оптимизировать. Очевидно, я не знаю точно, что делает ваш спрок, чтобы дать представление о том, насколько это легко / жизнеспособно для вас.
Относительно того, почему SP занимает больше времени, чем запрос - сказать сложно. Есть ли такая же нагрузка на систему при попытке каждого подхода? Если вы выполняете сам запрос при небольшой нагрузке, это будет лучше, чем при запуске SP во время большой нагрузки.
Кроме того, чтобы запрос действительно выполнялся быстрее, чем SP, необходимо исключить кеширование данных / плана выполнения, что ускоряет выполнение запроса при последующих запусках. Вы можете очистить кеш, используя:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
Но делайте это только на сервере dev/test db, а не на производстве. Затем запустите запрос, запишите статистику (например, из профилировщика). Очистите кеш еще раз. Запустите SP и сравните статистику.
Это из-за обрезки параметров. Но как это подтвердить?
Каждый раз, когда мы предполагаем оптимизировать SP, мы ищем план выполнения. Но в вашем случае вы увидите оптимизированный план из SSMS, потому что он занимает больше времени только тогда, когда он вызывается через код.
Для каждого SP и функции сервер SQL генерирует два оценочных плана из-за опции ARITHABORT. Один для SSMS, а второй - для внешних объектов (ADO Net).
ARITHABORT по умолчанию отключен в SSMS. Итак, если вы хотите проверить, какой именно план запроса использует ваш поставщик услуг при вызове из кода.
Просто включите опцию в SSMS и запустите свой SP, и вы увидите, что SP также займет 12-13 минут из SSMS. ВКЛЮЧИТЬ ARITHABORT EXEC YourSpName ВЫКЛЮЧИТЬ ARITHABORT
Чтобы решить эту проблему, вам просто нужно обновить план оценочного запроса.
Есть несколько способов обновить план оценочного запроса. 1. Обновите статистику таблицы. 2. перекомпилируйте SP 3. ВЫКЛЮЧИТЕ ARITHABORT в SP, чтобы он всегда использовал план запроса, созданный для SSMS (этот параметр не рекомендуется). Дополнительные параметры см. В этой замечательной статье - http://www.sommarskog.se/query-plan-mysteries.html
Я также столкнулся с проблемой, когда мы должны были создать некоторые временные таблицы, а затем манипулировать ими, чтобы вычислить некоторые значения на основе правил и, наконец, вставить вычисленные значения в третью таблицу. Это все, если положить в один SP занимает около 20-25 минут. Таким образом, для дальнейшей оптимизации мы разбили SP на 3 разных SP, и общее время, которое сейчас заняло, составило около 6-8 минут. Просто определите этапы, которые участвуют во всем процессе и как разбить их на разные sp. Конечно, при использовании этого подхода общее время, затрачиваемое на весь процесс, сократится.
Я думаю, это может быть связано с кэшированием. Если вы запустите хранимую процедуру дважды, будет ли она быстрее во второй раз?
Для дальнейшего изучения вы можете запустить их как из среды управления, в хранимой процедуре, так и в версии запроса с включенной опцией показа плана запроса в студии управления, а затем сравнить, какая область занимает больше времени в хранимой процедуре, чем при запуске в качестве запроса.
В качестве альтернативы вы можете опубликовать хранимую процедуру здесь, чтобы люди могли предложить оптимизацию.
1) Когда вы запускаете запрос в первый раз, это может занять больше времени. Еще один момент: если вы используете какой-либо коррелированный подзапрос и если вы жестко кодируете значения, он будет выполнен только один раз. Если вы не программируете его жестко и не выполняете его через процедуру, и если вы пытаетесь извлечь значение из входного значения, тогда это может занять больше времени.
2) В редких случаях это может быть связано с сетевым трафиком, а также там, где у нас не будет согласованности во времени выполнения запроса для одних и тех же входных данных.
Я хотел бы предположить, что проблема связана с типом временной таблицы (префикс #). Эта временная таблица содержит данные для этого сеанса базы данных. Когда вы запускаете его через приложение, временная таблица удаляется и создается заново.
Вы можете обнаружить, что при работе в SSMS он сохраняет данные сеанса и обновляет таблицу, а не создает ее. Надеюсь, это поможет:)