Introducción al Asistente para la optimización de consultas (QTA)
Tiene previsto migrar dos bases de datos de producción desde SQL Server 2012 a una nueva instancia de SQL Server 2022. Dado que las bases de datos están en uso, desea dejar inicialmente los niveles de compatibilidad en la configuración original y volver a seleccionar las aplicaciones. El estimador de cardinalidad ha cambiado desde SQL Server 2014 y quiere medir el rendimiento de las consultas antes de cambiar el nivel de compatibilidad de las bases de datos a SQL Server 2022 (160).
Debe buscar y corregir las consultas que regresan al mover el nivel de compatibilidad y aplicar el nuevo algoritmo del estimador de cardinalidad. Con este enfoque, se mide una línea de base para que el rendimiento se compare después de actualizar el nivel de compatibilidad.
Al habilitar el almacén de consultas se recopilan las métricas que necesita para buscar consultas de regresión y se usan las guías del asistente para la optimización de consultas (QTA) a través de la corrección de las consultas de regresión. En esta unidad se proporciona información general sobre el almacén de consultas y el QTA.
Supervisión del rendimiento mediante el almacén de consultas
La característica Almacén de consultas se introdujo en SQL Server 2016 para recopilar información sobre la ejecución y el rendimiento de las consultas en una base de datos de forma continua. El almacén de consultas funciona como una grabadora de datos piloto para recopilar información en tiempo de ejecución sobre consultas y planes. Si guarda estos datos en tiempo de ejecución, puede realizar un seguimiento del rendimiento a lo largo del tiempo. Si algo va mal, hay un historial de información para averiguar la causa del problema.
A partir de SQL Server 2022 y en Azure SQL Database y SQL Managed Instance, el almacén de consultas está habilitado para las nuevas bases de datos de forma predeterminada. En SQL Server 2016, SQL Server 2017 y SQL Server 2019, el almacén de consultas no está habilitado de forma predeterminada, pero cualquier base de datos de una instancia de SQL Server 2016 o superior puede habilitar, deshabilitar y configurar el almacén de consultas. Consulte la siguiente unidad para obtener instrucciones sobre cómo habilitar el almacén de consultas o confirmar su configuración en una base de datos.
El almacén de consultas puede funcionar en bases de datos con niveles de compatibilidad anteriores que la instancia de SQL Server. Por ejemplo, si se migra una base de datos de SQL Server 2012 a SQL Server 2022 y se deja el nivel de compatibilidad en 110, el Almacén de consultas puede funcionar todavía en la base de datos.
Sin embargo, muchas características del procesamiento de consultas inteligentes y otras mejoras automáticas de rendimiento solo están habilitadas para los niveles de compatibilidad de bases de datos más recientes. Por lo tanto, debe intentar probar el rendimiento de su aplicación en el último nivel de compatibilidad con bases de datos SQL Server. El almacén de consultas y el QTA pueden ayudar con esta prueba de rendimiento.
Cuando se habilita en una base de datos, el almacén de consultas recopila e informa de las siguientes estadísticas para las consultas:
- Consultas con regresión
- Consumo general de recursos
- Consultas que más recursos consumen
- Consultas con planes forzados
- Consultas con gran variación
- Estadísticas de espera de consulta
- Consultas con seguimiento
Una consulta con regresión se produce cuando el optimizador de consultas usa un nuevo plan de consulta que hace que el rendimiento se degrada. La regresión puede producirse después de cambios importantes, como agregar, quitar o modificar un índice, actualizar estadísticas o cambiar la cardinalidad de los datos.
Antes del almacén de consultas, SQL Server no proporcionaba información sobre la causa de las regresiones y la identificación de problemas era un problema para los desarrolladores y administradores de las bases de datos. Ahora puede usar el almacén de consultas para buscar consultas con regresión y forzar que el optimizador use un plan determinado del historial.
Es habitual que unas pocas consultas, de entre miles posibles, consuman la mayor parte de los recursos del sistema. El Almacén de consultas identifica las consultas que más consumen, ya sea debido a la regresión o a una optimización deficiente. En función de la configuración, puede filtrar los resultados por duración, CPU, memoria, E/S o número de ejecuciones.
Puede usar el almacén de consultas para supervisar el rendimiento continuo y para las pruebas A/B para comparar el rendimiento antes y después de aplicar un único cambio. Por ejemplo, puede optimizar el rendimiento de una consulta agregando un índice a una tabla a la que hace referencia la consulta, por lo que la búsqueda de combinación es más rápida. Comparar las estadísticas en el almacén de consultas antes y después de agregar el índice indica si el índice afecta al rendimiento. También puede comparar estadísticas después de agregar nuevo hardware o actualizar una aplicación.
Introducción al asistente para la optimización de consultas
El asistente para la optimización de consultas (QTA) usa datos del almacén de consultas para buscar consultas que empiezan a regresar. El QTA experimenta automáticamente para encontrar una solución que acelere la consulta, antes de que esta tenga un rendimiento inferior hasta el punto de afectar a los usuarios.
Puede usar el almacén de consultas y el QTA para supervisar y optimizar el rendimiento de la base de datos después de actualizar. Después de migrar una base de datos a SQL Server 2016 o superior, deje el nivel de compatibilidad de la base de datos sin cambios y habilite el almacén de consultas para recopilar estadísticas de rendimiento de consultas de línea base.
A continuación, cambie el nivel de compatibilidad y siga usando datos del almacén de consultas para medir las estadísticas de rendimiento. Puede comparar las estadísticas para averiguar si cada consulta está funcionando mejor, igual o peor que antes de la actualización.
Al cambiar el nivel de compatibilidad para actualizar la base de datos, SQL Server cambia la versión del estimador de cardinalidad que usa. El QTA busca posibles patrones de regresión de consultas debido al cambio en el estimador de cardinalidad y experimentos para encontrar mejoras de rendimiento. Después, puede crear guías de plan para las consultas que muestran la mejora.
Resumen
El Almacén de consultas mide continuamente las estadísticas de rendimiento de las consultas, de la misma forma que un registrador de datos de vuelos de un avión captura la actividad. Puede habilitar el almacén de consultas en cualquier base de datos de SQL Server 2016 o superior, independientemente del nivel de compatibilidad. Use el almacén de consultas para supervisar continuamente el rendimiento de las consultas y para las pruebas A/B para medir los efectos de un único cambio.
Al actualizar una base de datos a SQL Server 2014 o posterior, los cambios en el estimador de cardinalidad podrían ralentizar las consultas que eran rápidas en la versión anterior de SQL Server. Lo ideal es encontrar y corregir las regresiones antes de que afecten a los usuarios. Habilitar el Almacén de consultas en las bases de datos permite que las estadísticas se recopilen continuamente en las consultas. A continuación, puede usar el QTA para identificar y corregir las consultas de regresión antes de que se conviertan en un problema.