Supervisar el rendimiento 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

La característica Almacén de consultas proporciona información sobre la elección y el rendimiento del plan de consulta para SQL Server, Azure SQL Database, Fabric SQL Database, Azure SQL Instancia administrada 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.

Puede configurar Almacén de consultas mediante la ALTER DATABASE SET opción de opciones .

Important

Si usa Almacén de consultas para obtener información puntual sobre la carga de trabajo en SQL Server 2016 (13.x), planee instalar las correcciones de rendimiento y escalabilidad de 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 READ_WRITE modo 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.
  • El Almacén de consultas no está habilitado de forma predeterminada para las nuevas bases de datos de Azure Synapse Analytics.

Usar la página de Almacén de consultas en 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.

    Note

    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 en una base de datos determinada. Por ejemplo:

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

Actualmente, las opciones para configurar Almacén de consultas en la base de datos SQL de Fabric con ALTER DATABASE son limitadas.

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 ALTER DATABASE SET opciones.

Note

El Almacén de consultas no se puede habilitar para las bases de datos master ni tempdb.

Important

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 de 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 forzar un plan. El forzado de planes en Almacén de consultas se realiza mediante un mecanismo similar a la sugerencia de consulta Query hints, pero no requiere ningún cambio en las aplicaciones de usuario. Forzar un plan puede resolver una regresión del rendimiento de una consulta provocada por un cambio de plan en muy poco tiempo.

Note

El Almacén de consultas recopila planes de ejecución para instrucciones DML como SELECT, INSERT, UPDATE, DELETE, MERGE y 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.

El Almacén de consultas no recopila datos para procedimientos almacenados compilados de forma nativa de forma predeterminada. 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 del rendimiento del plan forzando el plan de consulta anterior. Corregir las consultas que han experimentado recientemente una regresión del rendimiento debido a cambios en el 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 n consultas principales que están a la espera de recursos.
  • Comprender la naturaleza de espera de una consulta o un plan determinados.

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 datos de forma asíncrona. Para minimizar el espacio utilizado, las estadísticas de ejecución del entorno de ejecución se agregan en el almacén de estadísticas del entorno de ejecución durante un intervalo de tiempo fijo. La información de estos almacenes es visible mediante consultas a las vistas del catálogo de Almacén de consultas.

La siguiente consulta devuelve información sobre las consultas, sus planes y las estadísticas de tiempo de compilación y 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 para réplicas secundarias

Se aplica a: SQL Server 2025 (17.x), Azure SQL Database

La característica Almacén de consultas para réplicas secundarias permite la misma funcionalidad de Almacén de consultas en las cargas de trabajo de réplicas secundarias que está disponible 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. En esencia, hay un almacén de consultas compartido entre las réplicas 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 más información, consulte Almacén de consultas para réplicas secundarias.

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.

Note

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. El panel Consultas con regresión muestra las consultas y los planes en el 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áfico. 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 se pueden forzar los planes que se guardaron mediante la función de plan de consulta y que todavía se conservan en la caché de planes 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 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). Seleccionar un plan para ver el plan de consulta gráfico. Hay botones para ver la consulta original, forzar y dejar de forzar 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
Esperas elevadas de RESOURCE_SEMAPHORE por base de datos Esperas elevadas de memoria en Almacén de consultas para consultas específicas 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.
Elevadas esperas LCK_M_X por base de datos Esperas elevadas por bloqueo en Almacén de consultas para consultas específicas 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.
Número elevado de esperas PAGEIOLATCH_SH por base de datos Esperas de búfer elevadas IO en el Almacén de consultas para consultas específicas Encuentre las consultas con un gran número de lecturas físicas en el Almacén de consultas. Si coinciden con las consultas con esperas elevadas IO , considere la posibilidad de introducir un índice en la entidad subyacente, con el fin de realizar búsquedas en lugar de exámenes y, por tanto, minimizar la IO sobrecarga de las consultas.
Esperas elevadas de SOS_SCHEDULER_YIELD por base de datos Largos tiempos de espera de CPU en Almacén de consultas para consultas específicas Busque las consultas del Almacén de consultas que consumen más CPU. Entre ellas, identifique las consultas para las que una tendencia elevada de uso de CPU se correlaciona con tiempos de espera de CPU elevados en las consultas afectadas. Céntrese en optimizar esas consultas; podría haber una regresión en el plan de ejecución o quizá la falta de un índice.

Opciones de configuración

Para conocer las opciones disponibles para configurar Almacén de consultas parámetros, consulte ALTER DATABASE SET opciones (Transact-SQL).

Consulta la vista sys.database_query_store_options para conocer las opciones actuales de 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.

Note

Para 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 de Almacén de consultas

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

sys.fn_stmt_sql_handle_from_sql_stmt

Vistas de catálogo de Almacén de consultas

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

sys.database_query_store_options

sys.query_context_settings

sys.query_store_plan

sys.query_store_query

sys.query_store_query_text

sys.query_store_runtime_stats

sys.query_store_wait_stats

sys.query_store_runtime_stats_interval

sys.database_query_store_internal_state

Procedimientos almacenados de Almacén de consultas

Los procedimientos almacenados configuran el Almacén de consultas.

sp_query_store_flush_db

sp_query_store_reset_exec_stats

sp_query_store_force_plan

sp_query_store_unforce_plan

sp_query_store_remove_plan

sp_query_store_remove_query

sp_query_store_clear_message_queues

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 de 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

  • Last updated on