Compartir a través de

Ajuste automático

Se aplica a: SQL Server 2017 (14.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed InstanceSQL database en Microsoft Fabric

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

El ajuste automático, introducido en SQL Server 2017 (14.x), le notifica cada vez que se detecta un posible problema de rendimiento y le permite aplicar acciones correctivas o permite que el motor de base de datos corrija automáticamente los problemas de rendimiento. El ajuste automático de SQL Server identifica y corrige los problemas de rendimiento causados por regresiones de elección del plan de ejecución de consultas. El ajuste automático en Azure SQL Database y SQL Database en Microsoft Fabric también crea índices necesarios y quita índices sin usar. Para obtener más información sobre los planes de ejecución de consultas, consulte 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 tiene un mecanismo de inteligencia integrado que puede optimizar 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:

  • La corrección automática del plan identifica los planes de ejecución de consultas problemáticos, como una sensibilidad de parámetros o problemas de detección de parámetros , y corrige los problemas de rendimiento relacionados con el plan de ejecución de consultas al forzar 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, Base de datos SQL en Microsoft Fabric e Instancia administrada de Azure SQL

  • La administración automática de índices identifica los índices que se deben agregar en la base de datos y los índices que se deben quitar. Se aplica a: Azure SQL Database y SQL Database en Microsoft Fabric

Note

En este artículo, las características y los comportamientos de Azure SQL Database también se aplican a SQL Database en Microsoft Fabric.

¿Por qué el ajuste automático?

Tres de las tareas principales de la administración de bases de datos clásicas supervisan la carga de trabajo, identifican las consultas críticas Transact-SQL e identifican los índices que se deben agregar 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 e índices que necesita supervisar. Sin embargo, la supervisión constante de una base de datos es una tarea difícil y tediosa, especialmente cuando se trabaja con muchas bases de datos. La administración de un gran número de bases de datos puede ser imposible de hacer de forma eficaz. En lugar de supervisar y ajustar la base de datos manualmente, es posible que considere la posibilidad de delegar algunas de las acciones de supervisión y optimización en el motor de base de datos mediante la característica de ajuste automático.

¿Cómo funciona el ajuste automático?

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

Proceso de ajuste automático.

Este proceso permite que la base de datos se adapte dinámicamente a la carga de trabajo mediante la búsqueda de los índices y planes que podrían mejorar el rendimiento de las cargas de trabajo y los índices que afectan a las cargas de trabajo. 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 reduzca el rendimiento general 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 de elección del plan de ejecución y corrige automáticamente el problema al forzar el último plan correcto conocido. Para obtener más información sobre los planes de ejecución de consultas y el optimizador de consultas, consulte la Guía de arquitectura de procesamiento de consultas.

Important

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 cargas de trabajo.

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

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

Regresión en la elección del plan de ejecución de consultas.

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

Important

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

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 detecta una regresión de elección de plan.

Corrección de la elección del plan de ejecución de consultas.

El motor de base de datos detecta automáticamente cualquier posible regresión de elección de plan, incluido el plan que se debe usar 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. Dado que el plan resultante puede no ser idéntico al último plan correcto conocido, el rendimiento del plan forzado puede variar. En raras ocasiones, la diferencia de rendimiento puede ser significativa y negativa; en este caso, la corrección automática del plan dejará 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 produjera la regresión, supervisa automáticamente el rendimiento del plan forzado. Si el plan forzado no es mejor que el plan con regresión, el nuevo plan no se aplicará y el motor de base de datos compilará un nuevo plan. Si el motor de base de datos comprueba que el plan forzado es mejor que el plan con regresión, se conservará el plan forzado. Se conservará hasta que se produzca una nueva compilación (por ejemplo, en la siguiente actualización de estadísticas o cambio de esquema). Para obtener más información sobre cómo forzar el plan y los tipos de planes que se pueden forzar, consulte Limitaciones de forzamiento de planes.

Note

Si se reinicia la instancia de SQL Server antes de verificar una acción para forzar un plan, ese plan se desactivará automáticamente. De otro modo, la imposición del plan se mantiene durante los reinicios de 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 cada vez que se detecte alguna regresión de cambios de plan. El ajuste automático está habilitado mediante el siguiente comando:

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

Una vez que habilite esta opción, el motor de base de datos forzará 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 es mayor que el número de errores del plan recomendado y comprobará que el plan forzado es mejor que el actual.

Para habilitar el ajuste automático en Azure SQL Database e Instancia administrada de Azure SQL, consulte Habilitación del ajuste automático en Azure SQL Database mediante Azure Portal.

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

Sin el ajuste automático, los usuarios deben supervisar periódicamente el sistema y buscar las consultas con regresión. Si algún plan se ha revertido, el usuario debe encontrar un plan correcto anterior y forzarlo en lugar de utilizar el actual mediante 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 estadísticos o de índice. El usuario que fuerza el último plan correcto conocido debe supervisar el rendimiento de la consulta que se ejecuta mediante el plan forzado y comprobar que el plan forzado funciona según lo previsto. Según los resultados de la supervisión y el análisis, el plan debe forzarse o el usuario debe encontrar otra manera de optimizar la consulta, como volver a escribirla. Los planes forzados manualmente no deben forzarse para siempre, ya que el motor de base de datos debe ser capaz de aplicar planes óptimos. El usuario o DBA deben finalmente desforzar el plan utilizando el procedimiento sp_query_store_unforce_plan y permitir que el motor de base de datos encuentre el plan óptimo.

Tip

Alternativamente, utilice la vista Consultas con Planes Forzados del Almacén de Consultas para localizar y desactivar planes.

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

En SQL Server 2016 (13.x), puede encontrar regresiones de opciones de plan mediante vistas del sistema de Query Store. A partir de SQL Server 2017 (14.x), el Motor de base de datos detecta y muestra posibles regresiones en la elección del plan, así como las acciones recomendadas que se deben aplicar en el 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 identificador del plan con regresión, el identificador 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 description datetime score details ...
FORCE_LAST_GOOD_PLAN Tiempo de CPU cambiado de 4 ms a 14 ms 3/17/2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN Tiempo de CPU 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 lista siguiente:

  • Tipo de la acción FORCE_LAST_GOOD_PLANrecomendada.
  • Descripción que contiene información por la que el motor de base de datos cree que este cambio de plan es una posible regresión del rendimiento.
  • Fecha y hora cuando se detecta la posible regresión.
  • Puntuación de esta recomendación.
  • Detalles sobre los problemas, como el identificador del plan detectado, el identificador del plan con regresión, el identificador del plan que se debe forzar para corregir el problema, Transact-SQL script que se puede aplicar para corregir el problema, etc. Los detalles se almacenan en formato JSON.

Use la consulta siguiente 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;

Este es el conjunto de resultados.

reason score script query_id plan_id actual plan_id recomendado ganancia_estimada propenso a errores
Tiempo de CPU 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 guardarían si el plan recomendado se usara para la ejecución de consultas en lugar del plan actual. El plan recomendado debe imponerse en lugar del plan actual si la ganancia es mayor que diez (10) segundos. Si hay más errores (por ejemplo, tiempos de espera 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 se debe forzar el plan recomendado en lugar del actual.

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

Note

Los datos de la sys.dm_db_tuning_recommendations DMV 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 es fácil porque Azure SQL Database aprende sobre la carga de trabajo y garantiza que los datos siempre se indexan de forma óptima. El diseño adecuado del índice es fundamental para un rendimiento óptimo de la carga de trabajo y la administración automática de índices puede ayudarle a optimizar los índices. La administración automática de índices puede corregir problemas de rendimiento en bases de datos indizadas 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 de Transact-SQL que leen datos de las tablas.
  • Identifica índices redundantes o índices que no se usaron en un período de tiempo más largo que 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é necesita administración de índices?

Los índices aceleran algunas de las consultas que leen datos de las tablas, pero pueden ralentizar las consultas que actualizan los datos. Debe analizar cuidadosamente cuándo crear un índice y qué columnas necesita incluir en el índice. Es posible que algunos índices no sean necesarios después de algún tiempo. Por lo tanto, tendría que identificar y quitar periódicamente estos índices que no aportan ninguna ventaja. Si omite los índices sin usar, el rendimiento de las consultas que actualizan los datos se reduciría sin ninguna ventaja para las consultas que leen los datos. Los índices sin usar también afectan al rendimiento general del sistema porque las actualizaciones adicionales requieren un registro innecesario.

Encontrar el conjunto óptimo de índices que mejoran el rendimiento de las consultas que leen datos de las tablas y tienen un impacto mínimo en las actualizaciones podría requerir un análisis continuo y complejo.

Azure SQL Database usa inteligencia integrada y reglas avanzadas que analizan las consultas, identifican los índices que serían óptimos para las cargas de trabajo actuales e identifican los índices que podrían necesitar quitarse. Azure SQL Database garantiza que tiene un conjunto mínimo necesario de índices que optimizan las consultas que leen datos, con un impacto mínimo 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 encuentra que las reglas integradas mejoran el rendimiento de la base de datos, puede permitir 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 conservará si se mejoran los rendimientos de las consultas afectadas. El índice eliminado se volverá a crear automáticamente si hay algunas consultas que se ejecutan más lentamente debido a la ausencia del índice.

Consideraciones de administración automática de índices

Las acciones necesarias para crear índices necesarios en Azure SQL Database podrían 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 un período de tiempo adecuado para cualquier operación de administración de índices. La acción de optimización 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 se pueden usar para la tarea de mantenimiento. Una característica importante en la administración automática de índices es una 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 general. Si no ha traído una mejora significativa, la acción se revierte inmediatamente. De este modo, Azure SQL Database garantiza 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 o cambiar el nombre de las columnas, no están bloqueados por la presencia de índices creados automáticamente. Los índices creados automáticamente por Azure SQL Database se quitan inmediatamente cuando se quitan las columnas o la tabla relacionada.

Alternativa: administración manual de índices

Sin la administración automática de índices, un usuario o DBA necesitaría consultar manualmente la vista de sys.dm_db_missing_index_details (Transact-SQL) o usar el informe panel de rendimiento de 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 se deben quitar, los usuarios deben supervisar las estadísticas de uso operativo de los índices para buscar índices que rara vez se usan.

Azure SQL Database simplifica este proceso. Azure SQL Database analiza la carga de trabajo, identifica las consultas que se podrían ejecutar más rápido con un nuevo índice e identifica los índices sin usar o duplicados. Obtenga más información sobre la identificación de índices que se deben cambiar en Búsqueda de recomendaciones de índice en Azure Portal.

Pasos siguientes

  • Last updated on