Usar la sugerencia de consulta USE PLAN
La sugerencia de consulta USE PLAN toma xml_plan como argumento. xml_plan es un literal de cadena derivado del plan de consulta con formato XML que se genera para la consulta. La sugerencia de consulta USE PLAN se puede especificar como una sugerencia de consulta en una instrucción SQL independiente, o especificarse en el parámetro @hints de una guía de plan. Para adjuntar un plan de consultas a una guía de plan, recomendamos que use el parámetro xml_showplan en sp_create_plan_guide o el procedimiento almacenado sp_create_plan_guide_from_handle.
.gif "Nota importante") Importante |
|---|
Deberá indicar siempre xml_plan como literal Unicode especificando el prefijo N, como en N'xml_plan'. Esto último garantiza que no se pierda ninguno de los caracteres del plan específico del estándar Unicode cuando SQL Server Database Engine (Motor de base de datos de SQL Server) interprete la cadena. |
En SQL Server, pueden generarse planes de consulta con formato XML de las siguientes formas:
-
Importante
Cuando se generan planes de consulta mediante SET SHOWPLAN_XML, a las comillas (') que aparecen en el plan hay que agregarles unas segundas comillas antes de utilizar el plan con la sugerencia de consulta USE PLAN. Por ejemplo, a un plan que contiene WHERE A.varchar = 'This is a string' habrá que agregarle unas segundas comillas para que el código quede de este modo WHERE A.varchar = ''This is a string''.
Consultando la columna query_plan de la función de administración dinámica sys.dm_exec_query_plan.
Las clases de eventos Showplan XML, Showplan XML Statistics Profile y Showplan XML For Query Compile del Analizador de SQL Server.
Para obtener más información acerca de la forma de generar y analizar planes de consulta, vea Analizar una consulta.
El plan de consulta con formato XML especificado en xml_plan debe validarse con el esquema XSD Showplanxml.xsd en el directorio de instalación de SQL Server. Además, en la ruta de acceso que contiene los elementos <ShowPlanXML> <BatchSequence> <Batch> <Statements>, debe aparecer uno de los siguientes elementos:
Uno o varios elementos <StmtSimple>, de los cuales uno y sólo uno incluye un subelemento <QueryPlan>.
Un elemento <StmtCursor> que incluye exactamente un subelemento <CursorPlan>.
Uno o más elementos <StmtSimple> sin ningún subelemento <QueryPlan> y un elemento <StmtCursor> con un subelemento <CursorPlan>.
Puede modificar el plan antes de utilizarlo mediante USE PLAN, del mismo modo que al modificar órdenes y operadores de combinación y que al ajustar recorridos y búsquedas. Sin embargo, el formato del plan debe seguir coincidiendo con Showplanxml.xsd. No se podrá forzar un plan después haberlo modificado. Se producirá un error si se utiliza un plan en una sugerencia USE PLAN cuando dicho plan no sea uno de los planes que SQL Server normalmente consideraría para la consulta durante la optimización.
Los planes de consultas generados con la sugerencia de consulta USE PLAN se almacenan en memoria caché como cualquier otro plan de consultas.
Limitaciones de la sugerencia de consulta USE PLAN
Los cambios que se produzcan en la base de datos, como la eliminación de índices, pueden invalidar un plan de consulta especificado mediante USE PLAN. Un plan de consulta puede quedarse obsoleto aunque no se haga directamente referencia en el plan a un objeto eliminado. Por ejemplo, puede que no se haga referencia explícitamente a un índice único en un plan de consulta, pero que el índice exija de todos modos una restricción única en los datos. Un plan de consulta al que se hace referencia mediante USE PLAN puede utilizar esta restricción para impedir el uso de determinados operadores para exigir unicidad.
En algunas ocasiones, instalar un Service Pack o una nueva versión de SQL Server puede impedir que fuerce un plan generado en una versión anterior. Por lo tanto, deberán probarse todas las sugerencias USE PLAN siempre que se actualice el servidor.
El uso de la sugerencia USE PLAN en una consulta reemplaza todas las sugerencias de combinación y de índice utilizadas en la misma consulta.
USE PLAN no se puede utilizar con las sugerencias de consulta FORCE ORDER, EXPAND VIEWS, GROUP, UNION o JOIN, ni cuando SET FORCEPLAN se establece en ON.
Los únicos planes de consulta que pueden forzarse mediante USE PLAN son los que pueden encontrarse mediante la estrategia de búsqueda típica del optimizador de consultas. Por lo general, estos planes especifican que uno de los elementos secundarios de cada combinación esté en el nivel hoja. El uso de USE PLAN para forzar otros tipos de consultas puede producir un error.
Elementos de planes de consulta forzados
No todos los elementos de los planes de consulta con formato XML se fuerzan con la sugerencia USE PLAN. Los elementos que calculan expresiones escalares se omiten, así como algunas expresiones relacionales. El plan de consulta se fuerza para los siguientes tipos de elementos:
Estructura en árbol del plan y orden de evaluación.
Algoritmos de ejecución como tipos de combinación, ordenación y uniones.
Operaciones de índice como recorridos, búsquedas, intersecciones y uniones.
Objetos a los que se hace referencia explícitamente, como otras tablas, índices y funciones.
En concreto, SQL Server fuerza los elementos LogicalOp, PhysicalOp y NodeID que se encuentran por debajo del elemento <RelOp> y, también, cualquier subelemento que pertenezca al operador <PhysicalOp>. USE PLAN no tiene en consideración el resto del contenido que se encuentra por debajo del elemento <RelOp>.
| Importante |
|---|
La sugerencia de consulta USE PLAN no fuerza la información sobre estimaciones de cardinalidad dictada por el elemento <EstimateRows>. Como el optimizador de consultas utiliza la estimación de cardinalidad para determinar la cantidad de memoria que debe destinarse a la ejecución de una consulta, es necesario mantener estadísticas precisas, incluso cuando se utiliza USE PLAN. Para obtener más información, vea Utilizar las estadísticas para mejorar el rendimiento de las consultas. |
En la siguiente tabla se muestran los valores de operadores relacionales forzados con la sugerencia de consulta USE PLAN para los elementos PhysicalOp y LogicalOp, y cualquier subelemento necesario para cada valor PhysicalOp. En la tabla también se incluye información adicional necesaria para cada operador en forma de rutas de acceso de estilo XPath relativas al subelemento.
PhysicalOp |
LogicalOp |
Subelemento |
Información adicional1 |
|---|---|---|---|
Concatenation |
Concatenation Async Concat |
Concat |
No aplicable |
Constant Scan |
Constant Scan |
ConstantScan |
No aplicable |
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 |
No aplicable |
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 |
No aplicable |
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 |
No aplicable |
Merge Interval |
Merge Interval |
MergeInterval |
No aplicable |
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 |
No aplicable |
Parallelism |
Gather Streams Repartition Streams Distribute Streams |
Parallelism |
No aplicable |
Row Count Spool |
Eager Spool Lazy Spool |
RowCountSpool2 |
No aplicable |
Segment |
Segment |
Segment |
No aplicable |
Sequence |
Sequence |
Sequence |
No aplicable |
Sequence Project |
Compute Scalar |
SequenceProject |
No aplicable |
Sort |
Sort Distinct Sort |
Sort |
No aplicable |
Table Spool Index Spool |
Eager Spool Lazy Spool |
Spool2 |
@PrimaryNodeId (sólo para colas secundarias) ../RelOp/@NodeId (sólo para elementos RelOp que representan colas principales) |
Stream Aggregate |
Aggregate |
StreamAggregate |
No aplicable |
Switch |
Switch |
Switch |
No aplicable |
Table Scan |
Table Scan |
TableScan |
Object/@Database, Object/@Schema, Object/@Table |
Table-valued function |
Table-valued function |
TableValuedFunction |
Object/@Database, Object/@Schema, Object/@Table (el nombre de la función con valores de tabla es Object/@Table) |
Top |
Top |
Top |
No aplicable |
Sort |
Sort |
Sort |
No aplicable |
Top Sort |
TopN Sort |
TopSort |
No aplicable |
Table Insert |
Insert |
Update |
Object/@Table |
1 El número y el orden de estas entradas de cada operador relacional debe aparecer tal y como se muestra en la tabla para poder forzar un plan con USE PLAN.
2 La posibilidad de forzar un plan es limitada en cuanto a que si el plan contiene un subelemento <RowCountSpool>, puede aparecer en un plan forzado como un subelemento <RowCountSpool> o bien como un subelemento <Spool>. Del mismo modo, si el plan contiene un subelemento <Spool>, puede aparecer en un plan forzado como un subelemento <Spool> o <RowCountSpool>.
USE PLAN omite los operadores Assert, Bitmap, ComputeScalar y PrintDataFlow. USE PLAN tiene en cuenta el operador Filter, pero no puede forzarse su ubicación exacta en el plan.
Para obtener más información sobre los operadores lógicos y físicos que se utilizan en los planes de consulta, vea Referencia sobre operadores lógicos y físicos.
Compatibilidad con cursores
Puede utilizar la sugerencia de consulta USE PLAN junto con consultas que especifiquen cursores estáticos o de sólo avance rápido, solicitados a través de Transact-SQL o de una función de cursor de la API. Se admiten los cursores estáticos de Transact-SQL con una opción de sólo avance. No se admiten los cursores dinámicos de sólo avance controlados por conjunto de claves.
Para obtener más información, vea Usar la sugerencia de consulta USE PLAN en consultas con cursores.
Vea también