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 :
-
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''.
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.
Voir aussi