Procedimientos recomendados para supervisar cargas de trabajo con Almacén de consultas

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

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 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.
• En Azure Synapse Analytics, 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 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. Descargue la versión más reciente de SQL Server Management Studio.

Para obtener una descripción rápida sobre cómo usar el almacén de consultas en escenarios de solución de problemas, vea Query Store 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 se puede usar Management Studio y Azure Data Studio para obtener el consumo de recursos detallado de todas las consultas (CPU, memoria y E/S), Información de rendimiento de consultas ofrece una forma rápida y eficaz de determinar su efecto sobre el consumo global de DTU de la base de datos. Para obtener más información, vea Información de rendimiento de consultas de Base de datos SQL de Azure.

Uso del Almacén de consultas con bases de datos de Grupo elástico

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;

El almacén de consultas tarda algún 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 del nodo de la base de datos en el explorador de objetos de Management Studio para abrir las vistas de solución de problemas de escenarios concretos.

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 localizar vistas del 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 mucho peor que el anterior, puede usar el mecanismo que fuerza el plan. SQL Server intenta forzar el plan en el optimizador. Si se produce un error al exigir el plan, se producirá un evento XEvent y el optimizador realizará su trabajo de forma normal.

• 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.

Comprobación de que el almacén de consultas recopila datos de consulta de forma continua

El almacén de consultas puede cambiar el modo de operación automáticamente. 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 frecuente es que el almacén de consultas cambie al modo de solo lectura automáticamente. 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, encontrará que el almacén de consultas ha cambiado al modo de solo lectura porque se ha superado la cuota de tamaño. En ese caso, readonly_reason se establece en 65536. Por otros motivos, consulte sys.database_query_store_options (Transact-SQL).

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 automáticos de modo de operación aplicando procedimientos recomendados. 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 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
    Exec [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 ha realizado correctamente, puede intentar borrar el almacén de consultas antes de establecer 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 será deficiente y el almacén de consultas podría cambiar al modo de solo lectura o eliminar datos constantemente en un intente de mantenerse al día con 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 el almacén de consultas mediante la consulta siguiente, mediante DMV del almacén de consultas o 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 una sesión de eventos extendidos para capturar el evento query_store_db_diagnostics, que puede ser ú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. Sin embargo, para Azure SQL Managed Instance, debe especificar una ubicación de 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 el recuento de planes en el almacén de consultas, y también 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 mayor de lo normal, puede indicar un aumento en 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;

Evitar un patrón DROP y CREATE para objetos contenedores

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.

Comprobación periódica del estado de los planes forzados

Forzar el plan es un mecanismo conveniente para corregir el rendimiento de las consultas críticas y hacer que sean más predecibles. 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 vuelve a la recompilación de consultas mientras el motivo real del error de la operación de forzado aparece 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 forzados del plan y solucionarlos.

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 cambia el nombre de una base de datos, al forzar el plan se produce un error que provoca la recompilación en todas las ejecuciones de consulta posteriores.

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 más información, vea Marcas de seguimiento.

• 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 del almacén de consultas que se han recopilado, pero que todavía no han almacenado en el disco, se perderán, hasta la ventana de tiempo definida con DATA_FLUSH_INTERVAL_SECONDS.
• La marca de seguimiento 7752 permite la carga asincrónica del 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

Almacén de consultas en una réplica de replicación geográfica activa secundaria de Azure SQL Database será una copia de solo lectura de la actividad en 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 velocidad del registro de transacciones en la réplica principal debido a un retraso secundario.

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.

Mantener Almacén de consultas ajustado a la 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

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