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

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure нетAzure Synapse Analytics (хранилище данных SQL) нетParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Структуры планов позволяют оптимизировать производительность запросов, если невозможно или нежелательно непосредственно изменять текст фактически имеющегося запроса в SQL Server 2019 (15.x)SQL Server 2019 (15.x).Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL Server 2019 (15.x)SQL Server 2019 (15.x). Структуры планов влияют на оптимизацию запросов путем присоединения к ним указаний запроса или постоянного плана запроса.Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them. Структуры планов полезны, когда небольшое подмножество запросов в приложении базы данных стороннего разработчика выполняются не так, как ожидается.Plan guides can be useful when a small subset of queries in a database application provided by a third-party vendor are not performing as expected. В структуре плана задается инструкция Transact-SQL, которую нужно оптимизировать, и либо предложение OPTION, содержащее указания запросов, либо конкретный план запроса, с помощью которого планируется оптимизировать запрос.In the plan guide, you specify the Transact-SQL statement that you want optimized and either an OPTION clause that contains the query hints you want to use or a specific query plan you want to use to optimize the query. При выполнении запроса SQL ServerSQL Server сопоставляет инструкцию Transact-SQL со структурой плана и присоединяет предложение OPTION к запросу во время выполнения или использует указанный план запроса.When the query executes, SQL ServerSQL Server matches the Transact-SQL statement to the plan guide and attaches the OPTION clause to the query at run time or uses the specified query plan.

Общее число структур планов, которые можно создать, ограничивается только доступными системными ресурсами.The total number of plan guides you can create is limited only by available system resources. Тем не менее использование структур планов должно быть ограничено критически важными запросами, затрагиваемыми в целях улучшения или стабилизации производительности.Nevertheless, plan guides should be limited to mission-critical queries that are targeted for improved or stabilized performance. Структуры планов не следует использовать для основной массы запросов развернутого приложения.Plan guides should not be used to influence most of the query load of a deployed application.

Примечание

Структуру планов можно использовать не во всех выпусках MicrosoftMicrosoftSQL ServerSQL Server.Plan guides cannot be used in every edition of MicrosoftMicrosoftSQL ServerSQL Server. Сведения о функциях, поддерживаемых различными выпусками SQL ServerSQL Server, см. в статье Возможности, поддерживаемые выпусками SQL Server 2016.For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016. Структуры планов видны в любом выпуске.Plan guides are visible in any edition. Можно также присоединить базу данных, содержащую структуры планов, к любой версии.You can also attach a database that contains plan guides to any edition. Структуры планов остаются нетронутыми при восстановлении или присоединении базы данных к обновленной версии SQL ServerSQL Server.Plan guides remain intact when you restore or attach a database to an upgraded version of SQL ServerSQL Server.

Типы структур плановTypes of Plan Guides

Могут быть созданы структуры планов следующих типов.The following types of plan guides can be created.

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

Структура плана OBJECT соответствует запросам, выполняемым в контексте хранимых процедур языка Transact-SQLTransact-SQL , определяемых пользователем скалярных функций, определяемых пользователем функций с несколькими инструкциями, возвращающих табличные значения, и триггеров DML.An OBJECT plan guide matches queries that execute in the context of Transact-SQLTransact-SQL stored procedures, scalar user-defined functions, multi-statement table-valued user-defined functions, and DML triggers.

Предположим, что следующая хранимая процедура, которая принимает параметр @Country_region, относится к приложению базы данных, которое развертывается в связи с базой данных AdventureWorks2012AdventureWorks2012.Suppose the following stored procedure, which takes the @Country_region parameter, is in a database application that is deployed against the AdventureWorks2012AdventureWorks2012 database:

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' (Австралия).Assume that this stored procedure has been compiled and optimized for @Country_region = N'AU' (Australia). Но из Австралии поступает относительно небольшое количество заказов на продажу, поэтому производительность снижается, если запрос выполняется с использованием значений параметров, относящихся к тем странам, где имеется большее количество заказов на продажу.However, because there are relatively few sales orders that originate from Australia, performance decreases when the query executes using parameter values of countries with more sales orders. Так как страна, из которой поступает больше всего заказов на продажу, — США, план запроса, сформированный для значения @Country_region = N'US' , вероятно, обеспечит лучшую производительность для всех возможных значений параметра @Country_region .Because the most sales orders originate in the United States, a query plan that is generated for @Country_region = N'US' would likely perform better for all possible values of the @Country_region parameter.

Чтобы решить эту проблему, измените хранимую процедуру и добавьте указание OPTIMIZE FOR в запрос.You could address this problem by modifying the stored procedure to add the OPTIMIZE FOR query hint to the query. Однако так как хранимая процедура находится в развернутом приложении, напрямую менять код приложения нельзя.However, because the stored procedure is in a deployed application, you cannot directly modify the application code. Вместо этого можно создать следующую структуру плана в базе данных AdventureWorks2012AdventureWorks2012 .Instead, you can create the following plan guide in the AdventureWorks2012AdventureWorks2012 database.

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'') .When the query specified in the sp_create_plan_guide statement executes, the query is modified before optimization to include the OPTIMIZE FOR (@Country = N''US'') clause.

Структура плана SQLSQL plan guide

Структура плана SQL соответствует запросам, выполняющимся в контексте изолированных инструкций Transact-SQLTransact-SQL и пакетов, не входящих ни в один объект базы данных.An SQL plan guide matches queries that execute in the context of stand-alone Transact-SQLTransact-SQL statements and batches that are not part of a database object. Структуры планов SQL также можно использовать для соответствия запросам с параметрами.SQL-based plan guides can also be used to match queries that parameterize to a specified form. Структуры планов SQL применяются к изолированным инструкциям Transact-SQLTransact-SQL и пакетам.SQL plan guides apply to stand-alone Transact-SQLTransact-SQL statements and batches. Часто эти инструкции передаются приложением с помощью хранимой процедуры sp_executesql .Frequently, these statements are submitted by an application by using the sp_executesql system stored procedure. Например, рассмотрим следующий изолированный пакет:For example, consider the following stand-alone batch:

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

Чтобы избежать создания параллельного плана выполнения для этого запроса, создайте приведенную ниже структуру плана и присвойте указанию запроса MAXDOP значение 1 в параметре @hints .To prevent a parallel execution plan from being generated on this query, create the following plan guide and set the MAXDOP query hint to 1 in the @hints parameter.

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.As another example, consider the following SQL statement submitted using sp_executesql.

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

Чтобы создать уникальный план для каждого выполнения этого запроса, создайте следующую структуру плана и используйте подсказку OPTION (RECOMPILE) в запросе для параметра @hints.To create a unique plan for every execution of this query, create the following plan guide and use the OPTION (RECOMPILE) query hint in the @hints parameter.

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 , должны соответствовать тексту настоящего запроса.The values that are supplied for the @module_or_batch and @params arguments of the sp_create_plan guide statement must match the corresponding text submitted in the actual query. Дополнительные сведения см. в разделах sp_create_plan_guide (Transact-SQL) и Использование приложения SQL Server Profiler для создания и проверки руководств планов.For more information, see sp_create_plan_guide (Transact-SQL) and Use SQL Server Profiler to Create and Test Plan Guides.

Кроме того, структуры планов SQL можно создавать для запросов с той же параметризованной формой, если значением параметра базы данных PARAMETERIZATION является SET или FORCED либо если создана структура плана TEMPLATE, определяющая, что класс запросов должен быть параметризован.SQL plan guides can also be created on queries that parameterize to the same form when the PARAMETERIZATION database option is SET to FORCED, or when a TEMPLATE plan guide is created specifying that a parameterized class of queries.

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

Структура плана TEMPLATE соответствует изолированным инструкциям с параметрами.A TEMPLATE plan guide matches stand-alone queries that parameterize to a specified form. Эти структуры планов используются для замещения текущего параметра PARAMETERIZATION инструкции SET базы данных для класса запросов.These plan guides are used to override the current PARAMETERIZATION database SET option of a database for a class of queries.

Структуры планов TEMPLATE создаются в одной из следующих ситуаций.You can create a TEMPLATE plan guide in either of the following situations:

  • Параметр базы данных PARAMETERIZATION установлен равным FORCED, но некоторые запросы желательно скомпилировать в соответствии с правилами простой параметризации.The PARAMETERIZATION database option is SET to FORCED, but there are queries you want compiled according to the rules of Simple Parameterization.

  • Параметр базы данных PARAMETERIZATION установлен равным SIMPLE (значение по умолчанию), но вы хотите включить принудительную параметризацию для определенного класса запросов.The PARAMETERIZATION database option is SET to SIMPLE (the default setting), but you want Forced Parameterization to be tried on a class of queries.

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

Структуры планов действительны в области видимости базы данных, в которой они создаются.Plan guides are scoped to the database in which they are created. Поэтому с запросом могут быть согласованы только структуры планов, находящиеся в базе данных, которая является текущей при выполнении запроса.Therefore, only plan guides that are in the database that is current when a query executes can be matched to the query. Например, если AdventureWorks2012AdventureWorks2012 является текущей базой данных и выполняется нижеследующий запрос:For example, if AdventureWorks2012AdventureWorks2012 is the current database and the following query executes:

SELECT FirstName, LastName FROM Person.Person;

Только руководства планов в базе данных AdventureWorks2012AdventureWorks2012 подлежат сопоставлению с этим запросом.Only plan guides in the AdventureWorks2012AdventureWorks2012 database are eligible to be matched to this query. Но если AdventureWorks2012AdventureWorks2012 является текущей базой данных и выполняются нижеследующие инструкции:However, if AdventureWorks2012AdventureWorks2012 is the current database and the following statements are run:

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

Для согласования с запросом применимы только структуры планов в DB1 , поскольку запрос выполняется в контексте DB1.Only plan guides in DB1 are eligible to be matched to the query because the query is executing in the context of DB1.

В структурах плана, основанных на SQL или TEMPLATE, SQL ServerSQL Server посимвольно сравнивает значения аргументов @module_or_batch и @params, переданных в запросе.For SQL- or TEMPLATE-based plan guides, SQL ServerSQL Server matches the values for the @module_or_batch and @params arguments to a query by comparing the two values character by character. Это означает, что необходимо предоставить текст точно в том же виде, в каком SQL ServerSQL Server получит его в действительном пакете.This means you must provide the text exactly as SQL ServerSQL Server receives it in the actual batch.

Если @type = 'SQL' и @module_or_batch имеет значение NULL, параметр @module_or_batch получает значение @stmt. Из этого следует, что значение для statement_text должно быть предоставлено в идентичном формате, символ к символу, так как оно передается в SQL ServerSQL Server.When @type = 'SQL' and @module_or_batch is set to NULL, the value of @module_or_batch is set to the value of @stmt. This means that the value for statement_text must be provided in the identical format, character-for-character, as it is submitted to SQL ServerSQL Server. Для упрощения соответствия формата внутренние преобразования не выполняются.No internal conversion is performed to facilitate this match.

Если к инструкции могут быть применены и обычная структура плана (SQL или OBJECT), и структура плана TEMPLATE, то используется только обычная структура плана.When both a regular (SQL or OBJECT) plan guide and a TEMPLATE plan guide can apply to a statement, only the regular plan guide will be used.

Примечание

Пакет, содержащий инструкцию, для которой необходимо создать структуру плана, не может содержать инструкцию USE database .The batch that contains the statement on which you want to create a plan guide cannot contain a USE database statement.

Влияние структуры плана на кэш плановPlan Guide Effect on the Plan Cache

Создание структуры плана в модуле стирает план запроса для этого модуля из кэша планов.Creating a plan guide on a module removes the query plan for that module from the plan cache. Создание структуры плана типа OBJECT или SQL в потоке стирает план запроса для потока, который имеет такое же значение хеш-функции.Creating a plan guide of type OBJECT or SQL on a batch removes the query plan for a batch that has the same hash value. Создание структуры плана типа TEMPLATE стирает все потоки с одним оператором из кэша планов через базу данных.Creating a plan guide of type TEMPLATE removes all single-statement batches from the plan cache within that database.

ЗадачаTask РазделTopic
Описано, как создать структуру плана.Describes how to create a plan guide. Создание структуры планаCreate a New Plan Guide
Описано, как создать структуру плана для параметризованных запросов.Describes how to create a plan guide for parameterized queries. Создание руководства плана для параметризованных запросовCreate a Plan Guide for Parameterized Queries
Описано, как управлять режимом параметризации запроса с использованием структур планов.Describes how to control query parameterization behavior by using plan guides. Указание механизма параметризации запросов с помощью структур планаSpecify Query Parameterization Behavior by Using Plan Guides
Описано, как включить постоянный план запроса в структуру плана.Describes how to include a fixed query plan in a plan guide. Применение фиксированного плана запроса к структуре планаApply a Fixed Query Plan to a Plan Guide
Описано, как задать указания запросов в структуре плана.Describes how to specify query hints in a plan guide. Присоединение указаний запросов к структуре планаAttach Query Hints to a Plan Guide
Описано, как просматривать свойства структуры плана.Describes how to view plan guide properties. Просмотр свойств структуры планаView Plan Guide Properties
Описано, как использовать профилировщик SQL Server для создания и проверки структур планов.Describes how to use SQL Server Profiler to create and test plan guides. Использование приложения SQL Server Profiler для создания и проверки руководств плановUse SQL Server Profiler to Create and Test Plan Guides
Описано, как проверять структуры планов.Describes how to validate plan guides. Проверка руководств плана после обновленияValidate Plan Guides After Upgrade

См. также:See Also

sp_create_plan_guide (Transact-SQL) sp_create_plan_guide (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL) sp_create_plan_guide_from_handle (Transact-SQL)
sp_control_plan_guide (Transact-SQL) sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL) sys.plan_guides (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)sys.fn_validate_plan_guide (Transact-SQL)