Каковы основные различия между OPTION(ОПТИМИЗАЦИЯ ДЛЯ НЕИЗВЕСТНОГО) и OPTION(RECOMPILE)?

Question

Каковы основные различия между OPTION(ОПТИМИЗАЦИЯ ДЛЯ НЕИЗВЕСТНОГО) и OPTION(RECOMPILE)?

Я столкнулся с классическими проблемами перехвата параметров в SQL Server 2012. Основываясь на некоторых исследованиях, я нашел несколько вариантов решения этой проблемы. Два варианта, которые мне нужны, чтобы понять разницу между OPTION(OPTIMIZE FOR UNKNOWN) против OPTION(RECOMPILE),

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

Так что я использую лучшее доступное решение, каковы реальные различия между этими двумя вариантами?

Будет OPTION(OPTIMIZE FOR UNKNOWN) повторно использовать кеш вместо перекомпиляции каждый раз?

31

sql-server t-sql sql-server-2012 parameter-sniffing

Источник

user4967389 04 ноя '16 в 22:54

3 ответа

Другие вопросы по тегам sql-server t-sql sql-server-2012 parameter-sniffing

user4116017 05 ноя '16 в 11:33 2016-11-05 11:33 · Answer 1 · 2016-11-05 11:33

Будет OPTION(OPTIMIZE FOR UNKNOWN) повторно использовать кеш вместо перекомпиляции каждый раз?

Да, это будет.

Есть два основных различия между OPTION(OPTIMIZE FOR UNKNOWN) а также OPTION(RECOMPILE) как видно из этой цитаты из MSDN:

OPTIMIZE FOR UNKNOWN
Указывает оптимизатору запросов использовать статистические данные вместо начальных значений для всех локальных переменных при компиляции и оптимизации запроса, включая параметры, созданные с принудительной параметризацией.

RECOMPILE
Указывает ядру СУБД SQL Server отказаться от плана, сгенерированного для запроса после его выполнения, заставляя оптимизатор запросов перекомпилировать план запроса при следующем выполнении того же запроса. Без указания RECOMPILEкомпонент Database Engine кэширует планы запросов и использует их повторно. При составлении планов запросов RECOMPILE подсказка запроса использует текущие значения любых локальных переменных в запросе и, если запрос находится внутри хранимой процедуры, текущие значения передаются любым параметрам.

Итак, два основных различия:

Кэширование (или нет) плана запроса.

Обычно сгенерированный план запроса кэшируется и используется повторно. OPTIMIZE FOR UNKNOWN не влияет на эту особенность двигателя. RECOMPILE подавляет эту функцию и говорит движку отказаться от плана и не помещать его в кеш.

Использование (или нет) фактических значений параметров во время генерации плана.

Обычно оптимизатор "вынюхивает" значения параметров и использует эти значения при создании плана. OPTIMIZE FOR UNKNOWN подавляет эту функцию и говорит движку обрабатывать все параметры, как если бы их значения были неизвестны. В оптимизаторе есть встроенные правила и эвристика, как использовать доступную статистику для различных критериев фильтрации. См. Оптимизировать для... Посредственный? Больше подробностей. Обычно сниффинг параметров используется при первом запуске запроса / хранимой процедуры и использует значения параметров во время первого запуска. Сгенерированный план кэшируется и позже может быть использован повторно.

Здесь следует помнить одну неочевидную вещь: в обоих случаях (нормально, без подсказок и OPTIMIZE FOR UNKNOWN подсказка) сгенерированный план должен быть действительным и давать правильный результат для любого возможного значения параметра. Он адаптирован к сниффинным значениям, которые использовались во время первого запуска в обычном случае / без подсказки; он не привязан к какому-либо конкретному значению в OPTIMIZE FOR UNKNOWN случай, но это все еще допустимо, если параметр изменяется позже любым способом.

Это важно и мешает оптимизатору выполнять определенные преобразования и упрощения плана.

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

Этот эффект в основном виден для запросов условий динамического поиска. Например:

SELECT ...
FROM T
WHERE
    (@ParamSomeID = 0)
    OR
    (
        @ParamSomeID = -1
        AND
        T.SomeID NOT IN
        (
            SELECT OtherTable.SomeID
            FROM OtherTable
        )
    )
    OR
    (
        T.SomeID IN
        (
            SELECT OtherTable.SomeID
            FROM OtherTable
            WHERE OtherTable.SomeID = @ParamSomeID
        )
    )
OPTION(RECOMPILE)

Если @ParamSomeID является 0 оптимизатор будет обрабатывать запрос так, как если бы он не WHERE пункт вообще. План не будет упоминать OtherTable совсем.

Если @ParamSomeID является -1, план присоединится T в OtherTable используя Left Anti Semi Join и будет сканировать весь OtherTable,

Если @ParamSomeID скажем, 5, план будет выполнять поиск индекса по уникальному индексу на OtherTable и читать только одну строку из OtherTable,

Без OPTION(RECOMPILE) такого рода упрощение и преобразование не произошло бы.

Еще одна причина использовать OPTION(RECOMPILE) когда ваше распределение данных очень искажено. Например, у вас есть таблица с 1М строк. Один столбец имеет значение 0 в строках 990K и значения от 1 до 10 в строках 1K. Запросы, которые фильтруют в этом столбце, должны иметь разные планы в зависимости от фактического значения фильтра.

В обоих примерах выше OPTIMIZE FOR UNKNOWN будет генерировать посредственный план.

user105929 05 ноя '16 в 07:55 2016-11-05 07:55 · Answer 2 · 2016-11-05 07:55

Будет ли OPTION(OPTIMIZE FOR UNKNOWN) повторно использовать кэш вместо перекомпиляции каждый раз?

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

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

У вас также есть другие варианты в вашем распоряжении:

План руководства
Хранилище запросов (только в SQL 2016), поскольку оно позволяет "закрепить" предпочтительный план выполнения

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

user611545 05 ноя '16 в 04:15 2016-11-05 04:15 · Answer 3 · 2016-11-05 04:15

Я использовал оба. OPTION(OPTIMIZE FOR UNKNOWN) была использована для тяжелой поисковой хранимой процедуры, которая принимала множество параметров. Были определенные условия, неизвестные мне (статистика и что-то еще), которые могли бы отбросить оптимизацию, запрос был обыденным, однако это вызвало бы серьезные задержки (и даже тайм-аут). OPTION(OPTIMIZE FOR UNKNOWN) решил эту проблему, но не был идеальным.

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

Суть хранимой процедуры привела к появлению решения "результат по мере ввода", в котором каждые три нажатия клавиш запускают поиск в БД, а результаты отображаются в раскрывающемся списке.

В итоге я удалил OPTION(OPTIMIZE FOR UNKNOWN) и просто добавил EXEC sp_recompile<sp> к моей ночной работе по обслуживанию, и это решило все проблемы.