Información sobre el rendimiento cuando la necesite y acelere el rendimiento sin necesidad de modificar el código
SQL Server 2022 proporciona funcionalidades integradas para reducir el tiempo de optimización de consultas, incluidas las características Almacén de consultas y Procesamiento de consultas inteligentes (IQP) de última generación, para ayudarle a obtener un rendimiento más rápido y coherente sin cambios en el código.
Desafíos para la optimización de consultas
Los desarrolladores y expertos en SQL coinciden en que, aunque algunas consultas funcionan, pueden surgir algunas situaciones de rendimiento para las consultas que requieren ejercicios de solución de problemas de rendimiento y optimización de consultas. La optimización del rendimiento de las consultas puede ser un proceso costoso y a menudo largo.
Soluciones para la optimización de consultas mediante el Almacén de consultas
El Almacén de consultas es un conjunto integrado de estadísticas de rendimiento de consultas almacenadas en una base de datos de usuario. El Almacén de consultas captura automáticamente un historial de consultas, planes y estadísticas en tiempo de ejecución, y conserva esta información para su revisión. Además, separa los datos por ventanas de tiempo, lo que permite ver patrones de uso de la base de datos y comprender cuándo se produjeron cambios del plan de consultas en el servidor. Para más información, consulte Supervisión del rendimiento mediante el almacén de consultas.
SQL Server 2022 incluye mejoras importantes en el Almacén de consultas para reducir la cantidad de tiempo para la optimización de consultas.
Almacén de consultas activado de forma predeterminada
Antes de SQL Server 2022, el Almacén de consultas debe habilitarse mediante la instrucción T-SQL ALTER DATABASE. Para SQL Server 2022, cualquier nueva base de datos creada tendrá el Almacén de consultas habilitado de forma predeterminada. Las bases de datos restauradas a partir de versiones anteriores de SQL Server conservarán la configuración del Almacén de consultas capturada cuando se realizó una copia de seguridad de la base de datos. El Almacén de consultas ha experimentado varias mejoras desde su inicio en SQL Server 2016 que permiten a los usuarios habilitar el Almacén de consultas sin afectar significativamente al rendimiento de la aplicación. Además, en el Almacén de consultas se pueden realizar varias configuraciones nuevas para permitir a los usuarios controlar más fácilmente cómo se captura y limpia la información de rendimiento de las consultas. Los usuarios pueden deshabilitar el Almacén de consultas en cualquier momento mediante la instrucción T-SQL ALTER DATABASE.
Sugerencias del Almacén de consultas
La característica Sugerencias del almacén de consultas proporciona un método fácil de usar para dar forma a los planes de consulta sin necesidad de cambiar el código de la aplicación. Puede tomar cualquier consulta almacenada en el Almacén de consultas y usar procedimientos almacenados del sistema para aplicar una sugerencia de consulta. La sugerencia de consulta afecta al plan de consulta con la intención de mejorar el rendimiento de las consultas, sin cambiar el código de la aplicación. Por ejemplo, podría aplicar una sugerencia de almacén de consultas para requerir que una consulta use un valor específico MAXDOP sin cambiar el texto de la consulta.
Las sugerencias del Almacén de consultas no están diseñadas para usarse como un paso normal para optimizar el rendimiento de las consultas, pero pueden ser una herramienta útil para la optimización de consultas, especialmente si no puede cambiar el texto de la consulta en una aplicación. Además, algunas nuevas características del Procesamiento de consultas inteligentes usan una sugerencia del almacén de consultas. Puede ver las sugerencias del almacén de consultas persistentes en la vista de catálogo sys.query_store_query_hints. Para obtener más información, vea Sugerencias del Almacén de consultas.
Almacén de consultas para las réplicas de lectura
Aunque el Almacén de consultas es beneficioso para reducir la cantidad de tiempo necesario para optimizar las consultas o identificar fácilmente los problemas de rendimiento de las consultas, la información de rendimiento solo está disponible para las consultas ejecutadas en la réplica principal en un grupo de disponibilidad Always On. En SQL Server 2022, hay disponible una nueva opción mediante la instrucción T-SQL ALTER DATABASE para permitir que el Almacén de consultas recopile información de rendimiento para las consultas de solo lectura ejecutadas en réplicas secundarias. Toda la información de rendimiento de todas las réplicas se conserva en la réplica principal. La nueva información se captura en el Almacén de consultas para indicar qué réplica está asociada a una consulta o un plan de consulta.
Nota:
Se requiere la marca de seguimiento 12606 para habilitar el Almacén de consultas para las réplicas secundarias.
Almacén de consultas para el procesamiento de consultas inteligentes
Aunque el Almacén de consultas recopila información de rendimiento clave para las consultas, el procesador de consultas de SQL Server 2022 también usará el Almacén de consultas para conservar la información para acelerar el rendimiento de las consultas. Estas características incluyen el forzado de plan optimizado, comentarios de concesión de memoria, comentarios del modelo de estimación de cardinalidad (CE) y grado de paralelismo (DOP).
Soluciones para un rendimiento más rápido con la próxima generación de procesamiento de consultas inteligentes
El Procesamiento de consultas inteligentes (IQP) es una familia de funcionalidades integradas en el procesador de consultas del motor de base de datos, diseñado para acelerar el rendimiento sin cambios en el código. La próxima generación de Procesamiento de consultas inteligentes se basa en un conjunto de funcionalidades que se encuentran en SQL Server 2017 y 2019, como se muestra en el diagrama siguiente:
Como puede ver, varias características del Procesamiento de consultas inteligentes formaron parte de SQL Server 2017 y SQL Server 2019. SQL Server 2022 agrega varias funcionalidades nuevas para el procesamiento de consultas inteligentes. Puede mantenerse al día de todas las funcionalidades más recientes del procesamiento de consultas inteligentes en Procesamiento de consultas inteligentes en bases de datos SQL. Examinemos cada una de estas funcionalidades nuevas.
El motor de base de datos usa dos principios para tomar decisiones para el procesamiento de consultas inteligentes:
- Evite provocar regresiones de rendimiento de consultas mediante un nuevo método o automatización.
- Proporcione un método en el nivel de base de datos o consulta para deshabilitar una funcionalidad de IQP específica. Puede seleccionar y elegir qué característica de procesamiento de consultas inteligentes quiere habilitar en el nivel de base de datos o consulta, mientras usa otra característica de procesamiento de consultas inteligentes en función del nivel de compatibilidad de la base de datos.
Funcionalidades después de actualizar a SQL Server 2022
Si actualiza a SQL Server 2022, hay nuevas funcionalidades para acelerar el rendimiento independientemente del nivel de compatibilidad de la base de datos para su base de datos. El nivel de compatibilidad le permite aprovechar las nuevas características incluso si necesita usar un nivel de compatibilidad de base de datos de una versión anterior de SQL Server. Para obtener más información, vea Certificación de compatibilidad.
Funciones de percentil aproximadas
SQL Server incluye dos funciones de Transact-SQL (T-SQL) para ayudar a la carga de trabajo analítica a calcular un percentil de un intervalo de valores:
- PERCENTILE_CONT
- PERCENTILE_DISC
SQL Server 2022 proporciona un equivalente aproximado a estas dos funciones:
- APPROX_PERCENTILE_CONT
- APPROX_PERCENTILE_DISC
Las funciones de percentil aproximadas podrían ser útiles para cargas de trabajo analíticas con conjuntos de datos excepcionalmente grandes. Estas funciones se ejecutarán más rápido y la implementación garantiza hasta un 1,33 % de tasa de errores dentro de una probabilidad del 99 %.
Forzado de plan optimizado
El forzado de plan optimizado es una nueva funcionalidad de SQL Server 2022 destinada a reducir el tiempo necesario para compilar determinadas consultas si el plan de consulta se fuerza en el Almacén de consultas.
Por su naturaleza, algunas consultas pueden tardar mucho tiempo en compilarse. El forzado de plan optimizado proporciona un método para reducir el tiempo necesario para compilar una consulta mediante el almacenamiento en los pasos de compilación del Almacén de consultas para las consultas aptas que tienen planes de consulta que se fuerzan en el Almacén de consultas. La exigencia del plan de consulta permite bloquear un plan de consulta para una consulta específica. La próxima vez que se deba compilar una consulta que tenga habilitada el forzado de plan optimizado, los pasos de compilación se usan para acelerar significativamente la fase de compilación para ejecutar una consulta.
Para más información, vea Forzado de plan optimizado con Almacén de consultas.
Las funcionalidades de procesamiento de consultas inteligentes de SQL Server 2022 mediante el nivel de compatibilidad de la base de datos 140 o superior
Puede obtener más funcionalidades de procesamiento de consultas inteligentes para mejorar los comentarios de concesión de memoria en SQL Server 2022 si usa un nivel de compatibilidad de base de datos 140 o superior. Los comentarios de concesión de memoria se introdujeron en SQL Server 2017 (modo por lotes) y SQL Server 2019 (modo de fila). Los comentarios de concesión de memoria son un mecanismo en el que el procesador de consultas aprenderá de los comentarios de ejecución para ajustar una concesión de memoria para futuras ejecuciones, lo que evita o reduce los volcados de tempdb y los tiempos de espera de RESOURCE_SEMAPHORE.
Percentiles de comentarios de concesión de memoria
Antes de SQL Server 2022, los comentarios de concesión de memoria se basaban en la ejecución más reciente de una consulta específica. Esto podría dar lugar a algunos casos de diferentes ajustes de comentarios, lo que podría llevar al procesador de consultas a deshabilitar los comentarios de concesión de memoria para una consulta específica. En SQL Server 2022, los comentarios de concesión de memoria usan un método de percentil para examinar las concesiones de memoria en varias ejecuciones antes de usar comentarios de concesión de memoria.
Persistencia de los comentarios de concesión de memoria
Antes de SQL Server 2022, los comentarios de concesión de memoria solo se almacenaban en un plan almacenado en caché en la memoria. Si se desalojara el plan de caché, los comentarios de concesión de memoria tendrían que volver a calcularse en las nuevas ejecuciones de consulta. En SQL Server 2022 con el Almacén de consultas habilitado, los comentarios de concesión de memoria se conservarán en el Almacén de consultas. Puede ver la persistencia de comentarios de concesión de memoria en la vista de catálogo sys.query_store_plan_feedback.
Para más información, consulte Comentarios de concesión de memoria.
Las funcionalidades de procesamiento de consultas inteligentes de SQL Server 2022 mediante el nivel de compatibilidad de la base de datos 160 o superior
Puede obtener más funcionalidades de procesamiento de consultas inteligentes, como optimización del plan confidencial de parámetros, mejoras en la estimación de la cardinalidad y grado de paralelismo si usa un nivel de compatibilidad de base de datos 160 o superior.
Optimización del plan de confidencialidad de parámetros
Cuando se compila una consulta, el plan de ejecución integrado tiene en cuenta los valores de todos los parámetros usados en las consultas de un procedimiento almacenado o una consulta con parámetros. Este concepto se denomina examen de parámetros. Solo puede existir un plan de consulta en caché para las instrucciones de un procedimiento almacenado o una consulta con parámetros. En la mayoría de los casos, esto no genera problemas de rendimiento para las aplicaciones. Sin embargo, hay situaciones en las que los datos recuperados para las consultas basadas en parámetros se pueden sesgar o no distribuirse uniformemente. En estos casos, es posible que el plan de caché única no sea óptimo para valores de parámetros diferentes. Este problema se conoce como un plan confidencial de parámetros.
En SQL Server 2022, el optimizador puede detectar escenarios de planes confidenciales de parámetros y almacenar en caché varios planes para el mismo procedimiento almacenado o consulta con parámetros. El optimizador usa un concepto denominado variantes de consulta para agregar conjuntos de valores de parámetros para que coincidan con un plan de consulta más adecuado para esos valores de parámetro.
Para más información, consulte Optimización del plan confidencial de parámetros.
Comentarios sobre la estimación de cardinalidad (CE)
En SQL Server 2014 con el nivel de compatibilidad de base de datos 120, Microsoft comenzó a usar un nuevo modelo dentro del procesador de consultas para realizar ciertas hipótesis sobre la estimación de cardinalidad para determinados patrones de consulta. En algunos casos, el nuevo modelo generó un plan de consulta más correcto, pero podría dar lugar a un rendimiento más lento que con el modelo de CE heredado. Los escenarios del modelo de CE incluyen correlación, contención de combinación y objetivo de fila. A partir de SQL Server 2014, se han incluido varias opciones para usar el modelo de CE heredado o controlar el comportamiento de CE en el nivel de base de datos o en el nivel de consulta con marcas de seguimiento o sugerencias de consulta.
En SQL Server 2022, cuando el Almacén de consultas está habilitado, el optimizador evaluará consultas muy repetitivas que coincidan con patrones para escenarios de modelos de CE en los que el modelo pueda realizar una hipótesis incorrecta. A continuación, el optimizador intentará probar y comprobar si se podría usar una sugerencia de consulta para permitir que la consulta se ejecute más rápido. Tras comprobar que el rendimiento es más rápido, se conservará una sugerencia de consulta en el Almacén de consultas que se usará para futuras ejecuciones de consultas. Puede ver las sugerencias de consulta aplicadas para los comentarios de CE en la vista de catálogo sys.query_store_query_hints y los detalles de los comentarios de CE en la vista de catálogo sys.query_store_plan_feedback. Los comentarios sobre CE no se usarán si el modelo de CE heredado se ha habilitado, si se fuerza un plan de consulta en el almacén de consultas o si una consulta tiene sugerencias de almacén de consultas existentes.
Para obtener más información, consulte Estimación de cardinalidad.
Comentarios de grado de paralelismo (DOP)
El optimizador de SQL Server ejecutará partes del plan de consulta (denominadas operadores) mediante el paralelismo con varios subprocesos simultáneos. El número de subprocesos usados para un operador de plan de consulta se denomina grado de paralelismo (DOP). SQL Server puede controlar el número máximo de subprocesos por operador mediante la configuración de servidor, base de datos, grupo de recursos o consulta denominada grado máximo de paralelismo (MAXDOP). Configurar un valor de MAXDOP adecuado para una implementación de SQL Server puede ser un ejercicio complejo y, a veces, difícil.
En SQL Server 2022, el optimizador puede usar una técnica denominada comentarios de DOP para encontrar la eficacia paralela para una consulta. La eficiencia paralela es el DOP mínimo para una consulta que puede tener la misma duración general de la consulta (factorización de tiempos de espera comunes). Reducir el DOP de una consulta puede proporcionar más subprocesos y recursos de CPU para otras consultas o aplicaciones.
Los comentarios de DOP requieren que el Almacén de consultas esté habilitado, el nivel de compatibilidad de la base de datos 160 y la activación de una configuración de base de datos llamada DOP_FEEDBACK. Con esta configuración, el optimizador trabajará en coordinación con las tareas en segundo plano del Almacén de consultas para buscar consultas repetitivas y de larga duración que podrían beneficiarse de un DOP inferior. Se usará un ciclo de comentarios para validar que la duración de una consulta ajustada (factorización de tiempos de espera) no genere una regresión con un valor DOP inferior y se observará una CPU general inferior para la consulta. Después de un período de validación, un DOP inferior se considera estabilizado y se conservará en el Almacén de consultas. El optimizador seguirá validando los valores de DOP inferiores de forma descendente para encontrar la mejor eficacia paralela o un DOP mínimo, que es 2. Los comentarios de DOP nunca aumentarán el valor de DOP y respetarán la configuración de MAXDOP de una consulta en función del servidor, la base de datos, el regulador de recursos o la sugerencia de consulta que se haya aplicado.
Los comentarios de DOP no requieren recompilación, pero la validación se examinará en cualquier compilación de consulta nueva. Puede observar los valores de comentarios de DOP persistentes en la vista de catálogo sys.query_store_plan_feedback. Puede ver cuál es el DOP más reciente que se usa para una consulta mediante la columna last_dop de la vista de administración dinámica sys.dm_exec_query_stats y la vista de catálogo sys.query_store_runtime_stats.
Para más información, vea Comentarios sobre grados de paralelismo (DOP).