Descripción del ajuste automático

El ajuste automático es una característica de supervisión y análisis que aprende continuamente sobre la carga de trabajo e identifica posibles problemas y mejoras.

Las recomendaciones de ajuste automático se basan en los datos recopilados de Almacén de consultas. Los planes de ejecución evolucionan con el tiempo debido a cambios en el esquema, modificaciones de índices o cambios en los datos que provocan actualizaciones de las estadísticas. Esta evolución puede hacer que las consultas tengan un rendimiento bajo, ya que el plan de ejecución ya no cumple las demandas de la consulta dada.

Además, el ajuste automático permite la recopilación y aplicación del aprendizaje automático en las métricas de rendimiento para proporcionar mejoras sugeridas o incluso permitir la corrección automática.

Ya sea en el entorno local o en la nube, el ajuste automático le permite identificar problemas causados por la regresión del plan de ejecución de consultas. Además, en Azure SQL Database puede mejorar el rendimiento de las consultas al ajustar el índice. El ajuste automático de Azure SQL Database puede identificar los índices que deben agregarse o incluso quitarse de la base de datos para mejorar el rendimiento de las consultas.

Corrección automática del plan

Con la ayuda de los datos de Almacén de consultas, el motor de base de datos puede determinar cuándo se han devuelto los planes de ejecución de consultas en el rendimiento. Aunque puede identificar manualmente un plan con regresión mediante la interfaz de usuario, el almacén de consultas también proporciona una opción de notificación automática.

En el ejemplo anterior, puede ver una marca de verificación en el id. de plan 1, lo cual significa que el plan se ha forzado. Una vez habilitada la característica, el motor de base de datos forzará automáticamente cualquier plan de ejecución de consultas recomendado, cuando:

• El plan anterior tenía una tasa de errores más alta que el plan recomendado
• La ganancia estimada de CPU era superior a 10 segundos
• El plan de fuerza ha funcionado mejor que el anterior

El plan volverá al último plan correcto conocido después de 15 ejecuciones de la consulta.

Cuando la fuerza del plan se produce automáticamente, el motor de base de datos aplica el último plan correcto conocido y mantiene el ojo en el rendimiento de la ejecución de consultas. Si el plan forzado no funciona mejor que el anterior, no se aplica y se compila un nuevo plan. Sin embargo, si el plan forzado sigue superando el plan incorrecto anterior, permanece en su lugar hasta que se produzca una recompilación.

Puede habilitar la corrección automática del plan a través de una consulta T-SQL. El almacén de consultas debe estar habilitado y en modo de lectura-escritura para que el comando se ejecute correctamente. Si no se cumplen cualquiera de esos dos criterios, se produce un error en la instrucción ALTER.

ALTER DATABASE [WideWorldImporters] SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Puede examinar las recomendaciones de ajuste automático mediante una vista de administración dinámica (DMV), sys.dm_db_tuning_recommendations, que está disponible en SQL Server 2017 o versiones posteriores y también está disponible en las soluciones de Azure SQL Database. Esta DMV proporciona información como las razones por las que se proporcionó la recomendación, el tipo de recomendación y el estado de la recomendación. Para confirmar que el ajuste automático está habilitado para una base de datos, compruebe la vista sys.database_automatic_tuning_options.

Administración automática de índices

Azure SQL Database tiene la capacidad de realizar el ajuste automático de índices. Con el tiempo, aprende de las cargas de trabajo existentes y proporciona recomendaciones para agregar o quitar índices para mejorar el rendimiento. De forma similar a forzar planes de consulta mejorados, la base de datos se puede configurar para crear o quitar índices automáticamente en función de su rendimiento, como se muestra en la imagen siguiente.

Cuando está habilitada, la página Recomendaciones de rendimiento identifica índices que se pueden crear o quitar en función del rendimiento de las consultas. Recuerde que esta característica no está disponible para bases de datos locales y solo está disponible para Azure SQL Database.

Como alternativa, use la consulta siguiente para ver las características de ajuste automático habilitadas en la base de datos:

SELECT name,
    desired_state_desc,
    actual_state_desc,
    reason_desc
FROM sys.database_automatic_tuning_options

La creación de nuevos índices puede consumir recursos, y el momento elegido para la creación de índices es fundamental de cara a garantizar que no hay ningún efecto negativo en las cargas de trabajo.

Azure SQL Database supervisa los recursos necesarios para implementar nuevos índices para evitar la degradación del rendimiento. La acción de ajuste se pospone hasta que los recursos disponibles estén disponibles, por ejemplo, si los recursos son necesarios para las cargas de trabajo existentes y no están disponibles para crear un índice.

La supervisión garantiza que las acciones realizadas no dañarán el rendimiento. Si se quita un índice y el rendimiento de las consultas se degrada notablemente, el índice eliminado recientemente se vuelve a crear automáticamente.