Supervisión del rendimiento mediante el Almacén de consultas

  • Artículo

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics (solo grupo de SQL dedicado)

La característica de Almacén de consultas proporciona información sobre la elección y el rendimiento del plan de consulta para SQL Server, Azure SQL Database, Azure SQL Managed Instance y Azure Synapse Analytics. El Almacén de consultas simplifica la solución de problemas de rendimiento al permitirle encontrar rápidamente las diferencias de rendimiento provocadas por cambios en los planes de consulta. El Almacén de consultas captura automáticamente un historial de consultas, planes y estadísticas en tiempo de ejecución y las conserva para su revisión. Además, separa los datos por ventanas de tiempo, lo que permite ver patrones de uso de la base de datos y comprender cuándo se produjeron cambios del plan de consultas en el servidor. El almacén de consultas se puede configurar con la opción ALTER DATABASE SET .

Importante

Si usa el almacén de consultas para obtener información de carga de trabajo “Just-In-Time” en SQL Server 2016 (13.x), planee instalar las correcciones de escalabilidad de rendimiento en KB 4340759 lo antes posible.

Habilitación del Almacén de consultas

  • El Almacén de consultas está habilitado de manera predeterminada para las nuevas bases de datos de Azure SQL Database y Azure SQL Managed Instance.
  • El almacén de consultas no está habilitado de forma predeterminada para SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x). Está habilitado de forma predeterminada en el modo READ_WRITE para las nuevas bases de datos a partir de SQL Server 2022 (16.x). Para habilitar las características para realizar un seguimiento mejor del historial de rendimiento, solucionar problemas relacionados con el plan de consulta y habilitar nuevas funcionalidades en SQL Server 2022 (16.x), se recomienda habilitar el almacén de consultas en todas las bases de datos.
  • Almacén de consultas no está habilitado de forma predeterminada para nuevas bases de datos de Azure Synapse Analytics.

Uso de la página del Almacén de consultas de SQL Server Management Studio

  1. En el Explorador de objetos, haga clic con el botón derecho en una base de datos y, luego, seleccione Propiedades.

    Nota:

    Requiere al menos la versión 16 de Management Studio.

  2. En el cuadro de diálogo Propiedades de la base de datos , seleccione la página Almacén de consultas .

  3. En el cuadro Modo de operación (solicitado) seleccione Lectura y escritura.

Uso de instrucciones Transact-SQL

Use la instrucción ALTER DATABASE para habilitar el almacén de consultas para una base de datos concreta. Por ejemplo:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

En Azure Synapse Analytics, habilite el Almacén de consultas sin opciones adicionales, por ejemplo:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

Para obtener más opciones de sintaxis relacionadas con el Almacén de consultas, consulte Opciones de ALTER DATABASE SET (Transact-SQL).

Nota:

No se puede habilitar el Almacén de consultas para las bases de datos master o tempdb.

Importante

Para obtener información sobre cómo habilitar el Almacén de consultas y hacer que siga ajustándose a su carga de trabajo, consulte Procedimiento recomendado con el Almacén de consultas.

Información del Almacén de consultas

Los planes de ejecución de cualquier consulta específica en SQL Server suelen evolucionar con el tiempo debido a una serie de motivos diferentes, como cambios de estadísticas, cambios de esquema, creación o eliminación de índices, etc. La caché de procedimientos (donde se almacenan los planes de consulta almacenados en caché) solo almacena el plan de ejecución más reciente. Los planes también se eliminan de la caché de planes debido a la presión de memoria. Como resultado, es posible que las regresiones de rendimiento de consultas provocadas por los cambios de planes de ejecución no sean triviales y que su resolución lleve mucho tiempo.

Como el Almacén de consultas conserva varios planes de ejecución por consulta, puede aplicar directivas para dirigir el procesador de consultas para que use un plan de ejecución concreto para una consulta. Esto se conoce como forzado de plan. El forzado de plan en la consulta de almacenes se ofrece mediante un mecanismo similar al de la sugerencia de consulta USE PLAN , pero no requiere ningún cambio en las aplicaciones de usuario. El plan de forzado puede resolver una regresión del rendimiento de consultas provocado por un cambio de plan en un período de tiempo muy breve.

Nota:

Almacén de consultas recopila planes para instrucciones DML como SELECT, INSERT, UPDATE, DELETE, MERGEy BULK INSERT.

Por diseño, el almacén de consultas no recopila planes para instrucciones DDL como CREATE INDEX, etc. El almacén de consultas captura el consumo acumulativo de recursos mediante la recopilación de planes para las instrucciones DML subyacentes. Por ejemplo, el almacén de consultas puede mostrar las instrucciones SELECT e INSERT ejecutadas internamente para rellenar un nuevo índice.

De forma predeterminada, el Almacén de consultas no recopila datos para los procedimientos almacenados compilados de forma nativa. Use sys.sp_xtp_control_query_exec_stats para habilitar la recopilación de datos para los procedimientos almacenados compilados de forma nativa.

Las estadísticas de espera son otra fuente de información que ayuda a solucionar problemas de rendimiento en el motor de base de datos. Durante mucho tiempo, las estadísticas de espera solo han estado disponibles en el nivel de instancia, lo que dificultaba hacer un seguimiento hacia atrás de las esperas a una consulta específica. A partir de SQL Server 2017 (14.x) y Azure SQL Database, el almacén de consultas incluye una dimensión que realiza un seguimiento de las estadísticas de espera. En el ejemplo siguiente se habilita el almacén de consultas para recopilar estadísticas de espera.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

Entre los escenarios comunes para usar la característica Almacén de consultas se encuentran:

  • Buscar y corregir rápidamente una regresión de rendimiento de plan forzando el plan de consulta anterior. Corregir las consultas de las que se ha realizado regresión recientemente en el rendimiento debido a cambios del plan de ejecución.
  • Determinar el número de veces en que se ha ejecutado una consulta en una ventana de tiempo determinado, ayudando a un DBA en la solución de problemas de rendimiento de recursos.
  • Identificar las principales n consultas (por tiempo de ejecución, consumo de memoria, etc.) en las últimas x horas.
  • Auditar el historial de planes de consulta para una consulta determinada.
  • Analizar los patrones de uso (CPU, E/S y memoria) de recursos para una base de datos determinada.
  • Identificar las principales n consultas que están esperando recursos.
  • Comprender la naturaleza de espera de una consulta o un plan determinados.

El Almacén de consultas contiene tres almacenes:

  • un almacén de planes para conservar la información del plan de ejecución,
  • un almacén de estadísticas de runtime para conservar la información de las estadísticas de ejecución,
  • un almacén de estadísticas de espera para conservar la información de las estadísticas de espera.

El número de planes únicos que se pueden almacenar para una consulta en el almacén de planes se limita por la opción de configuración max_plans_per_query . Para mejorar el rendimiento, la información se escribe en los almacenes de forma asincrónica. Para minimizar el uso del espacio, se agregan las estadísticas de ejecución en tiempo de ejecución en el almacén de estadísticas de tiempo de ejecución en una ventana de tiempo fijo. La información de estos almacenes se puede ver al consultar las vistas del catálogo del Almacén de consultas.

La consulta siguiente devuelve información sobre las consultas, sus planes, el tiempo de compilación y las estadísticas en tiempo de ejecución del Almacén de consultas.

SELECT Txt.query_text_id, Txt.query_sql_text, Pln.plan_id, Qry.*, RtSt.*
FROM sys.query_store_plan AS Pln
INNER JOIN sys.query_store_query AS Qry
    ON Pln.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats RtSt
ON Pln.plan_id = RtSt.plan_id;

Almacén de consultas en réplicas secundarias

Se aplica a: SQL Server (a partir de SQL Server 2022 [16.x])

La característica de Almacén de consultas para réplicas secundarias habilita la misma funcionalidad del Almacén de consultas en las cargas de trabajo de réplicas secundarias que hay disponibles para las réplicas principales. Cuando se habilita el Almacén de consultas para réplicas secundarias, las réplicas envían la información de ejecución de consultas que normalmente se almacenaría en el Almacén de consultas de la réplica principal. A continuación, la réplica principal conserva los datos en el disco dentro de su propio Almacén de consultas. Básicamente, hay un Almacén de consultas compartido entre la réplica principal y todas las secundarias. El Almacén de consultas existe en la réplica principal y almacena los datos de todas las réplicas en un mismo lugar.

Para obtener información completa sobre el almacén de consultas para réplicas secundarias, consulte Almacén de consultas para réplicas secundarias del grupo de disponibilidad AlwaysOn.

Uso de la característica Consultas con regresión

Después de habilitar el Almacén de consultas, actualice la parte de la base de datos del panel del Explorador de objetos para agregar la sección Almacén de consultas.

Captura de pantalla del árbol de informes de Almacén de consultas en el Explorador de objetos de SSMS.

Nota:

Para Azure Synapse Analytics, las vistas del Almacén de consultas están disponibles en Vistas del sistema en la sección de la base de datos del panel Explorador de objetos.

Seleccione Consultas con regresión para abrir el panel Consultas con regresión en SQL Server Management Studio. En el panel Regressed Queries (Consultas devueltas) se muestran las consultas y los planes del Almacén de consultas. Usa los cuadros de la lista desplegable de la parte superior para filtrar las consultas según diversos criterios: Duración (ms) (valor predeterminado), Tiempo de CPU (ms), Lecturas lógicas (KB), Escrituras lógicas (KB), Lecturas físicas (KB), Tiempo de CLR (ms), DOP, Consumo de memoria (KB), Recuento de filas, Memoria usada (KB), Memoria de base de datos temporal utilizada (KB) y Tiempo de espera (ms).

Seleccione un plan para ver el plan de consulta gráfica. Los botones están disponibles para ver la consulta de origen, forzar y no forzar un plan de consulta, alternar entre los formatos de cuadrícula y gráfico, comparar los planes seleccionados (si se ha seleccionado más de uno) y actualizar la pantalla.

Captura de pantalla del informe de consultas devueltas de SQL Server en el Explorador de objetos de SSMS.

Para forzar un plan, seleccione una consulta y un plan y, luego, haga clic en Forzar plan. Solo puede forzar planes que se guardaron mediante la característica del plan de consulta y que todavía se conservan en la caché del plan de consulta.

Búsqueda de consultas en espera

A partir de SQL Server 2017 (14.x) y Azure SQL Database, las estadísticas de espera por consulta a lo largo del tiempo están disponibles en el almacén de consultas.

En el Almacén de consultas, los tipos de espera se combinan en categorías de espera. La asignación de categorías de espera a tipos de espera está disponible en sys.query_store_wait_stats (Transact-SQL).

Seleccione Estadísticas de espera de consulta para abrir el panel Estadísticas de espera de consulta en SQL Server Management Studio 18.0 o en versiones superiores. En el panel Estadísticas de espera de consulta se muestra un gráfico de barras que contiene las categorías de espera principales del Almacén de consultas. Usa la lista desplegable de la parte superior para seleccionar un criterio de agregado para el tiempo de espera: avg, max, min, std dev y total (valor predeterminado).

Captura de pantalla del informe de estadísticas de espera de consulta de SQL Server en el Explorador de objetos de SSMS.

Seleccione una categoría de espera seleccionando en la barra; aparece una vista de detalles en la categoría de espera seleccionada. Este gráfico de barras nuevo contiene las consultas que han contribuido a esa categoría de espera.

Captura de pantalla de la vista de detalle de estadísticas de espera de consulta de SQL Server en el Explorador de objetos de SSMS.

Usa el cuadro de la lista desplegable de la parte superior para filtrar las consultas según diversos criterios de tiempo de espera para la categoría de espera seleccionada: avg, max, min, std dev y total (valor predeterminado). Seleccione un plan para ver el plan de consulta gráfica. Los botones están disponibles para ver la consulta de origen, aplicar y eliminar la aplicación de un plan de consulta, y actualizar la pantalla.

Las categorías de espera combinan distintos tipos de espera en cubos similares por naturaleza. Las distintas categorías de espera exigen un análisis de seguimiento diferente para resolver el problema, pero los tipos de espera de la misma categoría dan lugar a experiencias de solución de problemas muy similares; proporcionar la consulta afectada además de las esperas sería la pieza que falta para completar correctamente la mayoría de las investigaciones de este tipo.

Estos son algunos ejemplos de cómo se puede obtener más información sobre la carga de trabajo antes y después de introducir categorías de espera en el Almacén de consultas:

Experiencia anterior Nueva experiencia Action
Altas esperas RESOURCE_SEMAPHORE por base de datos Altas esperas de memoria en el Almacén de consultas para consultas concretas Encuentre las consultas que más memoria consumen en el Almacén de consultas. Estas consultas probablemente retrasan el progreso de las consultas afectadas. Considere la posibilidad de usar la sugerencia de consulta MAX_GRANT_PERCENT para estas consultas o para las consultas afectadas.
Altas esperas LCK_M_X por base de datos Altas esperas de bloqueo en el Almacén de consultas para consultas concretas Compruebe los textos de consulta para las consultas afectadas e identifique las entidades de destino. En el Almacén de consultas, busque otras consultas que modifiquen la misma entidad, que se ejecuten con frecuencia o que tengan una gran duración. Tras identificar estas consultas, considere la posibilidad de cambiar la lógica de aplicación para mejorar la simultaneidad o usar un nivel de aislamiento menos restrictivo.
Altas esperas PAGEIOLATCH_SH por base de datos Altas esperas de E/S del búfer en el Almacén de consultas para consultas concretas Encuentre las consultas con un gran número de lecturas físicas en el Almacén de consultas. Si coinciden con las consultas con altas esperas de E/S, considere la posibilidad de introducir un índice en la entidad subyacente para llevar a cabo búsquedas en lugar de análisis y así minimizar la sobrecarga de E/S de las consultas.
Altas esperas SOS_SCHEDULER_YIELD por base de datos Altas esperas de CPU en el Almacén de consultas para consultas concretas Busque las consultas del Almacén de consultas que consumen más CPU. Entre ellas, identifique las consultas cuya alta tendencia de CPU se correlaciona con altas esperas de CPU para las consultas afectadas. Céntrese en la optimización de las consultas: podría ser una regresión de plan o posiblemente un índice que falta.

Opciones de configuración

Para consultar las opciones disponibles a fin de configurar los parámetros del Almacén de consultas, vea Opciones de ALTER DATABASE SET (Transact-SQL).

Consulte la vista sys.database_query_store_options para determinar las opciones actuales del Almacén de consultas. Para obtener más información sobre los valores, vea sys.database_query_store_options.

Para obtener ejemplos sobre cómo establecer opciones de configuración mediante instrucciones Transact-SQL, consulte Administración de opciones.

Nota:

En Azure Synapse Analytics, el Almacén de consultas se puede habilitar como en otras plataformas, pero no se admiten opciones de configuración adicionales.

Vea y administre el almacén de consultas a través de Management Studio o mediante las siguientes vistas y procedimientos.

Funciones del Almacén de consultas

Las funciones ayudan a las operaciones del Almacén de consultas.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

Vistas de catálogo del Almacén de consultas

Las vistas de catálogo presentan información sobre el Almacén de consultas.

sys.database_query_store_options (Transact-SQL)

sys.query_context_settings (Transact-SQL)

sys.query_store_plan (Transact-SQL)

sys.query_store_query (Transact-SQL)

sys.query_store_query_text (Transact-SQL)

sys.query_store_runtime_stats (Transact-SQL)

sys.query_store_wait_stats (Transact-SQL)

sys.query_store_runtime_stats_interval (Transact-SQL)

sys.database_query_store_internal_state (Transact-SQL)

Procedimientos almacenados del Almacén de consultas

Los procedimientos almacenados configuran el Almacén de consultas.

sp_query_store_flush_db (Transact-SQL)

sp_query_store_reset_exec_stats (Transact-SQL)

sp_query_store_force_plan (Transact-SQL)

sp_query_store_unforce_plan (Transact-SQL)

sp_query_store_remove_plan (Transact-SQL)

sp_query_store_remove_query (Transact-SQL)

sp_query_store_clear_message_queues (Transact-SQL)

sp_query_store_consistency_check (Transact-SQL)1

1 En escenarios extremos, Almacén de consultas puede entrar en el estado ERROR debido a errores internos. A partir de SQL Server 2017 (14.x), si esto sucede, el Almacén de consultas se puede recuperar mediante la ejecución del procedimiento almacenado sp_query_store_consistency_check en la base de datos afectada. Consulte sys.database_query_store_options para conocer más detalles de la descripción de columna actual_state_desc.

Mantenimiento del almacén de consultas

Los procedimientos recomendados y las recomendaciones para el mantenimiento y la administración del almacén de consultas se han ampliado en este artículo: Procedimientos recomendados para administrar el almacén de consultas.

Auditoría del rendimiento y solución de problemas

Para obtener más información sobre cómo optimizar el rendimiento con el Almacén de consultas, consulte Optimización del rendimiento con el Almacén de consultas.

Otros temas relacionados con el rendimiento:

Contenido relacionado