sp_query_store_set_hints (Transact-SQL)

Artículo
06/15/2023

Se aplica a: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance

Crea o actualiza Almacén de consultas sugerencias para un query_id determinado.

Convenciones de sintaxis de Transact-SQL

Sintaxis

sp_query_store_set_hints
    [ @query_id = ] query_id ,
    [ @query_hints = ] 'query_hints'
    [ , [ @query_hint_scope = ] 'replica_group_id' ]
[ ; ]

Argumentos

[ @query_id = ] query_id

Columna Almacén de consultas query_id de sys.query_store_query. @query_id es bigint.

[ @query_hints = ] N'query_hints'

Cadena de caracteres de las opciones de consulta a partir 'OPTIONde . @query_hints es nvarchar(max). Para obtener más información, consulte Sugerencias de consulta admitidas en este artículo.

[ @query_hint_scope = ] 'replica_group_id'

De forma predeterminada, el ámbito de una nueva sugerencia de Almacén de consultas es solo la réplica local. @query_hint_scope es tinyint. Este parámetro opcional determina el ámbito en el que se aplicará la sugerencia en una réplica secundaria cuando se habilite Almacén de consultas para réplicas secundarias. El argumento opcional query_hint_scope solo tiene como valor predeterminado la réplica local (principal o secundaria), pero opcionalmente puede especificar un replica_group_id que haga referencia a sys.query_store_replicas.

Valores devueltos

0 (correcto) o 1 (error).

Observaciones

Las sugerencias se especifican en un formato de cadena T-SQL válido de tipo N'OPTION (..)'.

Si no existe ninguna sugerencia de Almacén de consultas para un query_id específico, se creará una nueva sugerencia de Almacén de consultas.
Si ya existe una sugerencia de Almacén de consultas para un query_id específico, el último valor proporcionado invalidará los valores especificados previamente para la consulta asociada.
Si no existe un query_id , se generará un error.

En los casos en los que una sugerencia provocaría un error en una consulta, se omite la sugerencia y los detalles del error más recientes se pueden ver en sys.query_store_query_hints.

Para quitar sugerencias asociadas a un query_id, use el procedimiento almacenado del sistema sys.sp_query_store_clear_hints.

Sugerencias de consulta admitidas

Estas sugerencias de consulta se admiten como sugerencias del Almacén de consultas:

{ 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
  | MAX_GRANT_PERCENT = percent
  | MIN_GRANT_PERCENT = percent
  | MAXDOP number_of_processors
  | NO_PERFORMANCE_SPOOL
  | OPTIMIZE FOR UNKNOWN
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN
  | USE HINT ( '<hint_name>' [ , ...n ] )

Actualmente no se admiten las siguientes sugerencias de consulta:

OPTIMIZE FOR ( @var = val)
MAXRECURSION
USE PLAN(en su lugar, considere la posibilidad de forzar la capacidad del plan original de Almacén de consultas, sp_query_store_force_plan).
DISABLE_DEFERRED_COMPILATION_TV
DISABLE_TSQL_SCALAR_UDF_INLINING
Sugerencias de tabla (por ejemplo, FORCESEEK, READUNCOMMITTED, INDEX)

Permisos

Requiere el permiso ALTER en la base de datos.

Ejemplos

Identificación de una consulta en el Almacén de consultas

En el ejemplo siguiente se consultan sys.query_store_query_text y sys.query_store_query para devolver el query_id de un fragmento de texto de consulta ejecutado.

En este ejemplo, la consulta que estamos intentando optimizar está en la base de datos de ejemplo SalesLT:

SELECT * FROM SalesLT.Address as A
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;

Almacén de consultas no refleja inmediatamente los datos de consulta en sus vistas del sistema.

Identifique la consulta en las vistas de catálogo del sistema del Almacén de consultas:

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'%PostalCode =%'
  AND query_sql_text not like N'%query_store%';
GO

En los ejemplos siguientes, el ejemplo de consulta anterior de la SalesLT base de datos se identificó como query_id 39.

Aplicación de una sugerencia única

En el ejemplo siguiente se aplica la sugerencia RECOMPILE a query_id 39, como se identifica en Almacén de consultas:

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

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

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

Aplicación de varias sugerencias

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

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

Visualización de sugerencias del Almacén de consultas

En el siguiente ejemplo se devuelven las sugerencias del Almacén de consultas existentes:

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc
FROM sys.query_store_query_hints
WHERE query_id = 39;

Eliminación de la sugerencia de una consulta

Use el ejemplo siguiente para quitar la sugerencia de query_id 39 mediante el procedimiento almacenado del sistema sp_query_store_clear_hints (Transact-SQL ).

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Compartir vía