Comparteix a través de

Sugerencias del Almacén de consultas

  • Article

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

En este artículo se describe cómo aplicar sugerencias de consulta mediante el almacén de consultas. 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.

Sugerencias del almacén de consultas está disponible 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 en SQL Server 2022 (16.x).

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 vídeo para obtener información general sobre las sugerencias del Almacén de consultas:

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. El Almacén de consultas simplifica en gran medida la experiencia general del cliente al optimizar 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:

  • Al volver a compilar una consulta en cada ejecución.
  • Al limitar el tamaño de 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.
  • Al usar 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.
  • Al deshabilitar la optimización del objetivo de filas para una consulta SELECT TOP.

Uso de Sugerencias del almacén de consultas:

  1. Identifique el query_id del almacén de consultas de la instrucción de 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.
  2. Ejecute sys.sp_query_store_set_hints con el query_id y la cadena de sugerencia 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. Sugerencias del almacén de consultas reemplaza 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 una sugerencia del almacén de consultas, si no existe una sugerencia para un query_id específico en el Almacén de consultas, se crea una nueva.
  • 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

  • Sugerencias del almacén de consultas reemplaza las sugerencias de nivel de instrucción codificadas de forma rígida y las sugerencias existentes de la guía de plan.
  • Las consultas siempre se ejecutan. Se ignorarán las sugerencias contrarias del almacén de consultas 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 admite Sugerencias del almacén de consultas para instrucciones que cumplan los requisitos para la parametrización simple.
  • Parametrización forzada: la sugerencia RECOMPILE no es compatible con la parametrización forzada establecida en el nivel de base de datos. Si la base de datos tiene establecida una parametrización forzada y la sugerencia RECOMPILE forma parte de la cadena de sugerencias establecida en el almacén de consultas para una consulta, SQL Server ignora la sugerencia RECOMPILE y aplica cualquier otra sugerencia si se aplica.
    • Además, SQL Server emite una advertencia (código de error 12461) que indica que se ha omitido la sugerencia RECOMPILE.
    • 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 creadas manualmente en el almacén de consultas no se limpian. La sugerencia y la consulta no se limpiarán del almacén de consultas por 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 de almacén de consultas generadas automáticamente por los comentarios de CE están sujetas a limpieza por la retención automática de la directiva de captura.
    • Los comentarios DOP y los comentarios de concesión de memoria determinan el 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 datos de los comentarios de DOP y de concesión de memoria.
    • Puede crear manualmente la misma sugerencia de almacén de consultas que implementó los comentarios de CE, y entonces la consulta con la sugerencia ya no estaría sujeta a limpieza por la retención automática de la directiva de captura.

Sugerencias y grupos de disponibilidad del almacén de consultas

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 podían aplicar a la réplica principal de un grupo de disponibilidad.
  • A partir de SQL Server 2022 (16.x), cuando se habilita el almacén de consultas para réplicas secundarias, las sugerencias del almacén de consultas también tienen en cuenta las réplicas para las réplicas secundarias de los grupos de disponibilidad.
  • Puede agregar una sugerencia de almacén de consultas a una réplica o conjunto de réplicas específico cuando tenga activado el 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.
  • Buscar 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 de 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 el entorno no puede usar el nivel de compatibilidad más reciente, Sugerencias del almacén de consultas, mediante la sugerencia RECOMPILE, se puede utilizar en cualquier nivel de compatibilidad compatible.
  • Sugerencias del almacén de consultas invalida 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 Sugerencias del almacén de consultas, las sugerencias del plan, 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 Sugerencias del almacén de consultas genere planes de ejecución subóptimos.

Ejemplos

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

En el siguiente ejemplo paso a paso de Sugerencias del almacén de consultas de Azure SQL Database se usa 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 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 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, quite la sugerencia de query_id 39 mediante sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Contenido relacionado