Ajuste automático

Se aplica a: SQL Server 2017 (14.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed Instance

El ajuste automático es una característica de base de datos que proporciona información de los posibles problemas de rendimiento de las consultas, recomienda soluciones y corrige automáticamente los problemas identificados.

El ajuste automático, que se introdujo en SQL Server 2017 (14.x), avisa cada vez que se detecta un posible problema de rendimiento y permite tomar medidas correctoras, o bien deja que el Motor de base de datos arregle automáticamente los problemas de rendimiento. El ajuste automático de SQL Server identifica y corrige problemas de rendimiento debidos a regresiones por la elección del plan de ejecución de consultas. El ajuste automático de Azure SQL Database también crea los índices necesarios y quita los índices sin usar. Para obtener más información sobre los planes de ejecución de consultas, vea Planes de ejecución.

El Motor de base de datos de SQL Server supervisa las consultas que se ejecutan en la base de datos y mejora automáticamente el rendimiento de la carga de trabajo. El Motor de base de datos posee un mecanismo de inteligencia integrado que puede ajustar y mejorar automáticamente el rendimiento de las consultas mediante la adaptación dinámica de la base de datos a la carga de trabajo. Hay dos características de ajuste automático disponibles:

  • Corrección automática del plan: identifica los planes de ejecución de consultas problemáticos, como los casos de sensibilidad de parámetros o examen de parámetros, y corrige los problemas de rendimiento relacionados con el plan de ejecución de consultas forzando el último plan correcto conocido antes de que se produjera la regresión. Se aplica a: SQL Server (a partir de SQL Server 2017 (14.x)), Azure SQL Database y Azure SQL Managed Instance]

  • Administración automática de índices: identifica los índices que deben agregarse a la base de datos y los que deben quitarse. Se aplica a: Azure SQL Database

¿Por qué el ajuste automático?

Tres de las tareas principales de la administración de bases de datos clásica son la supervisión de la carga de trabajo, la identificación de las consultas Transact-SQL críticas y la identificación de los índices que deben agregarse para mejorar el rendimiento o los índices que rara vez se usan y se pueden quitar para mejorar el rendimiento. El Motor de base de datos de SQL Server proporciona información detallada sobre las consultas y los índices que es necesario supervisar. Sin embargo, supervisar constantemente una base de datos es una tarea ardua y tediosa, sobre todo cuando se trabaja con muchas bases de datos. Administrar un número enorme de bases de datos de manera eficiente puede resultar imposible. En lugar de supervisar y ajustar su base de datos manualmente, puede considerar la posibilidad de delegar algunas de las acciones de supervisión y ajuste en el Motor de base de datos con la característica de ajuste automático.

¿Cómo funciona el ajuste automático?

El ajuste automático es un proceso continuo de supervisión y análisis del rendimiento que obtiene información constante sobre las características de la carga de trabajo e identifica posibles problemas y mejoras.

Automatic tuning process.

Este proceso permite que la base de datos se adapte de forma dinámica a la carga de trabajo mediante la búsqueda de los índices y planes que pueden mejorar el rendimiento de las cargas de trabajo y los índices que les afectan negativamente. En función de lo que encuentre, el ajuste automático aplica medidas de optimización que mejoran el rendimiento de la carga de trabajo. Además, el ajuste automático supervisa continuamente el rendimiento de la base de datos después de implementar cambios para asegurarse de que mejora el rendimiento de la carga de trabajo. Cualquier acción que no mejore el rendimiento se revierte automáticamente. Este proceso de comprobación es una característica clave que garantiza que cualquier cambio realizado por el ajuste automático no disminuye el rendimiento de la carga de trabajo.

Corrección automática del plan

La corrección automática del plan es una característica de ajuste automático que identifica la regresión por la elección del plan de ejecución y corrige automáticamente el problema forzando el último plan correcto conocido. Para obtener más información sobre los planes de ejecución de consultas y el optimizador de consultas, vea la Guía de arquitectura de procesamiento de consultas.

Importante

La corrección automática del plan depende de que el Almacén de consultas esté habilitado en la base de datos para el seguimiento de la carga de trabajo.

¿Qué es la regresión por la elección del plan de ejecución?

El Motor de base de datos de SQL Server puede usar diferentes planes de ejecución para las consultas Transact-SQL. Los planes de consulta dependen de las estadísticas, los índices y otros factores. El plan óptimo que debe usarse para ejecutar una consulta Transact-SQL puede cambiar con el tiempo en función de cómo varíen estos factores. En algunos casos, es posible que el nuevo plan no sea mejor que el anterior y que pueda provocar una regresión del rendimiento, como un problema relacionado con la sensibilidad de un parámetro o el examen de parámetros.

Query execution plan choice regression.

Cada vez que observe que se ha producido una regresión por la elección del plan, debe buscar un plan correcto anterior y forzar su uso en lugar del actual. Esto se puede hacer con el procedimiento sp_query_store_force_plan. El Motor de base de datos de SQL Server 2017 (14.x) proporciona información sobre los planes con regresión y las acciones correctoras recomendadas. Además, el Motor de base de datos le permite automatizar completamente este proceso y dejar que el motor corrija cualquier problema relacionado con el cambio del plan.

Importante

La corrección automática del plan debe usarse en el ámbito de una actualización de nivel de compatibilidad de la base de datos, una vez capturada una línea base, para mitigar automáticamente los riesgos de la actualización de la carga de trabajo. Para obtener más información sobre este tema, consulte Mantenimiento de la estabilidad del rendimiento durante la actualización a una versión más reciente de SQL Server.

Corrección automática de la elección del plan

El Motor de base de datos puede cambiar automáticamente al último plan correcto conocido cada vez que se detecte una regresión por la elección del plan.

Query execution plan choice correction.

El Motor de base datos detecta automáticamente cualquier posible regresión por la elección del plan, incluido el plan que debe usarse en lugar del plan incorrecto. El plan de ejecución resultante forzado por la corrección automática del plan será el mismo o similar al último plan correcto conocido. Puesto que es posible que el plan resultante no sea idéntico al último plan correcto conocido, el rendimiento del plan forzado puede variar. En raras ocasiones, la diferencia de rendimiento puede ser importante y negativa. En este caso, la corrección automática del plan deja de intentar forzar automáticamente el plan de reemplazo.

Cuando el Motor de base de datos aplica el último plan correcto conocido antes de que se produzca la regresión, supervisa automáticamente el rendimiento del plan forzado. Si el plan forzado no es mejor que el plan con regresión, deja de forzarse el nuevo plan y el Motor de base de datos compila un nuevo plan. Si el Motor de base de datos comprueba que el plan forzado es mejor que el plan con regresión, se mantiene el plan forzado. Se mantiene hasta que tenga lugar una recompilación (por ejemplo, en la siguiente actualización de estadísticas o cambio de esquema). Para obtener más información sobre el forzado de planes y los tipos de planes que se pueden forzar, consulte Limitaciones de forzar un plan.

Nota:

Si se reinicia la instancia de SQL Server antes de comprobar una acción de forzado de un plan, ese plan deja de forzarse automáticamente. De lo contrario, se mantiene el forzado del plan al reiniciar SQL Server.

Habilitación de la corrección automática de la elección del plan

Puede habilitar el ajuste automático por base de datos y especificar que se debe forzar el último plan correcto siempre que se detecte alguna regresión por cambio de plan. El ajuste automático se habilita con el siguiente comando:

ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Una vez activada esta opción, el Motor de base de datos fuerza automáticamente cualquier recomendación en la que la ganancia de CPU estimada sea superior a 10 segundos o el número de errores del nuevo plan sea mayor que el número de errores del plan recomendado, y comprueba que el plan forzado sea mejor que el actual.

Para habilitar el ajuste automático en Azure SQL Database y Azure SQL Managed Instance, consulte Habilitación del ajuste automático en Azure SQL Database mediante Azure Portal.

Alternativa: corrección manual de elección del plan

Sin el ajuste automático, los usuarios deben supervisar el sistema de forma periódica y buscar las consultas con regresión. Si algún plan causa regresión, el usuario debe buscar un plan correcto anterior y forzarlo en lugar del actual con el procedimiento sp_query_store_force_plan. El procedimiento recomendado sería forzar el último plan correcto conocido, porque los planes más antiguos podrían no ser válidos debido a cambios de estadísticas o de índice. El usuario que fuerza el último plan correcto conocido debe supervisar el rendimiento de la consulta que se ejecuta con el plan forzado y comprobar que este funciona según lo previsto. En función de los resultados de la supervisión y el análisis, el plan debe forzarse o el usuario debe buscar otra manera de optimizar la consulta (por ejemplo, reescribiéndola). Los planes forzados manualmente no deben forzarse para siempre, ya que el Motor de base de datos debe poder aplicar planes óptimos. El usuario o el administrador de la base de datos deben dejar de forzar el plan con el procedimiento sp_query_store_unforce_plan y permitir que el Motor de base de datos busque el plan óptimo.

Sugerencia

Como alternativa, use la vista Consultas con planes forzados del Almacén de consultas para localizar y dejar de forzar planes.

SQL Server proporciona todas las vistas y los procedimientos necesarios para supervisar el rendimiento y corregir problemas en el Almacén de consultas.

En SQL Server 2016 (13.x), puede encontrar regresiones por la elección del plan usando las vistas del sistema del Almacén de consultas. Desde SQL Server 2017 (14.x), el Motor de base de datos detecta y muestra posibles regresiones por la elección del plan y las acciones recomendadas que deben aplicarse en la DMV sys.dm_db_tuning_recommendations (Transact-SQL). La DMV muestra información sobre el problema, la importancia del problema y detalles como la consulta identificada, el id. del plan con regresión, el id. del plan que se usó como línea base para la comparación y la instrucción Transact-SQL que se puede ejecutar para corregir el problema.

type descripción datetime score detalles ...
FORCE_LAST_GOOD_PLAN El tiempo de CPU ha cambiado de 4 ms a 14 ms 17/3/2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN El tiempo de CPU ha cambiado de 37 ms a 84 ms 3/16/2017 26 queryId recommendedPlanId regressedPlanId T-SQL

Algunas columnas de esta vista se describen en la siguiente lista:

  • Tipo de la acción recomendada FORCE_LAST_GOOD_PLAN.
  • Descripción que contiene información sobre el motivo por el que el Motor de base de datos cree que este cambio de plan puede producir una regresión del rendimiento.
  • Fecha y hora de detección de la posible regresión.
  • Puntuación de esta recomendación.
  • Detalles sobre los problemas, como el id. del plan detectado, el id. del plan con regresión, el id. del plan que debe forzarse para corregir el problema, el script Transact-SQL que puede aplicarse para corregir el problema, etc. Los detalles se almacenan con el formato JSON.

Utilice la siguiente consulta para obtener un script que corrija el problema e información adicional sobre la ganancia estimada:

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

El conjunto de resultados es el siguiente:

reason score script query_id current plan_id recommended plan_id estimated_gain error_prone
El tiempo de CPU ha cambiado de 3 ms a 46 ms 36 EXEC sp_query_store_force_plan 12, 17; 12 28 17 11,59 0

La columna estimated_gain representa el número estimado de segundos que se ahorrarían si se utilizara el plan recomendado para la ejecución de consultas en lugar del plan actual. El plan recomendado debe forzarse en lugar del plan actual si la ganancia es mayor que 10 segundos. Si hay más errores (por ejemplo, tiempo de espera agotado o ejecuciones anuladas) en el plan actual que en el plan recomendado, la columna error_prone se establecería en el valor YES. Un plan propenso a errores es otro motivo por el que debe forzarse el plan recomendado en lugar del actual.

Aunque el Motor de base de datos proporciona toda la información necesaria para identificar regresiones por la elección del plan, la supervisión y corrección continuas de problemas de rendimiento pueden convertirse en un proceso tedioso. El ajuste automático facilita mucho este proceso.

Nota:

Los datos de la DMV sys.dm_db_tuning_recommendations no se conservan después de reiniciar el Motor de base de datos. Use la columna sqlserver_start_time en sys.dm_os_sys_info para encontrar la hora del último inicio del motor de base de datos.

Administración automática de índices

En Azure SQL Database, la administración de índices resulta sencilla porque Azure SQL Database obtiene información acerca de la carga de trabajo y se asegura de que los datos estén siempre indexados de forma óptima. El diseño adecuado de índices es fundamental para un rendimiento óptimo de la carga de trabajo y la administración automática de índices puede ayudar a optimizar los índices. La administración automática de índices puede corregir problemas de rendimiento en bases de datos indexadas incorrectamente o mantener y mejorar los índices en el esquema de base de datos existente. El ajuste automático en Azure SQL Database realiza las siguientes acciones:

  • Identifica índices que podrían mejorar el rendimiento de las consultas Transact-SQL que leen datos de las tablas.
  • Identifica índices redundantes o índices que no se han usado durante un período de tiempo más largo y se podrían quitar. La eliminación de índices innecesarios mejora el rendimiento de las consultas que actualizan los datos de las tablas.

¿Por qué se necesita la administración de índices?

Los índices aceleran algunas de las consultas que leen datos de las tablas. Sin embargo, pueden ralentizar las consultas que actualizan datos. Debe analizar cuidadosamente cuándo se debe crear un índice y qué columnas debe incluir en él. Es posible que algunos índices no sean necesarios pasado un tiempo. Por tanto, debería identificar y quitar periódicamente los índices que no aporten ventajas. Si pasa por alto los índices no usados, el rendimiento de las consultas que actualizan datos podría reducirse sin ninguna ventaja para las consultas que leen datos. Además, los índices no usados afectan al rendimiento general del sistema porque las actualizaciones adicionales necesitan tareas de registro innecesarias.

Para encontrar el conjunto óptimo de índices que mejore el rendimiento de las consultas que leen datos de las tablas y tenga un impacto mínimo en las actualizaciones, es posible que se requiera un análisis complejo y continuado.

Azure SQL Database usa inteligencia y reglas avanzadas integradas que analizan las consultas e identifican los índices que serían óptimos para las cargas de trabajo actuales y los que quizá deberían quitarse. Azure SQL Database se asegura de que tenga un conjunto mínimo necesario de índices que optimicen las consultas que leen datos, con un impacto minimizado en las demás consultas.

Administración automática de índices

Además de la detección, Azure SQL Database puede aplicar automáticamente recomendaciones identificadas. Si descubre que las reglas integradas mejoran el rendimiento de la base de datos, podría dejar que Azure SQL Database administre automáticamente los índices.

Cuando Azure SQL Database aplica una recomendación CREATE INDEX o DROP INDEX, supervisa automáticamente el rendimiento de las consultas afectadas por el índice. El nuevo índice solo se mantiene si mejora el rendimiento de las consultas afectadas. El índice quitado se vuelve a crear automáticamente si hay algunas consultas que se ejecutan más lentamente debido a la ausencia del índice.

Consideraciones sobre la administración automática de índices

Las acciones que se requieren para crear los índices necesarios en Azure SQL Database pueden consumir recursos y afectar temporalmente al rendimiento de la carga de trabajo. Para minimizar el impacto de la creación de índices en el rendimiento de la carga de trabajo, Azure SQL Database busca el período de tiempo adecuado para cualquier operación de administración de índices. La acción de ajuste se pospone si la base de datos necesita recursos para ejecutar la carga de trabajo, y se reinicia cuando la base de datos tiene suficientes recursos no utilizados que puedan usarse para la tarea de mantenimiento. Una característica importante de la administración automática de índices es la comprobación de las acciones. Cuando Azure SQL Database crea o quita un índice, un proceso de supervisión analiza el rendimiento de la carga de trabajo para comprobar que la acción ha mejorado el rendimiento global. Si no ha aportado una mejora considerable, la acción se revierte de inmediato. De esta manera, Azure SQL Database se asegura de que las acciones de ajuste automático no afecten negativamente al rendimiento de la carga de trabajo. Los índices creados por el ajuste automático son transparentes para la operación de mantenimiento en el esquema subyacente. Los cambios de esquema, como quitar columnas o cambiarlas de nombre, no quedan bloqueados por la presencia de índices creados de forma automática. Los índices que Azure SQL Database crea automáticamente se quitan de inmediato cuando se quitan la tabla o las columnas relacionadas.

Alternativa: administración manual de índices

Sin la administración automática de índices, un usuario o administrador de base de datos tendría que consultar manualmente la vista sys.dm_db_missing_index_details (Transact-SQL) o usar el informe Panel de rendimiento en Management Studio para buscar índices que podrían mejorar el rendimiento, crear índices con los detalles proporcionados en esta vista y supervisar manualmente el rendimiento de la consulta. Para buscar los índices que deberían quitarse, los usuarios deben supervisar las estadísticas de uso operativo de los índices para detectar los que rara vez se utilizan.

Azure SQL Database simplifica este proceso. Azure SQL Database analiza la carga de trabajo, identifica las consultas que podrían ejecutarse más rápido con un nuevo índice e identifica los índices no utilizados o duplicados. Para más información sobre la identificación de los índices que deben cambiarse, consulte cómo buscar recomendaciones de índices en Azure Portal.

Pasos siguientes