Rendimiento inteligente
SQL Server y Azure SQL incluyen capacidades de automatización que ayudan a proporcionar un rendimiento estable de la aplicación. Estas capacidades de automatización se denominan colectivamente rendimiento inteligente.
El rendimiento inteligente de Azure SQL incluye el procesamiento de consultas inteligentes, la corrección automática del plan y el ajuste automático.
Procesamiento de consultas inteligentes
El procesamiento de consultas inteligentes (IQP) es un conjunto de nuevas funciones integradas en el procesador de consultas que se habilitan por medio del nivel de compatibilidad de base de datos más reciente. Las aplicaciones pueden aumentar su rendimiento usando el nivel de compatibilidad de la base de datos más reciente. No se requieren cambios de código. Un ejemplo de IQP es la compilación diferida de variables de tabla para facilitar que las consultas en las que se usan variables de tabla se ejecuten más rápido.
Azure SQL Database y Azure SQL Managed Instance admiten el mismo nivel de compatibilidad de la base de datos necesario (150) para usar IQP que SQL Server 2019 y versiones posteriores.
Corrección automática del plan
Las regresiones del plan de consulta son uno de los problemas de rendimiento más difíciles de resolver en SQL Server. Una regresión de un plan de consulta se produce cuando se vuelve a compilar la misma consulta y un nuevo plan da como resultado un peor rendimiento.
En SQL Server 2017 y Azure SQL Database se incluyó el concepto de corrección automática del plan por medio del análisis de los datos del Almacén de consultas. Cuando el Almacén de consultas se habilita con una base de datos en SQL Server 2017 (o posterior) y en Azure SQL Database, el motor de SQL Server buscará regresiones del plan de consulta y proporcionará recomendaciones. Estas recomendaciones se pueden ver en la sys.dm_db_tuning_recommendations vista de administración dinámica (DMV). Estas recomendaciones incluyen instrucciones de T-SQL para forzar manualmente un plan de consulta cuando el rendimiento estaba en buen estado.
Si confía en estas recomendaciones, puede habilitar SQL Server para forzar los planes de forma automática cuando se encuentren regresiones. Habilite la corrección automática del plan mediante ALTER DATABASE y el argumento AUTOMATIC_TUNING.
En Azure SQL Database, la corrección automática del plan también se puede habilitar a través de las opciones de ajuste automático de Azure Portal o las API REST. Las recomendaciones de corrección automática del plan siempre están habilitadas en cualquier base de datos en la que esté habilitado el Almacén de consultas (que es el valor predeterminado en Azure SQL Database y Azure SQL Managed Instance). En cuanto a las bases de datos nuevas, la corrección automática del plan (FORCE_PLAN) está habilitada de forma predeterminada en Azure SQL Database.
Ajuste automático de Azure SQL Database
La corrección automática del plan es un ejemplo de ajuste automático en Azure SQL y SQL Server, pero un aspecto único del ajuste automático de Azure SQL Database es la indexación automática.
Nota:
La indexación automática no está disponible actualmente en SQL Managed Instance.
La nube proporciona un método para que Microsoft ofrezca servicios adicionales en forma de recomendaciones de rendimiento y automatización fuera de las recomendaciones del plan. Esta funcionalidad se conoce como ajuste automático en Azure SQL Database. Estos servicios se ejecutan como programas en segundo plano que analizan los datos de rendimiento de una instancia de Azure SQL Database, y están incluidos en el precio de cualquier suscripción de base de datos.
El escenario principal de ajuste automático está diseñado para solucionar índices. El ajuste automático analizará los datos de la telemetría de una base de datos (incluidos el Almacén de consultas y las DMV) para recomendar la creación de índices que puedan mejorar el rendimiento de la aplicación. Además, el ajuste automático se puede habilitar para crear automáticamente índices que mejoren el rendimiento de las consultas. El ajuste automático también supervisa los cambios de índice y recomienda eliminar (o elimina automáticamente) los índices que no mejoren el rendimiento de las consultas.
El ajuste automático de Azure SQL Database adopta un enfoque conservador para recomendar índices. Esto significa que es posible que las recomendaciones que pueden aparecer en una DMV como sys.dm_db_missing_index_details o un plan de consulta no se muestren inmediatamente como recomendaciones de ajuste automático. Los servicios de ajuste automático supervisan las consultas en el tiempo y usan algoritmos de aprendizaje automático para realizar recomendaciones que afecten realmente al rendimiento de las consultas.
Conviene mencionar que el ajuste automático de recomendaciones de índices no tiene en cuenta la sobrecarga que un índice puede provocar en distintas operaciones, como inserciones, actualizaciones o eliminaciones. Normalmente, los nuevos índices no agrupados que crea la característica de índices automáticos tienen un gran impacto positivo en el rendimiento.
Las consultas parametrizadas son otro escenario de ajuste automático. Las consultas con valores sin parámetros pueden llevar a una sobrecarga del rendimiento porque el plan de ejecución se vuelve a compilar cada vez que los valores sin parámetros son diferentes. En muchos casos, las mismas consultas con distintos valores de parámetro generan los mismos planes de ejecución. Estos planes, sin embargo, se agregan aún por separado a la caché de planes. El proceso de volver a compilar planes de ejecución usa recursos de base de datos, aumenta el tiempo de duración de la consulta y desborda la caché de planes. Estos eventos, a su vez, hacen que los planes se expulsen de la caché.
Puede usar la optimización de planes de distinción de parámetros (PSP) para abordar este escenario. La optimización de PSP habilita automáticamente varios planes activos almacenados en caché para una sola instrucción parametrizada. Los planes de ejecución almacenados en caché admiten diferentes tamaños de datos en función de los valores de parámetros de tiempo de ejecución proporcionados por el cliente.
Ejemplo de índices con ajuste automático en Azure SQL Database
El siguiente es un ejemplo de Azure Portal en el que se recomiendan índices para una base de datos en función del análisis de la carga de trabajo en el tiempo. Aún no hemos enviado suficiente actividad al espacio aislado de Azure SQL Database para generar recomendaciones como esta. Las recomendaciones CREATE INDEX se generan a lo largo del tiempo a medida que se captura la carga de trabajo y no en el breve período de tiempo de este ejercicio.
En Información general de rendimiento en Azure Portal se puede ver información de rendimiento de las cinco consultas que más recursos consumen según el Almacén de consultas. También se indica una recomendación.
En Azure Portal también se ofrece Información de rendimiento de consultas, una herramienta de informes visuales basados en el Almacén de consultas. En este ejemplo, Información de rendimiento de consultas muestra la consulta concreta que consume la mayoría de los recursos y sugiere cómo mejorar el rendimiento de las consultas.
En Azure Portal también se proporciona una forma directa de ver cualquier recomendación de rendimiento.
Esta vista muestra recomendaciones específicas y el historial de las acciones de ajuste automático. En el caso de un índice, se muestran los detalles del índice y de la tabla. La opción Automatizar habilita el ajuste automático.
Las opciones de ajuste automático se pueden establecer en el nivel de base de datos o de servidor de base de datos. Si hubiera habilitado el ajuste automático en este escenario, el índice se crearía de forma automática.
También puede ver las opciones de ajuste automático a través de la DMV sys.database_automatic_tuning_options.
Nota:
Las recomendaciones y la automatización de índices y planes de distinción de parámetros no están disponibles para Azure SQL Managed Instance ni SQL Server. La corrección automática del plan sí está disponible.
Si selecciona el índice recomendado, obtendrá más detalles sobre ese índice concreto.
Verá detalles sobre el índice, la tabla y el espacio necesarios. Tiene la opción de aplicar el índice recomendado o ver un script de T-SQL que aplica dicho índice.
Observe que es un índice no agrupado que se aplica como un índice en línea. Cuando se ha aplicado un índice en función de una recomendación, ya sea manualmente o a través del ajuste automático, el motor de recomendaciones también supervisa el rendimiento de las consultas durante un período de elemento con el índice aplicado. Si el rendimiento de las consultas se degrada en comparación con antes de aplicar el índice, este se puede quitar.