Sugerencias del Query Store

2025-04-13

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores De Azure SQL DatabaseAzureSQL Managed InstanceSQL Database en Microsoft Fabric

En este artículo se describe cómo aplicar consejos de consulta mediante Query Store. La característica Sugerencias del almacén de consultas 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.

Las sugerencias del almacén de consultas están disponibles en Azure SQL Database y Azure SQL Managed Instance. Las sugerencias del Almacén de consultas también son una característica introducida en SQL Server desde SQL Server 2022 (16.x).

Para obtener más información sobre cómo configurar y administrar el almacén de consultas, consulte Optimización del rendimiento mediante el almacén de consultas.
Para obtener información sobre cómo detectar información procesable y ajustar el rendimiento con el almacén de consultas, consulte Optimización del rendimiento mediante el almacén de consultas.
Para obtener más información sobre cómo funciona el almacén de consultas en Base de datos SQL de Azure, vea Funcionamiento del almacén de consultas de Base de datos SQL de Azure.

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. Para más información, consulte Sugerencias de consultas.

Vea este video para obtener una visión general sobre las sugerencias del Query Store:

Información general

Idealmente, el optimizador de consultas selecciona un plan de ejecución óptimo para una consulta.

Si no se selecciona un plan óptimo, es posible que un desarrollador o DBA quiera optimizar manualmente para condiciones específicas. Las sugerencias de consulta se especifican a través de la cláusula OPTION y se pueden usar para afectar al comportamiento de ejecución de la consulta. Aunque las sugerencias de consulta ayudan a proporcionar soluciones localizadas para varios problemas relacionados con el rendimiento, requieren que se reescriba el texto de consulta original. Podría ser que los administradores y los desarrolladores de bases de datos no siempre puedan realizar cambios directamente en el código Transact-SQL para insertar una sugerencia de consulta. Transact-SQL puede estar codificado de forma rígida en una aplicación o generarse automáticamente por parte de la aplicación. Antes, los desarrolladores tenían que basarse en guías de plan, que pueden ser complejas de utilizar.

Para obtener información sobre qué sugerencias de consulta se pueden aplicar, consulte Sugerencias de consulta admitidas.

Cuándo se deben usar las sugerencias del Almacén de consultas

Como su nombre indica, esta característica amplía el Almacén de consultas y depende de este. El Almacén de consultas permite capturar consultas, planes de ejecución y estadísticas en tiempo de ejecución asociadas. Query Store simplifica en gran medida la experiencia general del cliente al mejorar el rendimiento. SQL Server 2016 (13.x) introdujo primero Almacén de consultas y ahora está habilitado de manera predeterminada en SQL Server 2022 (16.x), Azure SQL Managed Instance y base de datos de Azure SQL.

Flujo de trabajo de las sugerencias del Almacén de consultas.

Ejemplos en los que las sugerencias del Almacén de consultas pueden ayudar a resolver problemas de rendimiento de nivel de consulta:

Volver a compilar una consulta en cada ejecución.
Limite el tamaño de la concesión de memoria para una operación de inserción masiva.
Limitar el grado máximo de paralelismo en la actualización de estadísticas.
Use una combinación hash en lugar de una combinación de bucles anidados.
Al usar el nivel de compatibilidad 110 para una consulta específica, mientras todo lo demás dentro de la base de datos se mantiene en el nivel de compatibilidad 150.
Deshabilite la optimización del objetivo de filas para una consulta SELECT TOP.

Para usar las sugerencias del Almacén de consultas:

Identifique el almacén de consultas query_id de la consulta que quiere modificar. Puede hacerlo de varias maneras:
- Mediante la consulta de las vistas de catálogo del Almacén de consultas.
- Uso de informes de almacén de consultas integrados en SQL Server Management Studio.
- Uso de Información de rendimiento de consultas del portal de Azure para Azure SQL Database.
Ejecute sys.sp_query_store_set_hints con el query_id y la cadena de sugerencias de consulta que desea aplicar a la consulta. Esta cadena puede contener una o varias sugerencias de consulta. Para obtener información completa, consulte sys.sp_query_store_set_hints.

Una vez que se han creado, las sugerencias del Almacén de consultas se conservan y perduran tras los reinicios y las conmutaciones por error. Las sugerencias del Almacén de consultas reemplazan las sugerencias de nivel de instrucción codificadas de forma rígida y las sugerencias existentes de la guía de plan.

Si una sugerencia de consulta contradice lo que es posible para la optimización de consulta, la ejecución de la consulta no se bloquea y la sugerencia no se aplica. 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.

Procedimientos almacenados del sistema para las sugerencias del Almacén de consultas

Para crear o actualizar sugerencias, use sys.sp_query_store_set_hints. Las sugerencias se especifican en un formato de cadena válido de tipo N'OPTION (...)'.

Cuando crea un indicador del Almacén de Consultas, si no existe un indicador para un query_id específico en el Almacén de Consultas, se crea uno nuevo.
Cuando crea o actualiza una sugerencia de almacén de consultas, si ya existe una sugerencia de almacén de consultas para un query_id específica, el último valor proporcionado anula los valores especificados anteriormente para la consulta asociada.
Si query_id no existe, se produce un error.

Nota:

Para obtener una lista completa de las sugerencias que se admiten, consulte sys.sp_query_store_set_hints.

Para quitar las sugerencias asociadas a un query_id, use sys.sp_query_store_clear_hints.

Atributos XML del plan de ejecución

Cuando se aplican las sugerencias, aparece el siguiente conjunto de resultados en el elemento StmtSimple del Plan de ejecución en formato XML:

Atributo	Descripción
`QueryStoreStatementHintText`	Sugerencias del Almacén de consultas aplicadas a la consulta
`QueryStoreStatementHintId`	Identificador único de una sugerencia de consulta
`QueryStoreStatementHintSource`	Origen de la sugerencia del Almacén de consultas (por ejemplo: "Usuario")

Nota:

Estos elementos XML están disponibles a través de la salida de los comandos Transact-SQL SET STATISTICS XML y SET SHOWPLAN XML.

Sugerencias del Almacén de consultas e interoperabilidad de características

Las sugerencias del Almacén de consultas reemplazan otras sugerencias de nivel de instrucción codificadas de forma rígida y las guías del plan.
Con la excepción de la ABORT_QUERY_EXECUTION sugerencia, las consultas con sugerencias de Query Store siempre se ejecutan. Las sugerencias del Almacén de consultas opuestas se omiten ya que, de lo contrario, provocarían un error.
Si las sugerencias del almacén de consultas son contradictorias, SQL Server no bloquea la ejecución de la consulta y no se aplica la sugerencia del almacén de consultas.
Parametrización simple: no se admiten sugerencias del Almacén de consultas para instrucciones que cumplan los requisitos para la parametrización simple.
Parametrización forzada: la RECOMPILE sugerencia no es compatible con la parametrización forzada establecida en el nivel de base de datos. Si la base de datos tiene establecido la parametrización forzada y la RECOMPILE sugerencia forma parte de la cadena de sugerencias establecida en el Almacén de consultas para una consulta, SQL Server omite la RECOMPILE sugerencia y aplicará cualquier otra sugerencia si se aplican.
- Además, SQL Server emite una advertencia (código de error 12461) que indica que se omitió la RECOMPILE sugerencia.
- Para obtener más información sobre las consideraciones de casos de uso de parametrización forzada, vea Directrices para usar la parametrización forzada.
Las sugerencias del Almacén de consultas creadas manualmente están exentas de la limpieza. La sugerencia y la consulta no se limpiarán del Almacén de consultas mediante la retención automática de la directiva de captura.
- Las consultas se pueden eliminar manualmente por los usuarios, lo que también eliminaría la sugerencia asociada del almacén de consultas.
- Las sugerencias del Almacén de consultas generadas automáticamente por los comentarios de CE están sujetas a la limpieza mediante la retención automática de la directiva de captura.
- Los comentarios de DOP y los comentarios de concesión de memoria dan forma al comportamiento de la consulta sin usar sugerencias del Almacén de consultas. Cuando las consultas se limpian mediante la retención automática de la directiva de captura, también se limpian los comentarios de DOP y los datos de comentarios de la concesión de memoria.
- Puede crear manualmente la misma sugerencia del Almacén de consultas que implementaron los comentarios de CE y, a continuación, la consulta con la sugerencia ya no estaría sujeta a la limpieza mediante la retención automática de la directiva de captura.

Sugerencias de Query Store y grupos de disponibilidad

Las sugerencias del Almacén de consultas no tienen ningún efecto en las réplicas secundarias a menos que esté habilitado el Almacén de consultas para réplicas secundarias. Para obtener más información, consulte Almacén de consultas para réplicas secundarias.

Antes de SQL Server 2022 (16.x), las sugerencias del Almacén de consultas se pueden aplicar a la réplica principal de un grupo de disponibilidad.
A partir de SQL Server 2022 (16.x), cuando el Almacén de consultas para réplicas secundarias está habilitado, las sugerencias del Almacén de consultas también son compatibles con réplicas para réplicas secundarias en grupos de disponibilidad.
Puede agregar una sugerencia del Almacén de consultas a una réplica o un conjunto de réplicas específicos cuando tenga habilitado almacén de consultas para réplicas secundarias. En sys.sp_query_store_set_query_hints, esto se establece mediante el parámetro @query_hint_scope, que se presentó en SQL Server 2022 (16.x).
Encuentre los conjuntos de réplicas disponibles consultando sys.query_store_replicas.
Busque planes forzados en réplicas secundarias con sys.query_store_plan_forcing_locations.

Procedimientos recomendados de sugerencias del Almacén de consultas

Complete el mantenimiento de índices y estadísticas antes de evaluar las consultas para detectar posibles sugerencias nuevas del Almacén de consultas.
Pruebe la base de datos de su aplicación en el último nivel de compatibilidad antes de usar las sugerencias del almacén de consultas.
- Por ejemplo, la optimización del plan confidencial de parámetros (PSP) se introdujo en SQL Server 2022 (16.x) (nivel de compatibilidad 160), que utiliza varios planes activos por consulta para abordar distribuciones de datos no uniformes. Si su entorno no puede utilizar el nivel de compatibilidad más reciente, puede emplear las sugerencias del almacén de consultas, incluida la sugerencia RECOMPILE, en cualquier nivel de compatibilidad que las soporte.
Las sugerencias del Almacén de consultas invalidan el comportamiento del plan de consulta de SQL Server. Se recomienda utilizar Sugerencias del almacén de consultas sólo cuando sea necesario solucionar problemas relacionados con el rendimiento.
Se recomienda volver a evaluar las sugerencias del Almacén de consultas, las sugerencias de nivel de la instrucción, las guías de plan y los planes forzados del Almacén de consultas en cualquier momento en que cambien las distribuciones de datos y durante los proyectos de migraciones de base de datos. Los cambios en la distribución de datos podrían provocar que las sugerencias del Almacén de Consultas generen planes de ejecución subóptimos.

Ejemplos

Un. Demostración de las sugerencias del Almacén de consultas

En la siguiente explicación paso a paso sobre las sugerencias del Almacén de Consultas en Azure SQL Database, se utiliza una base de datos importada mediante un archivo BACPAC (.bacpac). Para aprender a importar una nueva base de datos a un servidor de Azure SQL Database, consulte Inicio rápido: Importación de un archivo BACPAC a una base de datos.

-- ************************************************************************ --
-- Query Store hints demo

-- Demo uses "PropertyMLS" database which can be imported from BACPAC here:
-- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store

-- Email QSHintsFeedback@microsoft.com for questions\feedback
-- ************************************************************************ --

/*
    Demo prep, connect to the PropertyMLS database
*/

ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR;
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE  (QUERY_CAPTURE_MODE = ALL);
GO

-- Should be READ_WRITE
SELECT actual_state_desc
FROM sys.database_query_store_options;
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints.
    Checking if any already exist (should be none).
*/
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;
GO

/*
     The PropertySearchByAgent stored procedure has a parameter
     used to filter AgentId.  Looking at the statistics for AgentId,
     you will see that there is a big skew for AgentId 101.
*/
SELECT	hist.range_high_key AS [AgentId],
        hist.equal_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'NCI_Property_AgentId';


-- Show actual query execution plan to see plan compiled.
-- Agent with many properties will have a scan with parallelism.
EXEC [dbo].[PropertySearchByAgent] 101;

-- Agents with few properties still re-use this plan (assuming no recent plan eviction).
EXEC [dbo].[PropertySearchByAgent] 4;


/*
    Now let's find the query_id associated with this query.
*/
SELECT query_sql_text, q.query_id
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

/*
     We can set the hint associated with the query_id returned in the previous result set, as below.
     Note, we can designate one or more query hints
*/
EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)';
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints
*/
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;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see two different plans, one for AgentId 101 and one for AgentId 4.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

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

/*
    We can remove the hint using sp_query_store_clear_query_hints
*/
EXEC sp_query_store_clear_hints @query_id = 10;
GO

/*
    That Query Store Hint is now removed
*/
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;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see one plan again.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

B. 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 que devuelvan el query_id de un fragmento de un texto de consulta ejecutado.

En esta demostración 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;

El 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 Query Store.

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 base de datos SalesLT se ha identificado como query_id 39.

Una vez identificado, aplique la sugerencia para forzar un tamaño máximo de concesión de memoria en porcentaje del límite de memoria configurado al query_id:

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

También puede aplicar sugerencias de consulta con la sintaxis siguiente, por ejemplo, la opción para forzar el estimador de cardinalidad heredada:

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

Puede aplicar varias sugerencias de consulta con una lista separada por comas:

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

Revise la sugerencia del Almacén de consultas aplicada para query_id 39:

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;

Por último, elimine el indicio de query_id 39 mediante sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;