Actualización de bases de datos mediante el Asistente para la optimización de consultas

Artículo
09/07/2023

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL Database Azure Synapse Analytics Analytics Platform System (PDW)

Al migrar desde una versión anterior de SQL Server a SQL Server 2014 (12.x) o posterior, y al actualizar el nivel de compatibilidad de la base de datos al más reciente disponible, una carga de trabajo podría quedar expuesta al riesgo de regresión del rendimiento. Esto es posible también en menor grado al actualizar entre SQL Server 2014 (12.x) y cualquier versión más reciente.

A partir de SQL Server 2014 (12.x), y con cada nueva versión, todos los cambios del optimizador de consultas se canalizan al nivel de compatibilidad de la base de datos más reciente, por lo que los planes de ejecución no se cambian en el momento de la actualización, sino cuando un usuario cambia la opción de base de datos COMPATIBILITY_LEVEL a la más reciente disponible. Para obtener más información sobre los cambios del optimizador de consultas presentados en SQL Server 2014 (12.x), consulte Estimador de cardinalidad. Para obtener más información sobre los niveles de compatibilidad y cómo pueden afectar a las actualizaciones, vea Actualizaciones del motor de base de datos y niveles de compatibilidad.

Esta capacidad de canalización proporcionada por el nivel de compatibilidad de la base de datos, en combinación con el Almacén de consultas, ofrece un gran nivel de control sobre el rendimiento de las consultas durante el proceso de actualización si la actualización sigue el flujo de trabajo recomendado que se indica a continuación. Para obtener más información sobre el flujo de trabajo recomendado para actualizar el nivel de compatibilidad, vea Cambiar el nivel de compatibilidad de la base de datos y usar el almacén de consultas.

Recommended database upgrade workflow using Query Store

Este control sobre las actualizaciones se mejoró aún más con SQL Server 2017 (14.x), donde se incorporó el ajuste automático y permite automatizar el último paso del flujo de trabajo recomendado anteriormente.

A partir de SQL Server Management Studio v18, la nueva función del Asistente para la optimización de consultas (QTA) guiará a los usuarios a través del flujo de trabajo recomendado para mantener la estabilidad del rendimiento durante las actualizaciones a versiones más recientes de SQL Server, como se documenta en la sección Mantener la estabilidad del rendimiento durante la actualización a versiones más recientes de SQL Server de Escenarios de uso del almacén de consultas. Sin embargo, QTA no puede revertir a un buen plan conocido previamente como se muestra en el último paso del flujo de trabajo recomendado. En su lugar, QTA realizará el seguimiento de las regresiones encontradas en la vista Consultas devueltas de Query Stores y recorrerá en iteración las permutaciones posibles de variaciones de modelos de optimizador aplicables para que se genere un plan mejor.

Importante

QTA no genera carga de trabajo de usuario. Si ejecuta QTA en un entorno que no se usa en sus aplicaciones, asegúrese de que aún puede ejecutar una carga de trabajo de prueba representativa en el motor de base de datos SQL Server objetivo por otros medios.

Flujo de trabajo del Asistente para la optimización de consultas

El punto inicial de QTA supone que una base de datos de una versión anterior de SQL Server se traslada (mediante CREATE DATABASE ... FOR ATTACH o RESTORE) a una versión más reciente del motor de base de datos de SQL Server, y el nivel de compatibilidad de la base de datos anterior a la actualización no se modifica inmediatamente. QTA le guía a través de los pasos siguientes:

Configure el Almacén de consultas conforme a la configuración recomendada para la duración de la carga de trabajo (en días) establecida por el usuario. Piense en la duración de la carga de trabajo que coincida con el ciclo comercial típico.
Solicite iniciar la carga de trabajo necesaria para que el Almacén de consultas pueda recopilar una línea de base de datos de carga de trabajo (si no hay ninguna disponible aún).
Actualice al nivel de compatibilidad de la base de datos de destino elegido por el usuario.
Solicite que se recopile una segunda pasada de datos de carga de trabajo para la comparación y la detección de regresiones.
Recorra en iteración las regresiones detectadas en función de la vista Consultas con regresión del Almacén de consultas, experimente mediante la recopilación de estadísticas en tiempo de ejecución sobre las posibles permutaciones de variaciones de modelo del optimizador aplicables y mida el resultado.
Informe sobre las mejoras medidas y, opcionalmente, permita que los cambios se conserven mediante guías de plan.

Para obtener más información sobre cómo asociar una base de datos, vea Adjuntar y separar bases de datos.

Vea a continuación cómo QTA solo cambia los últimos pasos del flujo de trabajo recomendado para actualizar el nivel de compatibilidad mediante el Almacén de consultas visto arriba. En lugar de dar la opción de elegir entre el plan de ejecución actualmente ineficaz y el último plan de ejecución bueno conocido, QTA presenta opciones de optimización que son específicas de las consultas con regresión seleccionadas, con el fin de crear un nuevo estado mejorado con planes de ejecución optimizados.

Recommended database upgrade workflow using QTA

Espacio de búsqueda interno de optimización de QTA

QTA solo se ocupa de las consultas SELECT que se pueden ejecutar desde el Almacén de consultas. Las consultas parametrizadas son aptas si se conoce el parámetro compilado. Las consultas que dependen de construcciones en tiempo de ejecución, como tablas temporales o variables de tabla, no son aptas en este momento.

QTA tiene como destino posibles patrones conocidos de regresiones de consulta debidos a cambios en las versiones de Estimación de cardinalidad. Por ejemplo, al actualizar una base de datos de SQL Server 2012 (11.x) y nivel de compatibilidad de la base de datos 110, a SQL Server 2017 (14.x) y nivel de compatibilidad de la base de datos 140, algunas consultas pueden experimentar retrocesos porque se diseñaron específicamente para funcionar con la versión CE que existía en SQL Server 2012 (11.x) (CE 70). Esto no significa que la reversión de estimación de cardinalidad 140 a 70 sea la única opción. Si el cambio que provoca la regresión es solo uno de los cambios de la versión más reciente, se puede indicar a la consulta que use solo la parte de la versión anterior de CE que funcionaba mejor para la consulta en cuestión, sin dejar de usar el resto de mejoras de las versiones más recientes de CE. Además permite que las demás consultas de la carga de trabajo que no han sufrido regresión se beneficien de las últimas mejoras de estimación de cardinalidad.

Los patrones de estimación de cardinalidad que busca QTA son los siguientes:

Independencia contra correlación: si la hipótesis de independencia proporciona mejores estimaciones para la consulta específica, entonces la sugerencia de consulta USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') hace que SQL Server genere un plan de ejecución mediante el uso de la selectividad mínima al estimar predicados AND para filtros para tener en cuenta la correlación. Para obtener más información, vea USE HINT y Versiones de la estimación de cardinalidad.
Independencia simple contra independencia base: Si una contención de unión diferente proporciona mejores estimaciones para la consulta específica, entonces la sugerencia de consulta USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') hace que SQL Server genere un plan de ejecución mediante el uso de la hipótesis de contención simple en lugar de la hipótesis de contención base predeterminada. Para obtener más información, vea USE HINT y Versiones de la estimación de cardinalidad.
Estimación de cardinalidad fija para funciones con valores de tabla de varias instrucciones (MSTVF) de 100 filas contra a 1 fila: Si la estimación fija predeterminada para TVF de 100 filas no da como resultado un plan más eficaz que el uso de la estimación fija para TVF de 1 fila (correspondiente al valor predeterminado en el modelo CE del optimizador de consultas de SQL Server 2008 R2 (10.50.x) y versiones anteriores), se utiliza la sugerencia de consulta QUERYTRACEON 9488 para generar un plan de ejecución. Para obtener más información sobre las MSTVF, vea Creación de funciones definidas por el usuario (motor de base de datos).

Nota:

Como último recurso, si las sugerencias de ámbito estrecho no generan resultados lo suficientemente buenos para los patrones de consulta aptos, también se considera el uso completo de estimación de cardinalidad 70, mediante la sugerencia de consulta USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') para generar un plan de ejecución.

Importante

Cualquier sugerencia fuerza ciertos comportamientos que pueden ser abordados en futuras actualizaciones de SQL Server. Se recomienda aplicar sugerencias únicamente cuando no exista ninguna otra opción, y planee revisar el código sugerido con cada nueva actualización. Al forzar comportamientos, puede estar impidiendo que su carga de trabajo se beneficie de las mejoras incorporadas en versiones más recientes de SQL Server.

Iniciar el Asistente para la optimización de consultas para actualizaciones de bases de datos

QTA es una característica basada en sesión que almacena el estado de sesión en el esquema msqta de la base de datos de usuario donde se crea una sesión por primera vez. Se pueden crear varias sesiones de optimización en una sola base de datos con el tiempo, pero solo puede existir una sesión activa de cualquier base de datos determinada.

Creación de una sesión de actualización de base de datos

En SQL Server Management Studio abra el Explorador de objetos y conéctese al motor de base de datos.
Para la base de datos cuyo nivel de compatibilidad se desea actualizar, haga clic con el botón derecho del ratón en el nombre de la base de datos, seleccione Tareas, seleccione Actualización de base de datos y seleccione Nueva sesión de actualización de base de datos.
En la ventana del asistente QTA, se requieren dos pasos para configurar una sesión:
1. En la ventana Setup (Configuración), configure el Almacén de consultas para capturar el equivalente a un ciclo comercial completo de datos de carga de trabajo para analizar y optimizar.
  - Especifique la duración de la carga de trabajo esperada en días (el mínimo es 1 día). Se usa para proponer la configuración recomendada del Almacén de consultas para permitir que se recopile la línea de base completa. La captura de una buena línea de base es importante para garantizar que las consultas con regresión detectadas después de cambiar el nivel de compatibilidad de la base de datos se puedan analizar.
  - Establezca el nivel de compatibilidad previsto de la base de datos de destino en el que debería estar la base de datos de usuario una vez completado el flujo de trabajo de QTA. Cuando haya terminado, seleccione Siguiente.
2. En la ventana Configuración, dos columnas muestran el estado Actual del Almacén de consultas de la base de destino, así como la configuración Recomendada.
  - La configuración recomendada se selecciona de forma predeterminada, pero al seleccionar el botón radio en la columna actual, se acepta la configuración actual y también se permite ajustar con precisión la configuración actual del Almacén de consultas.
  - El umbral de consultas obsoletas propuesto es el doble de la duración prevista de la carga de trabajo, en días. Esto se debe a que el Almacén de consultas debe contener información sobre la carga de trabajo de línea de base y la carga de trabajo de actualización posterior a la base de datos. Cuando haya terminado, seleccione Siguiente.
  Importante
  
  El valor Tamaño máximo propuesto es un valor arbitrario que puede ser adecuado para una carga de trabajo de breve duración. Pero tenga en cuenta que puede ser insuficiente para contener información sobre la línea de base y las cargas de trabajo de actualización posteriores a la base de datos de cargas de trabajo muy intensivas, concretamente cuando pueden generarse muchos planes diferentes. Si anticipa que este pueda ser el caso, escriba un valor más alto que sea adecuado.
La ventana Optimización concluye la configuración de la sesión e indica los pasos siguientes para abrir la sesión y continuar con ella. Cuando haya finalizado, seleccione Finalizar.

Ejecutar el flujo de trabajo de actualización de la base de datos

En la base de datos cuyo nivel de compatibilidad de base de datos intenta actualizar, haga clic con el botón derecho en el nombre de la base de datos, seleccione Tareas, seleccione Actualización de base de datos y seleccione Supervisar sesiones.
La página de administración de sesiones enumera las sesiones actuales y pasadas de la base de datos en cuestión. Seleccione la sesión deseada y seleccione Detalles.

Nota:

Si la sesión actual no aparece, seleccione el botón Actualizar.

La lista contiene la información siguiente:
- Id. de sesión
- Nombre de sesión: nombre generado por el sistema compuesto por el nombre de la base de datos, la fecha y la hora de creación de la sesión.
- Estado: estado de la sesión (activa o cerrada).
- Descripción: generado por el sistema compuesto por el nivel de compatibilidad de la base de datos objetivo seleccionado por el usuario y el número de días para la carga de trabajo del ciclo de comercial.
- Hora de inicio: fecha y hora de creación de la sesión.
Nota:

Eliminar sesión elimina todos los datos almacenados de la sesión seleccionada, aunque la eliminación de una sesión cerrada no elimina guías de plan implementadas previamente. Si elimina una sesión con guías de plan implementadas, no puede usar QTA para revertir. En su lugar, busque guías de plan mediante la tabla del sistema sys.plan_guides y elimine manualmente mediante sp_control_plan_guide.
El punto de entrada de una nueva sesión es el paso Recopilación de datos.

Nota:

El botón Sesiones devuelve a la página de administración de sesiones y deja la sesión activa tal cual.

Este paso tiene tres subpasos:
1. Recopilación de datos de línea de base solicita al usuario que ejecute el ciclo de carga de trabajo representativo para que el Almacén de consultas pueda recopilar una línea de base. Una vez completada esa carga de trabajo, active Ejecución de la carga de trabajo finalizada y seleccione Siguiente.
  
  Nota:
  
  La ventana de QTA se puede cerrar mientras la carga de trabajo se ejecuta. Al volver a la sesión que permanece en estado activo en un momento posterior, se reanuda desde el mismo paso donde se había quedado.
2. Actualizar base de datos le pide permiso para actualizar el nivel de compatibilidad de la base de datos al destino deseado. Para continuar con el siguiente subpaso, seleccione Sí.
  
  La siguiente página confirma que el nivel de compatibilidad de la base de datos se ha actualizado correctamente.
3. Recopilación de datos observados solicita al usuario que vuelva a ejecutar el ciclo de carga de trabajo representativo para que el Almacén de consultas pueda recopilar una línea de base comparativa que se use para buscar oportunidades de optimización. Mientras se ejecuta la carga de trabajo, use el botón Actualizar para seguir actualizando la lista de consultas con regresión, si se ha detectado alguna. Cambie el valor Queries to show (Consultas que se van a mostrar) para limitar el número de consultas que aparecen. El orden de la lista se ve afectado por Métrica (Duración o Tiempo de CPU) y Agregación (Promedio es el valor predeterminado). Seleccione también cuántas consultas se van a mostrar. Una vez completada esa carga de trabajo, active Ejecución de la carga de trabajo finalizada y seleccione Siguiente.
  
  La lista contiene la información siguiente:
  - Id. de consulta
  - Texto de consulta: Instrucción Transact-SQL que puede ampliarse seleccionando el botón ...
  - Ejecuciones: muestra el número de ejecuciones de esa consulta para toda la colección de cargas de trabajo.
  - Métrica de línea de base: métrica seleccionada (Duración o Tiempo de CPU) en milisegundos para la colección de datos de línea de base antes de la actualización de compatibilidad de base de datos.
  - Métrica observada: métrica seleccionada (Duración o Tiempo de CPU) en milisegundos para la colección de datos después de la actualización de compatibilidad de base de datos.
  - % de cambio: porcentaje de cambio de la métrica seleccionada entre el estado anterior y posterior a la actualización de la compatibilidad de la base de datos. Un número negativo representa la cantidad de regresión medida de la consulta.
  - Optimizable: Verdadero o Falso en función de si la consulta es apta para experimentación.
Ver análisis permite la selección de las consultas con las que se va a experimentar y buscar oportunidades de optimización. El valor de consultas que se van a mostrar se convierte en el ámbito de las consultas aptas con las que se va a experimentar. Una vez activadas las consultas deseadas, seleccione Siguiente para iniciar la experimentación.

Nota:

Las consultas con Optimizable = Falso no se pueden seleccionar para experimentación.

Importante

Un símbolo del sistema advierte de que una vez que QTA pasa a la fase de experimentación, no es posible volver a la página Ver análisis.
Si no selecciona todas las consultas aptas antes de pasar a la fase de experimentación, debe crear una nueva sesión posteriormente y repetir el flujo de trabajo. Esto requiere el restablecimiento del nivel de compatibilidad de la base de datos al valor anterior.
Visualización de resultados permite la selección de las consultas que van a implementar la optimización propuesta como guía de plan.

La lista contiene la información siguiente:
- Id. de consulta
- Texto de consulta: Instrucción Transact-SQL que puede ampliarse seleccionando el botón ...
- Estado: muestra el estado de experimentación actual de la consulta.
- Métrica de línea de base: métrica seleccionada (Duración o Tiempo de CPU) en milisegundos para la consulta tal como se ha ejecutado en el Paso 2: subpaso 3, que representa la consulta con regresión después de la actualización de compatibilidad de base de datos.
- Métrica observada: métrica seleccionada (Duración o Tiempo de CPU) en milisegundos para la consulta después de la experimentación, para una optimización propuesta lo suficientemente buena.
- % de cambio: porcentaje de cambio para la métrica seleccionada entre el estado antes y después de la experimentación, que representa la cantidad de mejora medida para la consulta con la optimización propuesta.
- Opción de consulta: vínculo a la sugerencia propuesta que mejora la métrica de ejecución de consulta.
- Puede implementar: Verdadero o Falso en función de si la optimización de consultas propuesta se puede implementar como guía de plan.
Comprobación muestra el estado de implementación de consultas previamente seleccionadas de esta sesión. La lista de esta página difiere de la página anterior al cambiar la columna Puede implementar por Puede revertir. Esta columna puede ser Verdadero o Falso en función de si se puede revertir la optimización de consultas implementada y se puede quitar su guía de plan.

Si más adelante es necesario revertir en una optimización propuesta, seleccione la consulta correspondiente y, después, Revertir. La guía de plan de consulta se quita y la lista se actualiza para quitar la consulta revertida. Tenga en cuenta que en la siguiente imagen se ha quitado la consulta 8.

Nota:

La eliminación de una sesión cerrada no elimina guías de plan implementadas previamente. Si elimina una sesión con guías de plan implementadas, no puede usar QTA para revertir. En su lugar, busque guías de plan mediante la tabla del sistema sys.plan_guides y elimine manualmente mediante sp_control_plan_guide.