Sugerencias de consulta (Transact-SQL)

Sugerencias de consulta especifica que en toda la consulta se deben utilizar las sugerencias especificadas. Afectan a todos los operadores de la instrucción. Si hay un argumento UNION implicado en la consulta principal, solo la última consulta que implique una operación UNION puede contener la cláusula OPTION. Las sugerencias de consulta se especifican como parte de la cláusula OPTION. Si una o varias sugerencias de consulta provocan que el optimizador de consultas no genere un plan válido, se produce el error 8622.

Advertencia
Como el optimizador de consultas de SQL Server suele seleccionar el mejor plan de ejecución para las consultas, se recomienda que solo los desarrolladores y administradores de bases de datos experimentados usen estas sugerencias y que lo hagan como último recurso.

Se aplica a:

DELETE

INSERT

SELECT

UPDATE

MERGE

Convenciones de sintaxis de Transact-SQL

Sintaxis

<query_hint > ::= 
{ { HASH | ORDER } GROUP 
  | { CONCAT | HASH | MERGE } UNION 
  | { LOOP | MERGE | HASH } JOIN 
  | EXPAND VIEWS 
  | FAST number_rows 
  | FORCE ORDER 
  | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
  | KEEP PLAN 
  | KEEPFIXED PLAN
  | MAXDOP number_of_processors 
  | MAXRECURSION number 
  | OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
  | OPTIMIZE FOR UNKNOWN
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN 
  | USE PLAN N'xml_plan'
  | TABLE HINT ( exposed_object_name [ , <table_hint> [ [, ]...n ] ] )
}

<table_hint> ::=
[ NOEXPAND ] { 
    INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )
  | FORCESEEK [( index_value ( index_column_name [,... ] ) ) ]
  | FORCESCAN
  | HOLDLOCK 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | READUNCOMMITTED 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | SPATIAL_WINDOW_MAX_CELLS = integer
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK
}

Argumentos

• { HASH | ORDER } GROUP
  Especifica que las agregaciones especificadas en la cláusula GROUP BY o DISTINCT de la consulta deben usar hash o un orden.

• { MERGE | HASH | CONCAT } UNION
  Especifica que todas las operaciones UNION se deben realizar mediante la mezcla, hash o concatenación de conjuntos UNION. Si se especifica más de una sugerencia UNION, el optimizador de consultas seleccionará la estrategia menos costosa entre las sugerencias especificadas.

• { LOOP | MERGE | HASH } JOIN
  Especifica que todas las operaciones de combinación se realicen mediante LOOP JOIN, MERGE JOIN o HASH JOIN en toda la consulta. Si se especifica más de una sugerencia de combinación, el optimizador seleccionará la estrategia menos costosa de entre las permitidas.

  Si, en la misma consulta, en la cláusula FROM se especifica también una sugerencia de combinación para una pareja de tablas específica, la sugerencia de combinación tendrá prioridad en la combinación de las dos tablas aunque aún se deban respetar las sugerencias de consulta. Por tanto, es posible que la sugerencia de combinación para la pareja de tablas solo restrinja la selección de los métodos de combinación permitidos en la sugerencia de consulta. Para obtener más información, vea Sugerencias de combinación (Transact-SQL).

• EXPAND VIEWS
  Especifica que las vistas indizadas se expanden y que el optimizador de consultas no considerará ninguna vista indizada como sustituto de una parte de la consulta. Una vista se expande cuando se reemplaza el nombre de la vista por la definición de la vista en el texto de la consulta.

  Esta sugerencia de consulta virtualmente no permite el uso directo de vistas indizadas ni índices en vistas indizadas en el plan de consulta.

  La vista indizada no se expande solo si se hace referencia directa a la vista en la parte SELECT de la consulta y se especifica WITH (NOEXPAND) o WITH (NOEXPAND, INDEX( index_value [ ,...n ] ) ). Para obtener más información acerca de la sugerencia de consulta WITH (NOEXPAND), vea FROM.

  La sugerencia solo afecta a las vistas en la parte SELECT de las instrucciones, incluidas las vistas en las instrucciones INSERT, UPDATE, MERGE y DELETE.

• FAST number_rows
  Especifica que se optimice la consulta para una recuperación rápida de las primeras number_rows. Es un entero no negativo. Después de que se devuelven las primeras number_rows, la consulta continúa la ejecución y presenta su conjunto de resultados completo.

• FORCE ORDER
  Especifica que el orden de combinación que indica la sintaxis de la consulta se mantenga durante la optimización de la consulta. El uso de FORCE ORDER no afecta al posible comportamiento de inversión de roles del optimizador de consultas.

  [!NOTA]

  En una instrucción MERGE, se obtiene acceso a la tabla de origen antes que a la tabla de destino como el orden de combinación predeterminado, a menos que se especifique la cláusula WHEN SOURCE NOT MATCHED. Al especificar FORCE ORDER, se conserva este comportamiento predeterminado.

• KEEP PLAN
  Fuerza al optimizador de consultas a aumentar el umbral estimado para volver a compilar una consulta. El umbral estimado para volver a compilar es el punto en el que una consulta se vuelve a compilar automáticamente cuando se ha realizado el número estimado de cambios de columnas indizados en una tabla al ejecutar las instrucciones UPDATE, DELETE, MERGE o INSERT. Al especificar KEEP PLAN, se asegura de que no se volverá a compilar una consulta con tanta frecuencia cuando se producen varias actualizaciones en una tabla.

• KEEPFIXED PLAN
  Fuerza al optimizador de consultas a no compilar de nuevo una consulta debido a cambios en las estadísticas. Al especificar KEEPFIXED PLAN, se asegura de que solo se volverá a compilar una consulta si el esquema de las tablas subyacentes cambia o si sp_recompile se ejecuta en estas tablas.

• IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
  Impide que la consulta use un índice no clúster de almacén de columnas optimizado de memoria xVelocity. Si la consulta contiene la sugerencia de consulta para evitar el uso del índice de almacén de columnas y una sugerencia de índice para usar un índice de almacén de columnas, las sugerencias están en conflicto y la consulta devuelve un error.

• MAXDOP number
  Invalida la opción de configuración max degree of parallelism de sp_configure y el regulador de recursos para la consulta que especifica esta opción. La sugerencia de consulta MAXDOP puede superar el valor configurado con sp_configure. Si MAXDOP supera el valor configurado con el regulador de recursos, el Motor de base de datos usa el valor MAXDOP del regulador de recursos, descrito en ALTER WORKLOAD GROUP (Transact-SQL). Se pueden aplicar todas las reglas semánticas utilizadas con la opción de configuración max degree of parallelism cuando se utiliza la sugerencia de consulta MAXDOP. Para obtener más información, vea Establecer la opción de configuración del servidor Grado máximo de paralelismo.

  Advertencia
  Si MAXDOP se establece en cero, el servidor elige el grado máximo de paralelismo.

• MAXRECURSION number
  Especifica el número máximo de recursiones permitidas para esta consulta. number es un número entero no negativo entre 0 y 32767. Cuando se especifica 0, no se aplica ningún límite. Si no se especifica esta opción, el límite predeterminado para el servidor es 100.

  Cuando se alcanza el número predeterminado o especificado para el límite de MAXRECURSION durante la ejecución de la consulta, la consulta finaliza y se devuelve un error.

  Debido a este error, todos los efectos de la instrucción se revierten. Si la instrucción es una instrucción SELECT, es posible que no se devuelva ningún resultado o que los resultados sean parciales. Puede que los resultados parciales no incluyan todas las filas de los niveles de recursividad que superen el nivel de recursividad máximo especificado.

  Para obtener más información, vea WITH common_table_expression (Transact-SQL).

• OPTIMIZE FOR ( @variable\_name { UNKNOWN | = literal_constant } [ , ...n ] )
  Indica al optimizador de consultas que utilice un valor concreto para una variable local cuando la consulta se compila y optimiza. El valor se utiliza solo durante la optimización de la consulta y no durante la ejecución de la misma.

  • @variable\_name
    Es el nombre de una variable local que se utiliza en una consulta, a la que se puede asignar un valor para utilizarlo con la sugerencia de consulta OPTIMIZE FOR.

  • UNKNOWN
    Indica al optimizador de consultas que use datos estadísticos en lugar del valor inicial para determinar el valor de una variable local durante la optimización de la consulta.

  • literal_constant
    Es un valor constante literal al que se asigna a @variable\_name para usarlo con la sugerencia de consulta OPTIMIZE FOR. literal_constant se usa solo durante la optimización de la consulta y no como el valor de @variable\_name durante la ejecución de la consulta. literal_constant puede tener cualquier tipo de datos de sistema de SQL Server que se pueda expresar como una constante literal. El tipo de datos de literal_constant debe convertirse de forma implícita al tipo de datos al que @variable\_name hace referencia en la consulta.

  OPTIMIZE FOR puede contrarrestar el comportamiento predeterminado de detección de parámetros del optimizador o puede utilizarse cuando se crean guías de plan. Para obtener más información, vea Volver a compilar un procedimiento almacenado.

• OPTIMIZE FOR UNKNOWN
  Indica al optimizador de consultas que use datos estadísticos en lugar de los valores iniciales para todas las variables locales al compilar y optimizar la consulta, incluidos los parámetros creados mediante parametrización forzada.

  Si se usan OPTIMIZE FOR @variable\_name = literal_constant y OPTIMIZE FOR UNKNOWN en la misma sugerencia de consulta, el optimizador de consultas usará el valor literal_constant especificado para un valor determinado y UNKNOWN para los valores de las variables restantes. Los valores se usan solo durante la optimización de la consulta y no durante la ejecución de la misma.

• PARAMETERIZATION { SIMPLE | FORCED }
  Especifica las reglas de parametrización que aplica el optimizador de consultas de SQL Server cuando se compila la consulta.

  Importante
  La sugerencia de consulta PARAMETERIZATION solo puede especificarse en una guía de plan. No se puede especificar directamente en una consulta.

  SIMPLE indica al optimizador de consultas para que intente la parametrización simple. FORCED indica al optimizador que intente la parametrización forzada. La sugerencia de consulta PARAMETERIZATION se utiliza para invalidar la configuración actual de la opción SET de base de datos PARAMETERIZATION de una guía de plan. Para obtener más información, vea Especificar el comportamiento de parametrización de consultas por medio de guías de plan.

• RECOMPILE
  Indica a Motor de base de datos de SQL Server que descarte el plan generado para la consulta una vez ejecutada, obligando así al optimizador de consultas a que vuelva a compilar un plan de consulta la próxima vez que se ejecute la misma consulta. Sin especificar RECOMPILE, el Motor de base de datos almacena en la memoria caché planes de consulta y los reutiliza. Cuando se compilan planes de consulta, la sugerencia de consulta RECOMPILE utiliza los valores actuales de cualquier variable local de la consulta y, si la consulta está en un procedimiento almacenado, los valores actuales enviados a cualquier parámetro.

  RECOMPILE es una alternativa útil a la creación de un procedimiento almacenado que utiliza la cláusula WITH RECOMPILE cuando solo se debe volver a compilar un subconjunto de consultas del procedimiento almacenado, en lugar de todo el procedimiento almacenado. Para obtener más información, vea Volver a compilar un procedimiento almacenado. RECOMPILE también es útil al crear guías de plan.

• ROBUST PLAN
  Fuerza al optimizador de consultas a intentar aplicar un plan que funcione para el tamaño máximo de fila posible en detrimento del rendimiento. Cuando se procesa la consulta, es posible que las tablas intermedias y los operadores necesiten guardar y procesar filas más anchas que las filas de entrada. Las filas pueden llegar a ser tan anchas que, en algunos casos, el operador especificado no puede procesar la fila. Si esto sucede, el Motor de base de datos genera un error durante la ejecución de la consulta. Mediante la utilización de ROBUST PLAN, puede indicar al optimizador de consultas que no tenga en cuenta los planes de consulta donde pueda ocurrir este problema.

  Si no es posible realizar tal plan, el optimizador de consultas devuelve un error en lugar de diferir la detección de errores hasta la ejecución de la consulta. Las filas pueden contener columnas de longitud variable; el Motor de base de datos permite definir filas con un tamaño potencial máximo que supere la capacidad del Motor de base de datos para procesarlas. Normalmente, a pesar del tamaño potencial máximo, una aplicación almacena filas cuyo tamaño real se encuentra dentro de los límites que puede procesar el Motor de base de datos. Si el Motor de base de datos encuentra una fila demasiado larga, devuelve un error de ejecución.

• USE PLAN N**'xml_plan'**
  Fuerza al optimizador de consultas a usar un plan de consulta existente en una consulta especificada por 'xml_plan'. USE PLAN no puede especificarse con las instrucciones INSERT, UPDATE, MERGE ni DELETE.

• TABLE HINT (exposed_object_name [ , <table_hint> [ [, ]...n ] ] )
  Aplica la sugerencia de la tabla especificada a la tabla o vista que corresponde a exposed_object_name. Solo se recomienda usar una sugerencia de tabla como una sugerencia de consulta en el contexto de una guía de plan.

  exposed_object_name puede ser una de las referencias siguientes:

  • Cuando se usa un alias para la tabla o vista en la cláusula FROM de la consulta, el alias es exposed_object_name.

  • Cuando no se usa un alias, exposed_object_name es la coincidencia exacta de la tabla o vista a la que se hace referencia en la cláusula FROM. Por ejemplo, si se hace referencia a la tabla o vista con un nombre de dos partes, exposed_object_name es el mismo nombre de dos partes.

  Cuando se especifica exposed_object_name sin especificar además una sugerencia de tabla, se omite cualquier índice especificado en la consulta como parte de una sugerencia de tabla para el objeto y el optimizador de consultas determina el uso de índices. Puede emplear esta técnica para eliminar el efecto de una sugerencia de tabla INDEX cuando no se puede modificar la consulta original. Vea el ejemplo J.

• <table_hint> ::= { [ NOEXPAND ] { INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FORCESEEK [(index_value(index_column_name [,... ] )) ]| FORCESCAN | HOLDLOCK | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE |SPATIAL_WINDOW_MAX_CELLS | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }
  Es la sugerencia de tabla que se aplica a la tabla o vista que corresponde a exposed_object_name como una sugerencia de consulta. Para obtener una descripción de estas sugerencias, vea Sugerencias de tabla (Transact-SQL).

  Las sugerencias de tabla distintas de INDEX, FORCESCAN y FORCESEEK no están permitidas como sugerencias de consulta, a menos que la consulta ya tenga una cláusula WITH que especifique la sugerencia de tabla. Para obtener más información, vea la sección Comentarios.

  Advertencia
  Al especificar FORCESEEK con parámetros se limita el número de planes que el optimizador puede considerar en comparación con cuando se especifica FORCESEEK sin parámetros. Esto puede producir un error "No se puede generar el plan" en más casos. En una versión futura, las modificaciones internas realizadas en el optimizador pueden permitir que se consideren más planes.

Comentarios

No se pueden especificar sugerencias de consulta en una instrucción INSERT excepto cuando se usa una cláusula SELECT en la instrucción.

Solo se pueden especificar sugerencias de consulta en la consulta de nivel superior, no en las subconsultas. Cuando se especifica una sugerencia de tabla como una sugerencia de consulta, la sugerencia se puede especificar en la consulta de nivel superior o en una subconsulta; sin embargo, el valor especificado para exposed_object_name en la cláusula TABLE HINT debe coincidir exactamente con el nombre expuesto en la consulta o subconsulta.

Especificar sugerencias de tabla como sugerencias de consulta

Se recomienda usar la sugerencia de tabla INDEX, FORCESCAN o FORCESEEK como sugerencia de consulta únicamente en el contexto de una guía de plan. Las guías de plan son útiles cuando no se puede modificar la consulta original, por ejemplo, porque es una aplicación de otro fabricante. La sugerencia de consulta especificada en la guía de plan se agrega a la consulta antes de compilarla y optimizarla. Para las consultas ad hoc, utilice la cláusula TABLE HINT únicamente en las pruebas de instrucciones de guías de plan. Para todas las demás consultas ad hoc, se recomienda especificar estas sugerencias únicamente como sugerencias de tabla.

Cuando se especifican como una sugerencia de consulta, las sugerencias de tabla INDEX, FORCESCAN y FORCESEEK son válidas para los objetos siguientes:

• Tablas

• Vistas

• Vistas indizadas

• Expresiones de tabla comunes (la sugerencia se debe especificar en la instrucción SELECT cuyo conjunto de resultados rellena la expresión de tabla común)

• Vistas de administración dinámica

• Subconsultas con nombre

Se pueden especificar las sugerencias de tabla INDEX, FORCESCAN y FORCESEEK como sugerencias de consulta para una consulta que no tenga ninguna sugerencia de tabla existente, o bien se pueden usar para reemplazar en la consulta las respectivas sugerencias INDEX, FORCESCAN o FORCESEEK existentes. Las sugerencias de tabla distintas de INDEX, FORCESCAN y FORCESEEK no están permitidas como sugerencias de consulta, a menos que la consulta ya tenga una cláusula WITH que especifique la sugerencia de tabla. En este caso, también se debe especificar una consulta coincidente como sugerencia de consulta mediante el uso de TABLE HINT en la cláusula OPTION para conservar la semántica de la consulta. Por ejemplo, si la consulta contiene la sugerencia de tabla NOLOCK, la cláusula OPTION del parámetro @hints de la guía de plan también debe contener la sugerencia NOLOCK. Vea el ejemplo K. Cuando se especifica una sugerencia de tabla distinta de INDEX, FORCESCAN o FORCESEEK usando TABLE HINT en la cláusula OPTION sin una sugerencia de consulta coincidente, o viceversa, se genera el error 8702 (que indica que la cláusula OPTION puede hacer que la semántica de la consulta cambie) y la consulta produce un error.

Ejemplos

A.Usar MERGE JOIN

En el ejemplo siguiente se especifica que MERGE JOIN realiza la operación JOIN en la consulta.

USE AdventureWorks2012;
GO
SELECT * 
FROM Sales.Customer AS c
INNER JOIN Sales.vStoreWithAddresses AS sa 
    ON c.CustomerID = sa.BusinessEntityID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO

B.Usar OPTIMIZE FOR

En el ejemplo siguiente se indica al optimizador de consultas que use el valor 'Seattle' para la variable local @city\_name y que use datos estadísticos para determinar el valor de la variable local @postal\_code al optimizar la consulta.

USE AdventureWorks2012;
GO
DECLARE @city_name nvarchar(30);
DECLARE @postal_code nvarchar(15);
SET @city_name = 'Ascheim';
SET @postal_code = 86171;
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );
GO

C.Usar MAXRECURSION

MAXRECURSION se puede utilizar para impedir que una expresión de tabla común recursiva con formato incorrecto entre en un bucle infinito. En el ejemplo siguiente se crea un bucle infinito intencionadamente y se utiliza la sugerencia MAXRECURSION para limitar el número de niveles de recursividad a dos.

USE AdventureWorks2012;
GO
--Creates an infinite loop
WITH cte (CustomerID, PersonID, StoreID) AS
(
    SELECT CustomerID, PersonID, StoreID
    FROM Sales.Customer
    WHERE PersonID IS NOT NULL
  UNION ALL
    SELECT cte.CustomerID, cte.PersonID, cte.StoreID
    FROM cte 
    JOIN  Sales.Customer AS e 
        ON cte.PersonID = e.CustomerID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT CustomerID, PersonID, StoreID
FROM cte
OPTION (MAXRECURSION 2);
GO

Después de corregir el error de código, ya no se requiere MAXRECURSION.

D.Usar MERGE UNION

En el siguiente ejemplo se utiliza la sugerencia de consulta MERGE UNION.

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e1
UNION
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO

E.Usar HASH GROUP y FAST

En el siguiente ejemplo se utilizan las sugerencias de consulta HASH GROUP y FAST.

USE AdventureWorks2012;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10);
GO

F.Usar MAXDOP

En el siguiente ejemplo se utiliza la sugerencia de consulta MAXDOP.

USE AdventureWorks2012 ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2);
GO

G.Usar INDEX

Los ejemplos siguientes usan la sugerencia INDEX. El primer ejemplo especifica un índice único. El segundo ejemplo especifica varios índices para obtener una única referencia de tabla. En ambos ejemplos, como la sugerencia INDEX se aplica a una tabla que usa un alias, la cláusula TABLE HINT también debe especificar el mismo alias que el nombre del objeto expuesto.

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE e.OrganizationLevel = 2;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e, INDEX (IX_Employee_OrganizationLevel_OrganizationNode)))';
GO
EXEC sp_create_plan_guide 
    @name = N'Guide2', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE e.OrganizationLevel = 2;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e, INDEX(PK_Employee_BusinessEntityID, IX_Employee_OrganizationLevel_OrganizationNode)))';
GO

H.Usar FORCESEEK

En el siguiente ejemplo se utiliza la sugerencia de tabla FORCESEEK. Como la sugerencia INDEX se aplica a una tabla que usa un nombre de dos partes, la cláusula TABLE HINT también debe especificar el mismo nombre de dos partes que el nombre del objeto expuesto.

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide3', 
    @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.JobTitle
              FROM HumanResources.Employee
              JOIN Person.Person AS c ON HumanResources.Employee.BusinessEntityID = c.BusinessEntityID
              WHERE HumanResources.Employee.OrganizationLevel = 3
              ORDER BY c.LastName, c.FirstName;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT( HumanResources.Employee, FORCESEEK))';
GO

I.Usar varias sugerencias de tabla

El ejemplo siguiente aplica la sugerencia INDEX a una tabla y la sugerencia FORCESEEK a otra.

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide4', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode ) ) 
                       , TABLE HINT ( c, FORCESEEK) )';
GO

J.Usar TABLE HINT para invalidar una sugerencia de tabla existente

El ejemplo siguiente muestra cómo usar la sugerencia TABLE HINT sin especificar una sugerencia para anular temporalmente el comportamiento de la sugerencia de tabla INDEX que se especificó en la cláusula FROM de la consulta.

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide5', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_OrganizationLevel_OrganizationNode))
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e))';
GO

K.Especificar sugerencias de tabla que afectan a la semántica

El ejemplo siguiente contiene dos sugerencias de tabla en la consulta: NOLOCK, que afecta a la semántica, e INDEX, que no la afecta. Para conservar la semántica de la consulta, la sugerencia NOLOCK se especifica en la cláusula OPTIONS de la guía de plan. Además de la sugerencia NOLOCK, las sugerencias INDEX y FORCESEEK se especifican y reemplazan la sugerencia INDEX que no afecta a la semántica en la consulta cuando la instrucción se compila y optimiza.

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide6', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode) , NOLOCK, FORCESEEK ))';
GO

El ejemplo siguiente muestra un método alternativo para conservar la semántica de la consulta y permitir al optimizador elegir un índice distinto del índice que se especificó en la sugerencia de tabla. Esto se consigue especificando la sugerencia NOLOCK en la cláusula OPTIONS (porque afecta a la semántica) y especificando la palabra clave TABLE HINT solamente con una referencia de tabla y ninguna sugerencia INDEX.

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide7', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 2;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, NOLOCK))';
GO

Vea también

Referencia

Sugerencias (Transact-SQL)

sp_create_plan_guide (Transact-SQL)

sp_control_plan_guide (Transact-SQL)