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

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

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

DECLARE @stmt NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
DECLARE @hints NVARCHAR(MAX);

SET @stmt = N'SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = @CustID';
SET @params = N'@CustID int';
SET @hints = N'OPTION (TABLE HINT (Sales.SalesOrderHeader, INDEX (IX_SalesOrderHeader_CustomerID)))';

EXEC sp_control_plan_guide @operation = N'CREATE',
    @name = N'Guide_ForceIndex',
    @stmt = @stmt,
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = @params,
    @hints = @hints;

Результат: Направляющая плана успешно создана.

Пример деактивации направляющей:

EXEC sp_control_plan_guide @operation = N'DISABLE', 
    @name = N'Guide_ForceIndex';

Результат: Направляющая плана деактивирована.

Пример удаления направляющей:

EXEC sp_control_plan_guide @operation = N'DROP', 
    @name = N'Guide_ForceIndex';

Результат: Направляющая плана удалена.

Похожие механизмы в MS SQL

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

• Хранимая процедура sys.sp_create_plan_guide: Прямой аналог операции CREATE функции sp_control_plan_guide. Предоставляет более простой синтаксис для создания направляющих.
• Подсказки запросов (Query Hints): Указания, добавляемые непосредственно в текст запроса (например, OPTION (RECOMPILE)). Их использование предпочтительнее, когда возможна модификация кода запроса.
• Хранимые процедуры управления планами: sys.sp_create_plan_guide_from_handle (создание из кэша планов) и sys.fn_validate_plan_guide (проверка валидности).

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

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

Другие системы управления базами данных предлагают схожие механизмы:

Oracle: Использует SQL Plan Baselines и SQL Profiles. Пример создания Baseline:

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

План загружен в Baseline.

PostgreSQL: Расширение pg_hint_plan позволяет добавлять указания через специальные комментарии:

/*+ IndexScan(orders) */
SELECT * FROM orders WHERE order_id = 100;

MySQL: Поддерживает оптимизаторные подсказки в комментариях:

SELECT /*+ INDEX(t1 idx1) */ * FROM t1 WHERE col1 = 10;

SQLite: Не имеет прямых аналогов направляющих планов. Оптимизация контролируется преимущественно через прагмы и построение индексов.

В отличие от MS SQL, где управление направляющими централизовано через системные процедуры, в Oracle и PostgreSQL используются расширения или отдельные пакеты для управления планами выполнения.

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

Ошибка при создании направляющей с несуществующим объектом:

EXEC sp_control_plan_guide @operation = N'CREATE',
    @name = N'InvalidGuide',
    @stmt = N'SELECT * FROM NonExistentTable',
    @type = N'SQL',
    @hints = N'OPTION (MAXDOP 1)';

Сообщение 10504, уровень 16, состояние 1
Не удается создать направляющую плана "InvalidGuide", потому что инструкция, заданная в @stmt, содержит недопустимые объекты.

Ошибка при попытке удалить несуществующую направляющую:

EXEC sp_control_plan_guide @operation = N'DROP', 
    @name = N'UnknownGuide';

Сообщение 10504, уровень 16, состояние 1
Не удается удалить направляющую плана "UnknownGuide", так как она не существует в текущей базе данных.

Ошибка несовместимых параметров:

EXEC sp_control_plan_guide @operation = N'CREATE',
    @name = N'TestGuide',
    @type = N'OBJECT',
    @module_or_batch = NULL; -- Для типа OBJECT необходимо указать объект

Сообщение 10524, уровень 16, состояние 1
Параметры @module_or_batch и @statement не могут быть равны NULL одновременно.

Изменения в версиях SQL Server

В SQL Server 2016 и более поздних версиях были внесены следующие изменения, связанные с направляющими планов:

• Улучшена интеграция с функцией автоматической настройки (Automatic Tuning), которая может автоматически создавать и удалять направляющие планы.
• В SQL Server 2017 добавлена возможность использования направляющих планов для запросов в хранилище данных.
• Для направляющих типа TEMPLATE улучшена обработка принудительной параметризации в распределенных запросах.
• В последних версиях улучшена диагностика недействительных направляющих через системные представления.

Синтаксис самой процедуры sp control plan guide существенно не менялся с момента введения в SQL Server 2008.

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

Создание направляющей для принудительной параметризации (тип TEMPLATE):

DECLARE @stmt NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
DECLARE @hints NVARCHAR(MAX);

SET @stmt = N'SELECT * FROM Sales.SalesOrderDetail 
WHERE ProductID = 100 AND OrderQty = 10';
SET @params = N'@0 int, @1 int';
SET @hints = N'OPTION (PARAMETERIZATION FORCED)';

EXEC sp_control_plan_guide @operation = N'CREATE',
    @name = N'Guide_ForceParam',
    @stmt = @stmt,
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = @params,
    @hints = @hints;

Создана направляющая для принудительной параметризации конкретного шаблона запроса.

Направляющая для хранимой процедуры (тип OBJECT):

EXEC sp_control_plan_guide @operation = N'CREATE',
    @name = N'Guide_ForProcedure',
    @stmt = NULL,
    @type = N'OBJECT',
    @module_or_batch = N'usp_GetSalesData',
    @params = NULL,
    @hints = N'OPTION (RECOMPILE)';

Направляющая применяет указание RECOMPILE ко всем запросам в указанной хранимой процедуре.

Пакетное создание и управление направляющими:

-- Деактивация всех направляющих в базе данных
DECLARE @guide_name sysname;
DECLARE guide_cursor CURSOR FOR
SELECT name FROM sys.plan_guides WHERE is_disabled = 0;

OPEN guide_cursor;
FETCH NEXT FROM guide_cursor INTO @guide_name;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_control_plan_guide @operation = N'DISABLE', 
        @name = @guide_name;
    FETCH NEXT FROM guide_cursor INTO @guide_name;
END;
CLOSE guide_cursor;
DEALLOCATE guide_cursor;

Все активные направляющие планы в базе данных деактивированы.