Características de comentarios de procesamiento de consultas

Este artículo contiene descripciones detalladas de diversas características de comentarios de procesamiento de consultas inteligentes (IQP). Las características de comentarios de procesamiento de consultas forman parte de la familia de características de procesamiento de consultas inteligentes. Los comentarios de procesamiento de consultas son un proceso por el que el procesador de consultas de SQL Server, Azure SQL Database y Azure SQL Managed Instance usa datos históricos sobre la ejecución de una consulta para decidir si la consulta puede recibir ayuda de uno o varios cambios en la forma en que se compila y ejecuta. Los datos de rendimiento se recopilan en el almacén de consultas, con varias sugerencias para mejorar la ejecución de consultas. Si se ejecuta correctamente, conservamos estas modificaciones en el disco en memoria o en el almacén de consultas para su uso futuro. Si las sugerencias no producen una mejora suficiente, se descartan y la consulta continúa ejecutándose sin esos comentarios.

Las características de comentarios que se describen en este artículo son:

Comentarios de concesión de memoria

A veces, una consulta se ejecuta con una concesión de memoria demasiado grande o demasiado pequeña. Si la concesión de memoria es demasiado grande, se impide el paralelismo en el servidor. Si es demasiado pequeño, es posible que volteemos al disco, que es una operación costosa. Los comentarios de concesión de memoria intentan recordar las necesidades de memoria de una ejecución anterior (a partir de SQL Server 2022 (16.x), varias ejecuciones) de una consulta y ajustar la concesión dada a la consulta en consecuencia. Esta característica se ha lanzado en tres oleadas. Comentarios de concesión de memoria en modo por lotes, seguidos de los comentarios de concesión de memoria del modo de fila y en SQL Server 2022 (16.x), estamos introduciendo comentarios de concesión de memoria en la persistencia del disco mediante la Almacén de consultas y un algoritmo mejorado conocido como concesión de percentil.

Comentarios de concesión de memoria de modo de proceso por lotes

Se aplica a: SQL Server (a partir de SQL Server 2017 (14.x)), Azure SQL Database

El plan de ejecución de una consulta incluye la memoria mínima necesaria para la ejecución y el tamaño de concesión de memoria ideal para que todas las filas se ajusten a la memoria. El rendimiento se ve afectado si los tamaños de concesión de memoria son incorrectos. A su vez, unas concesiones excesivas se traducen en memoria desperdiciada y en simultaneidad reducida. Las concesiones de memoria insuficientes provocan un costoso desbordamiento en disco. Al ocuparse de las cargas de trabajo repetidas, los comentarios de concesión de memoria de modo de proceso por lotes vuelven a calcular la memoria real necesaria para una consulta y luego actualizan el valor de la concesión del plan almacenado en caché. Cuando se ejecuta una instrucción de consulta idéntica, la consulta usa el tamaño de concesión de memoria revisado, con lo que se reducen las concesiones de memoria excesivas que afectan a la simultaneidad y se solucionan las concesiones de memoria subestimadas que provocan costosos desbordamientos en disco.

El gráfico siguiente muestra un ejemplo de uso de los comentarios de concesión de memoria adaptable de modo de proceso por lotes. Para la primera ejecución de la consulta, la duración es de 88 segundos, debido a los grandes desbordamientos:

DECLARE @EndTime datetime = '2016-09-22 00:00:00.000';
DECLARE @StartTime datetime = '2016-09-15 00:00:00.000';

SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime AND @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;

Gráfico de MB concedidos frente a mb de memoria desbordados, lo que indica grandes desbordamientos.

Con los comentarios de concesión de memoria habilitados para la segunda ejecución, la duración es de 1 segundo (partiendo de 88 segundos), los desbordamientos se eliminan por completo y la concesión es superior:

Gráfico de MB concedidos frente a mb de memoria desbordados, lo que indica que no se han desbordado.

Tamaño de los comentarios de concesión de memoria

En el caso de una condición de concesión de memoria excesiva, si la memoria concedida es más de dos veces el tamaño de la memoria usada real, los comentarios de concesión de memoria volverán a calcular la concesión de memoria y actualizarán el plan almacenado en caché. Los planes con concesiones de memoria inferiores a 1 MB no se volverán a calcular para uso por encima del límite.

Para una condición de concesión de memoria de tamaño insuficiente que da lugar a un desbordamiento en el disco para los operadores de modo por lotes, los comentarios de concesión de memoria desencadenarán un recálculo de la concesión de memoria. Los eventos de desbordamiento se notifican a los comentarios de concesión de memoria y se pueden mostrar a través del spilling_report_to_memory_grant_feedback evento extendido. Este evento devuelve el identificador de nodo del plan y el tamaño de los datos desbordados de ese nodo.

La concesión de memoria ajustada se muestra en el plan real (posterior a la ejecución) a través de la GrantedMemory propiedad .

Puede ver esta propiedad en el operador raíz del plan de presentación gráfico o en la salida XML del plan de presentación:

<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="10336" RequiredMemory="1024" DesiredMemory="10336" RequestedMemory="10336" GrantWaitTime="0" GrantedMemory="10336" MaxUsedMemory="9920" MaxQueryMemory="725864" />

Para que las cargas de trabajo sean aptas automáticamente para esta mejora, habilite el nivel de compatibilidad 140 para la base de datos.

Ejemplo:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;

Comentarios de concesión de memoria y escenarios confidenciales de parámetros

Los distintos valores de parámetros también pueden necesitar diferentes planes de consulta para seguir siendo óptimos. Este tipo de consulta se define como "sensible a parámetros".

En el caso de los planes sensibles a parámetros, los comentarios de concesión de memoria se deshabilitarán en una consulta si esta tiene requisitos de memoria inestables. La característica de comentarios de concesión de memoria se deshabilita después de varias ejecuciones repetidas de la consulta y se puede observar mediante la supervisión del memory_grant_feedback_loop_disabled evento extendido. Esta condición se mitiga con el modo de persistencia y percentil para los comentarios de concesión de memoria introducidos en SQL Server 2022 (16.x). La característica de persistencia de los comentarios de concesión de memoria requiere que el Almacén de consultas esté habilitado en la base de datos y se establezca en modo de "lectura y escritura".

Para obtener más información sobre el examen de parámetros y la sensibilidad de los parámetros, consulte la Guía de arquitectura de procesamiento de consultas.

Almacenamiento en caché de los comentarios de concesión de memoria

Los comentarios pueden almacenarse en el plan almacenado en caché para una sola ejecución. Sin embargo, son las ejecuciones consecutivas de esa instrucción que se benefician de los ajustes de comentarios de concesión de memoria. Esta característica se aplica a la ejecución repetida de instrucciones. Los comentarios de concesión de memoria solo cambian el plan almacenado en caché. Antes de SQL Server 2022 (16.x), los cambios no se capturaron en el Almacén de consultas.

Los comentarios no se conservan si el plan se expulsa de la memoria caché. Los comentarios también se perderán si hay una conmutación por error. Una instrucción que usa OPTION (RECOMPILE) crea un nuevo plan y no la almacena en caché. Puesto que no se almacena en caché, no se genera ningún comentario de concesión de memoria y no se almacena para esa compilación y ejecución. Sin embargo, si se almacena en caché una instrucción equivalente (es decir, con el mismo hash de consulta) que no usó OPTION (RECOMPILE) y, a continuación, se vuelve a ejecutar, las ejecuciones consecutivas segunda y posterior pueden beneficiarse de los comentarios de concesión de memoria.

Seguimiento de la actividad de comentarios de concesión de memoria

Puede realizar un seguimiento de los eventos de comentarios de concesión de memoria mediante el memory_grant_updated_by_feedback evento extendido. Este evento realiza un seguimiento del historial de recuentos de ejecución actual, del número de veces que los comentarios de concesión de memoria han provocado una actualización del plan, de la concesión de memoria adicional ideal antes de la modificación y de la concesión de memoria adicional ideal después de que los comentarios de concesión de memoria hayan modificado el plan almacenado en caché.

Comentarios de concesión de memoria, regulador de recursos y sugerencias de consulta

La memoria real concedida respeta el límite de memoria de consulta determinado por el regulador de recursos o la sugerencia de consulta.

Deshabilitación de los comentarios de concesión de memoria del modo por lotes sin cambiar el nivel de compatibilidad

Los comentarios de concesión de memoria se pueden deshabilitar en el ámbito de base de datos o de instrucción mientras se mantiene el nivel de compatibilidad de base de datos 140 o posterior. Para deshabilitar los comentarios de concesión de memoria del modo por lotes para todas las ejecuciones de consulta que se originan en la base de datos, ejecute las instrucciones SQL siguientes en el contexto de la base de datos aplicable:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Cuando se habilita, esta opción aparecerá como habilitada en sys.database_scoped_configurations.

Para volver a habilitar los comentarios de concesión de memoria del modo por lotes para todas las ejecuciones de consulta que se originan en la base de datos, ejecute las instrucciones SQL en el contexto de la base de datos aplicable:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

También puede deshabilitar los comentarios de concesión de memoria en modo por lotes para una consulta específica si designa DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK como sugerencia de consulta USE HINT. Por ejemplo:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));

Una sugerencia de consulta USE HINT tiene prioridad sobre una configuración con ámbito de base de datos o una configuración de marca de seguimiento.

Comentarios de concesión de memoria del modo de fila

Se aplica a: SQL Server (a partir de SQL Server 2019 (15.x)), Azure SQL Database

Los comentarios de concesión de memoria del modo de fila se expanden en la característica de comentarios de concesión de memoria de modo de proceso por lotes al ajustar los tamaños de concesión de memoria tanto para los operadores del modo de proceso por lotes como del modo de fila.

Para habilitar los comentarios de concesión de memoria en modo de fila en Azure SQL Database, habilite el nivel de compatibilidad de la base de datos 150 o superior para la base de datos a la que está conectado al ejecutar la consulta.

Ejemplo:

ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;

Al igual que con los comentarios de concesión de memoria en modo por lotes, la actividad de comentarios de concesión de memoria del modo de fila es visible a través de memory_grant_updated_by_feedback XEvent. También se presentan dos nuevos atributos del plan de ejecución de consultas para mejorar la visibilidad del estado actual de una operación de comentarios de concesión de memoria para el modo de fila y por lotes.

Los comentarios de concesión de memoria no requieren el Almacén de consultas, sin embargo, las mejoras de persistencia introducidas en SQL Server 2022 (16.x) requieren que el Almacén de consultas esté habilitado para la base de datos y en un estado de "lectura y escritura". Para obtener más información sobre la persistencia, consulte Comentarios de concesión de memoria en modo percentil y persistencia más adelante en este artículo.

La actividad de comentarios de concesión de memoria del modo de fila es visible a través del memory_grant_updated_by_feedback evento extendido.

A partir de los comentarios de concesión de memoria del modo de fila, se muestran dos nuevos atributos del plan de consulta para los planes reales posteriores a la ejecución: IsMemoryGrantFeedbackAdjusted y LastRequestedMemory, que se agregan al elemento XML del MemoryGrantInfo plan de consulta.

  • El LastRequestedMemory atributo muestra la memoria concedida en Kilobytes (KB) de la ejecución de la consulta anterior.
  • El IsMemoryGrantFeedbackAdjusted atributo permite comprobar el estado de los comentarios de concesión de memoria para la instrucción dentro de un plan de ejecución de consultas real.

Los valores que se exponen en este atributo son los siguientes:

Valor de IsMemoryGrantFeedbackAdjusted Descripción
No: First Execution Los comentarios de concesión de memoria no ajustan la memoria para la primera compilación y ejecución asociada.
No: Accurate Grant Si no hay ningún desbordamiento en el disco y la instrucción usa al menos el 50 % de la memoria concedida, no se desencadenan los comentarios de concesión de memoria.
No: Feedback disabled Si los comentarios de concesión de memoria se desencadenan continuamente y fluctúan entre las operaciones de aumento de memoria y disminución de memoria, el motor de base de datos deshabilitará los comentarios de concesión de memoria para la instrucción.
Yes: Adjusting Se aplicaron los comentarios de concesión de memoria y se pueden seguir ajustando para la próxima ejecución.
Yes: Stable Se aplicaron los comentarios de concesión de memoria y ahora la memoria concedida es estable, lo que significa que lo último que se concedió para la ejecución anterior es lo que se concedió para la ejecución actual.

Deshabilitar los comentarios de concesión de memoria del modo de fila sin cambiar el nivel de compatibilidad

Los comentarios de concesión de memoria en modo de fila se pueden deshabilitar en el ámbito de base de datos o de instrucción mientras se mantiene el nivel de compatibilidad de base de datos 150 o posterior. Para deshabilitar los comentarios de concesión de memoria del modo de fila para todas las ejecuciones de consulta que se originan en la base de datos, ejecute las instrucciones SQL en el contexto de la base de datos aplicable:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Para volver a habilitar los comentarios de concesión de memoria en modo de fila para todas las ejecuciones de consultas que se originan en la base de datos, ejecute lo siguiente en el contexto de la base de datos aplicable:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

También puede deshabilitar los comentarios de concesión de memoria en modo de fila para una consulta específica si designa DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK como sugerencia de consulta USE HINT. Por ejemplo:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

Una sugerencia de consulta USE HINT tiene prioridad sobre una configuración con ámbito de base de datos o una configuración de marca de seguimiento.

Comentarios de concesión de memoria en modo percentil y persistencia

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores

Esta característica se introdujo en SQL Server 2022 (16.x), pero esta mejora de rendimiento está disponible para las consultas que operan en el nivel de compatibilidad de la base de datos 140 (introducido en SQL Server 2017) o superior, o la sugerencia de QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n de 140 y versiones posteriores, y cuando Almacén de consultas está habilitada para la base de datos y está en un estado de "lectura y escritura".

  • Los comentarios de concesión de memoria de percentil están habilitados de forma predeterminada en SQL Server 2022 (16.x), pero no tiene ningún efecto si Almacén de consultas no está habilitado y en un estado de "lectura y escritura".
  • La persistencia de los comentarios de concesión de memoria, CE y DOP está activada de forma predeterminada en SQL Server 2022 (16.x), pero no tiene ningún efecto si Almacén de consultas no está habilitado y en un estado de "lectura y escritura".
  • Los comentarios de concesión de memoria de percentil no están disponibles actualmente en Azure SQL Database y Azure SQL Managed Instance.
  • La persistencia no está disponible actualmente en Azure SQL Database y Azure SQL Managed Instance.

Se recomienda tener una línea base de rendimiento para la carga de trabajo antes de habilitar la característica para la base de datos. Los números de línea base le ayudarán a determinar si obtiene la ventaja prevista de la característica.

Los comentarios de concesión de memoria (MGF) son una característica existente que ajusta el tamaño de la memoria asignada para una consulta en función del rendimiento pasado. Sin embargo, las fases iniciales de este proyecto solo almacenaron el ajuste de concesión de memoria con el plan en la memoria caché; si un plan se expulsa de la memoria caché, el proceso de comentarios debe iniciarse de nuevo, lo que da lugar a un rendimiento deficiente las primeras veces que se ejecuta una consulta después de la expulsión. La nueva solución consiste en conservar la información de concesión con la otra información de consulta en el Almacén de consultas para que las ventajas duren en las expulsiones de caché. La persistencia de los comentarios de concesión de memoria y el percentil abordan las limitaciones existentes de los comentarios de concesión de memoria de forma no intrusiva.

Además, los ajustes de tamaño de concesión solo se contabilizan para la concesión usada más recientemente. Por lo tanto, si una consulta o carga de trabajo con parámetros requiere tamaños de concesión de memoria considerablemente diferentes con cada ejecución, la información de concesión más reciente podría ser inexacta. Podría estar fuera del paso con las necesidades reales de la consulta que se está ejecutando. Los comentarios de concesión de memoria en este escenario no son útiles para el rendimiento porque siempre se ajusta la memoria en función del último valor de concesión usado. En la imagen siguiente se muestra el comportamiento posible con comentarios de concesión de memoria sin percentil y modo de persistencia.

Gráfico de comportamiento de memoria concedido frente al comportamiento real de memoria necesaria en los comentarios de concesión de memoria sin percentil y comentarios de concesión de memoria en modo de persistencia.

Como puede ver, en este comportamiento de consulta inusual pero posible, la oscilación entre las cantidades de memoria necesarias reales y concedidas da como resultado una cantidad de memoria desperdiciada e insuficiente si la propia ejecución de la consulta alterna en términos de la cantidad de memoria. En este escenario, los comentarios de concesión de memoria se deshabilitan, reconociendo que está haciendo más daño que bien.

Con un cálculo basado en percentil en el historial reciente de la consulta, en lugar de simplemente la última ejecución, podemos suavizar los valores de tamaño de concesión en función del historial de uso de ejecución anterior e intentar optimizar para minimizar los desbordamientos. Por ejemplo, la misma carga de trabajo alternada vería el siguiente comportamiento de concesión de memoria:

Gráfico de comportamiento de memoria concedido frente al comportamiento real de memoria necesaria en comentarios de concesión de memoria con comentarios de concesión de memoria en modo percentil y persistencia.

El optimizador de consultas usa un percentil alto de los requisitos de tamaño de concesión de memoria pasadas para las ejecuciones del plan almacenado en caché para calcular los tamaños de concesión de memoria, utilizando los datos almacenados en la Almacén de consultas. El ajuste de percentil, que realizará los ajustes de concesión de memoria se basa en el historial reciente de ejecuciones. Con el tiempo, la concesión de memoria dada reduce los desbordamientos y la memoria desperdiciada.

La persistencia también se aplica a los comentarios de DOP y a los comentarios de CE, que también se detallan en este artículo.

Habilitación de los comentarios de concesión de memoria: persistencia y percentil

Para habilitar la persistencia y el percentil de concesión de memoria, use el nivel de compatibilidad de base de datos 140 o superior para la base de datos a la que está conectado al ejecutar la consulta. La peristencia y los comentarios de percentil están habilitados de forma predeterminada.

ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER

El almacén de datos de consultas debe estar habilitado para cada base de datos en la que se use la parte de persistencia de esta característica.

Deshabilitar percentil

Para deshabilitar el percentil de comentarios de concesión de memoria para todas las ejecuciones de consulta que se originan en la base de datos, ejecute lo siguiente en el contexto de la base de datos aplicable:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE = OFF;

El valor predeterminado de MEMORY_GRANT_FEEDBACK_PERCENTILE es OFF.

Deshabilitación de la persistencia

Permite deshabilitar la persistencia de los comentarios de concesión de memoria de todas las ejecuciones de consultas que se originan en la base de datos.

Ejecute lo siguiente en el contexto de la base de datos aplicable:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

Al deshabilitar la persistencia de comentarios de concesión de memoria, también se quitarán los comentarios recopilados existentes.

El valor predeterminado de MEMORY_GRANT_FEEDBACK_PERSISTENCE es ON.

Consideraciones sobre los comentarios de concesión de memoria

Puede ver la configuración actual consultando sys.database_scoped_configurations.

Nota:

Esta característica no funcionará si y BATCH_MODE_MEMORY_GRANT_FEEDBACKROW_MODE_MEMORY_GRANT_FEEDBACK están establecidas en OFF.

Los datos de comentarios proporcionados ahora se conservan en el Almacén de consultas, hay un aumento en los requisitos de uso de Almacén de consultas.

La concesión de memoria basada en el percentil peca de reducir los volcados. Dado que ya no se basa en la última ejecución, sino en una observación de las varias ejecuciones anteriores, esto podría aumentar el uso de memoria para las cargas de trabajo oscilantes con una gran varianza en los requisitos de concesión de memoria entre ejecuciones.

A partir de SQL Server 2022 (16.x), cuando Almacén de consultas para réplicas secundarias está habilitada, los comentarios de concesión de memoria son compatibles con réplicas para réplicas secundarias en grupos de disponibilidad. Los comentarios de concesión de memoria pueden aplicar comentarios de forma diferente en una réplica principal y en una réplica secundaria. Sin embargo, los comentarios de concesión de memoria no se conservan en las réplicas secundarias y, en la conmutación por error, los comentarios de concesión de memoria de la réplica principal antigua se aplican a la nueva réplica principal. Los comentarios aplicados a la réplica secundaria cuando se convierten en la réplica principal se pierden. Para obtener más información, consulte Almacén de consultas para réplicas secundarias.

Comentarios de grado de paralelismo (DOP)

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores

SQL Server 2022 (16.x) introdujo una nueva característica denominada grado de paralelismo (DOP) para mejorar el rendimiento de las consultas mediante la identificación de ineficiencias de paralelismo para las consultas repetidas, en función del tiempo transcurrido y las esperas. Los Comentarios de grado de paralelismo forman parte de la familia de características de procesamiento de consultas inteligentes y abordan el uso poco óptimo del paralelismo para repetir consultas. Este escenario ayuda a optimizar el uso de recursos y a mejorar la escalabilidad de las cargas de trabajo, cuando el paralelismo excesivo puede provocar problemas de rendimiento. En lugar de incurrir en los problemas de un ajuste predeterminado o manual que abarca todas las consultas, los comentarios de DOP se ajustan automáticamente para evitar los problemas descritos anteriormente.

En lugar de incurrir en los problemas de un ajuste predeterminado o manual que abarca todo lo que abarca cada consulta, los comentarios de DOP autoajusta DOP para evitar el exceso de paralelismo. Si el uso del paralelismo se considera ineficaz, los comentarios de DOP reducen el DOP para la siguiente ejecución de la consulta, desde cualquier elemento del DOP configurado y comprueban si lo ayudan.

El paralelismo suele ser beneficioso para los informes y las consultas analíticas, o las consultas que, de lo contrario, administran grandes volúmenes de datos. Por el contrario, las consultas centradas en OLTP que se ejecutan en paralelo podrían experimentar problemas de rendimiento cuando el tiempo dedicado a coordinar todos los subprocesos supera las ventajas de usar un plan paralelo. Para más información, consulte la sección Ejecución de un plan paralelo.

  • Para habilitar los comentarios de DOP, habilite la DOP_FEEDBACKconfiguración con ámbito de base de datos en una base de datos.

  • El Almacén de consultas debe estar habilitado para cada base de datos en la que se usen los Comentarios de grado de paralelismo, y en el estado "Lectura y escritura". Los comentarios se conservarán en la vista de catálogo de sys.query_store_plan_feedback cuando alcancemos un grado estable de valor de comentarios de paralelismo.

  • Los comentarios de DOP están disponibles para las consultas que funcionan en el nivel de compatibilidad de la base de datos 160 (introducido con SQL Server 2022 (16.x)) o superior.

  • Solo se conservan los comentarios comprobados. Si el grado de paralelismo ajustado da como resultado una regresión de rendimiento, los Comentarios de grado de paralelismo volverán al último grado de paralelismo correcto conocido. En este contexto, una consulta cancelada por el usuario también se percibe como una regresión. Los comentarios de DOP no vuelven a compilar los planes.

  • Los comentarios estables se reverifican tras la recompilación del plan y pueden reajustarse hacia arriba o hacia abajo, pero nunca por encima de la configuración de MAXDOP (incluida una sugerencia MAXDOP).

  • Para deshabilitar los comentarios de DOP en el nivel de base de datos, use la ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = OFFconfiguración con ámbito de base de datos.

  • Para deshabilitar los Comentarios de grado de paralelismo en el nivel de consulta, use la sugerencia de consulta DISABLE_DOP_FEEDBACK.

  • A partir de SQL Server 2022 (16.x), cuando se habilita Almacén de consultas para réplicas secundarias, los comentarios de DOP también son compatibles con réplicas para las réplicas secundarias en grupos de disponibilidad. Los comentarios de DOP pueden aplicar comentarios de forma diferente en una réplica principal y en una réplica secundaria. Sin embargo, los comentarios de DOP no se conservan en las réplicas secundarias y, en la conmutación por error, los comentarios de DOP de la réplica principal antigua no se aplican a la nueva réplica principal. En la conmutación por error, se pierden los comentarios aplicados a las réplicas principales o secundarias. Para obtener más información, consulte Almacén de consultas para réplicas secundarias.

Implementación de comentarios de DOP

Los comentarios de DOP identificarán las ineficiencias de paralelismo para las consultas repetidas, en función del tiempo transcurrido y las esperas. Si el uso del paralelismo se considera ineficaz, los comentarios de DOP reducirán el DOP para la siguiente ejecución de la consulta, desde lo que sea el DOP configurado y comprobarán si ayuda.

Para evaluar la idoneidad de las consultas, el tiempo transcurrido de la consulta ajustada se mide durante algunas ejecuciones. El tiempo total transcurrido de cada consulta se ajusta ignorando los bloqueos temporales del búfer, la E/S de búfer y las esperas de E/S de red que son externas a la ejecución de consultas paralelas. El objetivo de la característica de comentarios de DOP es aumentar la simultaneidad general y reducir significativamente las esperas, incluso si aumenta ligeramente el tiempo transcurrido de la consulta.

Solo se conservan los comentarios comprobados. Si el grado de paralelismo ajustado da como resultado una regresión de rendimiento, los Comentarios de grado de paralelismo volverán al último grado de paralelismo correcto conocido. En este contexto, una consulta cancelada por el usuario también se percibe como una regresión.

Nota:

Los comentarios de DOP no vuelven a compilar los planes.

Consideraciones sobre los Comentarios de grado de paralelismo

El DOP mínimo para cualquier consulta ajustada con comentarios de DOP es 2. Las ejecuciones en serie están fuera del ámbito de los comentarios de DOP.

Se puede realizar un seguimiento de la información de comentarios mediante la vista de catálogo de sys.query_store_plan_feedback .

Si una consulta tiene un plan de consulta forzado a través del Almacén de consultas, no se usan los Comentarios de grado de paralelismo para esa consulta.

Si una consulta usa la sugerencia MAXDOP, ya sea como sugerencias de consulta codificadas de forma rígida o a través del mecanismo de sugerencias de Almacén de consultas y la sugerencia MAXDOP es mayor que 2, los comentarios de DOP reducirán el DOP con el valor sugerido como límite máximo. Para obtener más información, vea Sugerencias (Transact-SQL): consulta y Sugerencias del Almacén de consultas.

Eventos extendidos para comentarios de DOP

Las siguientes XE están disponibles para la característica:

  • dop_feedback_eligible_query: se produce cuando el plan de consulta es apto para los Comentarios de grado de paralelismo. Se pueden desencadenar eventos adicionales si se produce un reinicio de instancia de SQL Server o una recompilación.
  • dop_feedback_provided: se produce cuando se proporcionan datos de Comentarios de grado de paralelismo para una consulta determinada. Este evento contiene estadísticas de base de referencia cuando se proporcionan comentarios por primera vez y estadísticas de comentarios anteriores cuando se proporcionan comentarios posteriores.
  • dop_feedback_validation: se produce cuando se realiza la validación de las estadísticas en tiempo de ejecución de la consulta en función de una base de referencia o estadísticas de comentarios anteriores.
  • dop_feedback_stabilized: se produce cuando se estabilizan los Comentarios de grado de paralelismo para una consulta.
  • dop_feedback_reverted: se produce cuando se revierte un comentario de grado de paralelismo. El evento se activará cuando se produzca un error en la validación de comentarios en los primeros comentarios proporcionados. El sistema se revertirá a un estado sin comentarios.
  • dop_feedback_analysis_stopped: se produce cuando se detiene el análisis de comentarios de grado de paralelismo para una consulta.

Comentarios de estimación de cardinalidad (CE)

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores. Actualmente en versión preliminar para Azure SQL Database y Azure SQL Managed Instance.

A partir de SQL Server 2022 (16.x), los comentarios de estimación de cardinalidad (CE) forman parte de la familia inteligente de procesamiento de consultas y abordan los planes de ejecución de consultas poco óptimos para las consultas repetidas cuando estos problemas resultan de suposiciones incorrectas del modelo ce. Este escenario ayuda a reducir los riesgos de regresión relacionados con la estimación de cardinalidad predeterminada cuando se actualizan versiones anteriores del Motor de base de datos.

Dado que ningún conjunto de modelos y suposiciones de estimación de cardinalidad puede admitir la amplia gama de cargas de trabajo y distribuciones de datos de los clientes, los comentarios sobre la estimación de cardinalidad proporcionan una solución que se adapta en función de las características del entorno de ejecución de las consultas. Los comentarios sobre la estimación de cardinalidad identifican y usan una suposición de modelo que se adapta mejor a una determinada consulta y distribución de datos para mejorar la calidad del plan de ejecución de consultas. Los comentarios se aplican cuando se encuentran errores importantes en la estimación del modelo que dan lugar a reducciones bruscas del rendimiento.

  • A partir de SQL Server 2022 (16.x), cuando se habilita Almacén de consultas para réplicas secundarias, los comentarios de CE no son compatibles con réplicas para las réplicas secundarias en los grupos de disponibilidad. Los comentarios de CE actualmente solo benefician a las réplicas principales. Para obtener más información, consulte Almacén de consultas para réplicas secundarias.

Descripción de la estimación de cardinalidad

Estimación de cardinalidad (CE) es el modo en el que el optimizador de consultas puede calcular el número total de filas procesadas en cada nivel de un plan de consulta. La estimación de cardinalidad en SQL Server se deriva principalmente de histogramas creados cuando se crean índices o estadísticas, ya sea manual o automáticamente. En ocasiones, SQL Server también utiliza información de restricciones y nuevas versiones lógicas de consultas para determinar la cardinalidad.

Diferentes versiones del Motor de base de datos usan diferentes suposiciones de modelo de estimación de cardinalidad, en función de cómo se distribuyen y consultan los datos. Para obtener más información, consulte Versiones de la estimación de cardinalidad.

Implementación de los comentarios sobre la estimación de cardinalidad

Los comentarios sobre la estimación de cardinalidad aprenden qué suposiciones de modelo de estimación de cardinalidad son óptimas a lo largo del tiempo y aplican la suposición más correcta históricamente:

  1. Los comentarios sobre la estimación de cardinalidad identifican las suposiciones relacionadas con el modelo y evalúan si son precisas para las consultas que se repiten.

  2. Si una suposición parece incorrecta, se prueba una ejecución posterior de la misma consulta con un plan de consulta que ajusta esa suposición de modelo de estimación de cardinalidad y comprueba si es útil.

  3. Si mejora la calidad del plan, el plan de consulta anterior se reemplaza por un plan de consulta que usa la sugerencia de consulta USE HINT adecuada que ajusta el modelo de estimación, implementado a través del mecanismo de sugerencias del Almacén de consultas.

Solo se conservan los comentarios comprobados. Los comentarios sobre la estimación de cardinalidad no se usan para esa consulta si la suposición del modelo ajustado da como resultado una regresión del rendimiento. En este contexto, una consulta cancelada por el usuario también se percibe como una regresión.

Escenarios de comentarios sobre la estimación de cardinalidad

Los comentarios sobre la estimación de cardinalidad abordan los problemas de regresión percibidos que resultan de suposiciones incorrectas del modelo de estimación de cardinalidad cuando se usa la estimación de cardinalidad predeterminada (CE120 o superior) y pueden usar diferentes suposiciones de modelo de forma selectiva. Los escenarios incluyen correlación, contención de combinación y objetivo de fila del optimizador.

Correlation

Cuando el optimizador de consultas calcula la selectividad de predicados en una tabla o vista determinadas, o el número de filas que satisfacen dicho predicado, utiliza suposiciones de modelo de correlación. Estas suposiciones pueden ser que los predicados:

  • Son totalmente independientes (valor predeterminado para CE70), donde la cardinalidad se calcula multiplicando las selectividades de todos los predicados.

  • Están parcialmente correlacionados (valor predeterminado para CE120 y superior), donde la cardinalidad se calcula usando una variación en el retroceso exponencial, ordenando las selectividades del predicado más selectivo al menos selectivo.

  • Están totalmente correlacionados, donde la cardinalidad se calcula usando las selectividades mínimas de todos los predicados.

En el ejemplo siguiente, se usa una correlación parcial cuando la compatibilidad de la base de datos está establecida en 120 o superior:

USE AdventureWorks2016_EXT;
GO
SELECT AddressID, AddressLine1, AddressLine2
FROM Person.Address
WHERE StateProvinceID = 79 AND City = N'Redmond';
GO

Cuando la compatibilidad de la base de datos está establecida en 160 y se usa la correlación predeterminada, los comentarios sobre la estimación de cardinalidad intentan mover la correlación en la dirección correcta un paso cada vez, en función de si la cardinalidad estimada se ha subestimado o sobrestimado en comparación con el número real de filas. Use la correlación total si el número real de filas es mayor que la cardinalidad estimada. Use la independencia total si el número real de filas es menor que la cardinalidad estimada.

Para obtener más información, consulte Versiones de la estimación de cardinalidad.

Contención de combinación

Cuando el optimizador de consultas calcula la selectividad de los predicados de combinación y los predicados de filtro aplicables, usa suposiciones de modelo de contención. Estas suposiciones son:

  • Contención simple (valor predeterminado para CE70): supone que los predicados de combinación están totalmente correlacionados, donde primero se calcula la selectividad de filtro y, después, se factoriza la selectividad de combinación.

  • Contención base (valor predeterminado para CE120 y superior): supone que no hay correlación entre los predicados de combinación y los filtros posteriores,

donde se calcula primero la selectividad de combinación y, después, se factoriza la selectividad de filtro.

En el ejemplo siguiente, se usa la contención base cuando la compatibilidad de la base de datos está establecida en 120 o superior:

USE AdventureWorksDW2016_EXT;
GO
SELECT *
FROM dbo.FactCurrencyRate AS f
INNER JOIN dbo.DimDate AS d ON f.DateKey = d.DateKey
WHERE d.MonthNumberOfYear = 7 AND f.CurrencyKey = 3 AND f.AverageRate > 1;
GO

Para obtener más información, consulte Versiones de la estimación de cardinalidad.

Objetivo de filas del optimizador

Cuando el optimizador de consultas calcula la cardinalidad de un plan de ejecución, suele suponer que deben procesarse todas las filas aptas de todas las tablas. Sin embargo, algunos patrones de consulta hacen que el optimizador de consultas busque un plan que devuelva un número menor de filas para reducir las operaciones de E/S. Si la consulta especifica un objetivo de número de filas que se puede esperar en tiempo de ejecución usando una palabra clave TOP, IN o EXISTS, la sugerencia de consulta FAST o una instrucción SET ROWCOUNT, ese objetivo de filas se usa como parte del proceso de optimización de consultas, como en el ejemplo siguiente:

USE AdventureWorks2016_EXT;
GO
SELECT TOP 1 soh.*
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID;
GO

Cuando se aplica el plan de objetivo de filas, se reduce el número estimado de filas del plan de consulta, porque el optimizador de consultas supone que se tendrá que procesar un número menor de filas para alcanzar el objetivo de filas.

Aunque el objetivo de filas es una estrategia de optimización beneficiosa para determinados patrones de consulta, si los datos no están distribuidos de manera uniforme, se pueden examinar más páginas de las estimadas, lo que significa que el objetivo de filas se vuelve ineficaz. Los comentarios sobre la estimación de cardinalidad pueden deshabilitar el examen del objetivo de filas y habilitar una búsqueda cuando se detecta esta ineficiencia.

En el plan de ejecución, no hay ningún atributo específico de los comentarios de CE, pero habrá un atributo enumerado para la sugerencia de Almacén de consultas. Busque para QueryStoreStatementHintSource que sea CE feedback.

Consideraciones sobre los comentarios de CE

Para habilitar los comentarios sobre la estimación de cardinalidad, habilite el nivel 160 de compatibilidad para la base de datos a la que se conecta cuando ejecuta la consulta. El Almacén de consultas debe estar habilitado y en modo READ_WRITE para cada base de datos en la que se usen los comentarios de CE.

La actividad de comentarios sobre la estimación de cardinalidad es visible a través de los XEvents query_feedback_analysis y query_feedback_validation.

Se puede hacer un seguimiento de las sugerencias establecidas por los comentarios sobre la estimación de cardinalidad usando la vista de catálogo sys.query_store_query_hints.

Se puede realizar un seguimiento de la información de comentarios mediante la vista de catálogo de sys.query_store_plan_feedback .

Para deshabilitar los comentarios de CE en el nivel de base de datos, use la ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFFconfiguración con ámbito de base de datos.

Para deshabilitar los comentarios sobre la estimación de cardinalidad en el nivel de consulta, use la sugerencia de consulta DISABLE_CE_FEEDBACK.

Si una consulta tiene un plan de consulta forzado a través del Almacén de consultas, no se usan los comentarios sobre la estimación de cardinalidad para esa consulta.

Si una consulta utiliza sugerencias de consulta codificadas de forma rígida o sugerencias del Almacén de consultas establecidas por el usuario, no se usan los comentarios sobre la estimación de cardinalidad para esa consulta. Para obtener más información, vea Sugerencias (Transact-SQL): consulta y Sugerencias del Almacén de consultas (versión preliminar).

Comentarios y problemas de informes

Para comentarios o preguntas, envíe un correo electrónico CEFfeedback@microsoft.com

Pasos siguientes