Retroalimentación sobre la estimación de cardinalidad (CE)

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores de Azure SQL DatabaseAzure 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 de CE. Este escenario ayuda a reducir los riesgos de regresión relacionados con la estimación de cardinalidad predeterminada al actualizar desde versiones anteriores del Motor de base de datos.

Dado que no existe un único conjunto de modelos y supuestos de estimación de cardinalidad que pueda dar cabida a la amplia variedad de cargas de trabajo de los clientes y distribuciones de datos, la retroalimentación de la estimación de cardinalidad proporciona una solución adaptable basada en las características del tiempo de ejecución de las consultas. La retroalimentación de la estimación de cardinalidad identifica y utiliza un supuesto del modelo que se ajusta mejor a una consulta determinada y a la distribución de los datos para mejorar la calidad del plan de ejecución de la consulta. Actualmente, la retroalimentación de CE puede identificar operadores del plan en los que el número estimado de filas y el número real de filas son muy diferentes. Los comentarios se aplican cuando se producen errores significativos de estimación del modelo y hay un modelo alternativo viable para intentarlo.

Para obtener información sobre otras funciones de comentarios de consultas, consulte comentarios sobre la concesión de memoria y comentarios sobre el grado de paralelismo (DOP).

Comprender los comentarios de estimación de cardinalidad (CE)

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.

Las distintas versiones del Motor de base de datos utilizan diferentes suposiciones del modelo de estimación de cardinalidad según cómo se distribuyen y consultan los datos. Para obtener más información, consulte las versiones de la CE.

Implementación de la retroalimentación sobre la estimación de cardinalidad (CE)

La retroalimentación de la estimación de cardinalidad (CE) aprende qué suposiciones del modelo de CE resultan óptimas con el tiempo y, a continuación, aplica la suposición que históricamente ha sido la más precisa:

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 un supuesto parece incorrecto, una ejecución posterior de la misma consulta se prueba con un plan de consulta que ajusta el supuesto relevante del modelo de estimación de cardinalidad y comprueba si ayuda. Identificamos la incorrección comparando las filas reales con las estimadas de los operadores del plan. No todos los errores se pueden corregir mediante variantes del modelo disponibles en los comentarios de CE.

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. La retroalimentación de CE no se utiliza para esa consulta si el supuesto ajustado del modelo provoca una regresión del rendimiento. En este contexto, una consulta cancelada por el usuario también se percibe como una regresión.

Escenarios de retroalimentación de estimación de cardinalidad (CE)

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

Correlación de la retroalimentación de la estimación de cardinalidad (CE)

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 sean:

• 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, la retroalimentación de CE intenta mover la correlación en la dirección correcta paso a paso, en función de si la cardinalidad estimada se subestimó o se sobrestimó 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 CE.

Comentarios sobre la estimación de cardinalidad (CE) para la contención de combinaciones

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 posterior): supone que los predicados de combinación no tienen ningún tipo de correlación con los filtros posteriores, donde primero se calcula 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 las versiones de CE.

Retroalimentación sobre la estimación de cardinalidad (CE) y el objetivo de filas del optimizador de consultas

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 podría 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 podrían examinar más páginas de las estimadas, lo que significa que el objetivo de filas se vuelve ineficaz. La retroalimentación de CE puede deshabilitar la exploración por objetivo de filas y habilitar una búsqueda por índice cuando se detecta esta ineficiencia.

En el plan de ejecución, no hay ningún atributo específico para la retroalimentación de CE, pero sí hay un atributo indicado para la sugerencia de Almacén de consultas. Busque que QueryStoreStatementHintSource sea CE feedback.

Consideraciones sobre la retroalimentación de la estimación de cardinalidad (CE)

• Para habilitar la retroalimentación de estimación de cardinalidad (CE), establezca el nivel de compatibilidad de la base de datos en 160 para la base de datos a la que esté conectado al ejecutar 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.

• Para deshabilitar la retroalimentación de CE a nivel de base de datos, use la CE_FEEDBACKconfiguración con ámbito de base de datos. Por ejemplo, en la base de datos de usuario:

  ALTER DATABASE SCOPED CONFIGURATION
  SET CE_FEEDBACK = OFF;
  

• Para deshabilitar la retroalimentación de CE a nivel de consulta, use la sugerencia de consulta DISABLE_CE_FEEDBACK.

La actividad de comentarios de CE es visible mediante los XEvents query_feedback_analysis y query_feedback_validation.

Se puede hacer un seguimiento de las sugerencias establecidas mediante la retroalimentación de CE usando la vista de catálogo sys.query_store_query_hints.

Se puede realizar un seguimiento de la información de retroalimentación a través de la vista de catálogo sys.query_store_plan_feedback.

Si una consulta tiene forzado un plan de consulta mediante Almacén de consultas, no se utiliza la retroalimentación de estimación de cardinalidad para esa consulta.

Si una consulta utiliza sugerencias de consulta codificadas de forma fija o sugerencias de Almacén de consultas establecidas por el usuario, no se utiliza la retroalimentación de estimación de cardinalidad para esa consulta. Para obtener más información, consulte Sugerencias de consulta y Sugerencias del Almacén de consultas.

A partir de SQL Server 2022 (16.x), cuando Almacén de consultas para réplicas secundarias está habilitado, la retroalimentación de CE no tiene en cuenta las réplicas secundarias en los grupos de disponibilidad. La retroalimentación de CE actualmente solo beneficia a las réplicas primarias. Al producirse una conmutación por error, se pierde la información de retroalimentación aplicada a la réplica principal o secundaria. Almacén de consultas está disponible en réplicas de grupos de disponibilidad secundarias a partir de SQL Server 2025 (17.x). Para obtener más información, consulte Almacén de consultas para secundarias legibles.

Persistencia de la retroalimentación de estimación de cardinalidad (CE)

Se aplica a SQL Server 2022 (16.x) y versiones posteriores, Azure SQL Database y Azure SQL Managed Instance.

La retroalimentación de estimación de cardinalidad (CE) puede detectar escenarios en los que la optimización de objetivo de fila debe persistirse y conservar este cambio al persistirlo en Almacén de consultas en forma de una sugerencia de Almacén de consultas. La nueva optimización se usa para futuras ejecuciones de la consulta. La retroalimentación de CE persiste en otros escenarios ajenos a los patrones de consulta de optimización de objetivos de fila, tal como se detalla en escenarios de retroalimentación. Actualmente, la retroalimentación de CE gestiona los casos de selectividad de predicados que utiliza el modelo de correlación de CE, así como los casos de predicados de combinación que gestiona el modelo de contención de CE.

Esta característica se introdujo en SQL Server 2022 (16.x); sin embargo, esta mejora del rendimiento está disponible para las consultas que se ejecutan con un nivel de compatibilidad 160 de la base de datos o superior, o con la sugerencia QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n de nivel 160 o superior, y cuando Almacén de consultas está habilitado para la base de datos y se encuentra en un estado de "lectura y escritura".

Problemas conocidos con la retroalimentación de estimación de cardinalidad (CE)

Detalles de los problemas conocidos

Ralentización del rendimiento de SQL Server después de aplicar la actualización acumulativa 8 para SQL Server 2022 en determinadas condiciones

A partir de la actualización acumulativa 8 de SQL Server 2022 (16.x), SQL Server podría presentar aumentos inesperados en el uso de CPU y memoria. Además, también podría observarse un aumento en las esperas de RESOURCE_SEMAPHORE_QUERY_COMPILE. También podría observar aumentos constantes en el número de objetos de caché de planes en uso que se acercan a los límites de la caché de planes, y borrar manualmente la caché de planes con técnicas como ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHE o DBCC FREEPROCCACHE no ayuda. Este comportamiento solo lo ha observado un pequeño número de clientes.

Este problema no afecta a todas las cargas de trabajo y depende tanto del número de planes diferentes generados como del número de planes para los que podía activarse la función de comentarios de CE. Mientras la retroalimentación de CE analiza los operadores del plan en busca de errores significativos de estimación del modelo, hay un caso en el que un plan al que se hace referencia puede perder la referencia durante esta fase de análisis. Esta situación impide que el plan se elimine de la memoria mediante el algoritmo habitual de descarte del menos utilizado recientemente (LRU). El mecanismo LRU es una de las formas en que SQL Server aplica directivas de descarte de planes. SQL Server también elimina planes de la memoria si el sistema sufre presión de memoria. Cuando SQL Server intenta eliminar los planes que desreferenciaron incorrectamente, no puede eliminar esos planes de la caché de planes, lo que hace que esta caché continúe creciendo. La caché creciente podría empezar a provocar compilaciones adicionales que, en última instancia, utilizara más CPU y memoria. Para obtener más información consulte Aspectos internos del caché de planes.

Síntoma: El número de entradas de la caché de planes en uso y marcadas como sucias, ya sean de planes SQL o de planes de objetos, aumenta con el tiempo hasta 50 000 o más. Si observa entradas de caché de planes que empiezan a aproximarse a este nivel junto con aumentos inesperados del uso de la CPU, es posible que el sistema este experimentando este problema. Se ha proporcionado una corrección en la actualización acumulativa 12 de SQL Server 2022 (16.x). Consulta KB5033663.

Para supervisar el número de entradas de caché de planes que usa el sistema, se pueden usar los ejemplos siguientes como vista a un momento dado del número de entradas de caché de planes que existen. Por ejemplo, observar el número de entradas de caché de planes que se marcan como desfasadas periódicamente a lo largo del tiempo es una manera de supervisar este fenómeno.

SELECT CASE WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
            WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
            ELSE '[All other cache stores]'
       END AS PlanType,
       COUNT(*) AS [Number of plans marked to be removed]
FROM sys.dm_os_memory_cache_entries AS mce
     LEFT OUTER JOIN sys.dm_exec_cached_plans AS ecp
         ON mce.memory_object_address = ecp.memory_object_address
WHERE mce.is_dirty = 1
      AND ecp.bucketid IS NULL
GROUP BY CASE WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
              WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
              ELSE '[All other cache stores]'
         END;

Otro conjunto de consultas que también proporcionan la misma información que el ejemplo anterior, al tiempo que le permiten observar métricas de rendimiento adicionales. Las tasas de aciertos de la caché de planes disminuyen, al igual que el número de compilaciones en relación con el número de solicitudes de lote por segundo. Las siguientes consultas pueden utilizarse para supervisar su sistema a lo largo del tiempo. Vigilar la tasa de aciertos de caché (caídas imprevistas), los objetos de caché en uso (aumentos del recuento hasta niveles próximos a 50 000 sin disminuir) y una tasa de solicitudes por lotes por segundo inferior a la esperada en comparación con el aumento de compilaciones por segundo.

--SQL Plan (Adhoc and Prepared plans)
SELECT CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
            WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
            WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
            WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
       END AS [SQLServer:Plan Cache (SQL Plans)],
       CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
            ELSE FORMAT(cntr_value, '#,###')
       END AS [Counter Value],
       CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN
                 FORMAT(TRY_CONVERT (DECIMAL (5, 2), (cntr_value * 1.0 / NULLIF ((SELECT cntr_value
                     FROM sys.dm_os_performance_counters
                     WHERE [object_name] LIKE '%:Plan Cache%'
                           AND [counter_name] = 'Cache Hit Ratio Base'
                           AND instance_name LIKE 'SQL Plan%'), 0))), '0.00%')
       END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
      AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
      AND instance_name LIKE 'SQL Plan%'
ORDER BY [counter_name];

--Module/Stored procedure based plans
SELECT CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
            WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
            WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
            WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
       END AS [SQLServer:Plan Cache (Object Plans)],
       CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
            ELSE FORMAT(cntr_value, '#,###')
       END AS [Counter Value],
       CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN
                 FORMAT(TRY_CONVERT (DECIMAL (5, 2), (cntr_value * 1.0 / NULLIF ((SELECT cntr_value
                     FROM sys.dm_os_performance_counters
                     WHERE [object_name] LIKE '%:Plan Cache%'
                           AND [counter_name] = 'Cache Hit Ratio Base'
                           AND instance_name LIKE 'Object Plan%'), 0))), '0.00%')
       END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
      AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
      AND instance_name LIKE 'Object Plan%'
ORDER BY [counter_name];

SELECT CASE WHEN [counter_name] = 'Batch Requests/sec' THEN 'Batch Requests/sec'
            WHEN [counter_name] = 'SQL Compilations/sec' THEN 'SQL Compilations/sec'
       END AS [SQLServer:SQL Statistics],
       FORMAT(cntr_value, '#,###') AS [Counter Value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:SQL Statistics%'
      AND counter_name IN ('Batch Requests/sec', 'SQL Compilations/sec');

Solución alternativa

Si el sistema sigue experimentando los síntomas descritos anteriormente, después de aplicar la actualización acumulativa 12 KB5033663, la característica de comentarios de CE se puede deshabilitar en el nivel de base de datos.

Para recuperar la memoria de la caché de planes ocupada por este problema, es necesario reiniciar la instancia de SQL Server. Esta acción de reinicio se puede realizar después de deshabilitar la característica de comentarios de CE. Para deshabilitar la retroalimentación de CE a nivel de base de datos, use la CE_FEEDBACKconfiguración con ámbito de base de datos. Por ejemplo, en la base de datos de usuario:

ALTER DATABASE SCOPED CONFIGURATION
SET CE_FEEDBACK = OFF;

Comentarios y problemas de informes

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

Contenido relacionado

• Retroalimentación de la estimación de cardinalidad en SQL Server 2022
• Procesamiento de consultas inteligente en bases de datos SQL
• Características de procesamiento de consultas inteligentes en detalle
• Estimación de cardinalidad (SQL Server)
• RECONFIGURE (Transact-SQL)
• Supervisión y optimización del rendimiento
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)