• Статья

Работа с подсказкой USE PLAN

Подсказка USE PLAN принимает в качестве аргумента xml_plan. xml_plan представляет собой строковый литерал, полученный в XML–формате из плана, созданного для запроса. Подсказку в запросе USE PLAN можно указать в виде подсказки в запросе в изолированной инструкции SQL или задать в параметре @hints структуры плана. Чтобы присоединить план запроса к структуре плана, рекомендуется использовать параметр xml_showplan в хранимой процедуре sp_create_plan_guide или хранимую процедуру sp_create_plan_guide_from_handle.

Важное примечаниеВажно!

Параметр xml_plan всегда нужно указывать как литерал в Юникоде, используя префикс N, например N'xml_plan'. Это гарантирует, что все символы в плане, характерные для стандарта Юникод, не будут утеряны при интерпретации строки сервером SQL Server Database Engine.

В SQL Server планы запросов в формате XML могут быть созданы следующими способами.

  • SET SHOWPLAN_XML

    Важное примечаниеВажно!

    При формировании планов запросов с помощью SET SHOWPLAN_XML одинарные кавычки ('), встречающиеся в плане, должны экранироваться вторыми кавычками, перед тем как план можно будет использовать в запросе с подсказкой USE PLAN. Например, план, содержащий WHERE A.varchar = 'This is a string', должен быть изменен и содержать WHERE A.varchar = ''This is a string''.

  • SET STATISTICS XML

  • Запрос к столбцу query_plan функции динамического управления sys.dm_exec_query_plan.

  • Классы событий Showplan XML, Showplan XML Statistics Profile и Showplan XML For Query Compile приложения SQL Server Profiler.

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

План запроса в формате XML, указанный в xml_plan , должен соответствовать определению схемы (XSD) Showplanxml.xsd, находящемуся в каталоге установки SQL Server. Кроме того, в конце пути, создаваемом элементами <ShowPlanXML> <BatchSequence> <Batch> <Statements>, должна присутствовать одна из следующих конструкций.

  • Один или более элементов <StmtSimple>, только один из которых содержит вложенный элемент <QueryPlan>.

  • Один элемент <StmtCursor>, содержащий только один вложенный элемент <CursorPlan>.

  • Один или более элементов <StmtSimple> без вложенных элементов <QueryPlan> и один элемент <StmtCursor>, содержащий один вложенный элемент <CursorPlan>.

Перед использованием плана с помощью USE PLAN его можно изменить, например изменив порядок и операторы соединений или настроив операции просмотра и поиска. Однако формат плана по-прежнему должен соответствовать Showplanxml.xsd. Может не удаться форсировать план после его изменения. Если в подсказке USE PLAN используется план, не входящий во множество планов, которые SQL Server обычно рассматривает во время оптимизации запроса, то произойдет ошибка.

Планы запросов, созданные с подсказкой в запросе USE PLAN, кэшируются так же, как и другие планы запросов.

Ограничения работы в запросе с подсказкой USE PLAN

Изменения базы данных, например удаление индексов, могут сделать недействительным план запроса, указанный USE PLAN. План запроса становится устаревшим, даже если на удаляемый объект нет прямых ссылок в плане. Например, явных ссылок на уникальный индекс в плане запроса может не быть, но индекс, тем не менее, накладывает на данные ограничение, гарантирующее уникальность. План запроса, на который ссылается USE PLAN, может использовать это ограничение, чтобы избежать применения определенных операторов для принудительного различения результатов.

Иногда установка пакета обновления или нового выпуска SQL Server может не дать форсировать план, созданный предыдущей версией. Поэтому все подсказки USE PLAN должны тестироваться после каждого обновления сервера.

Использование подсказки USE PLAN в запросе замещает все подсказки соединений и индексов, использующиеся в этом же запросе.

Подсказка USE PLAN не может использоваться с подсказками запросов FORCE ORDER, EXPAND VIEWS, GROUP, UNION или JOIN либо если параметру SET FORCEPLAN присвоено значение ON.

Форсировать с помощью USE PLAN можно только те планы запросов, которые были бы найдены обычной поисковой стратегией оптимизатора запросов. В таких планах обычно указывается, что один из потомков каждого соединения должен быть на конечном уровне. Использование USE PLAN для форсирования других типов запросов приведет к ошибке.

Форсируемые элементы плана запроса

Не все элементы плана запроса в формате XML форсируются с помощью подсказки USE PLAN. Элементы, вычисляющие скалярные выражения, пропускаются так же, как и некоторые выражения отношений. План запроса форсируется для следующих типов элементов.

  • Структура дерева плана и порядок вычислений.

  • Алгоритмы выполнения, например типы объединений, сортировок и соединений.

  • Операции над индексами, например просмотр, поиск, пересечение и объединение.

  • Объекты, на которые даны явные ссылки, например таблицы, индексы и функции.

В частности, SQL Server форсирует элементы LogicalOp, PhysicalOp и NodeID, найденные в элементе <RelOp>, а также все вложенные элементы, имеющие отношение к оператору <PhysicalOp>. Остальное содержимое элемента <RelOp> при использовании USE PLAN не рассматривается.

Важное примечаниеВажно!

Информация об оценке количества элементов, определяемая элементом <EstimateRows>, не форсируется в запросе подсказкой USE PLAN. Так как оптимизатор запросов использует оценки количества элементов результатов для определения количества памяти, которое понадобится запросу, следует поддерживать аккуратную статистику даже при использовании USE PLAN. Дополнительные сведения см. в разделе Использование статистики для повышения производительности запросов.

В следующей таблице перечислены значения операторов отношения, которые форсируются подсказкой в запросе USE PLAN для элементов PhysicalOp и LogicalOp, а также все вложенные элементы, необходимые для каждого значения PhysicalOp. В эту таблицу также включены дополнительные сведения, которые требуются каждому оператору в виде путей XPath относительно вложенных элементов.

PhysicalOp

LogicalOp

Вложенный элемент

Дополнительная информация1

Concatenation

Concatenation

Async Concat

Concat

Неприменимо

Constant Scan

Constant Scan

ConstantScan

Неприменимо

Deleted Scan

Deleted Scan

DeletedScan

Object/@Table

UDX

UDX

Extension

@UDXName

Hash Match

Inner Join

Left Outer Join

Right Outer Join

Full Outer Join

Left Semi Join

Left Anti Semi Join

Right Semi Join

Right Anti Semi Join

Aggregate

Partial Aggregate

Flow Distinct

Union

Hash

Неприменимо

RID Lookup

RID Lookup

IndexScan

Object/@Database, Object/@Schema, Object/@Table

Index Scan

Clustered Index Scan

Index Scan

Clustered Index Scan

IndexScan

Object/@Database, Object/@Schema, Object/@Table Object/@Index

Index Seek

Clustered Index Seek

Index Seek

Clustered Index Seek

IndexScan

Object/@Database, Object/@Schema, Object/@Table Object/@Index

Inserted Scan

Inserted Scan

InsertedScan

Object/@Table

Log Row Scan

Log Row Scan

LogRowScan

Неприменимо

Merge Join

Inner Join

Left Outer Join

Right Outer Join

Full Outer Join

Left Semi Join

Left Anti Semi Join

Right Semi Join

Right Anti Semi Join

Cross Join

Concatenation

Union

Merge

Неприменимо

Merge Interval

Merge Interval

MergeInterval

Неприменимо

Nested Loops

Inner Join

Left Outer Join

Right Outer Join

Full Outer Join

Left Semi Join

Left Anti Semi Join

Right Semi Join

Right Anti Semi Join

Cross Join

NestedLoops

Неприменимо

Parallelism

Gather Streams

Repartition Streams

Distribute Streams

Parallelism

Неприменимо

Row Count Spool

Eager Spool

Lazy Spool

RowCountSpool2

Неприменимо

Segment

Segment

Segment

Неприменимо

Sequence

Sequence

Sequence

Неприменимо

Sequence Project

Compute Scalar

SequenceProject

Неприменимо

Sort

Sort

Distinct Sort

Sort

Неприменимо

Table Spool

Index Spool

Eager Spool

Lazy Spool

Spool2

@PrimaryNodeId (только для вторичных буферов)

../RelOp/@NodeId (для элементов RelOp, представляющих только первичные буферы)

Stream Aggregate

Aggregate

StreamAggregate

Неприменимо

Switch

Switch

Switch

Неприменимо

Table Scan

Table Scan

TableScan

Object/@Database, Object/@Schema, Object/@Table

Table-valued function

Table-valued function

TableValuedFunction

Object/@Database, Object/@Schema, Object/@Table

(имя возвращающей табличное значение функции находится в Object/@Table)

Top

Top

Top

Неприменимо

Sort

Sort

Sort

Неприменимо

Top Sort

TopN Sort

TopSort

Неприменимо

Table Insert

Insert

Update

Object/@Table

1 Чтобы форсировать план с помощью USE PLAN, число и порядок операндов для каждого оператора отношения должны быть такими как показано в таблице.

2 Возможность форсировать план ограничена тем, что если план содержит вложенный элемент <RowCountSpool>, то в форсированном плане он может появиться как вложенный элемент <RowCountSpool> или как вложенный элемент <Spool>. Аналогичным образом, если в плане содержится вложенный элемент <Spool>, то в форсированном плане он может появиться как вложенный элемент <Spool> или как вложенный элемент <RowCountSpool>.

Операторы Assert, Bitmap, ComputeScalar и PrintDataFlow подсказкой USE PLAN не рассматриваются. Оператор Filter подсказкой USE PLAN рассматривается, но его конкретное расположение в плане не может быть форсировано.

Дополнительные сведения о логических и физических операторах в планах запросов см. в разделе Справочник по логическим и физическим операторам.

Поддержка курсоров

Подсказку USE PLAN можно использовать вместе с запросами, определяющими статические или опережающие курсоры, которые могут запрашиваться как с помощью Transact-SQL, так и через функции работы с курсорами API. Поддерживаются однонаправленные статические курсоры Transact-SQL. Динамические, управляемые набором ключей и однонаправленные курсоры не поддерживаются.

Дополнительные сведения см. в разделе Использование подсказки USE PLAN в запросах с курсорами.