Опция (перекомпиляция) всегда быстрее; почему?

я столкнулся с странной ситуацией, когда добавление OPTION (RECOMPILE) to my query заставляет его работать через полсекунды, в то время как его пропуск заставляет запрос занимать более пяти минут.

это тот случай, когда запрос выполняется из анализатора запросов или из моей программы 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)

при запуске теста из анализатора запросов я добавляю следующие строки:

declare @feedID int
select @feedID=20

declare @feedDate datetime
select @feedDate='1/2/2009'

при вызове его из моей программы C#, параметры передаются через SqlCommand.Parameters собственность.

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

4 134
sql sql-server sql-server-2008 compilation type-hinting

4 ответа:

бывают случаи, когда с помощью OPTION(RECOMPILE) имеет смысл. По моему опыту единственный раз это жизнеспособный вариант, когда вы используете динамический SQL. Прежде чем вы исследуете, имеет ли это смысл в вашей ситуации, я бы рекомендовал перестроить вашу статистику. Это можно сделать, выполнив следующие действия:

EXEC sp_updatestats

а затем воссоздать свой план выполнения. Это гарантирует, что при создании плана выполнения будет использоваться самая последняя информация.

добавить OPTION(RECOMPILE) перестраивает план выполнения при каждом выполнении запроса. Я никогда не слышал, чтобы это описывалось как creates a new lookup strategy но, может быть, мы просто используем разные термины для одного и того же.

при создании хранимой процедуры (я подозреваю, что вы вызываете ad-hoc sql из .NET, но если вы используете параметризованный запрос, то это заканчивается сохраненным вызовом proc) для SQL Server пытается определить наиболее эффективный план выполнения для этого запроса на основании данных в база данных и параметры, переданные в (нюхают параметр), а затем кэширует этот план. Это означает, что если вы создадите запрос, в котором есть 10 записей в базе данных, а затем выполните его, когда есть 100 000 000 записей, кэшированный план выполнения больше не может быть наиболее эффективным.

в целом - я не вижу никаких причин, что OPTION(RECOMPILE) было бы преимуществом здесь. Я подозреваю, что вам просто нужно обновить статистику и план выполнения. Восстановление статистики может быть важной частью работы DBA в зависимости от вашей ситуации. Если у вас все еще возникают проблемы после обновления статистики, я бы предложил опубликовать оба плана выполнения.

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

125

часто, когда есть резкое различие от запуска к запуску запроса, я нахожу, что это часто одна из 5 проблем.

  1. статистика - статистика устарела. База данных хранит статистику по диапазону и распределению типов значений в различных столбцах таблиц и индексов. Это помогает механизму запросов разработать "план" атаки для того, как он будет выполнять запрос, например тип метода, который он будет использовать для сопоставления ключей между таблицами используя хэш или просматривая весь набор. Вы можете вызвать обновление статистики по всей базе данных или только определенных таблиц или индексов. Это замедляет запрос от одного запуска к другому, потому что, когда статистика устарела, ее вероятный план запроса не является оптимальным для вновь вставленных или измененных данных для того же запроса (более подробно описано ниже). Возможно, не следует сразу обновлять статистику в производственной базе данных, поскольку будут некоторые накладные расходы, замедление и отставание в зависимости о количестве данных для выборки. Вы также можете использовать полное сканирование или выборку для обновления статистики. Если вы посмотрите на план запроса, вы также можете просмотреть статистику по используемым индексам, используя команду DBCC SHOW_STATISTICS (tablename, indexname). Это покажет вам распределение и диапазоны ключей, которые использует план запроса, чтобы основывать свой подход.

  2. ПАРАМЕТР НЮХАЕТ план запроса, который находится в кеше не оптимально для конкретных параметров, которые вы передаете, даже если сам запрос не изменился. Например, если вы передаете параметр, который извлекает только 10 из 1 000 000 строк, то созданный план запроса может использовать хэш-соединение, однако если переданный параметр будет использовать 750 000 из 1 000 000 строк, созданный план может быть сканированием индекса или сканированием таблицы. В такой ситуации вы можете указать оператору SQL использовать опцию ОПЦИЯ (ПЕРЕКОМПИЛЯЦИЯ) или SP для использования С ПЕРЕКОМПИЛЯЦИЕЙ. Чтобы сообщить движку, что это "единый план использования", а не использовать кэшированный план, который, вероятно, не применяется. Нет правила о том, как принять это решение, это зависит от того, как запрос будет использоваться пользователями.

  3. индексы - возможно, что запрос не изменился, но изменение в другом месте, такое как удаление очень полезного индекса, замедлило запрос.

  4. СТРОКИ ИЗМЕНЕНЫ - The строки, которые вы запрашиваете, резко меняются от вызова к вызову. Обычно статистика в этих случаях обновляется автоматически. Однако если вы создаете динамический SQL или вызываете SQL в узком цикле, есть вероятность, что вы используете устаревший план запроса, основанный на неправильном радикальном количестве строк или статистики. Опять же в этом случае ОПЦИЯ (ПЕРЕКОМПИЛЯЦИЯ) полезно.

  5. ЛОГИКА его логика, ваш запрос больше не эффективен, это было отлично подходит для небольшого количества строк, но больше не масштабируется. Это обычно включает в себя более глубокий анализ плана запроса. Например, вы больше не можете делать что-то навалом, но должны разбивать вещи и делать меньшие коммиты, или ваш Кросс-продукт был хорош для меньшего набора, но теперь занимает процессор и память, поскольку он масштабируется больше, это также может быть верно для использования DISTINCT, вы вызываете функцию для каждой строки, ваши ключевые совпадения не используют индекс из-за преобразования типа приведения или нулей или функции... Здесь слишком много возможностей.

В общем, когда вы пишете запрос,вы должны иметь некоторое представление о том, как определенные данные распределяются в вашей таблице. Например, столбец может иметь равномерно распределенное количество различных значений или он может быть перекошен, 80% времени имеют определенный набор значений, независимо от того, будет ли распределение часто меняться с течением времени или будет довольно статичным. Это даст вам лучшее представление о том, как построить эффективный запрос. Но и при отладке производительности запросов есть основание для построения гипотезы о том, почему она медленная или неэффективная.

119

чтобы добавить в отличный список(данный @CodeCowboyOrg) ситуаций, где опция (перекомпиляция) может быть очень полезной,

  1. Таблица Переменных. При использовании табличных переменных не будет никакой предварительно построенной статистики для табличной переменной, что часто приводит к большим различиям между оценочными и фактическими строками в плане запроса. Использование опции (перекомпиляция) для запросов с табличными переменными позволяет создать план запроса, который имеет гораздо лучшую оценку из числа задействованных строк. У меня было особенно критическое использование табличной переменной, которая была непригодна для использования, и которую я собирался отказаться, пока я не добавил опцию(перекомпиляция). Время выполнения шло от нескольких часов до нескольких минут. Это, вероятно, необычно, но в любом случае, если вы используете табличные переменные и работаете над оптимизацией, стоит посмотреть, имеет ли значение опция(перекомпиляция).
22

самые первые действия Перед настройкой запросов-это дефрагментация / перестройка индексов и статистики, иначе вы тратите свое время.

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

в качестве примера : создать индекс idx01_datafeed_trans На datafeed_trans ( feedid, feedDate) включить (acctNo, tradeDate)

Если план стабилен или вы можете стабилизировать его, вы можете выполнить предложение с помощью sp_executesql ("SQL sentence"), чтобы сохранить и использовать фиксированный план выполнения.

Если план нестабилен, вы должны использовать специальный оператор или EXEC('SQL sentence') для оценки и создания плана выполнения каждый раз. (или хранимая процедура "с перекомпиляцией").

надеюсь, что это помогает.

0