Partager via

  • Article

Utilisation de l'indicateur de requête USE PLAN

L'indicateur de requête USE PLAN considère un xml_plan comme un argument. xml_plan est un littéral chaîne dérivé du plan de requête au format XML qui est généré pour la requête. L'indicateur de requête USE PLAN peut être spécifié comme un indicateur de requête dans une instruction SQL autonome, ou spécifié dans le paramètre @hints d'un repère de plan. Pour joindre un plan de requête à un repère de plan, nous recommandons d'utiliser le paramètre xml_showplan dans la procédure stockée sp_create_plan_guide ou sp_create_plan_guide_from_handle.

Important

Vous devez toujours indiquer xml_plan sous forme de littéral Unicode en spécifiant le préfixe N, comme dans N'xml_plan'. Ce faisant, vous êtes sûr de ne pas perdre les caractères Unicode du plan lorsque le Moteur de base de données SQL Server interprète la chaîne.

Dans SQL Server, les plans de requête XML peuvent être générés des manières suivantes :

  • SET SHOWPLAN_XML

    Important

    Lorsque vous générez des plans de requête à l'aide de SET SHOWPLAN_XML, les apostrophes (') qui apparaissent dans le plan doivent être précédées par une deuxième apostrophe avant l'utilisation du plan par l'indicateur de requête USE PLAN. Par exemple, si un plan contient WHERE A.varchar = 'This is a string', vous devez utiliser un caractère d'échappement et modifier le code en WHERE A.varchar = ''This is a string''.

  • SET STATISTICS XML

  • Interrogation de la colonne query_plan de la fonction de gestion dynamique sys.dm_exec_query_plan.

  • Classes d'événements Showplan XML, Showplan XML Statistics Profile et Showplan XML For Query Compile du générateur de profils SQL Server.

Pour plus d'informations sur la génération et l'analyse des plans de requête, consultez Analyse d'une requête.

Le plan de requête XML spécifié dans xml_plan doit être validé par rapport au schéma XSD, Showplanxml.xsd, qui se trouve dans le répertoire d'installation de SQL Server. De plus, sous le chemin qui contient les éléments <ShowPlanXML> <BatchSequence> <Batch> <Statements>, vous devez trouver :

  • Un ou plusieurs éléments <StmtSimple> dont un seul qui contient un sous-élément <QueryPlan>.

  • Un élément <StmtCursor> qui a un seul sous-élément <CursorPlan>.

  • Un ou plusieurs éléments <StmtSimple> sans sous-élément <QueryPlan>, et un seul élément <StmtCursor> qui à un seul sous-élément <CursorPlan>.

Vous pouvez modifier le plan avant de l'utiliser avec USE PLAN, et notamment modifier l'ordre des jointures et les opérateurs, ou régler les analyses et les recherches. Toutefois, le format du plan doit toujours correspondre à Showplanxml.xsd. Vous ne pourrez pas procéder à l'application forcée d'un plan qui a été modifié. Une erreur se produit si le plan que vous utilisez dans un indicateur USE PLAN n'est pas l'un des plans que SQL Server envisage généralement pour la requête lors de l'optimisation.

Les plans de requête générés avec l'indicateur de requête USE PLAN sont mis en cache comme tout autre plan de requête.

Limites de l'indicateur de requête USE PLAN

Les modifications apportées à une base de données, telles que la suppression d'index, peuvent invalider un plan de requête spécifié par USE PLAN. Un plan de requête peut devenir obsolète même s'il n'est pas directement fait référence à un objet qui a été supprimé. Il peut arriver, par exemple, qu'un index unique ne soit pas explicitement mentionné dans un plan de requête, mais que l'index impose néanmoins une contrainte d'unicité sur les données. Un plan de requête auquel USE PLAN fait référence peut utiliser cette contrainte pour éviter d'avoir recours à certains opérateurs qui s'assurent de l'unicité.

Il arrive parfois que l'installation d'un service pack ou d'une nouvelle version de SQL Server vous empêche de forcer l'application d'un plan généré par une version antérieure. Il est, par conséquent, recommandé de tester tous les indicateurs USE PLAN à chaque mise à niveau du serveur.

Un indicateur USE PLAN utilisé dans une requête est prioritaire sur tous les indicateurs de jointure et d'index utilisés dans la même requête.

Il est impossible d'utiliser USE PLAN avec les indicateurs de requête FORCE ORDER, EXPAND VIEWS, GROUP, UNION ou JOIN, ou lorsque SET FORCEPLAN est défini sur ON.

Seuls les plans de requête qu'il est possible de localiser autrement par la stratégie de recherche classique de l'optimiseur de requête peuvent faire l'objet d'une application forcée par USE PLAN. Ces plans spécifient généralement qu'un enfant de chaque jointure se trouve au niveau feuille. Si vous utilisez USE PLAN pour forcer d'autres types de requêtes, une erreur sera générée.

Éléments forcés du plan de requête

Les éléments du plan de requête XML ne sont pas tous appliqués de force avec l'indicateur USE PLAN. Les éléments qui calculent des expressions scalaires sont ignorés ainsi que certaines expressions relationnelles. Le plan de requête est appliqué de force pour les types d'éléments suivants :

  • Structure arborescente du plan et ordre d'évaluation

  • Algorithmes d'exécution tels que les types de jointures, les tris et les unions

  • Opérations d'index telles que les analyses, les recherches, les intersections et les unions

  • Objets référencés explicitement, par exemple d'autres tables, index et fonctions

En particulier, SQL Server force les éléments LogicalOp, PhysicalOp et NodeID trouvés sous l'élément <RelOp>, ainsi que tout sous-élément qui appartient à l'opérateur <PhysicalOp>. Tout autre contenu figurant sous l'élément <RelOp> n'est pas pris en compte par USE PLAN.

Important

Les informations relatives aux estimations de cardinalité menées par l'élément <EstimateRows> ne sont pas imposées par l'indicateur de requête USE PLAN. Dans la mesure où l'optimiseur de requête se sert des estimations de cardinalité pour déterminer la quantité de mémoire à allouer à l'exécution d'une requête, vous avez tout intérêt à conserver des statistiques précises même si vous utilisez USE PLAN. Pour plus d'informations, consultez Utilisation des statistiques pour améliorer les performances des requêtes.

Le tableau suivant répertorie les valeurs des opérateurs relationnels qui sont forcées avec l'indicateur de requête pour les éléments PhysicalOp et LogicalOp, ainsi que tout sous-élément requis pour chacune des valeurs PhysicalOp. Le tableau regroupe aussi des informations complémentaires requises pour chaque opérateur sous la forme de chemins de style XPath relatifs au sous-élément.

PhysicalOp

LogicalOp

Sous-élément

Informations supplémentaires1

Concatenation

Concatenation

Async Concat

Concat

Non applicable

Constant Scan

Constant Scan

ConstantScan

Non applicable

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

Non applicable

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

Non applicable

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

Non applicable

Merge Interval

Merge Interval

MergeInterval

Non applicable

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

Non applicable

Parallelism

Gather Streams

Repartition Streams

Distribute Streams

Parallelism

Non applicable

Row Count Spool

Eager Spool

Lazy Spool

RowCountSpool2

Non applicable

Segment

Segment

Segment

Non applicable

Sequence

Sequence

Sequence

Non applicable

Sequence Project

Compute Scalar

SequenceProject

Non applicable

Sort

Sort

Distinct Sort

Sort

Non applicable

Table Spool

Index Spool

Eager Spool

Lazy Spool

Spool2

@PrimaryNodeId (pour les fichiers d'attente secondaires uniquement)

../RelOp/@NodeId (pour les éléments RelOp représentant des fichiers d'attente principaux uniquement)

Stream Aggregate

Aggregate

StreamAggregate

Non applicable

Switch

Switch

Switch

Non applicable

Table Scan

Table Scan

TableScan

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

Table-valued function

Table-valued function

TableValuedFunction

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

(le nom de la fonction table est Object/@Table)

Top

Top

Top

Non applicable

Sort

Sort

Sort

Non applicable

Top Sort

TopN Sort

TopSort

Non applicable

Table Insert

Insert

Update

Object/@Table

1 Le nombre et l'ordre de ces entrées pour chaque opérateur relationnel doivent apparaître comme le montre le tableau pour forcer l'application d'un plan avec USE PLAN.

2 La possibilité de forcer un plan est limitée en ce sens que si le plan contient un sous-élément <RowCountSpool>, il risque d'apparaître dans un plan forcé comme un sous-élément <RowCountSpool> ou <Spool>. De même, si le plan contient un sous-élément <Spool>, ce dernier peut apparaître dans un plan forcé comme un sous-élément <Spool> ou <RowCountSpool>.

Les opérateurs Assert, Bitmap, ComputeScalar et PrintDataFlow sont ignorés par USE PLAN. L'opérateur Filter est pris en compte par USE PLAN, mais son emplacement exact dans le plan ne peut pas être imposé.

Pour plus d'informations sur les opérateurs logiques et physiques utilisés dans les plans de requête, consultez Référence des opérateurs physiques et logiques.

Prise en charge des curseurs

Vous pouvez utiliser l'indicateur de requête USE PLAN avec des requêtes qui spécifient des curseurs statiques ou rapides avant uniquement, qu'ils soient requis par Transact-SQL ou une fonction curseur API. Les curseurs statiques Transact-SQL avec une option avant uniquement sont pris en charge. Les curseurs dynamiques, les curseurs pilotés par jeu de clés et les curseurs avant uniquement ne sont pas pris en charge.

Pour plus d'informations, consultez Utilisation de l'indicateur de requête USE PLAN sur les requêtes avec curseurs.