Sugerencias del Almacén de consultas (versión preliminar)

Se aplica a: SíAzure SQL Database SíInstancia administrada de Azure SQL

En este artículo se describe la característica Sugerencias del 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.

Nota

Las sugerencias del Almacén de consultas son una característica en vista previa pública disponible actualmente en Azure SQL Database, incluido en las bases de datos únicas de Azure SQL, los grupos elásticos, las instancias administradas y las bases de datos de hiperescala.

Información general

Idealmente, el optimizador de consultas selecciona un plan de ejecución óptimo para una consulta. Cuando esto no sucede, es posible que un desarrollador o un administrador de bases de datos quieran realizar una optimización de forma manual 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 los desarrolladores de bases de datos no siempre puedan realizar cambios directamente en código Transact-SQL para insertar una sugerencia de consulta. Transact-SQL podría 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 usar.

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 se introdujo en SQL Server 2016 (13.x) y se incluye de forma predeterminada en Azure SQL Database. Se trata de una característica que simplifica en gran medida la experiencia general del cliente al optimizar el rendimiento.

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

  Primero se ejecuta la consulta y, luego, se captura mediante el Almacén de consultas. Después, el administrador de bases de datos crea una sugerencia del Almacén de consultas sobre una consulta. A partir de ese momento, la consulta se ejecuta mediante la sugerencia 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.
  • Al limitar el grado máximo de paralelismo para una operación de 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.

Para usar las sugerencias del Almacén de consultas, haga lo siguiente:

  1. Identifique el elemento query_id del Almacén de consultas de la instrucción de consulta que quiere modificar. Puede hacerlo de varias maneras: 1.1. Mediante la consulta de las vistas de catálogo del Almacén de consultas. 1.2. Mediante el uso de informes integrados del Almacén de consultas de SQL Server Management Studio. 1.3. Mediante el uso de la Información de rendimiento de consultas de Azure Portal para Azure SQL Database.
  2. Ejecute sp_query_store_set_hints con el elemento query_id y la cadena de sugerencia de consulta que quiere 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 se puede hacer para optimizar las consultas, no se aplicará y no bloqueará la ejecución de la consulta. 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.

Vea este vídeo para obtener información general sobre las sugerencias del Almacén de consultas:

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 (...)'.

Nota

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

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

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

Atributos XML del plan de ejecución

Cuando se aplican sugerencias, se mostrará lo siguiente 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

Durante la versión preliminar pública de las sugerencias del Almacén de consultas, estos elementos XML solo estarán disponibles a través de la salida de los comandos Transact-SQL SET STATISTICS XML y SET SHOWPLAN XML.

Ejemplos

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

En el siguiente ejemplo paso a paso de la característica 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 elemento query_id de un fragmento de un texto de consulta ejecutado:

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

Después, aplique la sugerencia para exigir al elemento query_id un tamaño máximo de concesión de memoria en forma de porcentaje del límite de memoria configurado (en este ejemplo, el elemento query_id del conjunto de resultados de la consulta anterior era 39):

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 el elemento 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;

Pasos siguientes

Vea también