Exploración del Almacén de consultas
El Almacén de consultas de SQL Server es una característica por base de datos que captura automáticamente un historial de consultas, planes y estadísticas en tiempo de ejecución, lo que simplifica la solución de problemas de rendimiento y el ajuste de consultas. También proporciona información sobre los patrones de uso de la base de datos y el consumo de recursos.
El Almacén de consultas consta de tres almacenes:
- Almacén de planes: almacena la información del plan de ejecución estimado.
- Almacén de estadísticas en tiempo de ejecución: almacena información de estadísticas de ejecución.
- Almacén de estadísticas de espera: conserva la información de estadísticas de espera.
Habilitación del Almacén de consultas
El Almacén de consultas está habilitado de forma predeterminada en las bases de datos de Azure SQL. Si quiere usarlo con SQL Server y Azure Synapse Analytics, primero debe habilitarlo. Para habilitar la característica del Almacén de consultas, use la siguiente consulta válida para su entorno:
-- SQL Server
ALTER DATABASE <database_name> SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
-- Azure Synapse Analytics
ALTER DATABASE <database_name> SET QUERY_STORE = ON;
Cómo recopila datos el Almacén de consultas
El Almacén de consultas se integra con la canalización de procesamiento de consultas en varias fases. En cada punto de integración, los datos se recopilan en memoria y se escriben en el disco de forma asincrónica para minimizar la sobrecarga de E/S. Los puntos de integración son los siguientes:
Cuando una consulta se ejecuta por primera vez, su texto de consulta y el plan de ejecución inicial estimado se envían al Almacén de consultas y se conservan.
El plan se actualiza en el Almacén de consultas cuando se vuelve a compilar una consulta. Si la recompilación da como resultado un plan de ejecución recién generado, también se conserva en el Almacén de consultas para aumentar los planes anteriores. Además, el Almacén de consultas realiza un seguimiento de las estadísticas de ejecución de cada plan de consulta con fines de comparación.
Durante la compilación y comprobación de las fases de recompilación, el Almacén de consultas identifica si hay un plan forzado para que se ejecute la consulta. La consulta se vuelve a compilar si el Almacén de consultas proporciona un plan forzado diferente del plan en la memoria caché de procedimientos.
Cuando se ejecuta una consulta, sus estadísticas del entorno de ejecución se conservan en el Almacén de consultas. El Almacén de consultas agrega estos datos para garantizar una representación precisa de cada plan de consulta.
Para obtener más información sobre cómo Almacén de consultas recopila datos, consulte Cómo recopila datos el almacén de consultas.
Escenarios frecuentes
El Almacén de consultas de SQL Server proporciona información valiosa sobre el rendimiento de las operaciones de base de datos. Entre los escenarios habituales se incluyen los siguientes:
- Identificación y corrección de regresiones de rendimiento debido a una selección inferior del plan de ejecución de consultas.
- Identificación y ajuste de las consultas de consumo de recursos más altas.
- Pruebas A/B para evaluar los impactos de los cambios en la base de datos y la aplicación.
- Garantizar la estabilidad del rendimiento después de las actualizaciones de SQL Server.
- Determinación de las consultas usadas con más frecuencia.
- Auditar el historial de planes de consulta de una consulta.
- Identificación y mejora de las cargas de trabajo no planeadas.
- Descripción de las categorías de espera frecuentes de una base de datos y las consultas y planes de contribución que afectan a los tiempos de espera.
- Análisis de patrones de uso de base de datos a lo largo del tiempo en términos de consumo de recursos (CPU, E/S, Memoria).
Detección de las vistas del Almacén de consultas
Una vez que el Almacén de consultas está habilitado en una base de datos, la carpeta Almacén de consultas está visible para la base de datos en el Explorador de objetos. En el caso de Azure Synapse Analytics, las vistas del Almacén de consultas se muestran en Vistas del sistema. Las vistas del Almacén de consultas proporcionan información rápida y agregada sobre los aspectos de rendimiento de la base de datos de SQL Server.
Consultas con regresión
Una consulta con regresión experimenta una degradación del rendimiento a lo largo del tiempo debido a los cambios del plan de ejecución. Los planes de ejecución estimados pueden cambiar debido a varios factores, incluidos los cambios de esquema, los cambios de estadísticas y los cambios de índice. Investigar la caché de procedimientos puede ser el primer instinto, pero solo almacena el plan de ejecución más reciente para una consulta y los planes se pueden expulsar en función de las demandas de memoria del sistema. Sin embargo, el Almacén de consultas conserva varios planes de ejecución para cada consulta, lo que permite elegir un plan específico a través de la fuerza del plan para abordar la regresión del rendimiento de las consultas causada por los cambios del plan.
La vista Consultas con regresión puede identificar las consultas cuyas métricas de ejecución son regresivas debido a los cambios del plan de ejecución durante un período de tiempo especificado. Esta vista permite filtrar en función de una métrica seleccionada (por ejemplo, duración, tiempo de CPU, recuento de filas, etc.) y una estadística (total, promedio, mínimo, máximo o desviación estándar). A continuación, enumera las 25 consultas con regresión principales en función del filtro proporcionado. De forma predeterminada, se muestra una vista gráfica del gráfico de barras de las consultas, pero opcionalmente puede ver las consultas en un formato de cuadrícula.
Después de seleccionar una consulta en el panel de consulta de la parte superior izquierda, el panel de resumen del plan muestra los planes de consulta persistentes asociados a la consulta a lo largo del tiempo. Al seleccionar un plan de consulta en el panel Resumen del plan se muestra un plan de consulta gráfico en el panel inferior. Los botones de la barra de herramientas del panel de resumen del plan y el panel de plan gráfico de consulta permiten forzar el plan seleccionado para la consulta seleccionada. Esta estructura y comportamiento de panel se usan de forma coherente en todas las vistas de consulta SQL.
Como alternativa, puede usar el procedimiento almacenado sp_query_store_force_plan para usar el forzado del plan.
EXEC sp_query_store_force_plan @query_id=73, @plan_id=79
Consumo general de recursos
La vista Consumo general de recursos permite analizar el consumo total de recursos para varias métricas de ejecución (como el recuento de ejecuciones, la duración, el tiempo de espera, etc.) para un período de tiempo especificado. Los gráficos representados son interactivos; al seleccionar una medida de uno de los gráficos, aparece en una nueva pestaña una vista desglosada con las consultas asociadas a la medida elegida.
La vista de detalles proporciona las 25 principales consultas de consumidor de recursos que han contribuido a la métrica seleccionada. Esta vista de detalles usa la interfaz coherente que permite inspeccionar las consultas asociadas y sus detalles, evaluar los planes de consulta estimados guardados y, opcionalmente, usar el forzado de planes para mejorar el rendimiento. Esta vista es un recurso útil cuando la contención de recursos del sistema se convierte en un problema, como cuando el uso de la CPU alcanza su capacidad.
Consultas que más recursos consumen
La vista Consultas que más recursos consumen es similar a los detalles desglosados de la vista Consumo general de recursos. También permite seleccionar una métrica y una estadística como filtro. Sin embargo, las consultas que muestra son las 25 con más impacto según el filtro y el periodo de tiempo elegidos.
La vista Consultas de consumo de recursos principales proporciona la primera indicación de la naturaleza no planeada de la carga de trabajo al identificar y mejorar las cargas de trabajo no planeadas. Por ejemplo, en la imagen siguiente, se seleccionan la métrica Recuento de ejecuciones y la estadística Total para revelar que aproximadamente el 90 % de las consultas que más recursos consumen solo se ejecutan una vez.
Consultas con planes forzados
La vista Consultas con planes forzados proporciona una vista rápida de las consultas que tienen planes de consulta forzados. Esta vista es pertinente si un plan forzado ya no funciona según lo previsto y debe volver a evaluarse. Esta vista proporciona la capacidad de revisar todos los planes de ejecución estimados conservados para una consulta seleccionada para determinar fácilmente si otro plan ahora es más adecuado para el rendimiento. Si es así, los botones de la barra de herramientas están disponibles para dejar de forzar un plan según sea necesario.
Consultas con gran variación
El rendimiento de las consultas puede variar entre las ejecuciones. La vista Consultas con variación elevada contiene un análisis de las consultas que tienen la variación más alta o la desviación estándar de una métrica seleccionada. La interfaz es coherente con la mayoría de las vistas del Almacén de consultas, lo que permite la inspección detallada de consultas, la evaluación del plan de ejecución y, opcionalmente, el forzado de un plan específico. Use esta vista para optimizar las consultas impredecibles en un patrón de rendimiento más coherente.
Estadísticas de espera de consulta
La vista Estadísticas de espera de consulta analiza las categorías de espera más activas para la base de datos y representa un gráfico. Este gráfico es interactivo; al seleccionar una categoría de espera, se profundiza en los detalles de las consultas que contribuyen a la estadística de tiempo de espera.
La interfaz de la vista de detalles también es coherente con la mayoría de las vistas del Almacén de consultas, lo que permite la inspección detallada de consultas, la evaluación del plan de ejecución y, opcionalmente, el forzado de un plan específico. Esta vista ayuda a identificar las consultas que afectan a la experiencia del usuario en todas las aplicaciones.
Consulta de seguimiento
La vista Consulta de seguimiento permite analizar una consulta específica en función de en un valor de identificador de consulta especificado. Una vez ejecutada, la vista proporciona el historial de ejecución completo de la consulta. Una marca de verificación en una ejecución indica que se usó un plan forzado. Esta vista puede proporcionar información detallada sobre las consultas, como las que tienen planes forzados, para comprobar que el rendimiento de las consultas permanece estable.
Uso del Almacén de consultas para buscar tiempos de espera de consulta
Cuando el rendimiento de un sistema comienza a degradarse, es recomendable consultar las estadísticas de tiempo de espera de consulta para identificar potencialmente una causa. Además de identificar las consultas que necesitan optimizarse, también puede aclarar las posibles actualizaciones de infraestructura que serían beneficiosas.
El Almacén de consultas SQL proporciona la vista Estadísticas de espera de consulta para proporcionar información sobre las categorías de espera principales de la base de datos. Actualmente, hay 23 categorías de espera.
Un gráfico de barras muestra las categorías de espera con más impacto para la base de datos al abrir la vista Estadísticas de espera de consulta. Además, un filtro ubicado en la barra de herramientas del panel de categorías de espera permite calcular las estadísticas de espera en función del tiempo de espera total (valor predeterminado), el tiempo medio de espera, el tiempo de espera mínimo, el tiempo de espera máximo o el tiempo de espera de desviación estándar.
Al seleccionar una categoría de espera, se detallan los detalles de las consultas que contribuyen a esa categoría de espera. Desde esta vista, tiene la capacidad de investigar consultas individuales con más impacto. Puede acceder a los planes de ejecución estimados persistentes que se muestran en el panel Resumen del plan seleccionando una consulta en el panel de consultas. Al seleccionar un plan de consulta en el panel Resumen del plan, se muestra el plan de consulta gráfico en el panel inferior. Desde esta vista, tiene la capacidad de forzar o anular el forzado de un plan de consulta para que la consulta mejore el rendimiento.
Corrección automática del plan
En SQL Server 2017 y Azure SQL Database se incluyó el concepto de corrección automática del plan por medio del análisis de los datos del Almacén de consultas. Cuando el Almacén de consultas se habilita con una base de datos en SQL Server 2017 (o posterior) y en Azure SQL Database, el motor de SQL Server buscará regresiones del plan de consulta y proporcionará recomendaciones. Estas recomendaciones se pueden ver en la sys.dm_db_tuning_recommendations vista de administración dinámica (DMV). Estas recomendaciones incluyen instrucciones de T-SQL para forzar manualmente un plan de consulta cuando el rendimiento estaba en buen estado.
Si confía en estas recomendaciones, puede habilitar SQL Server para forzar los planes de forma automática cuando se encuentren regresiones. Habilite la corrección automática del plan mediante ALTER DATABASE y el argumento AUTOMATIC_TUNING.
En Azure SQL Database, la corrección automática del plan también se puede habilitar a través de las opciones de ajuste automático de Azure Portal o las API REST. Las recomendaciones de corrección automática del plan siempre están habilitadas en cualquier base de datos en la que esté habilitado el Almacén de consultas (que es el valor predeterminado en Azure SQL Database y Azure SQL Managed Instance). En cuanto a las bases de datos nuevas, la corrección automática del plan (FORCE_PLAN) está habilitada de forma predeterminada en Azure SQL Database.