Руководства планов

Статья
05/11/2023

Применимо к:база данных SQL Server Azure SQL Управляемый экземпляр SQL Azure

Структуры планов позволяют оптимизировать производительность запросов, если невозможно или нежелательно непосредственно изменять текст фактически имеющегося запроса в SQL Server. Структуры планов влияют на оптимизацию запросов путем присоединения к ним указаний запроса или постоянного плана запроса. Структуры планов полезны, когда небольшое подмножество запросов в приложении базы данных стороннего разработчика выполняются не так, как ожидается. В структуре плана задается инструкция Transact-SQL, которую нужно оптимизировать, и либо предложение OPTION, содержащее указания запросов, либо конкретный план запроса, с помощью которого планируется оптимизировать запрос. При выполнении запроса SQL Server сопоставляет инструкцию Transact-SQL со структурой плана и присоединяет предложение OPTION к запросу во время выполнения или использует указанный план запроса. Так как оптимизатор запросов SQL Server обычно выбирает оптимальный план выполнения для запроса, мы рекомендуем использовать только руководства по планам в качестве крайнего средства для опытных разработчиков и администраторов баз данных.

Примечание

хранилище запросов подсказки предоставляют более простой в использовании метод для формирования планов запросов без изменения кода приложения. хранилище запросов указания проще, чем руководства по планам. хранилище запросов указания доступны в базе данных Azure SQL и Управляемый экземпляр SQL Azure, а также в SQL Server 2022 г. (16.x) и более поздних версиях.

Общее число структур планов, которые можно создать, ограничивается только доступными системными ресурсами. Тем не менее использование структур планов должно быть ограничено критически важными запросами, затрагиваемыми в целях улучшения или стабилизации производительности. Структуры планов не следует использовать для основной массы запросов развернутого приложения.

Полученный при применении этой возможности план выполнения будет таким же, как принудительно применяемый план, или очень близким к нему. Так как итоговый план может не совпадать с планом, указанным в структуре плана, производительность этих планов может различаться. В редких случаях возможна значительная негативная разница в производительности, и тогда администратору следует удалить принудительный план.

Структуры планов можно использовать не во всех выпусках MicrosoftSQL Server. Сведения о функциях, поддерживаемых различными выпусками SQL Server, см. в статье Возможности, поддерживаемые выпусками SQL Server 2016. Структуры планов видны в любом выпуске. Можно также присоединить базу данных, содержащую структуры планов, к любой версии. Структуры планов остаются нетронутыми при восстановлении или присоединении базы данных к обновленной версии SQL Server.

Типы структур планов

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

OBJECT, руководство плана

Структура плана OBJECT соответствует запросам, выполняемым в контексте хранимых процедур Transact-SQL, определяемых пользователем скалярных функций, определяемых пользователем функций с несколькими инструкциями, возвращающих табличные значения, и триггеров DML.

Предположим, что следующая хранимая процедура, которая принимает параметр @Country_region, относится к приложению базы данных, которое развертывается в связи с базой данных AdventureWorks2022.

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))  
AS  
BEGIN  
    SELECT *  
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c,   
        Sales.SalesTerritory AS t  
    WHERE h.CustomerID = c.CustomerID  
        AND c.TerritoryID = t.TerritoryID  
        AND CountryRegionCode = @Country_region  
END;

Предположим, что эта хранимая процедура была скомпилирована и оптимизирована для @Country_region = N'AU' (Австралия). Однако, поскольку существует относительно мало заказов на продажу, поступающих из Австралии, производительность снижается при выполнении запроса с использованием значений параметров стран или регионов с большим количеством заказов на продажу. Так как страна, из которой поступает больше всего заказов на продажу, — США, план запроса, сформированный для значения @Country_region = N'US' , вероятно, обеспечит лучшую производительность для всех возможных значений параметра @Country_region .

Чтобы решить эту проблему, измените хранимую процедуру и добавьте указание OPTIMIZE FOR в запрос. Однако так как хранимая процедура находится в развернутом приложении, напрямую менять код приложения нельзя. Вместо этого можно создать следующую структуру плана в базе данных AdventureWorks2022 .

sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,  
        Sales.Customer AS c,  
        Sales.SalesTerritory AS t  
        WHERE h.CustomerID = c.CustomerID   
            AND c.TerritoryID = t.TerritoryID  
            AND CountryRegionCode = @Country_region',  
@type = N'OBJECT',  
@module_or_batch = N'Sales.GetSalesOrderByCountry',  
@params = NULL,  
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

При выполнении запроса, указанного в инструкции sp_create_plan_guide , этот запрос изменяется до оптимизации: в него добавляется предложение OPTIMIZE FOR (@Country = N''US'') .

Структура плана SQL

Структура плана SQL соответствует запросам, выполняющимся в контексте изолированных инструкций и пакетов Transact-SQL, не входящих ни в один объект базы данных. Структуры планов SQL также можно использовать для соответствия запросам с параметрами. Структуры планов SQL применяются к изолированным инструкциям и пакетам Transact-SQL. Часто эти инструкции передаются приложением с помощью хранимой процедуры sp_executesql . Например, рассмотрим следующий изолированный пакет:

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

Чтобы избежать создания параллельного плана выполнения для этого запроса, создайте приведенную ниже структуру плана и присвойте указанию запроса MAXDOP значение 1 в параметре @hints .

sp_create_plan_guide   
@name = N'Guide2',   
@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)';

В качестве другого примера рассмотрим следующую инструкцию SQL, отправленную с помощью sp_executesql.

exec sp_executesql N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id', N'@so_id int', @so_id = 43662;

Чтобы создать уникальный план для каждого выполнения этого запроса, создайте следующую структуру плана и используйте указание запроса OPTION (RECOMPILE) в параметре @hints.

exec sp_create_plan_guide   
@name = N'PlanGuide1_SalesOrders',   
@stmt = N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = N'@so_id int',   
@hints = N'OPTION (recompile)';

Важно!

Значения, передаваемые для аргументов @module_or_batch и @params инструкции sp_create_plan guide , должны соответствовать тексту настоящего запроса. Дополнительные сведения см. в разделах sp_create_plan_guide (Transact-SQL) и Использование SQL Server Profiler для создания и проверки структур планов.

Кроме того, структуры планов SQL можно создавать для запросов с той же параметризованной формой, если значением параметра базы данных PARAMETERIZATION является SET или FORCED либо если создана структура плана TEMPLATE, определяющая, что класс запросов должен быть параметризован.

TEMPLATE, структура плана

Структура плана TEMPLATE соответствует изолированным инструкциям с параметрами. Эти структуры планов используются для замещения текущего параметра PARAMETERIZATION инструкции SET базы данных для класса запросов.

Структуры планов TEMPLATE создаются в одной из следующих ситуаций.

Параметр базы данных PARAMETERIZATION установлен равным FORCED, но некоторые запросы желательно скомпилировать в соответствии с правилами простой параметризации.
Параметр базы данных PARAMETERIZATION установлен равным SIMPLE (значение по умолчанию), но вы хотите включить принудительную параметризацию для определенного класса запросов.

Требования по соответствию для структур планов

Структуры планов действительны в области видимости базы данных, в которой они создаются. Поэтому с запросом могут быть согласованы только структуры планов, находящиеся в базе данных, которая является текущей при выполнении запроса. Например, если AdventureWorks2022 является текущей базой данных и выполняется нижеследующий запрос:

SELECT FirstName, LastName FROM Person.Person;

Только руководства планов в базе данных AdventureWorks2022 подлежат сопоставлению с этим запросом. Но если AdventureWorks2022 является текущей базой данных и выполняются нижеследующие инструкции:

USE DB1; 
SELECT FirstName, LastName FROM Person.Person;

Для согласования с запросом применимы только структуры планов в DB1 , поскольку запрос выполняется в контексте DB1.

В структурах плана, основанных на SQL или TEMPLATE, SQL Server посимвольно сравнивает значения аргументов @module_or_batch и @params, переданных в запросе. Это означает, что необходимо предоставить текст точно в том же виде, в каком SQL Server получит его в действительном пакете.

Если @type = 'SQL' и @module_or_batch имеет значение NULL, параметр @module_or_batch получает значение @stmt. Из этого следует, что значение для statement_text должно быть предоставлено в идентичном формате, символ к символу, так как оно передается в SQL Server. Для упрощения соответствия формата внутренние преобразования не выполняются.

Если к инструкции могут быть применены и обычная структура плана (SQL или OBJECT), и структура плана TEMPLATE, то используется только обычная структура плана.

Примечание

Пакет, содержащий инструкцию, для которой необходимо создать структуру плана, не может содержать инструкцию USE database .

Влияние структуры плана на кэш планов

Создание структуры плана в модуле стирает план запроса для этого модуля из кэша планов. Создание структуры плана типа OBJECT или SQL в потоке стирает план запроса для потока, который имеет такое же значение хеш-функции. Создание структуры плана типа TEMPLATE стирает все потоки с одним оператором из кэша планов через базу данных.

Задача	Раздел
Описано, как создать структуру плана.	Создание структуры плана
Описано, как создать структуру плана для параметризованных запросов.	Создание структуры плана для параметризованных запросов
Описано, как управлять режимом параметризации запроса с использованием структур планов.	Указание механизма параметризации запросов с помощью структур плана
Описано, как включить постоянный план запроса в структуру плана.	Применение фиксированного плана запроса к структуре плана
Описано, как задать указания запросов в структуре плана.	Присоединение указаний запросов к структуре плана
Описано, как просматривать свойства структуры плана.	Просмотр свойств структуры плана
Описано, как использовать профилировщик SQL Server для создания и проверки структур планов.	Использование приложения SQL Server Profiler для создания и проверки структур плана
Описано, как проверять структуры планов.	Проверка структур плана после обновления

См. также:

sp_create_plan_guide (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL)
Хранимая процедура sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)

Руководства планов

Типы структур планов

OBJECT, руководство плана

Структура плана SQL

TEMPLATE, структура плана

Требования по соответствию для структур планов

Влияние структуры плана на кэш планов

См. также:

Дополнительные ресурсы

Дополнительные ресурсы

Руководства планов

Типы структур планов

OBJECT, руководство плана

Структура плана SQL

TEMPLATE, структура плана

Требования по соответствию для структур планов

Влияние структуры плана на кэш планов

Связанные задачи

См. также:

Дополнительные ресурсы

Дополнительные ресурсы