OPTION (RECOMPILE) всегда быстрее; Зачем?
Я столкнулся со странной ситуацией, когда добавление OPTION (RECOMPILE) мой запрос приводит к тому, что он выполняется за полсекунды, а если его опустить, запрос занимает больше пяти минут.
Это тот случай, когда запрос выполняется из Query Analyzer или из моей программы на C# через SqlCommand.ExecuteReader(), Звонить (или не звонить) DBCC FREEPROCCACHE или же DBCC dropcleanbuffers не имеет значения; Результаты запроса всегда возвращаются мгновенно с OPTION (RECOMPILE) и больше пяти минут без него. Запрос всегда вызывается с одинаковыми параметрами [ради этого теста].
Я использую SQL Server 2008.
Я довольно удобно писать SQL, но никогда не использовал OPTION Команда в запросе раньше и была незнакома с всей концепцией плана кеширования до сканирования постов на этом форуме. Мое понимание из постов заключается в том, что OPTION (RECOMPILE) это дорогая операция. По-видимому, это создает новую стратегию поиска для запроса. Так почему же тогда последующие запросы, которые опускают OPTION (RECOMPILE) так медленно? Разве последующие запросы не должны использовать стратегию поиска, которая была вычислена на предыдущем вызове, который включал подсказку перекомпиляции?
Весьма необычно иметь запрос, который требует подсказки перекомпиляции при каждом вызове?
Извините за вопрос начального уровня, но я не могу этого сделать.
ОБНОВЛЕНИЕ: меня попросили опубликовать запрос...
select acctNo,min(date) earliestDate
from(
select acctNo,tradeDate as date
from datafeed_trans
where feedid=@feedID and feedDate=@feedDate
union
select acctNo,feedDate as date
from datafeed_money
where feedid=@feedID and feedDate=@feedDate
union
select acctNo,feedDate as date
from datafeed_jnl
where feedid=@feedID and feedDate=@feedDate
)t1
group by t1.acctNo
OPTION(RECOMPILE)
При запуске теста из Query Analyzer я добавляю следующие строки:
declare @feedID int
select @feedID=20
declare @feedDate datetime
select @feedDate='1/2/2009'
При вызове из моей программы на C# параметры передаются через SqlCommand.Parameters имущество.
В целях этого обсуждения вы можете предположить, что параметры никогда не изменяются, поэтому мы можем исключить неоптимальный запах параметра в качестве причины.
6 ответов
Есть времена, что с помощью OPTION(RECOMPILE) имеет смысл. По моему опыту, единственно возможный вариант - использование динамического SQL. Прежде чем вы выясните, имеет ли это смысл в вашей ситуации, я бы порекомендовал пересмотреть статистику. Это можно сделать, выполнив следующее:
EXEC sp_updatestats
А затем воссоздать ваш план выполнения. Это гарантирует, что при создании вашего плана выполнения он будет использовать самую последнюю информацию.
Добавление OPTION(RECOMPILE) перестраивает план выполнения каждый раз, когда выполняется ваш запрос. Я никогда не слышал, чтобы это описывалось как creates a new lookup strategy но, возможно, мы просто используем разные термины для одного и того же.
Когда создается хранимая процедура (я подозреваю, что вы вызываете ad-hoc sql из.NET, но если вы используете параметризованный запрос, то это в конечном итоге является хранимым процедурным вызовом) SQL Server пытается определить наиболее эффективный план выполнения для этого запроса. на основе данных в вашей базе данных и параметров, передаваемых в ( сниффинг параметров), а затем кэширует этот план. Это означает, что если вы создаете запрос, в котором имеется 10 записей в вашей базе данных, а затем выполняете его, когда имеется 100000000 записей, кэшированный план выполнения может перестать быть наиболее эффективным.
Подводя итог, я не вижу причин, по которым OPTION(RECOMPILE) будет полезным здесь. Я подозреваю, что вам просто нужно обновить статистику и план выполнения. Восстановление статистики может быть важной частью работы администратора баз данных в зависимости от вашей ситуации. Если после обновления вашей статистики у вас все еще есть проблемы, я бы предложил опубликовать оба плана выполнения.
И чтобы ответить на ваш вопрос - да, я бы сказал, что для вас лучше всего перекомпилировать план выполнения каждый раз, когда вы выполняете запрос.
Часто, когда между запросами запускается радикальная разница, я обнаруживаю, что это часто одна из 5 проблем.
СТАТИСТИКА - Статистика устарела. База данных хранит статистику по диапазону и распределению типов значений в различных столбцах таблиц и индексов. Это помогает обработчику запросов разработать "план" атаки для того, как он будет выполнять запрос, например, тип метода, который он будет использовать для сопоставления ключей между таблицами, использующими хэш или просмотр всего набора. Вы можете вызвать обновление статистики для всей базы данных или только для определенных таблиц или индексов. Это замедляет запрос от одного прогона к другому, потому что, если статистика устарела, скорее всего, план запроса не является оптимальным для вновь вставленных или измененных данных для того же запроса (подробнее об этом ниже). Возможно, нецелесообразно немедленно обновлять статистику в производственной базе данных, так как будут некоторые накладные расходы, замедление и отставание в зависимости от объема данных для выборки. Вы также можете использовать полное сканирование или выборку для обновления статистики. Если вы посмотрите на план запроса, вы также сможете просмотреть статистику по используемым индексам, например, с помощью команды DBCC SHOW_STATISTICS (имя таблицы, имя индекса). Это покажет вам распределение и диапазоны ключей, которые план запроса использует для своего подхода.
SNIFFING PARAMETER - кэшированный план запроса не является оптимальным для конкретных параметров, которые вы передаете, даже если сам запрос не изменился. Например, если вы передаете параметр, который извлекает только 10 из 1000000 строк, то созданный план запроса может использовать Hash Join, однако если передаваемый параметр будет использовать 750 000 из 1 000 000 строк, созданный план может быть Сканирование индекса или таблицы. В такой ситуации вы можете указать оператору SQL использовать опцию OPTION (RECOMPILE) или SP для использования с RECOMPILE. Чтобы сообщить Механизму, что это "План одноразового использования", а не использовать кэшированный план, который, вероятно, не применяется. Не существует правила о том, как принимать это решение, оно зависит от знания того, как пользователи будут использовать запрос.
ИНДЕКСЫ. Возможно, что запрос не изменился, но изменение в другом месте, такое как удаление очень полезного индекса, замедлило запрос.
ROWS CHANGED - Строки, которые вы запрашиваете, кардинально меняется от вызова к вызову. Обычно статистика автоматически обновляется в этих случаях. Однако, если вы строите динамический SQL или вызываете SQL в узком цикле, есть вероятность, что вы используете устаревший план запросов, основанный на неправильном значительном числе строк или статистике. Опять же, в этом случае OPTION (RECOMPILE) полезен.
ЛОГИКА Это логика, ваш запрос больше не эффективен, он подходит для небольшого количества строк, но больше не масштабируется. Обычно это требует более глубокого анализа плана запросов. Например, вы больше не можете делать что-то массово, но вынуждены делать чанки и делать меньшие коммиты, или ваш кросс-продукт подходит для меньшего набора, но теперь он занимает ЦП и память, поскольку масштабируется больше, это также может быть верно для используя DISTINCT, вы вызываете функцию для каждой строки, ваши совпадения ключей не используют индекс из-за преобразования типа CASTING или NULLS или функций... Здесь слишком много возможностей.
В целом, когда вы пишете запрос, вы должны иметь представление о том, как определенные данные распределяются в вашей таблице. Например, столбец может иметь равномерно распределенное количество различных значений или может быть искажен, 80% времени имеют определенный набор значений, независимо от того, будет ли распределение часто меняться со временем или будет довольно статичным. Это даст вам лучшее представление о том, как создать эффективный запрос. Но и при отладке производительности запросов есть основания для построения гипотезы о том, почему он медленный или неэффективный.
Чтобы добавить в превосходный список (предоставленный @CodeCowboyOrg) ситуации, в которых OPTION(RECOMPILE) может быть очень полезным,
- Переменные таблицы. Когда вы используете табличные переменные, предварительно не будет построена статистика для табличной переменной, что часто приводит к большим различиям между оценочными и фактическими строками в плане запроса. Использование OPTION(RECOMPILE) в запросах с табличными переменными позволяет генерировать план запроса, который имеет гораздо лучшую оценку числа задействованных строк. Я особенно критично использовал табличную переменную, которая была непригодна для использования и которую я собирался отказаться, пока не добавил OPTION(RECOMPILE). Время выполнения ушло от часов до нескольких минут. Это, вероятно, необычно, но в любом случае, если вы используете табличные переменные и работаете над оптимизацией, стоит посмотреть, имеет ли значение OPTION(RECOMPILE).
Самые первые действия перед настройкой запросов - дефрагментировать / перестраивать индексы и статистику, иначе вы тратите время впустую.
Вы должны проверить план выполнения, чтобы убедиться, что он стабилен (то же самое, когда вы меняете параметры), в противном случае вам, возможно, придется создать индекс покрытия (в данном случае для каждой таблицы) (зная систему, вы можете создать такой, который полезно и для других запросов).
в качестве примера: создать индекс idx01_datafeed_trans для datafeed_trans (feedid, feedDate) INCLUDE (acctNo, tradeDate)
если план стабилен или вы можете стабилизировать его, вы можете выполнить предложение с помощью sp_executesql("sql предложение"), чтобы сохранить и использовать фиксированный план выполнения.
если план нестабилен, вы должны использовать специальный оператор или EXEC("sql предложение"), чтобы каждый раз оценивать и создавать план выполнения. (или хранимая процедура "с перекомпиляцией").
Надеюсь, поможет.
Отвечая на этот вопрос, но есть объяснение, которое, кажется, никто не рассматривал.
СТАТИСТИКА - Статистика недоступна или вводит в заблуждение
Если все следующее верно:
- Вероятно, столбцы feedid и feedDate будут сильно коррелированными (например, идентификатор канала более конкретен, чем дата канала, а параметр date является избыточной информацией).
- Нет индекса с обоими столбцами в качестве последовательных столбцов.
- Не существует созданной вручную статистики, охватывающей оба этих столбца.
Тогда sql-сервер может ошибочно полагать, что столбцы некоррелированы, что приводит к более низким, чем ожидалось, оценкам количества элементов для применения обоих ограничений и выбору плохого плана выполнения. В этом случае исправлением будет создание объекта статистики, связывающего два столбца, что не является дорогой операцией.
используется в реальных сценариях генерации слов. Я использовал его для устранения запаха параметров и оптимизации больших запросов. Это может быть ответом на вашу проблему, но есть признаки того, что оптимизация для неизвестного (например, локальных переменных) также может решить проблему.
Я, конечно, не избегаю этого варианта только потому, что несколько лет назад была исправлена временная ошибка. Главный риск