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

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

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

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

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

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

Предположим, что следующая хранимая процедура, которая принимает @Countryпараметр _region , находится в приложении базы данных, развернутом в базе данных AdventureWorks2012 :

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 в запрос. Однако так как хранимая процедура находится в развернутом приложении, напрямую менять код приложения нельзя. Вместо этого можно создать следующее руководство по плану в базе данных AdventureWorks2012 .

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 можно для запросов, которые приводятся к одной и той же форме при установке параметра базы данных PARAMETERIZATION в значение FORCED или при создании руководства планами TEMPLATE, указывающего на параметризацию класса запросов.

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

Вы можете создать руководство по плану TEMPLATE в одной из следующих ситуаций.

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

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

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

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

SELECT FirstName, LastName FROM Person.Person;

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

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, то будет использоваться только обычный план-гид.

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

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

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

См. также

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)