Я понимаю, что опция WITH RECOMPILE заставляет оптимизатор перестраивать план запроса для сохраненных procs, но когда вы хотите, чтобы это произошло?
Каковы некоторые эмпирические правила о том, когда использовать параметр WITH RECOMPILE и когда не нужно?
Каковы эффективные накладные расходы, связанные с просто поставкой его на каждый sproc?
Как говорили другие, вы не хотите просто включать WITH RECOMPILE в каждый хранимый процесс как по привычке. Таким образом, вы устраняете одно из основных преимуществ хранимых процедур: тот факт, что он сохраняет план запроса.
Почему это потенциально большое дело? Вычисление плана запроса намного более интенсивно, чем составление обычного процедурного кода. Поскольку синтаксис инструкции SQL указывает только , что, а не (как правило) , как, что позволяет базе данных иметь большую степень гибкости при создании физического (то есть пошаговые инструкции для фактического сбора и изменения данных). Существует множество "трюков", которые может выполнять предварительный процессор запроса к базе данных, и выбор, который он может сделать, - какой порядок объединения таблиц, какие индексы использовать, применять ли предложения WHERE до или после объединения и т.д.
Для простого оператора SELECT это может не повлиять, но для любого нетривиального запроса база данных будет тратить некоторое серьезное время (измеренное в миллисекундах, в отличие от обычных микросекунд), чтобы придумать оптимальный план. Для действительно сложных запросов он не может даже гарантировать оптимальный план, он должен просто использовать эвристику, чтобы придумать довольно хороший план. Поэтому, заставляя его перекомпилировать каждый раз, вы говорите ему, что он должен проходить этот процесс снова и снова, даже если план, который он получил до этого, был совершенно хорош.
В зависимости от поставщика должны быть автоматические триггеры для перекомпилирования планов запросов - например, если статистические данные в таблице значительно меняются (например, гистограмма значений в определенном столбце начинается равномерно распределенная с течением времени, становится сильно искаженной), то БД должна заметить это и перекомпилировать план. Но, вообще говоря, разработчики базы данных будут умнее об этом в целом, чем вы.
Как и в случае с любой производительностью, не делайте снимки в темноте; выяснить, где узкие места стоят 90% вашей производительности и решить их в первую очередь.
Размещение его в каждой хранимой процедуре НЕ является хорошей идеей, поскольку составление плана запроса является относительно дорогой операцией, и вы не увидите никакой выгоды от кэширования и повторного использования планов запросов.
Случай динамического предложения where, созданного внутри хранимой процедуры, может быть обработан с использованием sp_executesql для выполнения TSQL вместо добавления WITH RECOMPILE к хранимой процедуре.
Другое решение (начиная с SQL Server 2005) - использовать подсказку с конкретными параметрами, используя подсказку OPTIMIZE FOR. Это хорошо работает, если значения в строках являются статическими.
SQL Server 2008 представил малоизвестную функцию под названием " OPTIMIZE FOR UNKNOWN ":
Этот совет указывает оптимизатору запросов использовать стандартные алгоритмы, которые он всегда использовал, если значения параметров не были переданы в запрос вообще. В этом случае оптимизатор будет просматривать все доступные статистические данные, чтобы определить, какими должны быть значения локальных переменных, используемых для создания плана запроса, вместо того, чтобы рассматривать конкретные значения параметров, которые были переданы в запрос приложением.
Наиболее распространенное использование - когда у вас может быть динамическое предложение WHERE в процедуре... вы не хотите, чтобы этот конкретный план запроса был скомпилирован и сохранен для последующих исполнений, потому что он очень хорошо не мог быть тем же самым предложением при следующем вызове процедуры.
как правило, гораздо лучшей альтернативой WITH RECOMPILE является OPTION(RECOMPILE)
как вы можете видеть на объяснении ниже, взятом из ответа на этот вопрос здесь
Когда возникает проблема с параметрической чувствительностью, общая часть советы на форумах и Q & сайты должны "использовать перекомпилировать" (при условии, что другие варианты настройки, представленные ранее, непригодны). К сожалению, этот совет часто неверно интерпретируется как означающий добавление 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 (Service Pack 1 с накопительным обновлением 5), используя OPTION (RECOMPILE), есть другой значительное преимущество перед RECOMPILE: только OPTION (RECOMPILE) обеспечивает оптимизацию вложения параметров.
Его следует использовать только тогда, когда тестирование с помощью репрезентативных данных и контекста показывает, что выполнение без создания недопустимых планов запросов (каковы бы ни были возможные причины). Не предполагайте заранее (без тестирования), что SP не будет правильно оптимизирован.
Единственное исключение только для ручного вызова (т.е. не вводите его в СП): когда вы знаете, что существенно изменили характер целевых таблиц. например TRUNCATE, объемные нагрузки и т.д.
Это еще одна возможность для преждевременной оптимизации.
Примечание: у меня много очков. Если newby отправляет тот же ответ ниже, и вы согласны, повысьте их.