Правило о том, когда использовать опцию WITH RECOMPILE
Я понимаю, что опция WITH RECOMPILE вынуждает оптимизатор перестраивать план запроса для хранимых процедур, но когда вы хотите, чтобы это произошло?
Каковы некоторые практические правила, когда использовать опцию WITH RECOMPILE, а когда нет?
Каковы эффективные накладные расходы, связанные с тем, чтобы просто положить их на каждого спрока?
5 ответов
Как уже говорили другие, вы не хотите просто включать WITH RECOMPILE в каждом хранимом процессе по привычке. Тем самым вы исключите одно из основных преимуществ хранимых процедур: тот факт, что он сохраняет план запроса.
Почему это потенциально имеет большое значение? Вычисление плана запроса намного более интенсивно, чем компиляция обычного процедурного кода. Поскольку синтаксис оператора SQL задает только то, что вы хотите, а не (как правило), как его получить, это дает базе данных большую степень гибкости при создании физического плана (то есть пошаговые инструкции на самом деле собирать и изменять данные). Существует множество "хитростей", которые может выполнять препроцессор запросов к базе данных, и выбор, который он может сделать - какой порядок объединения таблиц, какие индексы использовать, применять ли WHERE пункты до или после объединений и т. д.
Для простого оператора SELECT это может не иметь значения, но для любого нетривиального запроса база данных потратит некоторое серьезное время (измеренное в миллисекундах, в отличие от обычных микросекунд), чтобы придумать оптимальный план. Для действительно сложных запросов он не может даже гарантировать оптимальный план, он должен просто использовать эвристику, чтобы придумать довольно хороший план. Таким образом, вынуждая его перекомпилировать каждый раз, вы говорите ему, что он должен проходить этот процесс снова и снова, даже если план, который он получил раньше, был совершенно хорошим.
В зависимости от поставщика, должны быть автоматические триггеры для перекомпиляции планов запросов - например, если статистические данные в таблице значительно изменяются (например, гистограмма значений в определенном столбце начинается равномерно, с течением времени становится сильно искаженной), затем БД должна это заметить и перекомпилировать план. Но, вообще говоря, разработчики базы данных будут в целом умнее, чем вы.
Как и во всем, что связано с производительностью, не делайте снимки в темноте; Выясните, где находятся узкие места, которые стоят 90% вашей производительности, и сначала решите их.
Размещение его в каждой хранимой процедуре НЕ является хорошей идеей, поскольку составление плана запроса является относительно дорогой операцией, и вы не увидите никакой выгоды от кэширования и повторного использования планов запросов.
Случай динамического предложения where, созданного внутри хранимой процедуры, может быть обработан с помощью sp_executesql выполнять TSQL вместо добавления WITH RECOMPILE к хранимой процедуре.
Другое решение (начиная с SQL Server 2005) - использовать подсказку с конкретными параметрами, используя OPTIMIZE FOR намек. Это хорошо работает, если значения в строках являются статическими.
SQL Server 2008 представил малоизвестную функцию под названием "OPTIMIZE FOR UNKNOWN":
Этот совет указывает оптимизатору запросов использовать стандартные алгоритмы, которые он всегда использовал, если значения параметров не были переданы в запрос вообще. В этом случае оптимизатор будет просматривать все доступные статистические данные, чтобы определить, какими должны быть значения локальных переменных, используемых для генерации плана запроса, вместо просмотра значений конкретных параметров, которые были переданы в запрос приложением.
Как правило, гораздо лучшая альтернатива WITH RECOMPILE является OPTION(RECOMPILE)как вы можете видеть из объяснения ниже, взятого из ответа на этот вопрос здесь
Когда возникает проблема чувствительности к параметрам, на форумах и сайтах вопросов и ответов часто советуют "использовать перекомпиляцию" (при условии, что другие опции настройки, представленные ранее, не подходят). К сожалению, этот совет часто неверно интерпретируется как добавление опции WITH RECOMPILE к хранимой процедуре.
Использование WITH RECOMPILE эффективно возвращает нас к поведению SQL Server 2000, когда вся хранимая процедура перекомпилируется при каждом выполнении. Лучшей альтернативой в SQL Server 2005 и более поздних версиях является использование подсказки запроса OPTION (RECOMPILE) только для оператора, который страдает от проблемы перехвата параметров. Эта подсказка запроса приводит к перекомпиляции только проблемного утверждения; Планы выполнения для других операторов в хранимой процедуре кэшируются и используются как обычно.
Использование WITH RECOMPILE также означает, что скомпилированный план для хранимой процедуры не кэшируется. В результате информация о производительности не поддерживается в DMV, таких как sys.dm_exec_query_stats. Вместо этого использование подсказки запроса означает, что скомпилированный план может быть кэширован, а информация о производительности доступна в DMV (хотя она ограничена самым последним выполнением только для затронутого оператора).
Для экземпляров, выполняющих как минимум SQL Server 2008 build 2746 (пакет обновления 1 с накопительным обновлением 5), использование OPTION (RECOMPILE) имеет еще одно существенное преимущество по сравнению с WITH RECOMPILE: только OPTION (RECOMPILE) включает оптимизацию встраивания параметров.
Чаще всего используется, когда в процедуре может быть динамическое предложение WHERE... вы не хотите, чтобы этот конкретный план запроса компилировался и сохранялся для последующих выполнений, потому что в следующий раз он может оказаться совершенно не таким же предложением процедура называется.
Его следует использовать только в том случае, если тестирование с репрезентативными данными и контекстом демонстрируют, что без него создаются неверные планы запросов (какими бы ни были возможные причины). Не предполагайте заранее (без тестирования), что SP не будет оптимизирован должным образом.
Единственное исключение только для ручного вызова (т.е. не кодируйте его в SP): когда вы знаете, что существенно изменили характер целевых таблиц. например, TRUNCATE, насыпные грузы и т. д.
Это еще одна возможность для преждевременной оптимизации.
Примечание: у меня много очков. Если новичок отправляет тот же ответ ниже, и вы согласны, проголосуйте за него.