Commentaires sur l’estimation de cardinalité (CE)

  • Article

S’applique à :SQL Server 2022 (16.x) et versions plus récentes.

À compter de SQL Server 2022 (16.x), le retour d’estimation de cardinalité (CE) fait partie de la famille intelligente de fonctionnalités de traitement des requêtes et traite les plans d’exécution de requête non optimaux pour les requêtes répétées lorsque ces problèmes résultent d’hypothèses incorrectes du modèle CE. Ce scénario permet de réduire les risques de régression liés à la mise à niveau de l’estimation de cardinalité par défaut à partir d’anciennes versions du Moteur de base de données.

Étant donné qu’aucun ensemble unique de modèles et d’hypothèses CE ne peut prendre en charge la vaste gamme de charges de travail client et de distributions de données, les commentaires CE fournissent une solution adaptable basée sur les caractéristiques du runtime de requête. Les commentaires CE identifient et utilisent une hypothèse de modèle qui correspond mieux à une requête et à une distribution de données spécifiques pour améliorer la qualité du plan d’exécution des requêtes. Actuellement, les commentaires CE peuvent identifier les opérateurs de plan où le nombre estimé de lignes et le nombre réel de lignes sont très différents. Les commentaires sont appliqués lorsque des erreurs significatives d’estimation de modèle se produisent et qu’il existe un autre modèle viable à essayer.

Pour obtenir d’autres fonctionnalités de commentaires sur les requêtes, consultez commentaires sur l’octroi de mémoire et degré de parallélisme (DOP).

Comprendre les commentaires de carte inalité (CE)

L’estimation de cardinalité (CE) est la façon dont l’optimiseur de requête peut estimer le nombre total de lignes traitées à chaque niveau d’un plan de requête. L’estimation de la cardinalité dans SQL Server est dérivée principalement d’histogrammes générés lors de la création manuelle ou automatique d’index ou de statistiques. Parfois, SQL Server utilise également des informations de contrainte et des réécritures logiques de requêtes pour déterminer carte inalité.

Différentes versions du Moteur de base de données utilisent différentes hypothèses de modèle CE en fonction de la façon dont les données sont distribuées et interrogées. Pour plus d’informations, consultez les versions de CE.

Implémentation des commentaires d’estimation de cardinalité (CE)

Les commentaires d’estimation de cardinalité (CE) apprennent quelles hypothèses du modèle CE sont optimales au fil du temps, puis appliquent l’hypothèse historiquement la plus correcte :

  1. Les commentaires CE identifient les hypothèses liées au modèle et évaluent si elles sont précises pour les requêtes répétées.

  2. Si une hypothèse semble incorrecte, une exécution ultérieure de la même requête est testée avec un plan de requête qui ajuste l’hypothèse du modèle CE impacté et vérifie si elle est utile. Nous identifions l’erreur en examinant les lignes réelles et estimées des opérateurs de plan. Toutes les erreurs ne peuvent pas être corrigées par les variantes de modèle disponibles dans les commentaires CE.

  3. S’il améliore la qualité du plan, l’ancien plan de requête est remplacé par un plan de requête qui utilise l’indicateur de requête USE HINT approprié qui ajuste le modèle d’estimation, implémenté par le biais du mécanisme d’indicateur de Magasin des requêtes.

Seuls les commentaires vérifiés sont conservés. Les commentaires CE ne sont pas utilisés pour cette requête si l’hypothèse du modèle ajusté entraîne une régression du niveau de performance. Dans ce contexte, une requête annulée par l’utilisateur est également perçue comme une régression.

Scénarios de commentaires d’estimation de cardinalité (CE)

Les commentaires d’estimation de cardinalité (CE) traitent des problèmes de régression perçus résultant d’hypothèses incorrectes du modèle CE lors de l’utilisation de la ce (CE120 ou supérieure) par défaut et peuvent utiliser de manière sélective différentes hypothèses de modèle. Les scénarios incluent la corrélation, l’endiguement de jointure et l’objectif de ligne d’optimiseur.

Corrélation des commentaires d’estimation de cardinalité (CE)

Lorsque l’optimiseur de requête estime la sélectivité des prédicats sur une table ou une vue donnée ou le nombre de lignes satisfaisant pour le prédicat dit, il utilise des hypothèses de modèle de corrélation. Ces hypothèses peuvent être que les prédicats sont les suivants :

  • Entièrement indépendants (valeur par défaut pour CE70), où la cardinalité est calculée en multipliant les sélections de tous les prédicats.

  • Partiellement corrélés (valeur par défaut pour CE120 et supérieure), où la cardinalité est calculée à l’aide d’une variation de retour exponentiel, en classant les sélections de la plupart vers le prédicat le moins sélectif.

  • Entièrement corrélés, où la cardinalité est calculée à l’aide des sélections minimales pour tous les prédicats.

L’exemple suivant utilise une corrélation partielle lorsque la compatibilité de la base de données a la valeur 120 ou supérieure :

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

Lorsque la compatibilité de la base de données est définie sur 160 et que la corrélation par défaut est utilisée, les commentaires CE tentent de déplacer la corrélation vers la direction correcte d’une étape à la fois selon que la carte inalité estimée a été sous-estimée ou surestimée par rapport au nombre réel de lignes. Utilisez la corrélation complète si un nombre réel de lignes est supérieur à la cardinalité estimée. Utilisez l’indépendance totale si un nombre réel de lignes est inférieur à la cardinalité estimée.

Pour plus d’informations, consultez les versions de CE.

Les commentaires d’estimation de cardinalité (CE) rejoignent l’endiguement

Lorsque l’optimiseur de requête estime la sélectivité des prédicats de jointure et des prédicats de filtre applicables, il utilise des hypothèses de modèle de confinement. Nous supposons que :

  • L’isolement simple (valeur par défaut pour CE70) suppose que les prédicats de jointure sont entièrement corrélés, où la sélectivité du filtre est calculée en premier, puis la sélectivité de jointure est prise en compte.

  • L’endiguement de base (valeur par défaut pour CE120 et versions ultérieures) suppose qu’aucune corrélation entre les prédicats de jointure et les filtres en aval, où la sélectivité de jointure est calculée en premier, puis la sélectivité du filtre est prise en compte.

L’exemple suivant utilise une autonomie de base lorsque la compatibilité de la base de données a la valeur 120 ou supérieure :

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

Pour plus d’informations, consultez les versions de CE.

Commentaires de l’estimation de cardinalité (CE) et de l’objectif de ligne de l’optimiseur de requête

Lorsque l’optimiseur de requête estime la cardinalité d’un plan d’exécution, il suppose généralement que toutes les lignes éligibles de toutes les tables doivent être traitées. Toutefois, certains modèles de requête entraînent la recherche d’un plan qui retourne un plus petit nombre de lignes pour réduire les E/S. Si la requête spécifie un nombre cible de lignes (objectif de ligne) qui peuvent être attendues lors de l’exécution à l’aide d’un TOPindicateur IN de requête ou EXISTS d’mot clé s, de l’indicateur FAST de requête ou d’une SET ROWCOUNT instruction, cet objectif de ligne est utilisé dans le cadre du processus d’optimisation de la requête, comme dans l’exemple suivant :

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

Lorsque le plan d’objectif de ligne est appliqué, le nombre estimé de lignes du plan de requête est réduit, car l’optimiseur de requête suppose qu’un plus petit nombre de lignes doit être traité pour atteindre l’objectif de ligne.

Bien que l’objectif de ligne soit une stratégie d’optimisation bénéfique pour certains modèles de requête, si les données ne sont pas distribuées uniformément, plus de pages peuvent être analysées que estimées, ce qui signifie que l’objectif de ligne devient inefficace. Les commentaires CE peuvent désactiver l’analyse de l’objectif de ligne et activer une recherche lorsque cette inefficacité est détectée.

Dans le plan d’exécution, il n’existe aucun attribut spécifique aux commentaires CE, mais il y aura un attribut répertorié pour l’indicateur de Magasin des requêtes. Recherchez l’être QueryStoreStatementHintSourceCE feedback.

Considérations relatives aux commentaires d’estimation de carte inalité (CE)

  • Pour activer les commentaires d’estimation de carte inalité (CE), activez le niveau de compatibilité de la base de données 160 pour la base de données à laquelle vous êtes connecté lors de l’exécution de la requête. Le Magasin des requêtes doit être activé et en mode READ_WRITE pour chaque base de données où les commentaires CE sont utilisés.

  • Pour désactiver les commentaires CE au niveau de la base de données, utilisez la configuration délimitée à la CE_FEEDBACKbase de données. Par exemple, dans la base de données utilisateur :

    ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

  • Pour désactiver les commentaires CE au niveau de la requête, utilisez l’indicateur de requête DISABLE_CE_FEEDBACK.

L’activité de commentaires CE est visible via les événements XEvents query_feedback_analysis et query_feedback_validation.

Les conseils définis par les commentaires CE peuvent être suivis à l’aide de l’affichage catalogue sys.query_store_query_hints.

Les informations de commentaires peuvent être suivies à l’aide de l’affichage catalogue sys.query_store_plan_feedback .

Si une requête a un plan de requête forcé via Magasin des requêtes, les commentaires CE ne seront pas utilisés pour cette requête.

Si une requête utilise des indicateurs de requête codés en dur ou utilise un ensemble de conseils du Magasin des requêtes définis par l’utilisateur, les commentaires CE ne seront pas utilisés pour cette requête. Pour plus d’informations, consultez Conseils (Transact-SQL) - Requête et Conseil du Magasin des requêtes.

À compter de SQL Server 2022 (16.x), lorsque Magasin des requêtes pour les réplicas secondaires est activé, les commentaires CE ne sont pas réplicas prenant en charge les réplicas secondaires dans les groupes de disponibilité. Actuellement, les commentaires CE bénéficient uniquement des réplicas principaux. Lors du basculement, les commentaires appliqués aux réplicas principaux ou secondaires sont perdus. Pour plus d’informations, consultez Magasin des requêtes pour les réplicas secondaires.

Persistance pour les commentaires d’estimation de carte inalité (CE)

S’applique à : SQL Server (à compter de SQL Server 2022 (16.x))

Les commentaires d’estimation de cardinalité (CE) peuvent détecter des scénarios lorsque l’optimisation de l’objectif de ligne doit être conservée et conserver cette modification en la conservant dans le magasin de requêtes sous la forme d’un indicateur de magasin de requêtes. La nouvelle optimisation sera utilisée pour les prochaines exécutions de la requête. Les commentaires CE conservent d’autres scénarios en dehors des modèles de requête d’optimisation des objectifs de ligne, comme détaillé dans les scénarios de commentaires. Les commentaires CE gèrent actuellement des scénarios de sélectivité de prédicat utilisés par le modèle de corrélation de la CE et joignent des scénarios de prédicat gérés par le modèle de confinement de la CE.

Cette fonctionnalité a été introduite dans SQL Server 2022 (16.x), mais cette amélioration des performances est disponible pour les requêtes qui fonctionnent dans le niveau de compatibilité de la base de données 160 ou supérieur, ou l’indicateur QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n de 160 et versions ultérieures, et quand Magasin des requêtes est activé pour la base de données et est dans un état « lecture écriture ».

Problèmes connus liés aux commentaires d’estimation de carte inalité (CE)

Problème Date de la détection Statut Date de la résolution
Ralentissement des performances de SQL Server après avoir appliqué la mise à jour cumulative 8 pour SQL Server 2022 (16.x) dans certaines conditions. Vous pouvez rencontrer une utilisation spectaculaire de la mémoire du cache de plan, ainsi que des augmentations inattendues de l’utilisation du processeur lorsque les commentaires CE sont activés. Décembre 2023 A une solution de contournement

Détails des problèmes connus

Ralentissement des performances de SQL Server après avoir appliqué la mise à jour cumulative 8 pour SQL Server 2022 dans certaines conditions

À compter de SQL Server 2022 (16.x) Mise à jour cumulative 8, SQL Server peut présenter des augmentations inattendues de l’utilisation du processeur et de la mémoire. En outre, une augmentation des attentes de RESOURCE_SEMAPHORE_QUERY_COMPILE peut également être observée. Vous remarquerez peut-être également une augmentation constante du nombre d’objets Plan Cache utilisés dans cette approche des limites du cache de plan et l’effacement manuel du cache de plan avec des techniques telles que ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHEou DBCC FREEPROCCACHE ne pas fournir d’assistance. Ce comportement n’a été observé que par un petit nombre de clients.

Ce problème n’affecte pas toutes les charges de travail et dépend du nombre de différents plans générés ainsi que du nombre de plans éligibles à la fonctionnalité de commentaires de l’CE. Pendant la période pendant laquelle les commentaires CE analyse analysent les opérateurs de plan où des misestimations de modèle significatives se sont produites, il existe un scénario dans lequel, au cours de cette phase d’analyse, un plan référencé peut devenir déréférencé en mémoire sans autoriser le plan à être supprimé par la suite de la mémoire par le biais de l’algorithme le moins récemment utilisé (LRU) normal. Le mécanisme LRU permet à SQL Server d’appliquer des stratégies d’éviction de plan. SQL Server supprime également les plans de la mémoire si le système est sous pression mémoire. Lorsque SQL Server tente de supprimer les plans qui ont été déréférés de manière incorrecte, il ne peut pas supprimer ces plans du cache du plan, ce qui entraîne la croissance du cache du cache. Le cache croissant peut commencer à provoquer des compilations supplémentaires qui utilisent finalement davantage de processeur et de mémoire. Pour plus d’informations, consultez Plan Cache Internals.

Symptôme : le nombre d’entrées de cache de plan en cours d’utilisation et marqués comme sale des plans SQL ou des plans d’objet augmente au fil du temps à 50 000 ou plus. Si vous observez des entrées de cache de plan qui commencent à approcher ce niveau, ainsi que des augmentations inattendues de l’utilisation du processeur, votre système peut rencontrer ce problème. Un correctif associé a été fourni dans SQL Server 2022 (16.x) Mise à jour cumulative 9. Voir Ko5030731. Le correctif a tenté de résoudre un problème dans lequel les entrées du cache de plan sont supprimées lorsque les commentaires d’estimation de cardinalité (CE) tentent d’obtenir le profil associé, ce qui provoque une altération de la mémoire. Des correctifs supplémentaires pour ce problème seront disponibles dans une prochaine mise à jour cumulative.

Pour surveiller le nombre d’entrées de cache de plan que votre système utilise, les exemples suivants peuvent être utilisés comme point dans le temps du nombre d’entrées de cache de plan qui existent. Par exemple, regarder le nombre d’entrées du cache de plan marquées comme sale, régulièrement au fil du temps est un moyen de surveiller ce phénomène.

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;

Un autre ensemble de requêtes qui fournira également les mêmes informations que l’exemple précédent, tout en vous permettant d’observer des métriques de performances supplémentaires. Les ratios d’accès au cache de plan diminuent, ainsi que le nombre de compilations par rapport au nombre de requêtes par lot/s. Les requêtes suivantes peuvent être utilisées pour surveiller votre système au fil du temps. Gardez un œil sur le taux d’accès du cache (baisses inattendues), les objets cache en cours d’utilisation (augmentations du nombre à des niveaux approchant de 50 000 sans diminuer) et un ratio inférieur aux demandes batch/s attendues par rapport à une augmentation des compilations/s.

--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' 
);

Solution de contournement : la fonctionnalité commentaires CE peut être désactivée au niveau de la base de données jusqu’à ce que des correctifs supplémentaires soient disponibles si votre système rencontre les symptômes décrits précédemment. Pour récupérer la mémoire du cache du plan qui avait été prise en charge par ce problème, un redémarrage de l’instance SQL Server est nécessaire. Cette action de redémarrage peut être effectuée après la désactivation de la fonctionnalité Commentaires CE. Pour désactiver les commentaires CE au niveau de la base de données, utilisez la configuration délimitée à la CE_FEEDBACKbase de données. Par exemple, dans la base de données utilisateur :

ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

Commentaires et problèmes de création de rapports

Pour les commentaires ou les questions, e-mail CEFfeedback@microsoft.com

Contenu connexe