Sugerencias de consulta (Transact-SQL)

Article
06/13/2024

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

Las sugerencias de consulta especifican que las sugerencias indicadas se utilizan en el ámbito de una consulta. Afectan a todos los operadores de la instrucción. Si UNION participa en la consulta principal, solo la última consulta que implica una UNION operación puede tener la OPTION cláusula . Las sugerencias de consulta se especifican como parte de la cláusula OPTION. El error 8622 se produce si una o varias sugerencias de consulta provocan que el optimizador de consultas no genere un plan válido.

Precaución

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:

Convenciones de sintaxis de Transact-SQL

Sintaxis

<query_hint> ::=
{ { HASH | ORDER } GROUP
  | { CONCAT | HASH | MERGE } UNION
  | { LOOP | MERGE | HASH } JOIN
  | DISABLE_OPTIMIZED_PLAN_FORCING
  | EXPAND VIEWS
  | FAST <integer_value>
  | FORCE ORDER
  | { FORCE | DISABLE } EXTERNALPUSHDOWN
  | { FORCE | DISABLE } SCALEOUTEXECUTION
  | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
  | KEEP PLAN
  | KEEPFIXED PLAN
  | MAX_GRANT_PERCENT = <numeric_value>
  | MIN_GRANT_PERCENT = <numeric_value>
  | MAXDOP <integer_value>
  | MAXRECURSION <integer_value>
  | NO_PERFORMANCE_SPOOL
  | OPTIMIZE FOR ( @variable_name { UNKNOWN | = <literal_constant> } [ , ...n ] )
  | OPTIMIZE FOR UNKNOWN
  | PARAMETERIZATION { SIMPLE | FORCED }
  | QUERYTRACEON <integer_value>
  | RECOMPILE
  | ROBUST PLAN
  | USE HINT ( <use_hint_name> [ , ...n ] )
  | USE PLAN N'<xml_plan>'
  | TABLE HINT ( <exposed_object_name> [ , <table_hint> [ [ , ] ...n ] ] )
  | FOR TIMESTAMP AS OF '<point_in_time>'
}

<table_hint> ::=
{ NOEXPAND [ , INDEX ( <index_value> [ , ...n ] ) | INDEX = ( <index_value> ) ]
  | 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
  | SNAPSHOT
  | SPATIAL_WINDOW_MAX_CELLS = <integer_value>
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK
}

<use_hint_name> ::=
{ 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'
  | 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'
  | 'ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES'
  | 'ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES'
  | 'DISABLE_BATCH_MODE_ADAPTIVE_JOINS'
  | 'DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'
  | 'DISABLE_DEFERRED_COMPILATION_TV'
  | 'DISABLE_INTERLEAVED_EXECUTION_TVF'
  | 'DISABLE_OPTIMIZED_NESTED_LOOP'
  | 'DISABLE_OPTIMIZER_ROWGOAL'
  | 'DISABLE_PARAMETER_SNIFFING'
  | 'DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'
  | 'DISABLE_TSQL_SCALAR_UDF_INLINING'
  | 'DISALLOW_BATCH_MODE'
  | 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'
  | 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'
  | 'FORCE_DEFAULT_CARDINALITY_ESTIMATION'
  | 'FORCE_LEGACY_CARDINALITY_ESTIMATION'
  | 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n'
  | 'QUERY_PLAN_PROFILE'
}

Argumentos

{ HASH | ORDER } GROUP

Especifica que las agregaciones que describe la cláusula o DISTINCT la GROUP BY consulta deben usar hash o ordenación.

{ MERGE | HASH | CONCAT } UNION

Especifica que todas las operaciones se ejecutan mediante la UNION combinación, el hash o la concatenación UNION de conjuntos. Si se especifica más de una UNION sugerencia, el optimizador de consultas selecciona la estrategia menos costosa de esas sugerencias especificadas.

{ LOOP | MERGE | HASH } JOIN

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

Si especifica una sugerencia de combinación en la misma cláusula de FROM consulta para un par de tabla específico, esta sugerencia de combinación tiene prioridad en la combinación de las dos tablas. Las sugerencias de consulta, sin embargo, todavía se deben respetar. La sugerencia de combinación para el par de tablas solo puede restringir la selección de los métodos de combinación permitidos en la sugerencia de consulta. Para obtener más información, consulte Sugerencias de combinación.

DISABLE_OPTIMIZED_PLAN_FORCING

Se aplica a: SQL Server (a partir de SQL Server 2022 [16.x])

Deshabilita el forzado de plan optimizado en una consulta.

El forzado de plan optimizado reduce la sobrecarga de compilación causado por la repetición de consultas forzadas. Una vez generado el plan de ejecución de consultas, los pasos de compilación específicos se almacenan para que puedan reutilizarse como un script de reproducción para optimización. Un script de reproducción para optimización se almacena como parte del XML del plan de presentación comprimido en el almacén de consultas, en un atributo OptimizationReplay oculto.

EXPAND VIEWS

Especifica que las vistas indexadas se expanden. También especifica que el optimizador de consultas no considera ninguna vista indexada como reemplazo de ninguna parte de la consulta. Una vista se expande cuando la definición de la vista reemplaza el nombre 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.

Nota:

La vista indizada permanece condensada si hay una referencia directa a la vista en la parte de SELECT la consulta. La vista también permanece condensada si especifica WITH (NOEXPAND) o WITH (NOEXPAND, INDEX( <index_value> [ , *...n* ] ) ). Para obtener más información sobre la sugerencia NOEXPANDde consulta , vea Uso de NOEXPAND.

La sugerencia solo afecta a las vistas de la parte de SELECT las instrucciones , incluidas esas vistas en INSERTlas instrucciones , UPDATE, MERGEy DELETE .

FAST integer_value

Especifica que la consulta está optimizada para una recuperación rápida de las primeras integer_value filas. Este resultado es un entero no negativo. Después de que se devuelven las primeras integer_value filas, 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 FORCE ORDER de no afecta al posible comportamiento de inversión de roles del optimizador de consultas.

Nota:

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

{ FORCE | DISABLE } EXTERNALPUSHDOWN

Fuerza o deshabilita la aplicación del cálculo de expresiones válidas en Hadoop. Solo se aplica a las consultas que usan PolyBase. No se inserta en Azure Storage.

{ FORCE | DISABLE } SCALEOUTEXECUTION

Fuerce o deshabilite la ejecución de escalado horizontal de consultas de PolyBase que usan tablas externas en Clústeres de macrodatos de SQL Server 2019. Esta sugerencia solo se respeta con una consulta que use la instancia maestra de un clúster de macrodatos de SQL. El escalado horizontal se produce en el grupo de proceso del clúster de macrodatos.

KEEP PLAN

Cambia los umbrales de recompilación de las tablas temporales y los hace idénticos a los umbrales de las tablas permanentes. El umbral estimado de recompilación inicia una recompilación automática para la consulta cuando se realiza el número estimado de cambios de columna indizada en una tabla mediante la ejecución de una de las siguientes instrucciones:

UPDATE
DELETE
MERGE
INSERT

Especificar KEEP PLAN garantiza que una consulta no se vuelva a compilar con tanta frecuencia cuando hay 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. Especificar KEEPFIXED PLAN garantiza que una consulta se vuelva a compilar solo si cambia el esquema de las tablas subyacentes o si sp_recompile se ejecuta en esas tablas.

IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX

Se aplica a: SQL Server (a partir de SQL Server 2012 (11.x)).

Impide que la consulta use un índice no agrupado de almacén de columnas optimizado para memoria. 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.

MAX_GRANT_PERCENT = <numeric_value>

Se aplica a: SQL Server [a partir de SQL Server 2012 (11.x) Service Pack 3, SQL Server 2014 (12.x) Service Pack 2] y Azure SQL Database.

Tamaño máximo de concesión de memoria en PERCENT el límite de memoria configurado. Se garantiza que la consulta no supere este límite si la consulta se ejecuta en un grupo de recursos definido por el usuario. En este caso, si la consulta no tiene la memoria mínima necesaria, el sistema genera un error. Si una consulta se ejecuta en el grupo de sistemas (predeterminado), obtiene como mínimo la memoria necesaria para ejecutarse. El límite real puede ser menor si la configuración de Resource Governor es inferior al valor especificado por esta sugerencia. Los valores válidos están comprendidos entre 0,0 y 100,0.

La sugerencia de concesión de memoria no está disponible para la creación de índices o la recompilación de índices.

MIN_GRANT_PERCENT = <numeric_value>

Se aplica a: SQL Server [a partir de SQL Server 2012 (11.x) Service Pack 3, SQL Server 2014 (12.x) Service Pack 2] y Azure SQL Database.

Tamaño mínimo de concesión de memoria en PERCENT el límite de memoria configurado. Se garantiza que la consulta obtendrá MAX(required memory, min grant) porque se requiere al menos la memoria necesaria para iniciar una consulta. Los valores válidos están comprendidos entre 0,0 y 100,0.

La opción de concesión de memoria min_grant_percent invalida la opción sp_configure (memoria mínima por consulta -KB-) independientemente del tamaño. La sugerencia de concesión de memoria no está disponible para la creación de índices o la recompilación de índices.

MAXDOP <integer_value>

Se aplica a: SQL Server (a partir de SQL Server 2008 [10.0.x]) y Azure SQL Database.

Invalida la opción de configuración de grado máximo de paralelismo de sp_configure. También invalida Resource Governor para la consulta que especifica esta opción. La MAXDOP sugerencia de consulta puede superar el valor configurado con sp_configure. Si MAXDOP supera el valor configurado con Resource Governor, el Motor de base de datos usa el valor de Resource GovernorMAXDOP, descrito en ALTER WORKLOAD GROUP. Todas las reglas semánticas usadas con la opción de configuración grado máximo de paralelismo son aplicables cuando se usa la MAXDOP sugerencia de consulta. 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 <integer_value>

Especifica el número máximo de recursiones permitidas para esta consulta. number es un entero positivo entre 0 y 32.767. 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 especificado o predeterminado para MAXRECURSION el límite 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 SELECT instrucción , se pueden devolver resultados parciales o ningún resultado. 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, consulte WITH common_table_expression.

NO_PERFORMANCE_SPOOL

Se aplica a: SQL Server (a partir de SQL Server 2016 (13.x)) y Azure SQL Database.

Impide que se agregue un operador de cola de impresión a planes de consulta (excepto a los planes en los que se requiere que la cola de impresión garantice una semántica de actualización válida). En algunos escenarios, el operador de cola de impresión puede reducir el rendimiento. Por ejemplo, la cola de impresión usa tempdb, y se puede producir la contención de tempdb si se ejecutan muchas consultas simultáneas con las operaciones de cola de impresión.

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

Nombre de una variable local usada en una consulta, a la que se puede asignar un valor para su uso con la sugerencia de OPTIMIZE FOR consulta.
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

Valor constante literal al que se va a asignar @variable_name para su uso con la sugerencia de OPTIMIZE FOR consulta. literal_constant se usa solo durante la optimización de las consultas y no como valor de @variable_name durante la ejecución de las consultas. literal_constant puede tener cualquier tipo de datos del sistema de SQL Server que se pueda expresar como una constante literal. El tipo de datos de literal_constant se debe poder cambiar de forma implícita al tipo de datos al que @variable_name hace referencia en la consulta.

OPTIMIZE FOR puede contrarrestar el comportamiento de detección de parámetros predeterminado del optimizador. También se usa OPTIMIZE FOR al crear guías de plan. Para más información, vea Volver a compilar un procedimiento almacenado.

OPTIMIZE FOR UNKNOWN

Indica al optimizador de consultas que use la selectividad media del predicado en todos los valores de columna, en lugar de usar el valor del parámetro en tiempo de ejecución cuando la consulta se compile y optimice.

Si utiliza OPTIMIZE FOR @variable_name = <literal_constant> y OPTIMIZE FOR UNKNOWN en la misma sugerencia de consulta, el optimizador de consultas usa el elemento literal_constant especificado para un valor específico. El optimizador de consultas usa 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 el optimizador de consultas de SQL Server aplica a la consulta cuando se compila.

Importante

La PARAMETERIZATION sugerencia de consulta solo se puede especificar dentro de una guía de plan para invalidar la configuración actual de la PARAMETERIZATION opción de base de datos SET . No se puede especificar directamente en una consulta.

Para más información, vea Especificar el comportamiento de parametrización de consultas por medio de guías de plan.

SIMPLE indica al optimizador de consultas que intente una parametrización sencilla. FORCED indica al optimizador de consultas que intente la parametrización forzada. Para más información, vea Parametrización forzada en la guía de arquitectura de procesamiento de consultas y Parametrización simple en la guía de arquitectura de procesamiento de consultas.

QUERYTRACEON <integer_value>

Esta opción permite habilitar una marca de seguimiento que afecte al plan solo durante la compilación de una única consulta. Como sucede con otras opciones de nivel de consulta, se puede usar junto con guías de plan para hacer coincidir el texto de una consulta que se ejecuta desde cualquier sesión y aplicar automáticamente una marca de seguimiento que afecte al plan al compilar esta consulta. La QUERYTRACEON opción solo se admite para las marcas de seguimiento del optimizador de consultas. Para obtener más información, vea Marcas de seguimiento.

El uso de esta opción no devuelve ningún error o advertencia si se usa un número de marca de seguimiento no admitido. Si la marca de seguimiento especificada no afecta a un plan de ejecución de consulta, la opción se omite de forma silenciosa.

Para usar más de una marca de seguimiento en una consulta, especifique una QUERYTRACEON sugerencia para cada número de marca de seguimiento diferente.

RECOMPILE

Indica a Motor de base de datos de SQL Server que genere un plan nuevo y temporal para la consulta y descarte de inmediato ese plan una vez que se completa la ejecución de la consulta. El plan de consulta generado no reemplaza un plan almacenado en caché cuando se ejecuta la misma consulta sin la RECOMPILE sugerencia . Sin especificar RECOMPILE, el Motor de base de datos almacena en caché los planes de consulta y los reutiliza. Cuando se compilan planes de consulta, la RECOMPILE sugerencia de consulta usa los valores actuales de las variables locales de la consulta. Si la consulta está dentro de un procedimiento almacenado, los valores actuales se pasan a cualquier parámetro.

RECOMPILE es una alternativa útil para crear un procedimiento almacenado. RECOMPILE usa la WITH RECOMPILE cláusula cuando solo se debe volver a compilar un subconjunto de consultas dentro del procedimiento almacenado, en lugar de todo el procedimiento almacenado. Para más información, vea Volver a compilar un procedimiento almacenado. RECOMPILE también resulta ú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 cuando la consulta se procesa. Las filas pueden llegar a ser tan anchas que, en algunos casos, el operador especificado no puede procesar la fila. Si las filas son tan anchas, el Motor de base de datos genera un error durante la ejecución de la consulta. ROBUST PLANMediante , se indica al optimizador de consultas que no tenga en cuenta ningún plan de consulta que pueda encontrarse con 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 se encuentra con una fila demasiado larga, devuelve un error de ejecución.

USE HINT ( 'hint_name' )

Se aplica a: SQL Server (a partir de SQL Server 2016 (13.x) SP1) y Azure SQL Database.

Proporciona una o varias sugerencias adicionales al procesador de consultas. Las sugerencias adicionales se especifican con un nombre de sugerencia entre comillas simples.

Sugerencia

Los nombres de sugerencia no distinguen mayúsculas de minúsculas.

Se admiten los siguientes nombres de sugerencia:

Sugerencia	Descripción
`'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'`	Hace que SQL Server genere un plan de consulta mediante la suposición de contención simple en lugar de la suposición de contención base predeterminada para las combinaciones, en el modelo de estimación de cardinalidad del optimizador de consultas de SQL Server 2014 (12.x) y versiones posteriores. Este nombre de sugerencia es equivalente a la marca de seguimiento 9476.
`'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'`	Hace que SQL Server genere un plan con una selectividad mínima al estimar predicados AND para que los filtros tengan en cuenta la correlación completa. Este nombre de sugerencia es equivalente a la marca de seguimiento 4137 cuando se usa con el modelo de estimación de cardinalidad de SQL Server 2012 (11.x) y versiones anteriores, y tiene un efecto similar cuando se usa la marca de seguimiento 9471 con el modelo de estimación de cardinalidad de SQL Server 2014 (12.x) y versiones posteriores.
`'ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES'`	Hace que SQL Server genere un plan con una selectividad máxima al estimar predicados AND para que los filtros tengan en cuenta la independencia completa. Este nombre de sugerencia es el comportamiento predeterminado del modelo de estimación de cardinalidad de SQL Server 2012 (11.x) y versiones anteriores, y equivalente a la marca de seguimiento 9472 cuando se usa con el modelo de estimación de cardinalidad de SQL Server 2014 (12.x) y versiones posteriores. Se aplica a: Azure SQL Database
`'ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES'`	Hace que SQL Server genere un plan con una selectividad de mayor a menor al estimar predicados AND para que los filtros tengan en cuenta la correlación parcial. Este nombre de sugerencia es el comportamiento predeterminado del modelo de estimación de cardinalidad de SQL Server 2014 (12.x) y versiones posteriores. Se aplica a: Azure SQL Database
`'DISABLE_BATCH_MODE_ADAPTIVE_JOINS'`	Deshabilita las combinaciones adaptables del modo por lotes. Para obtener más información, vea Combinaciones adaptables del modo por lotes. Se aplica a: SQL Server 2017 (14.x) y versiones posteriores, y Azure SQL Database.
`'DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'`	Deshabilita los comentarios de concesión de memoria en modo por lotes. Para obtener más información, vea Comentarios de concesión de memoria de modo de proceso por lotes. Se aplica a: SQL Server 2017 (14.x) y versiones posteriores, y Azure SQL Database.
`'DISABLE_DEFERRED_COMPILATION_TV'`	Deshabilita la compilación diferida de variables de tabla. Para obtener más información, consulte Compilación diferida de variables de tabla. Se aplica a: SQL Server 2019 (15.x) y versiones posteriores, y Azure SQL Database.
`'DISABLE_INTERLEAVED_EXECUTION_TVF'`	Deshabilita la ejecución intercalada de las funciones con valores de tabla de múltiples instrucciones. Para más información, consulte Ejecución intercalada de funciones con valores de tabla de múltiples instrucciones. Se aplica a: SQL Server 2017 (14.x) y versiones posteriores, y Azure SQL Database.
`'DISABLE_OPTIMIZED_NESTED_LOOP'`	Indica al procesador de consultas que no use una operación de ordenación (ordenación por lotes) para las combinaciones de bucle anidado optimizadas cuando se genera un plan de consulta. Este nombre de sugerencia es equivalente a la marca de seguimiento 2340. Esta sugerencia también se aplica a ordenación explícita y ordenación por lotes.
`'DISABLE_OPTIMIZER_ROWGOAL'`	Hace que SQL Server genere un plan que no usa las modificaciones del objetivo de fila con las consultas que contienen estas palabras clave: - `TOP` - `OPTION (FAST N)` - `IN` - `EXISTS` Este nombre de sugerencia es equivalente a la marca de seguimiento 4138.
`'DISABLE_PARAMETER_SNIFFING'`	Indica al optimizador de consultas que utilice el promedio de distribución de datos al compilar una consulta con uno o más parámetros. Esta instrucción hace que el plan de consulta sea independiente en el valor del parámetro que se utilizó en primer lugar cuando se compiló la consulta. Este nombre de sugerencia es equivalente a la marca de seguimiento 4136 o a la configuración `PARAMETER_SNIFFING = OFF` con ámbito de base de datos .
`'DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'`	Deshabilita los comentarios de concesión de memoria del modo de fila. Para más información, consulte Comentarios de concesión de memoria de modo de proceso por lotes. Se aplica a: SQL Server 2019 (15.x) y versiones posteriores, y Azure SQL Database.
`'DISABLE_TSQL_SCALAR_UDF_INLINING'`	Deshabilita la inserción de UDF escalar. Para obtener más información, vea Scalar UDF inlining (Inserción de UDF escalar). Se aplica a: SQL Server 2019 (15.x) y versiones posteriores, y Azure SQL Database.
`'DISALLOW_BATCH_MODE'`	Deshabilita la ejecución del modo por lotes. Para más información, consulte Modos de ejecución. Se aplica a: SQL Server 2019 (15.x) y versiones posteriores, y Azure SQL Database.
`'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'`	Habilita las estadísticas rápidas generadas automáticamente (modificación de histograma) para las columnas de índice iniciales para las que se necesite la estimación de cardinalidad. El histograma usado para calcular la cardinalidad se ajusta en tiempo de compilación de consulta para tener en cuenta el valor máximo o mínimo real de esta columna. Este nombre de sugerencia es equivalente a la marca de seguimiento 4139.
`'ENABLE_QUERY_OPTIMIZER_HOTFIXES'`	Permite revisiones del optimizador de consultas (cambios publicados en las actualizaciones acumulativas y Service Packs de SQL Server). Este nombre de sugerencia es equivalente a la marca de seguimiento 4199 o a la configuración `QUERY_OPTIMIZER_HOTFIXES = ON`con ámbito de base de datos .
`'FORCE_DEFAULT_CARDINALITY_ESTIMATION'`	Fuerza al optimizador de consultas a usar el modelo de estimación de la cardinalidad que se corresponde con el nivel de compatibilidad de la base de datos actual. Use esta sugerencia para invalidar la configuración `LEGACY_CARDINALITY_ESTIMATION = ON` con ámbito de base de datos o la marca de seguimiento 9481.
`'FORCE_LEGACY_CARDINALITY_ESTIMATION'`	Fuerza al optimizador de consultas a usar el modelo de estimación de la cardinalidad de SQL Server 2012 (11.x) y versiones anteriores. Este nombre de sugerencia es equivalente a la marca de seguimiento 9481 o a la configuración `LEGACY_CARDINALITY_ESTIMATION = ON` con ámbito de base de datos .
`'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n'` ¹	Fuerza el comportamiento del optimizador de consultas en un nivel de consulta. Este comportamiento se produce como si la consulta se compilara con nivel de compatibilidad de base de datos n, donde n es un nivel de compatibilidad de base de datos compatible. Para obtener una lista de los valores admitidos actualmente para n, consulte sys.dm_exec_valid_use_hints. Se aplica a: SQL Server 2017 (14.x) CU 10 y versiones posteriores, y Azure SQL Database
`'QUERY_PLAN_PROFILE'` ²	Habilita la generación de perfiles ligera para la consulta. Cuando finaliza una consulta que contiene esta nueva sugerencia, se desencadena un nuevo evento extendido, `query_plan_profile`, . Este evento extendido expone estadísticas de ejecución y XML del plan de ejecución real similar al `query_post_execution_showplan` evento extendido, pero solo para las consultas que contienen la nueva sugerencia. Se aplica a: SQL Server 2016 (13.x) SP 2 CU 3, SQL Server 2017 (14.x) CU 11 y versiones posteriores

¹ La QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n sugerencia no invalida el valor predeterminado o heredado de la estimación de cardinalidad, si lo fuerza a través de la configuración con ámbito de base de datos, marca de seguimiento u otra sugerencia de consulta, como QUERYTRACEON. Esta sugerencia solo afecta al comportamiento del optimizador de consultas. No afecta a otras características de SQL Server que pueden depender del nivel de compatibilidad de base de datos, como la disponibilidad de determinadas características de base de datos. Para obtener más información, consulte Elección del desarrollador: Modelo de ejecución de consultas de sugerencias.

² Si habilita la recopilación del query_post_execution_showplan evento extendido, la infraestructura de generación de perfiles estándar se agrega a cada consulta que se ejecuta en el servidor y, por lo tanto, puede afectar al rendimiento general del servidor. Si habilita la recopilación de eventos extendidos para usar la infraestructura de generación de query_thread_profile perfiles ligera en su lugar, esto produce una sobrecarga de rendimiento mucho menos, pero sigue afectando al rendimiento general del servidor. Si habilita el query_plan_profile evento extendido, esto solo habilita la infraestructura de generación de perfiles ligera para una consulta que se ejecuta con query_plan_profile y, por lo tanto, no afecta a otras cargas de trabajo en el servidor. Use esta sugerencia para generar perfiles de una consulta específica sin que esto afecte a otras partes de la carga de trabajo del servidor. Para obtener más información sobre la generación de perfiles ligera, consulte Infraestructura de generación de perfiles de consultas.

La lista de todos los nombres admitidos USE HINT se puede consultar mediante la vista de administración dinámica sys.dm_exec_valid_use_hints.

Importante

Algunas USE HINT sugerencias pueden entrar en conflicto con las marcas de seguimiento habilitadas en el nivel global o de sesión, o con la configuración con ámbito de base de datos. En este caso, la sugerencia de nivel de consulta (USE HINT) siempre tiene prioridad. Si entra USE HINT en conflicto con otra sugerencia de consulta o una marca de seguimiento habilitada en el nivel de consulta (por ejemplo, por QUERYTRACEON), SQL Server generará un error al intentar ejecutar la consulta.

USE PLAN N'xml_plan'

Obliga al optimizador de consultas a usar un plan de consulta existente para una consulta especificada por xml_plan. USE PLAN no se puede especificar con INSERTinstrucciones , UPDATE, MERGEo DELETE .

El plan de ejecución resultante forzado por esta característica es el mismo o similar al que se fuerza el plan. Dado que el plan resultante podría no ser idéntico al plan especificado por USE PLAN, el rendimiento de los planes puede variar. En raras ocasiones, la diferencia de rendimiento puede ser significativa y negativa. En ese caso, el administrador debe quitar el plan forzado.

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. Se recomienda usar una sugerencia de tabla como una sugerencia de consulta únicamente 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 FROM cláusula . 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.

Al especificar exposed_object_name sin especificar también una sugerencia de tabla, se descartan todos los índices que especifique en la consulta como parte de una sugerencia de tabla para el objeto. Después, el optimizador de consultas determina el uso de los índices. Puede usar esta técnica para eliminar el efecto de una INDEX sugerencia de tabla cuando no se puede modificar la consulta original. Vea el ejemplo J.

<table_hint>

Sugerencia de tabla que se va a aplicar a la tabla o vista que corresponde a exposed_object_name como sugerencia de consulta. Para obtener una descripción de estas sugerencias, consulte Sugerencias de tabla.

Precaución

Especificar FORCESEEK con parámetros limita el número de planes que el optimizador de consultas puede considerar más que al especificar FORCESEEK sin parámetros. Esto puede producir un error "No se puede generar el plan" en más casos.

FOR TIMESTAMP AS OF 'point_in_time'

Esto se aplica a: Almacén en Microsoft Fabric

Use la sintaxis TIMESTAMP de la cláusula OPTION para consultar los datos tal como existían en el pasado, parte de la característica de desplazamiento en el tiempo de Synapse Data Warehouse en Microsoft Fabric.

Especifique el point_in_time en el formato yyyy-MM-ddTHH:mm:ss[.fff] para devolver datos tal como aparecían en ese momento. La zona horaria siempre está en UTC. Use la sintaxis CONVERT para el formato datetime necesario con el estilo 126.

La sugerencia TIMESTAMP AS OF solo se puede especificar una vez mediante la cláusula OPTION. Para obtener más información y limitaciones, consulte Consulta de datos tal como existían en el pasado.

Comentarios

Las sugerencias de consulta no se pueden especificar en una INSERT instrucción , excepto cuando se usa una SELECT cláusula dentro de 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 puede especificarse en la consulta de nivel superior o en una subconsulta. Sin embargo, el valor especificado para exposed_object_name en la TABLE HINT cláusula debe coincidir exactamente con el nombre expuesto en la consulta o subconsulta.

Especificación de sugerencias de tabla como sugerencias de consulta

Se recomienda usar la INDEXsugerencia de tabla , FORCESCANo FORCESEEK como sugerencia de consulta solo 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 está optimizada. Para las consultas ad hoc, use la TABLE HINT cláusula solo cuando pruebe las instrucciones de la guía de plan. Para todas las demás consultas ad hoc, se recomienda especificar estas sugerencias únicamente como sugerencias de tabla.

Cuando se especifica como sugerencia de consulta, las INDEXsugerencias de tabla , FORCESCANy FORCESEEK son válidas para los objetos siguientes:

Tablas
Vistas
Vistas indizadas
Expresiones de tabla comunes (la sugerencia debe especificarse en la SELECT instrucción cuyo conjunto de resultados rellena la expresión de tabla común)
Vistas de administración dinámica (DMV)
Subconsultas con nombre

Puede especificar INDEXsugerencias de tabla , FORCESCANy FORCESEEK como sugerencias de consulta para una consulta que no tenga ninguna sugerencia de tabla existente. También puede usarlos para reemplazar las sugerencias , FORCESCANo FORCESEEK existentes INDEXen la consulta, respectivamente.

Las sugerencias de tabla que no sean INDEX, FORCESCANy FORCESEEK no se permiten como sugerencias de consulta a menos que la consulta ya tenga una WITH cláusula que especifique la sugerencia de tabla. En este caso, también debe especificarse una sugerencia coincidente como sugerencia de consulta. Especifique la sugerencia coincidente como sugerencia de consulta mediante TABLE HINT en la OPTION cláusula . Esta especificación conserva la semántica de la consulta. Por ejemplo, si la consulta contiene la sugerencia NOLOCKde tabla , la OPTION cláusula del parámetro @hints de la guía de plan también debe contener la NOLOCK sugerencia . Consulte el ejemplo K.

Especificación de sugerencias con las sugerencias del Almacén de consultas

La característica Sugerencias del Almacén de consultas permite aplicar sugerencias en las consultas identificadas mediante el Almacén de consultas sin necesidad de realizar cambios en el código. Use el procedimiento almacenado sys.sp_query_store_set_hints para aplicar una sugerencia a una consulta. Consulte el ejemplo N.

Ejemplos

A. Usar MERGE JOIN

En el ejemplo siguiente se especifica que MERGE JOIN ejecuta la JOIN operación en la consulta. En el ejemplo se usa la base de datos AdventureWorks2022.

SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID
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 @city_name y la selectividad promedio del predicado en todos los valores de columna para @postal_code al optimizar la consulta. En el ejemplo se usa la base de datos AdventureWorks2022.

CREATE PROCEDURE dbo.RetrievePersonAddress
@city_name NVARCHAR(30),
@postal_code NVARCHAR(15)
AS
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 usar para evitar que una expresión de tabla común recursiva mal formada 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. En el ejemplo se usa la base de datos AdventureWorks2022.

--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

Una vez corregido el error de codificación, MAXRECURSION ya no es necesario.

D. Usar MERGE UNION

En el ejemplo siguiente se usa la sugerencia de consulta MERGE UNION. En el ejemplo se usa la base de datos AdventureWorks2022.

SELECT *
FROM HumanResources.Employee AS e1
UNION
SELECT *
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO

E. Usar HASH GROUP y FAST

En el ejemplo siguiente se usan las HASH GROUP sugerencias de consulta y FAST . En el ejemplo se usa la base de datos AdventureWorks2022.

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 ejemplo siguiente se usa la sugerencia de consulta MAXDOP. En el ejemplo se usa la base de datos AdventureWorks2022.

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

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

EXEC sp_create_plan_guide
    @name = N'Guide1',
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (TABLE HINT(e, INDEX (IX_Employee_ManagerID)))';
GO
EXEC sp_create_plan_guide
    @name = N'Guide2',
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (TABLE HINT(e, INDEX(PK_Employee_EmployeeID, IX_Employee_ManagerID)))';
GO

H. Usar FORCESEEK

En el ejemplo siguiente se usa la sugerencia de FORCESEEK tabla. La TABLE HINT cláusula también debe especificar el mismo nombre de dos partes que el nombre del objeto expuesto. Especifique el nombre al aplicar la INDEX sugerencia en una tabla que use un nombre de dos partes. En el ejemplo se usa la base de datos AdventureWorks2022.

EXEC sp_create_plan_guide
    @name = N'Guide3',
    @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.Title
              FROM HumanResources.Employee
              JOIN Person.Contact AS c ON HumanResources.Employee.ContactID = c.ContactID
              WHERE HumanResources.Employee.ManagerID = 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

En el ejemplo siguiente se aplica la INDEX sugerencia a una tabla y la FORCESEEK sugerencia a otra. En el ejemplo se usa la base de datos AdventureWorks2022.

EXEC sp_create_plan_guide
    @name = N'Guide4',
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (TABLE HINT (e, INDEX( IX_Employee_ManagerID))
                       , TABLE HINT (c, FORCESEEK))';
GO

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

En el ejemplo siguiente se muestra cómo usar la TABLE HINT sugerencia . Puede usar la sugerencia sin especificar una sugerencia para invalidar el INDEX comportamiento de la sugerencia de tabla que especifique en la FROM cláusula de la consulta. En el ejemplo se usa la base de datos AdventureWorks2022.

EXEC sp_create_plan_guide
    @name = N'Guide5',
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_ManagerID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 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

En el ejemplo siguiente se incluyen dos sugerencias de tabla en la consulta: NOLOCK, que afecta a la semántica y INDEX, que no afecta a la semántica. Para conservar la semántica de la consulta, la NOLOCK sugerencia se especifica en la OPTIONS cláusula de la guía de plan. Junto con la NOLOCK sugerencia, especifique las INDEX sugerencias y y FORCESEEK reemplace la sugerencia que no afecta a INDEX la semántica en la consulta durante la compilación y optimización de instrucciones. En el ejemplo se usa la base de datos AdventureWorks2022.

EXEC sp_create_plan_guide
    @name = N'Guide6',
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (TABLE HINT (e, INDEX(IX_Employee_ManagerID), 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. Permita que el optimizador elija especificando la NOLOCK sugerencia en la OPTIONS cláusula . Especifique la sugerencia porque afecta a la semántica. A continuación, especifique la TABLE HINT palabra clave solo con una referencia de tabla y ninguna INDEX sugerencia. En el ejemplo se usa la base de datos AdventureWorks2022.

EXEC sp_create_plan_guide
    @name = N'Guide7',
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (TABLE HINT (e, NOLOCK))';
GO

L. Usar USE HINT

En el ejemplo siguiente se usan las RECOMPILE sugerencias de consulta y USE HINT . En el ejemplo se usa la base de datos AdventureWorks2022.

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (RECOMPILE, USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES', 'DISABLE_PARAMETER_SNIFFING'));
GO

M. Usar QUERYTRACEON HINT

En el ejemplo siguiente se usan las QUERYTRACEON sugerencias de consulta. En el ejemplo se usa la base de datos AdventureWorks2022. Puede habilitar todas las revisiones que afectan al plan controladas por la marca de seguimiento 4199 para una consulta determinada mediante la consulta siguiente:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (QUERYTRACEON 4199);

También puede usar varias marcas de seguimiento como en la consulta siguiente:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION  (QUERYTRACEON 4199, QUERYTRACEON 4137);

Hora Usar sugerencias del Almacén de consultas

La característica Sugerencias del Almacén de consultas de Azure SQL Database proporciona un método fácil de usar para dar forma a los planes de consulta sin necesidad de cambiar el código de la aplicación.

En primer lugar, identifique la consulta que ya se ha ejecutado en las vistas de catálogo del Almacén de consultas, por ejemplo:

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'
  AND query_sql_text not like N'%query_store%';
GO

En el ejemplo siguiente se aplica la sugerencia para forzar el estimador de cardinalidad heredada en el elemento query_id 39, identificado en el Almacén de consultas:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

En el ejemplo siguiente se aplica la sugerencia para aplicar un tamaño máximo de concesión de memoria en PERCENT el límite de memoria configurado a query_id 39, identificado en Almacén de consultas:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

En el ejemplo siguiente se aplican varias sugerencias de consulta a query_id 39, incluido RECOMPILE, MAXDOP 1y el comportamiento del optimizador de consultas de SQL Server 2012 (11.x):

EXEC sys.sp_query_store_set_hints @query_id= 39,
    @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

O. Consulta de datos a partir de un momento dado