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

  • Статья

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

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

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

  1. Запишите текущий план для каждого ответственного запроса при помощи хранимой процедуры sp_create_plan_guide, а затем укажите план запроса в подсказке в запросе USE PLAN.

  2. Убедитесь в том, что структура плана применена к запросу.

  3. Обновите базу данных до SQL Server 2008.

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

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

  4. Если после обновления выбираются менее эффективные планы, включите все или некоторые из структур планов, заменив ими новые.

Пример

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

Шаг 1. Получите план

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

Дополнительные сведения о создании и анализе планов запросов см. в разделе Анализ запроса.

Следующий пример собирает план запроса для инструкции 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. Убедитесь в том, что к запросу применена структура плана

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