Decidir si el Asistente para la optimización de consultas (QTA) es adecuado para usted
Conoce la posible regresión del plan de consulta y la pérdida de rendimiento después de las actualizaciones de la versión de la base de datos. Para ayudar a mantener el rendimiento después de las actualizaciones de la base de datos, debe encontrar el mejor método para identificar y mitigar las consultas con regresión. En esta unidad se describe cómo puede usar el almacén de consultas y el asistente para la optimización de consultas (QTA) para ayudar a garantizar que el rendimiento reducido no es un problema después de las actualizaciones.
Introducción al almacén de consultas y al asistente para la optimización de consultas
El QTA depende de los datos del almacén de consultas para buscar consultas que regresan después de una actualización. El almacén de consultas permite recopilar métricas en la versión anterior de la base de datos antes de actualizar.
El almacén de consultas se introdujo en SQL Server 2016 y el QTA se introdujo en SQL Server 2017. Cualquier versión de base de datos que se ejecute en una instancia de SQL Server 2022 puede usar ambas características. Estas herramientas están integradas en SQL Server Management Studio (SSMS) y operan en el nivel de base de datos.
El nivel de compatibilidad de la base de datos determina su versión, que a su vez determina la versión del estimador de cardinalidad que usa. El estimador de cardinalidad predice cuántas filas es probable que devuelva una consulta, por lo que el optimizador de consultas puede seleccionar el plan de menor costo. SQL Server 2014 introdujo un algoritmo estimador de cardinalidad actualizado que beneficia a la mayoría de consultas, pero que rara vez puede tener un impacto negativo en el rendimiento.
Para medir el impacto en el rendimiento, el almacén de consultas notifica consultas con regresión y consultas que consumen la mayoría de los recursos del sistema. El QTA compara los datos de rendimiento de las consultas del almacén de consultas antes y después de la actualización de la base de datos y experimenta en las consultas para mejorar el rendimiento.
Nota:
El QTA no está disponible para las bases de datos de Azure SQL Database o SQL Managed Instance. Para estas bases de datos, considere la posibilidad de usar la extensión de migración de Azure SQL para Azure Data Studio.
Corrección automática del plan Y QTA
Cuando SQL Server ejecuta una consulta de Transact-SQL (T-SQL), analiza los planes posibles que pueden ejecutarla. SQL Server almacena en caché planes para consultas que se ejecutan correctamente y los reutiliza cuando las consultas se vuelven a ejecutar.
SQL Server elige el mejor plan para la consulta y lo usa hasta que una circunstancia obliga a elegir uno nuevo. Estas circunstancias pueden incluir que el motor de la base de datos vuelva a compilar el plan, que se añada o elimine un índice o que se modifiquen las estadísticas.
No siempre ocurre que el nuevo plan sea una mejora del anterior. Puede ejecutar el siguiente comando para buscar consultas que tengan un plan con regresión.
SELECT * FROM sys.dm_db_tuning_recommendations
A continuación, puede usar el procedimiento almacenado sp_force_plan para forzar a SQL Server a usar un plan específico recomendado.
EXEC sp_force_plan @query_id = 1187, @plan_id = 1975
El procedimiento sp_force_plan es un proceso manual que es potencialmente tedioso si muchas consultas vuelven a realizar una copia de seguridad en una base de datos actualizada. SQL Server 2017 introdujo una nueva característica denominada corrección automática del plan para las consultas de ajuste automático y eliminar la necesidad de intervención manual. Puede habilitar la corrección automática del plan en una base de datos al ejecutar la siguiente instrucción:
ALTER DATABASE <database-name> SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)
Cuando se establece en un nivel de base de datos, la corrección automática del plan indica a SQL Server que use el último plan de consulta correcto. SQL Server sigue supervisando el plan para encontrar regresiones mientras se ejecuta y para asegurarse de que ofrece un rendimiento óptimo.
La corrección automática del plan funciona de forma diferente al QTA. El uso del último plan correcto puede significar revertir a un estimador de cardinalidad anterior. Por el contrario, el QTA realiza sus experimentos con la versión del estimador de cardinalidad asignado al nivel de compatibilidad de la base de datos de destino.
Resumen
El QTA está disponible en SQL Server 2022 y depende del Almacén de consultas para operar. El QTA debe tener datos de línea de base del Almacén de consultas para una base de datos en su nivel de compatibilidad anterior, de modo que pueda observar las consultas y realizar comparaciones después de una actualización.
La corrección automática del plan, introducida en SQL Server 2017, elimina la necesidad de identificar y forzar manualmente un plan de consulta. La corrección automática del plan se puede habilitar en el nivel de base de datos, pero podría revertir la versión del estimador de cardinalidad. El QTA usa la versión del estimador de cardinalidad asignado al nivel de compatibilidad de destino.