Перенос планов запросов

Статья
03/06/2017

В большинстве случаев обновление базы данных до последней версии SQL Server приведет к повышению производительности запросов. Однако при наличии ответственных запросов, для которых производительность тщательно настроена, перед началом обновления может оказаться полезным сохранить планы запросов, создав для каждого из них структуру плана. Если после обновления оптимизатор запросов выбирает для некоторых запросов менее эффективный план, можно разрешить использование старых структур планов и заставить оптимизатор запросов пользоваться ими.

Чтобы создать структуру плана, перед началом обновления выполните следующие действия.

Запишите текущий план для каждого критически важного запроса, используя хранимую процедуру sp_create_plan_guide и указав план запроса в указании запроса USE PLAN.
Убедитесь в том, что структура плана применена к запросу.
Обновите базу данных до более новой версии SQL Server.

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

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

Пример

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

Шаг 1. Сбор плана

План запроса, записываемый в структуру плана, должен иметь формат XML. Планы запросов в формате XML могут быть созданы следующими способами.

SET SHOWPLAN_XML
SET STATISTICS XML
Запрос столбца query_plan функции динамического управления sys.dm_exec_query_plan .
Классы событий SQL Server Profiler Showplan XML, Showplan XML Statistics Profile и Showplan XML For Query Compile.

Следующий пример собирает план запроса для инструкции SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC; путем запроса динамических административных представлений.

USE AdventureWorks;  
GO  
SELECT query_plan  
    FROM sys.dm_exec_query_stats AS qs   
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp  
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%';  
GO

Шаг 2. Создание руководства по плану для принудительного выполнения плана

Скопируйте план запроса в формате XML, полученный любым из описанных выше способов, из структуры плана, а затем вставьте его в виде строкового литерала в указание запроса USE PLAN предложения OPTION процедуры sp_create_plan_guide.

В самом XML-плане перед созданием структуры плана необходимо экранировать входящие в него кавычки ('). Например, план, содержащий WHERE A.varchar = 'This is a string', должен быть изменен и содержать WHERE A.varchar = ''This is a string''.

В следующем примере создается структура плана для плана запроса, собранного на шаге 1, и в параметр @hints вставляется XML Showplan для запроса. Для краткости в пример включена только часть выходных данных Showplan.

EXECUTE sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',  
@type = N'SQL',  
@module_or_batch = NULL,  
@params = NULL,  
@hints = N'OPTION(USE PLAN N''<ShowPlanXML xmlns=''''https://schemas.microsoft.com/sqlserver/2004/07/showplan''''   
    Version=''''0.5'''' Build=''''9.00.1116''''>  
    <BatchSequence><Batch><Statements><StmtSimple>  
    ...  
    </StmtSimple></Statements></Batch>  
    </BatchSequence></ShowPlanXML>'')';  
GO

Шаг 3. Проверка применения структуры плана к запросу

Выполните запрос еще раз и проверьте созданный план запроса. Убедитесь, что план выполнения совпадает с тем, что был указан в структуре плана.

См. также:

sp_create_plan_guide (Transact-SQL)
Указания запросов (Transact-SQL)
Руководства планов

Поделиться через