Sp create plan guide: примеры (SQL)

Короткие примеры использования

Пример 1: Создание руководства плана типа SQL для автономного запроса с подсказкой по соединению.

EXEC sp_create_plan_guide 
    @name = N'Guide1',
    @stmt = N'SELECT * FROM Sales.Orders WHERE CustomerID = @CustomerID',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@CustomerID int',
    @hints = N'OPTION (MERGE JOIN)';

Команда выполнена успешно.

Пример 2: Руководство плана для хранимой процедуры (тип OBJECT), закрепляющее конкретный план с использованием хинта LOOP JOIN.

EXEC sp_create_plan_guide 
    @name = N'Guide_ProcExample',
    @stmt = N'SELECT o.OrderID, c.CustomerName FROM Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID',
    @type = N'OBJECT',
    @module_or_batch = N'usp_GetOrders',
    @params = NULL,
    @hints = N'OPTION (LOOP JOIN)';

Команда выполнена успешно.

Пример 3: Создание шаблонного руководства плана (TEMPLATE) для принудительной параметризации простого запроса.

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM Products WHERE ProductID = 100',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (PARAMETERIZATION FORCED)';

Команда выполнена успешно.

Похожие функции в MS SQL Server

В SQL Server существуют альтернативные механизмы влияния на планы выполнения:

• sp_control_plan_guide: Управление существующими руководствами планов (включение, отключение, удаление). Используется для администрирования без пересоздания.
• Подсказки запросов (Query Hints) в самом SQL-коде: Например, OPTION (RECOMPILE), OPTION (MAXDOP 1). Применяются непосредственно в запросе. Их использование предпочтительно, когда есть возможность изменить код запроса, так как это более прозрачно и управляемо.
• Хранилище запросов (Query Store): В современных версиях SQL Server (2016+) Query Store предоставляет более удобный и гибкий способ фиксации и анализа планов выполнения, включая принудительное применение планов через графический интерфейс или системные представления.
• Trace Flags: Некоторые глобальные флаги трассировки могут влиять на поведение оптимизатора для всех запросов в экземпляре. Используются для тонкой настройки в сложных случаях.

Руководства планов предпочтительны, когда необходимо применить подсказку к запросу, который нельзя изменить, или для точечных исправлений в сторонних приложениях. Query Store рекомендуется для новых разработок и комплексного управления производительностью.

Аналоги в других СУБД

Oracle: Использует концепцию SQL Plan Baselines и SQL Profiles. План можно зафиксировать с помощью пакета DBMS_SPM.

DECLARE
  l_plans_loaded PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => 'g9x9j5z7q4m5c');
END;

PL/SQL procedure successfully completed.

PostgreSQL: Прямых аналогов нет. Для влияния на план могут использоваться настройки конфигурации (например, random_page_cost), расширения (pg_hint_plan) или ручное управление статистикой.

/* Использование pg_hint_plan */
/*+ IndexScan(orders) */ SELECT * FROM orders WHERE status = 'shipped';

Результат зависит от установки расширения.

MySQL: Поддерживает индексы для подсказок оптимизатора (USE INDEX, FORCE INDEX) прямо в SQL-запросе. Аналога глобальных руководств планов нет.

SELECT * FROM orders USE INDEX (idx_status) WHERE status = 'completed';

SQLite: Отсутствуют механизмы фиксации планов. Повлиять на выбор плана можно только через создание или удаление индексов, использование PRAGMA-команд (например, PRAGMA optimize) или переписывание запроса.

Ключевое отличие MS SQL — централизованное управление планами на уровне базы данных без изменения кода приложения через системные хранимые процедуры.

Типичные ошибки

Ошибка 1: Несоответствие текста запроса (@stmt) точному формату, отправляемому приложением, включая пробелы, переносы строк и регистр.

-- Запрос в приложении: SELECT * FROM t WHERE id=@p
EXEC sp_create_plan_guide 
    @name = N'BadGuide',
    @stmt = N'SELECT * FROM t WHERE id = @p', -- Лишние пробелы
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@p int',
    @hints = N'OPTION (MAXDOP 1)';

Руководство плана создано, но не будет применяться,
так как текст не совпадает.

Ошибка 2: Попытка создать руководство с уже существующим именем.

EXEC sp_create_plan_guide 
    @name = N'DuplicateName',
    @stmt = N'SELECT 1',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'';
-- Повторное выполнение того же вызова

Msg 10504, Level 16, State 1, Procedure sp_create_plan_guide
Разрешение с таким именем уже существует.

Ошибка 3: Указание несовместимых параметров, например, передача @module_or_batch для типа 'TEMPLATE'.

EXEC sp_create_plan_guide 
    @name = N'WrongTemplate',
    @stmt = N'SELECT * FROM Table1 WHERE Col1 = 1',
    @type = N'TEMPLATE',
    @module_or_batch = N'SomeBatch', -- Должно быть NULL
    @params = NULL,
    @hints = N'OPTION (PARAMETERIZATION FORCED)';

Msg 10531, Level 16, State 1, Procedure sp_create_plan_guide
Недопустимое значение для аргумента @module_or_batch.

Изменения в последних версиях

Начиная с SQL Server 2016, корпорация Microsoft рекомендует использовать Хранилище запросов (Query Store) для управления планами выполнения как более современную и функциональную альтернативу руководствам планов. Хранилище запросов предоставляет встроенные механизмы отслеживания производительности, анализа регрессий и принудительного применения планов через системные представления или графический интерфейс SQL Server Management Studio.

Функция sp_create_plan_guide остается поддерживаемой для обеспечения обратной совместимости, но может быть исключена в будущих версиях. В SQL Server 2017 и 2019 не было внесено значительных изменений в синтаксис или поведение этой функции.

Важное изменение в идеологии: вместо ручного создания руководств планов через T-SQL в современных версиях предпочтительнее использовать автоматизированные возможности Query Store, которые интегрированы с мониторингом и анализом.

Расширенные примеры применения

Пример 1: Использование руководства плана для принудительного применения конкретного индекса через подсказку INDEX для параметризованного запроса в пакете.

EXEC sp_create_plan_guide 
    @name = N'ForceIndexGuide',
    @stmt = N'SELECT OrderID, CustomerID, OrderDate 
              FROM Sales.Orders 
              WHERE Status = @Status AND Year(OrderDate) = @Year',
    @type = N'SQL',
    @module_or_batch = N'EXEC usp_Report @Status, @Year;',
    @params = N'@Status nvarchar(10), @Year int',
    @hints = N'OPTION (TABLE HINT(Sales.Orders, INDEX (IX_Status_OrderDate)))';

Команда выполнена успешно.

Пример 2: Создание руководства плана для запроса внутри триггера (тип OBJECT) с целью предотвращения параллельного выполнения, чтобы уменьшить contention.

EXEC sp_create_plan_guide 
    @name = N'TriggerMaxdopGuide',
    @stmt = N'UPDATE Inventory SET Stock = Stock - @Qty WHERE ProductID = @ProdID',
    @type = N'OBJECT',
    @module_or_batch = N'tr_Inventory_Update',
    @params = NULL,
    @hints = N'OPTION (MAXDOP 1)';

Команда выполнена успешно.

Пример 3: Комбинированное руководство плана с несколькими подсказками для сложного пакета, включающего CTE и соединения.

EXEC sp_create_plan_guide 
    @name = N'ComplexBatchGuide',
    @stmt = N';WITH CTE AS (
                SELECT CustomerID, COUNT(*) as OrderCount 
                FROM Sales.Orders 
                GROUP BY CustomerID
              )
              SELECT c.CustomerName, cte.OrderCount
              FROM CTE cte
              INNER JOIN Sales.Customers c ON cte.CustomerID = c.CustomerID
              WHERE cte.OrderCount > @Threshold',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@Threshold int',
    @hints = N'OPTION (RECOMPILE, MERGE JOIN, MAXDOP 2)';

Команда выполнена успешно.

Пример 4: Руководство плана для запроса с динамическим SQL внутри хранимой процедуры. Здесь важно точно передать текст динамического запроса.

EXEC sp_create_plan_guide 
    @name = N'DynamicSQLGuide',
    @stmt = N'SELECT * FROM Products WHERE CategoryID = @CatID AND Price > @MinPrice',
    @type = N'OBJECT',
    @module_or_batch = N'usp_SearchProducts',
    @params = N'@CatID int, @MinPrice decimal(10,2)',
    @hints = N'OPTION (OPTIMIZE FOR (@CatID = 5, @MinPrice = 100.0))';

Команда выполнена успешно.