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

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores

Al migrar desde una versión anterior de SQL Server a SQL Server 2014 (12.x) o versiones posteriores, y actualizar el nivel de compatibilidad de la base de datos a la versión más reciente disponible, es posible que una carga de trabajo se exponga 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.

En SQL Server 2014 (12.x) y versiones posteriores, todos los cambios del optimizador de consultas se incluyen en el nivel de compatibilidad de la base de datos más reciente, por lo que los planes de ejecución no cambian directamente en el momento de la actualización, sino cuando un usuario cambia la COMPATIBILITY_LEVEL opción de base de datos a la más reciente disponible. Para obtener más información sobre los cambios del optimizador de consultas introducidos en SQL Server 2014 (12.x), vea Estimación de cardinalidad (SQL Server) . 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 funcionalidad de acceso proporcionada por el nivel de compatibilidad de la base de datos, en combinación con el Almacén de consultas proporciona un gran nivel de control sobre el rendimiento de las consultas en el proceso de actualización si la actualización sigue el flujo de trabajo recomendado que se muestra en el diagrama siguiente. Para obtener más información sobre el flujo de trabajo recomendado para actualizar el nivel de compatibilidad, consulte Cambio del nivel de compatibilidad de la base de datos y uso del Almacén de consultas.

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

A partir de SQL Server Management Studio v18, la característica Asistente para la optimización de consultas (QTA) guía 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 sql Server más reciente 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 realiza un seguimiento de las regresiones encontradas en la vista Consultas Regresadas del Query Store y itera a través de las posibles permutaciones de las variaciones aplicables del modelo del optimizador, de modo que se pueda producir un plan mejorado.

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 de partida de QTA supone que una base de datos de una versión anterior de SQL Server se mueve (a través de Adjuntar una base de datos o instrucciones RESTORE) a una versión más reciente del motor de base de datos de SQL Server y que el nivel de compatibilidad de la base de datos anterior a la actualización no cambia inmediatamente. QTA guía por 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.

En el diagrama siguiente se muestra 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 anteriormente. En lugar de elegir entre el plan de ejecución actualmente ineficaz y el último plan de ejecución correcto conocido, QTA presenta opciones de optimización específicas para las consultas con regresión seleccionadas, para crear un nuevo estado mejorado con planes de ejecución optimizados.

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 se enfoca en patrones conocidos y posibles de regresiones de consulta debido a cambios en las versiones de estimación de cardinalidad (SQL Server). Por ejemplo, al actualizar una base de datos de SQL Server 2012 (11.x) y el nivel de compatibilidad de base de datos 110, a SQL Server 2017 (14.x) y al nivel de compatibilidad de base de datos 140, algunas consultas podrían retroceder porque se diseñaron específicamente para trabajar con la versión de 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 únicamente un cambio específico en la versión más reciente está introduciendo una regresión, entonces es posible indicar a la consulta que utilice solo la parte relevante de la versión anterior de CE que funcionaba mejor para esa consulta específica, mientras mantiene todas las demás 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 CE buscados por QTA son:

Independencia frente a correlación: si la suposición de independencia proporciona mejores estimaciones para la consulta específica, la sugerencia USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') de consulta hace que SQL Server genere un plan de ejecución mediante la selectividad mínima al calcular AND predicados para que los filtros tengan en cuenta la correlación. Para obtener más información, vea USE HINT y Versiones de la estimación de cardinalidad.
Contención simple frente a contención base: si una contención de combinación diferente proporciona mejores estimaciones para la consulta específica, la sugerencia USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') de consulta hace que SQL Server genere un plan de ejecución mediante la suposición de contención simple en lugar de la suposición 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).

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 determinados comportamientos que podrían abordarse 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 impedir que la carga de trabajo se beneficie de las mejoras introducidas 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). Esto se usa para proponer la configuración recomendada del Almacén de consultas para permitir provisionalmente recopilar toda la línea base. 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 en la base de datos de destino y 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 ajuste propuesto del Umbral de consulta obsoleta es dos veces el valor esperado de la duración 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 base y la carga de trabajo posterior a la actualización de la base de datos.
  Cuando haya terminado, seleccione Siguiente.
  
  Importante
  
  El tamaño máximo propuesto es un valor arbitrario que puede ser adecuado para una carga de trabajo con poco tiempo. Sin embargo, es posible que no sea suficiente contener información sobre las cargas de trabajo de actualización de línea base y posteriores a la base de datos para cargas de trabajo intensivas, es decir, cuando se pueden generar muchos planes diferentes. Si prevé que este será el caso, escriba un valor mayor 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 que implementó guías de planes, no puede usar QTA para restaurar. 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, compruebe la opción Listo con la ejecución de la carga de trabajo y seleccione Siguiente.
  
  Nota:
  
  La ventana de QTA se puede cerrar mientras la carga de trabajo se ejecuta. Volver a la sesión que permanece en estado activo en un momento posterior se reanuda desde el mismo paso donde se dejó.
2. Actualizar base de datos solicita 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. La recopilación de datos observada 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 usa 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, compruebe la opción Listo con la ejecución de la carga de trabajo 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.

No se pueden seleccionar consultas con ajustable establecido en False para la experimentación.

Importante

Un aviso indica que una vez que QTA pase a la fase de experimentación, no es posible volver a la página de 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.
- % Cambio: especifica el cambio por porcentaje de la métrica seleccionada entre el estado anterior 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 en una fecha posterior es necesario revertir una optimización propuesta, seleccione la consulta pertinente y seleccione 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 que implementó guías de planes, no puede usar QTA para restaurar. En su lugar, busque guías de plan mediante la tabla del sistema sys.plan_guides y elimine manualmente mediante sp_control_plan_guide.

Permisos

Requiere la pertenencia al rol db_owner.

Comentarios

¿Le ha resultado útil esta página?

Last updated on 2025-10-10