Comentarios de estimación de cardinalidad (CE)

Se aplica a: Se aplica a: SQL Server 2022 (16.x) y versiones posteriores, Azure SQL Database, Azure SQL Instancia administrada.

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 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. Actualmente, los comentarios de CE pueden identificar operadores de 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 ver otras características de comentarios de consulta, consulte Comentarios de concesión de memoria y Comentarios sobre 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.

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 comentarios de estimación de cardinalidad (CE)

Los comentarios sobre la estimación de cardinalidad (CE) 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. Identificamos valores incorrectos examinando las filas reales frente a las estimadas de los operadores de 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. 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 los comentarios 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 los comentarios de 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:

• 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 de los comentarios de estimación de cardinalidad (CE)

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 Versiones de la estimación de cardinalidad.

Comentarios de estimación de cardinalidad (CE) y el objetivo de fila 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. 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 hay un atributo enumerado para la sugerencia del almacén de consultas. Busque que QueryStoreStatementHintSource sea CE feedback.

Consideraciones para los comentarios de estimación de cardinalidad (CE)

• Para habilitar los comentarios sobre la estimación de cardinalidad (CE), habilite el nivel 160 de compatibilidad de 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.

• Para deshabilitar los comentarios de CE en el nivel de base de datos, use la configuración con ámbito de base de datos de CE_FEEDBACK. Por ejemplo, en la base de datos de usuario:

  ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;
  

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

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.

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 de consulta y Sugerencia del Almacén de consultas.

A partir de SQL Server 2022 (16.x), cuando se habilita el 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. 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.

Persistencia en los comentarios de estimación de cardinalidad (CE)

Se aplica a: Se aplica a: SQL Server 2022 (16.x) y versiones posteriores, Azure SQL Database, Azure SQL Instancia administrada.

Los comentarios de estimación de cardinalidad (CE) pueden detectar escenarios en los que se debe conservar la optimización del objetivo de fila y mantener este cambio conservando este cambio en el 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. Los comentarios de CE conservan otros escenarios fuera de los patrones de consulta de optimización de objetivos de fila, tal como se detalla en escenarios de comentarios. Actualmente, los comentarios de CE controlan los escenarios de selectividad de predicado que usa el modelo de correlación de la CE y los escenarios de predicado de combinación que controla el modelo de contención de la CE.

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

Problemas conocidos con los comentarios de estimación de cardinalidad (CE)

Problema	Fecha de detección	Estado	Fecha de resolución
Ralentiza el rendimiento de SQL Server después de aplicar la actualización acumulativa 8 para SQL Server 2022 (16.x) en determinadas condiciones. Es posible que observe un uso drástico de la memoria caché del plan junto con aumentos inesperados en el uso de CPU cuando se habilitan los comentarios de CE.	Diciembre de 2023	Resuelto	22 de abril de 2024 (CU 12)

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 se puede observar un aumento en las esperas de RESOURCE_SEMAPHORE_QUERY_COMPILE. También puede observar aumentos constantes en el número de objetos de caché de plan en uso que se aproximan a los límites de caché del plan y borrar manualmente la caché de planes con técnicas como ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHE o DBCC FREEPROCCACHE no ayudan. 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 del número de planes diferentes que se han generado, así como del número de planes que eran aptos para que la característica de comentarios de CE interactúe. Mientras que los comentarios de CE analizan los operadores del plan en busca de estimaciones erróneas significativas del modelo, existe un escenario en el que un plan referenciado puede ser desreferenciado durante esta fase de análisis. Esta situación impide que el plan se quite de la memoria mediante el algoritmo de menos usado recientemente (LRU). El mecanismo LRU de una manera en que SQL Server aplica directivas de expulsión de planes. SQL Server también elimina planes de la memoria si el sistema está bajo 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 en uso de la caché de planes y que se marcan como desfasadas desde planes de SQL o 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. La frecuencia de aciertos de caché de planes disminuyen, así como el número de compilaciones en relación con el número de solicitudes por lotes por segundo. Las siguientes consultas se pueden usar para supervisar el sistema a lo largo del tiempo. Es necesario supervisar la frecuencia de aciertos de caché (caídas imprevistas), los objetos de caché en uso (aumentos en el recuento hasta niveles que se aproximan a 50 000 sin disminuir) y una relación solicitudes por lotes/s inferior a la esperada en comparación con un aumento en 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 reclamar la memoria caché del plan que ha tomado este problema, se debe 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 los comentarios de CE en el nivel de base de datos, use la configuración con ámbito de base de datos de CE_FEEDBACK. 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

• Comentarios de 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)