Prácticas recomendadas para supervisar cargas de trabajo con Almacén de consultas

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores de Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (solo grupo de SQL dedicado)Base de datos SQL en Microsoft Fabric

En este artículo se describen los procedimientos recomendados para usar el almacén de consultas de SQL Server con la carga de trabajo.

• Para obtener más información sobre cómo configurar y administrar con el Almacén de consultas, consulte Supervisió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 con 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.
• En Azure Synapse Analytics, el Almacén de consultas no está habilitado de forma predeterminada para grupos de SQL dedicados, pero se puede habilitar. No se admiten más opciones de configuración para el Almacén de consultas. Para más información, consulte Almacenamiento y análisis de consultas históricos en Azure Synapse Analytics.

Utilice la versión más reciente de SQL Server Management Studio

SQL Server Management Studio tiene un conjunto de interfaces de usuario diseñadas para configurar el almacén de consultas y para consumir datos recopilados sobre la carga de trabajo. Instale la versión más reciente de SQL Server Management Studio (SSMS).

Para obtener una descripción rápida sobre cómo usar el almacén de consultas en escenarios de solución de problemas, vea Almacén de consultas Azure blogs.

Uso de Información de rendimiento de consultas en Base de datos SQL de Azure

Si ejecuta Almacén de consultas en Azure SQL Database, puede usar Información de rendimiento de consultas para analizar el consumo de recursos a lo largo del tiempo. Aunque puede usar Management Studio y la extensión MSSQL para Visual Studio Code para obtener un consumo detallado de recursos para todas las consultas, como CPU, memoria e E/S, Información de rendimiento de consultas le ofrece una manera rápida y eficaz de determinar su efecto en el consumo general de DTU para la base de datos. Para más información, consulte Información de rendimiento de consultas para Azure SQL Database.

Para supervisar el rendimiento en la base de datos SQL de Fabric, use el Panel de rendimiento.

Usar el Almacén de consultas con bases de datos de grupos elásticos

Puede usar Almacén de consultas en todas las bases de datos sin preocupaciones, incluso en grupos elásticos de Azure SQL Database muy densos. Se han resuelto todos los problemas anteriores relacionados con el uso excesivo de recursos que podrían haberse producido cuando se habilitó Almacén de consultas para el gran número de bases de datos de los grupos elásticos.

Inicio de la solución de problemas de rendimiento de consultas

El flujo de trabajo de solución de problemas con el almacén de consultas es sencillo, como se muestra en el diagrama siguiente:

Habilite el almacén de consultas mediante Management Studio como se ha descrito en la sección anterior, o ejecute la instrucción Transact-SQL siguiente:

ALTER DATABASE [DatabaseOne]
    SET QUERY_STORE = ON;

Almacén de consultas tarda un tiempo en recopilar el conjunto de datos que representa con precisión la carga de trabajo. Normalmente, un día es suficiente incluso para cargas de trabajo muy complejas. Pero puede empezar a explorar los datos e identificar las consultas que requieran atención inmediatamente después de habilitar la característica. Vaya a la subcarpeta Almacén de consultas dentro del nodo de la base de datos en el Explorador de objetos de Management Studio para abrir las vistas de solución de problemas para escenarios específicos.

El almacén de consultas de Management Studio funciona con el conjunto de métricas de ejecución, cada una expresada como cualquiera de las siguientes funciones estadísticas:

En el gráfico siguiente se muestra cómo encontrar las vistas de Almacén de consultas:

En la siguiente tabla se explica cuándo usar cada una de las vistas del Almacén de consultas:

Cuando identifique una consulta con un rendimiento deficiente, la acción depende de la naturaleza del problema.

• Si la consulta se ha ejecutado con varios planes y el último plan es significativamente peor que el plan anterior, puede usar el mecanismo para forzar ese plan. SQL Server intenta forzar el plan en el optimizador. Si no se puede forzar el plan, se desencadena un evento XEvent y se indica al optimizador que optimice de la forma habitual.

• Se podría concluir que a la consulta le falta un índice para que la ejecución sea óptima. Esta información aparece en el plan de ejecución de la consulta. Cree el índice que falta y compruebe el rendimiento de la consulta mediante el almacén de consultas.

Si ejecuta la carga de trabajo en SQL Database, suscríbase al asesor de indexación de SQL Database para recibir automáticamente las recomendaciones de índices.

• En algunos casos, podría exigir la recompilación estadística si ve que la diferencia entre el número de filas estimado y el real en el plan de ejecución es significativa.
• Vuelva a escribir las consultas con problemas, por ejemplo, para aprovechar las ventajas de la parametrización de la consulta o implementar lógica más óptima.

Verifique que Almacén de consultas recopila datos de las consultas de forma continua

Almacén de consultas puede cambiar silenciosamente el modo de operación. Supervise periódicamente el estado del almacén de consulta para asegurarse de que funciona y tomar medidas para evitar errores debido a causas evitables. Ejecute la siguiente consulta para determinar el modo de operación y ver los parámetros más importantes:

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

La diferencia entre actual_state_desc y desired_state_desc indica que se ha producido un cambio de modo de operación automáticamente. El cambio más habitual es que Almacén de consultas pase silenciosamente al modo de solo lectura. En circunstancias extremadamente raras, el almacén de consultas puede terminar en el estado ERROR debido a errores internos.

Cuando el estado real es de solo lectura, use la columna readonly_reason para determinar la causa raíz. Normalmente, verá que Almacén de consultas ha pasado al modo de solo lectura porque se superó la cuota de tamaño. En ese caso, readonly_reason se establece en 65536. Por otros motivos, consulte sys.database_query_store_options.

Tenga en cuenta los pasos siguientes para cambiar el Almacén de consultas al modo de lectura y escritura y activar la recopilación de datos:

• Aumente el tamaño de almacenamiento máximo mediante la opción MAX_STORAGE_SIZE_MB de ALTER DATABASE.

• Limpie los datos del Almacén de consultas mediante la siguiente instrucción:

  ALTER DATABASE [QueryStoreDB]
      SET QUERY_STORE CLEAR;
  

Puede aplicar uno de estos pasos o los dos si ejecuta la instrucción siguiente que vuelve a cambiar de forma explícita el modo de operación a lectura y escritura:

ALTER DATABASE [QueryStoreDB]
    SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Siga estos pasos para ser proactivo:

• Puede evitar cambios silenciosos del modo de funcionamiento aplicando las mejores prácticas. Asegúrese de que el tamaño del almacén de consultas es siempre menor que el valor máximo permitido para reducir considerablemente la posibilidad de cambiar al modo de solo lectura. Active la directiva basada en el tamaño como se describe en la sección Configuración del almacén de consultas para que el almacén de consultas limpie automáticamente los datos cuando el tamaño se aproxime al límite.
• Para asegurarse de que se retienen los datos más recientes, configure la directiva basada en tiempo para quitar información obsoleta frecuentemente.
• Por último, considere la posibilidad de establecer Modo de captura de Almacén de consultas en Automático ya que filtra las consultas que suelen ser menos relevantes para la carga de trabajo.

Estado DE ERROR

Para recuperar el almacén de consultas, intente establecer de forma explícita el modo de lectura y escritura, y vuelva a comprobar el estado real.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Si el problema continúa, significa que los datos del almacén de consultas siguen dañados en el disco.

A partir de SQL Server 2017 (14.x), el almacén de consultas se puede recuperar si se ejecuta el procedimiento almacenado sys.sp_query_store_consistency_check en la base de datos afectada. El almacén de consultas se debe deshabilitar antes de intentar la operación de recuperación. Esta es una consulta de ejemplo que se puede usar o modificar para realizar la comprobación de coherencia y la recuperación de QDS:

IF EXISTS (SELECT *
           FROM sys.database_query_store_options
           WHERE actual_state = 3)
BEGIN
    BEGIN TRY
        ALTER DATABASE [QDS]
            SET QUERY_STORE = OFF;
        EXECUTE [QDS].dbo.sp_query_store_consistency_check ;
        ALTER DATABASE [QDS]
            SET QUERY_STORE = ON;
        ALTER DATABASE [QDS]
            SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber,
                ERROR_SEVERITY() AS ErrorSeverity,
                ERROR_STATE() AS ErrorState,
                ERROR_PROCEDURE() AS ErrorProcedure,
                ERROR_LINE() AS ErrorLine,
                ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END

Para SQL Server 2016 (13.x), tendrá que borrar los datos del almacén de consultas, como se muestra a continuación.

Si la recuperación no se realizó correctamente, puede intentar vaciar Almacén de consultas antes de configurar el modo de lectura y escritura.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE CLEAR;
GO

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Evitar el uso de consultas sin parámetros

El uso de consultas sin parámetros cuando no es necesario no es un procedimiento recomendado. Un ejemplo es en el caso del análisis ad hoc. Los planes en caché no se pueden reutilizar, lo que obliga al optimizador de consultas a compilar consultas para cada texto de consulta única. Para más información, vea Instrucciones para usar la parametrización forzada.

Además, el almacén de consultas puede superar rápidamente la cuota de tamaño debido a la posibilidad de un gran número de textos de consulta diferentes y, por tanto, un gran número de planes de ejecución distintos con forma similar. Como resultado, el rendimiento de la carga de trabajo es subóptimo y Almacén de consultas podría pasar a modo de solo lectura o eliminar datos constantemente para intentar mantenerse al día con el ritmo de las consultas entrantes.

Considere las opciones siguientes:

• Parametrice las consultas cuando proceda. Por ejemplo, encapsule las consultas dentro de un procedimiento almacenado o sp_executesql. Para más información, vea Parámetros y reutilización de un plan de ejecución.
• Use la opción optimizar para cargas de trabajo ad hoc si la carga de trabajo contiene muchos lotes ad hoc de uso único con diferentes planes de consulta.
  • Compare el número de valores query_hash distintos con el número total de entradas en sys.query_store_query. Si la relación está cerca de 1, la carga de trabajo ad hoc genera consultas diferentes.
• Aplique la parametrización forzada para la base de datos o para un subconjunto de consultas si el número de planes de consulta diferentes no es grande.
  • Use una guía de plan para forzar la parametrización solo para la consulta seleccionada.
  • Configure la parametrización forzada mediante el comando opción de base de datos de parametrización si hay un pequeño número de planes de consulta diferentes en la carga de trabajo. Un ejemplo es cuando la relación entre el recuento de valores query_hash distintos y el número total de entradas de sys.query_store_query es mucho menor que 1.
• Establezca QUERY_CAPTURE_MODE en AUTO para filtrar automáticamente las consultas ad hoc con un pequeño consumo de recursos.

Búsqueda de consultas no parametrizadas en el almacén de consultas

Puede encontrar el número de planes almacenados en Almacén de consultas con la siguiente consulta, usando las DMV de Almacén de consultas, en SQL Server, Azure SQL Managed Instance o Azure SQL Database:

SELECT count(Pl.plan_id) AS plan_count,
       Qry.query_hash,
       Txt.query_text_id,
       Txt.query_sql_text
FROM sys.query_store_plan AS Pl
     INNER JOIN sys.query_store_query AS Qry
         ON Pl.query_id = Qry.query_id
     INNER JOIN sys.query_store_query_text AS Txt
         ON Qry.query_text_id = Txt.query_text_id
GROUP BY Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
ORDER BY plan_count DESC;

En el ejemplo siguiente se crea información general sobre eventos extendidos para capturar el evento query_store_db_diagnostics, que puede resultar útil para diagnosticar el consumo de recursos de consulta. En SQL Server esta sesión de eventos extendidos crea un archivo de eventos en la carpeta de registro de SQL Server de forma predeterminada. Por ejemplo, en una instalación predeterminada de SQL Server 2019 (15.x) en Windows, se debe crear el archivo de eventos (archivo .xel) en la carpeta C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log. Para Azure SQL Managed Instance, especifique en su lugar una ubicación en Azure Blob Storage. Para obtener más información, consulte event_file de XEvent para Azure SQL Managed Instance. El evento "qds.query_store_db_diagnostics" no está disponible para Azure SQL Database.

CREATE EVENT SESSION [QueryStore_Troubleshoot] ON SERVER
ADD EVENT qds.query_store_db_diagnostics
(
    ACTION (sqlos.system_thread_id,
        sqlos.task_address,
        sqlos.task_time,
        sqlserver.database_id,
        sqlserver.database_name)
)
ADD TARGET package0.event_file
(
    SET filename = N'QueryStore',
    max_file_size = (100)
)
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);

Con estos datos puede encontrar la cantidad de planes en Almacén de consultas, así como muchas otras estadísticas. Busque las columnas plan_count, query_count, max_stmt_hash_map_size_kb y max_size_mb de los datos del evento para comprender la cantidad de memoria usada y el número de planes cuyo seguimiento realiza el almacén de consultas. Si el recuento de planes es superior a lo normal, puede indicar un aumento de las consultas no parametrizadas. Use la siguiente consulta DMV del almacén de consultas para revisar las consultas con parámetros y las consultas no parametrizadas en el almacén de consultas.

Para consultas con parámetros:

SELECT qsq.query_id,
       qsqt.query_sql_text
FROM sys.query_store_query AS qsq
     INNER JOIN sys.query_store_query_text AS qsqt
         ON qsq.query_text_id = qsqt.query_text_id
WHERE qsq.query_parameterization_type <> 0
      OR qsqt.query_sql_text LIKE '%@%';

Para consultas no parametrizadas:

SELECT qsq.query_id,
       qsqt.query_sql_text
FROM sys.query_store_query AS qsq
     INNER JOIN sys.query_store_query_text AS qsqt
         ON qsq.query_text_id = qsqt.query_text_id
WHERE query_parameterization_type = 0;

Evite un patrón de DROP y CREATE para los objetos que contienen

El almacén de consultas asocia la entrada de consulta a un objeto contenedor, como un procedimiento almacenado, una función y un desencadenador. Cuando se vuelve a crear un objeto contenedor, se genera una nueva entrada de consulta para el mismo texto de consulta. Esto impide realizar el seguimiento de las estadísticas de rendimiento para esa consulta a lo largo del tiempo y usar un mecanismo para forzar el plan. Para evitar esta situación, use el proceso ALTER <object> para cambiar la definición de un objeto contenedor siempre que sea posible.

Compruebe regularmente el estado de los planes forzados

Forzar planes es un mecanismo práctico para estabilizar el rendimiento de las consultas críticas y hacer que este sea más predecible. Como sucede con las sugerencias de plan y las guías de plan, forzar un plan no es una garantía de que se va a usar en ejecuciones futuras. Normalmente, cuando se cambia el esquema de base de datos de forma que se modifican o se quitan objetos a los que hace referencia el plan de ejecución, al forzar el plan se empiezan a generar errores. En ese caso, SQL Server recurre a la recompilación de la consulta, mientras que el motivo real del error de forzado se muestra en sys.query_store_plan. La siguiente consulta devuelve información sobre planes forzados.

USE [QueryStoreDB];
GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;

Para obtener una lista completa de los motivos, vea sys.query_store_plan. También puede usar el XEvent query_store_plan_forcing_failed para realizar un seguimiento de los errores al forzar el plan y solucionar los problemas relacionados.

Evitar el cambio de nombre de las bases de datos para las consultas con planes forzados

Los planes de ejecución hacen referencia a objetos mediante nombres de tres partes como database.schema.object.

Si se cambia el nombre de una base de datos, el forzado del plan falla, lo que provoca la recompilación en todas las ejecuciones posteriores de consultas.

Uso de Almacén de consultas en servidores críticos

Las marcas de seguimiento globales 7745 y 7752 se pueden usar para mejorar la disponibilidad de las bases de datos mediante el almacén de consultas. Para obtener más información, consulte Establecimiento de marcas de seguimiento con DBCC TRACEON.

• La marca de seguimiento 7745 evita el comportamiento predeterminado en el que el almacén de consultas escribe datos en el disco antes de que SQL Server se pueda apagar. Esto significa que los datos de Almacén de consultas que se han recopilado, pero que todavía no se han guardado en disco, se perderán hasta el límite de tiempo definido con DATA_FLUSH_INTERVAL_SECONDS.
• El indicador de seguimiento 7752 habilita la carga asincrónica de Almacén de consultas. Esto permite que una base de datos vuelva a estar en línea y que las consultas se ejecuten antes de que el almacén de consultas se haya recuperado completamente. El comportamiento predeterminado consiste en realizar la carga sincrónica del Almacén de consultas. Este comportamiento impide que se ejecuten las consultas antes de que el almacén de consultas se haya recuperado, pero también impide que se pierdan consultas en la colección de datos.

Uso de Almacén de consultas en la replicación geográfica activa de Azure SQL Database

El Almacén de consultas de una réplica geográfica activa secundaria de Azure SQL Database será una copia de solo lectura de la actividad de la réplica principal.

Evite los niveles no coincidentes con la replicación geográfica de Azure SQL Database. Una base de datos secundaria debe tener el mismo tamaño de proceso que la base de datos principal o similar, y debe estar en el mismo nivel de servicio. Busque el tipo de espera HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO en sys.dm_db_wait_stats, que indica la limitación de la velocidad del registro de transacciones en la réplica principal debido al retraso de la réplica secundaria.

Para obtener más información sobre la estimación y configuración del tamaño de la base de datos de Azure SQL secundaria de replicación geográfica activa, vea Configuración de la base de datos secundaria.

Mantén Almacén de consultas ajustado según tu carga de trabajo

En este artículo se ha profundizado en los procedimientos recomendados y las recomendaciones para configurar y administrar el almacén de consultas: Procedimientos recomendados para administrar el almacén de consultas.

Contenido relacionado

• ALTER DATABASE SET options (Transact-SQL)
• Vistas de catálogo de Almacén de consultas (Transact-SQL)
• Procedimientos almacenados del almacén de consultas (Transact-SQL)
• Uso del Almacén de consultas con In-Memory OLTP
• Guía de arquitectura de procesamiento de consultas
• Sugerencias de Almacén de consultas
• Supervisión del rendimiento mediante el Almacén de consultas
• Optimice el rendimiento con Almacén de consultas
• Almacenamiento y análisis del historial de consultas en Azure Synapse Analytics