Compartir a través de

Sugerencias del Query Store

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.

Caution

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:

Overview

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 administrador de bases de datos (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. Es posible que los administradores y desarrolladores de bases de datos no siempre puedan realizar cambios directamente en Transact-SQL código para agregar 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.

Los hints del Query Store resuelven este problema al permitirle insertar un hint de consulta en una consulta sin modificar el texto de consulta Transact-SQL directamente. 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 por primera vez el Almacén de consultas y ahora está habilitado de forma predeterminada en SQL Server 2022 (16.x), Azure SQL Managed Instance, Azure SQL Database y SQL Database en Microsoft Fabric.

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:

  1. Identifique el almacén de consultas query_id de la consulta que quiere modificar. Puede hacerlo de varias maneras:

  2. 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 consultas, la ejecución de la consulta no se bloquea y no se aplica la sugerencia. En los casos en los que una sugerencia provocaría un error en una consulta, se omite la sugerencia y se pueden ver los detalles del error más recientes en sys.query_store_query_hints.

Antes de usar sugerencias del Almacén de consultas

Tenga en cuenta lo siguiente antes de empezar a usar sugerencias del Almacén de consultas.

  • Complete el mantenimiento de estadísticas y el mantenimiento del índice (si es necesario) antes de evaluar las consultas para posibles sugerencias del Query Store. El mantenimiento de estadísticas y, en menor medida, el mantenimiento de índices podría resolver un problema que de otro modo requeriría una sugerencia de consulta.
  • Antes de usar sugerencias del Almacén de consultas, pruebe la base de datos de la aplicación en el nivel de compatibilidad más reciente para ver si eso resuelve el problema que requiere una sugerencia de consulta.
    • Por ejemplo, la optimización del plan de confiabilidad de parámetros (PSP) se introdujo en SQL Server 2022 (16.x) en el nivel de compatibilidad 160. Usa 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, las sugerencias del Almacén de consultas que emplean el indicativo RECOMPILE se pueden aplicar a cualquier nivel de compatibilidad compatible.
  • Las sugerencias del Almacén de consultas invalidan el comportamiento predeterminado del plan de consulta del motor de base de datos. Solo debe usar sugerencias del Almacén de consultas cuando sea necesario para solucionar problemas relacionados con el rendimiento.
  • Debe 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 los volúmenes y las distribuciones de datos y durante los proyectos de migraciones de base de datos. Los cambios en el volumen de datos y la distribución pueden provocar que las pistas del Almacén de consultas generen planes de ejecución subóptimos.

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.

Para obtener una lista completa de sugerencias que se admiten como sugerencia del Almacén de consultas, consulte sys.sp_query_store_set_hints.

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

Tip

Es posible que tenga que establecer o borrar sugerencias para todos los valores de query_id que coinciden con un hash de consulta.

dbo.sp_query_store_modify_hints_by_query_hash es un procedimiento almacenado de ejemplo que llama al procedimiento almacenado del sistema sys.sp_query_store_set_hints o sys.sp_query_store_clear_hints en un bucle para llevar a cabo esta tarea.

Atributos XML del plan de ejecución

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

Attribute Description
QueryStoreStatementHintText Sugerencias actuales del Almacén de consultas que se aplican a la consulta
QueryStoreStatementHintId Identificador único de una sugerencia de consulta
QueryStoreStatementHintSource Origen de la sugerencia del Almacén de consultas (por ejemplo, User)

Note

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.
  • Excepto para la sugerencia ABORT_QUERY_EXECUTION, las consultas con sugerencias del Almacén de consultas 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 se contradecen, el motor de base de datos no bloquea la ejecución de consultas y no se aplica la sugerencia del Almacén de consultas.
  • Las sugerencias del Almacén de consultas no se admiten para las instrucciones que son aptas para la parametrización simple.
  • La RECOMPILE sugerencia no es compatible con la parametrización forzada establecida en el nivel de base de datos. Si una base de datos tiene establecida la parametrización forzada y la RECOMPILE sugerencia forma parte de las sugerencias del Almacén de consultas para una consulta, el motor de base de datos omite la RECOMPILE sugerencia y aplica cualquier otra sugerencia si se especifican.
    • El motor de base de datos 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 del Almacén. La sugerencia y la consulta no se limpian mediante la directiva de captura de retención automática.
    • Los usuarios pueden quitar manualmente las consultas. También quita la indicación 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.
    • Si crea la misma sugerencia del Almacén de consultas que implementaron los comentarios de CE manualmente, 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 réplicas secundarias

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 secundarias legibles.

  • En SQL Server 2022 (16.x) y versiones anteriores, las sugerencias del Almacén de consultas solo se pueden aplicar en la réplica principal.
  • En SQL Server 2025 (17.x) y versiones posteriores, cuando el Almacén de consultas para réplicas secundarias está habilitado, las sugerencias del Almacén de consultas se pueden aplicar en réplicas secundarias en grupos de disponibilidad. Para obtener compatibilidad completa con la plataforma, consulte Almacén de consultas para secundarias legibles.

Donde se admite el Almacén de consultas en las réplicas secundarias:

Examples

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

En el siguiente ejemplo paso a paso de las sugerencias del Almacén de consultas de Azure SQL Database se usa una base de datos importada mediante un archivo BACPAC (.bacpac). Aprenda 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 en Azure SQL Database o Instancia administrada de Azure SQL.

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

Contenido relacionado

  • Last updated on