Ajuste de consultas con el Asistente para la optimización de consultas (QTA)

  • 5 minutos

Ha decidido usar el Almacén de consultas para supervisar el rendimiento de la base de datos antes de la migración y comparar estos datos con el rendimiento después de la actualización. Tiene previsto usar el Asistente para la optimización de consultas (QTA) para buscar consultas con regresión y sugerir mejoras. En esta unidad, se describen los pasos para usar el Almacén de consultas y el QTA para mantener el rendimiento de las consultas.

Dado que las aplicaciones de base de datos son sistemas críticos que respaldan las operaciones de la empresa, debe tener un plan de acción y un plan de contingencia para la actualización. Actualice las bases de datos de producción principales de una en una. Determine un plan de prueba para las bases de datos actualizadas para asegurarse de que cada base de datos vuelva a estar en producción sin ningún problema antes de actualizar las demás.

Puede usar 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 cambio, como una actualización de la base de datos. El QTA proporciona compatibilidad automática después de una actualización para buscar y corregir consultas con regresión, en función de los datos capturados en el Almacén de consultas.

Para que el QTA funcione correctamente, debe aplicar los pasos siguientes en orden.

Importante

Asegúrese de usar la base de datos de la aplicación en producción o una base de datos con una carga de trabajo de aplicación muy similar a la carga de trabajo de la base de datos en producción; de esta manera, el Almacén de consultas puede recopilar métricas realistas en las consultas.

  1. Migre la base de datos a SQL Server 2022.
  2. Deje el nivel de compatibilidad sin cambios, en la versión anterior de SQL Server.
  3. Habilite el Almacén de consultas en la base de datos.
  4. Permita que el Almacén de consultas recopile métricas de base de referencia en las consultas en función de una actividad de usuario realista y suficiente.
  5. Actualice el nivel de compatibilidad a SQL Server 2022 (160).
  6. De nuevo, deje que el Almacén de consultas recopile datos sobre las consultas en función de una actividad de usuario realista y suficiente.
  7. Use el QTA para comparar el rendimiento de las consultas antes y después del cambio del nivel de compatibilidad de la base de datos. Si se encuentran consultas con regresión, identifique las correcciones.

Migración de la base de datos

Cuando esté listo para realizar la migración a SQL Server 2022, empiece por migrar la base de datos a la nueva instancia. Hay varias maneras de hacerlo. Por ejemplo, puede utilizar una copia de seguridad y una restauración sencillas, utilizar la creación de reflejo de la base de datos o una carga masiva. La opción más adecuada depende de la configuración del entorno actual y de la versión de SQL Server desde la que se realiza la migración. Azure Data Migration Service (DMS) es una buena solución, ya que admite bases de datos de SQL Server 2005 en adelante.

Nota:

Azure DMS también admite migraciones de bases de datos a Azure SQL Managed Instance. Use la extensión de migración de Azure SQL para Azure Data Studio para empezar.

No cambie nada en el nivel de compatibilidad

Después de migrar la base de datos, no cambie nada en el nivel de compatibilidad. Este paso es fundamental, ya que debe crear la base de referencia con la configuración de la base de datos actual. Hasta que mueva el nivel de compatibilidad a SQL Server 2014 (120) o una versión posterior, SQL Server usará el estimador de cardinalidad heredado. SQL Server 2014 introdujo un estimador de cardinalidad actualizado que beneficia a la mayoría de consultas, pero que rara vez puede tener un impacto negativo en el rendimiento.

Habilitación del Almacén de consultas

Aunque el nivel de compatibilidad de la base de datos permanece en la versión anterior, puede habilitar Almacén de consultas en la base de datos, ya que es una característica de nivel de servidor. Para habilitar el Almacén de consultas:

  1. En SQL Server Management Studio (SSMS), haga clic con el botón derecho en la base de datos y seleccione Propiedades.
  2. En la ventana Propiedades de la base de datos, seleccione Almacén de consultas en el panel izquierdo.
  3. Establezca el Modo de operación (solicitado) en Solo lectura o Lectura y escritura.
  4. Seleccione Aceptar.

Como alternativa, puede ejecutar la siguiente instrucción para habilitar el Almacén de consultas en el modo READ WRITE predeterminado:

ALTER DATABASE <database-name> SET QUERY_STORE = ON

Permitir que el Almacén de consultas recopile datos

Vuelva a poner la base de datos migrada en producción y cambie las conexiones de base de datos de aplicaciones o informes. La base de datos comienza a recibir consultas de aplicaciones de producción. Permita que el Almacén de consultas se ejecute lo suficiente para recopilar una carga de trabajo realista en la base de datos.

El Almacén de consultas debe capturar un ciclo típico de actividad empresarial, incluido el horario comercial, el procesamiento nocturno, las ventanas de mantenimiento y otras actividades. Para muchas empresas, la actividad de una semana es suficiente, pero, para algunas empresas, este período puede ser más corto o más largo.

Muchas empresas tienen grandes ciclos de negocio y, por tanto, una actividad única para el procesamiento de nóminas bisemanales o de fin de mes. Debe tener en cuenta el tiempo de los ciclos de negocio que experimentan las bases de datos. Para una tienda de comestibles, los ciclos semanales de llegada y reposición de inventario cubren la mayoría de la actividad de la base de datos.

Para ver los datos recopilados, vaya a las pestañas de Almacén de consultas. Para ver las pestañas, en SSMS, en Explorador de objetos, expanda el árbol de la base de datos para mostrar Almacén de consultas. Después de recopilar suficientes datos, puede programar la actualización.

Actualización del nivel de compatibilidad

Antes de realizar cualquier cambio en una base de datos, se recomienda realizar una copia de seguridad de la base de datos, fuera del horario laboral, si es posible. Después de realizar la copia de seguridad, actualice el nivel de compatibilidad de la manera siguiente:

  1. Haga clic con el botón derecho en la base de datos en el Explorador de objetos de SSMS y seleccione Propiedades.
  2. En la ventana Propiedades de la base de datos, seleccione la pestaña Opciones.
  3. Cambie el nivel de compatibilidad a SQL Server 2022 (160) y seleccione Aceptar.

Como alternativa, puede ejecutar la siguiente instrucción:

ALTER DATABASE <database-name> SET COMPATIBILITY_LEVEL = 160

Permitir que el Almacén de consultas continúe recopilando datos

Después de actualizar la base de datos y reanudar las aplicaciones, el Almacén de consultas continúa en ejecución en segundo plano para recopilar métricas para las consultas. Ahora las consultas se exponen a posibles problemas debido al nuevo estimador de cardinalidad que usa el optimizador de consultas.

Continúe ejecutando el Almacén de consultas y permita recopilar datos durante la misma duración que antes de la actualización. Sin embargo, la regresión de consultas puede aparecer inmediatamente para que pueda tomar medidas para corregir los problemas de rendimiento inmediatamente.

Ejecución del Asistente para la optimización de consultas

Ejecute el QTA para abordar las consultas de regresión. Para configurar el QTA:

  1. Haga clic con el botón derecho en la base de datos en Explorador de objetos de SSMS y seleccione Tareas>Actualizar>Nueva sesión de actualización de base de datos.
  2. En la pantalla Configuración del Asistente para la optimización de consultas, escriba la duración de la carga de trabajo (en días) por capturar y el nivel de compatibilidad de la base de datos de destino.
  3. Seleccione Siguiente para configurar las pantallas Configuración y Ajuste.
  4. Seleccione Finalizar.

Para supervisar el QTA, haga clic con el botón derecho en el nombre de la base de datos, seleccione Tareas>Actualización de base de datos>Supervisar sesiones. El QTA proporciona un informe de resumen de las principales consultas con regresión, comparando los datos observados con los de la línea de base. Ahí podrá ver los cambios que recomienda el QTA para optimizar las consultas en la que se ha reducido el rendimiento.

Resumen

Use el QTA después de la actualización de la base de datos para buscar y corregir consultas con regresión como resultado de la actualización. Para que el QTA encuentre las consultas con regresión, primero debe crear una línea de base mediante el Almacén de consultas con el fin de medir las consultas en el nivel de compatibilidad anterior.

Después, el Almacén de consultas recopila las métricas después de la actualización, que puede usar con el QTA para comparar el nuevo rendimiento con la base de referencia. Es fundamental para la operación de QTA que el Almacén de consultas recopile datos antes y después de la actualización.

Cuando el QTA encuentra consultas con regresión, realiza experimentos con el fin de encontrar las mejores acciones para mejorar el rendimiento. A continuación, puede aplicar estas acciones.