Tips for DBA: sp_create_plan_guide позволяет администратору менять план запроса

В статье речь пойдёт о системной хранимой процедуре sp_create_plan_guide, которая включена в поставки SQL Server Enterprise Edition и Standard Edition.

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

За этой путаной фразой из локализованного BOL скрывается следующий смысл:

Если Вы обслуживаете приложение баз данных не имея возможности менять тексты генерируемых приложением запросов и у Вас есть непреодолимое желание заставить эти запросы работать быстрее (за счёт добавления к ним подсказок оптимизатору), Вы теперь можете это сделать на стороне сервера, не трогая приложение. Грубо говоря, появилась возможность "перехватывать" запросы и добавлять к ним подсказки оптимизатору, что бы он мог выбрать указанный план исполнения запроса.
Для этого в Profiler получите оригинальный запрос от приложения, из SQL:BatchStarting скопируйте его текст и подставьте его в параметре @stmt указанной выше процедуры. Кроме прочих, обязательных параметров, нужно указать требуемые опции подсказки оптимизатору, которые нужно прописать в параметре @hints.
Кроме непосредственного влияния на планы запросов "кривых" приложений, можно очень избирательно балансировать нагрузку некоторых "прожорливых" запросов, добавляя подсказку с MAXDOP ;)
Поскольку все тонкости использования доступны в документации, я позволю себе ограничится тут только цитированием одного примера из BOL, который вполне наглядно всё поясняет. Цитирую:

В приведенном ниже примере создается руководство плана, которому сопоставляется запрос из пакета, переданного приложением, использующим системную хранимую процедуру sp_executesql.
Ниже представлен пакет.

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

Для предотвращения создания по данному запросу параллельного плана выполнения необходимо создать следующее руководство плана.

EXEC sp_create_plan_guide      @name = N'Guide1' ,@stmt = N'SELECT TOP 1 *                 FROM Sales.SalesOrderHeader                 ORDER BY OrderDate DESC' ,@type = N'SQL' ,@module_or_batch = NULL ,@params = NULL ,@hints = N'OPTION (MAXDOP 1)';