Retour d'expérience sur l'estimation de la cardinalité (CE)

S’applique à : SQL Server 2022 (16.x) et versions ultérieures d’Azure SQL DatabaseAzure SQL Managed Instance

À compter de SQL Server 2022 (16.x), le retour d’expérience sur l’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êtes 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 à l’estimateur de cardinalité (CE) par défaut lors de la mise à niveau depuis 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 dans lesquels le nombre estimé de lignes et le nombre réel de lignes sont très différents. Le retour d’expérience est appliqué 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 retour d’expérience sur les requêtes, consultez Retour d’expérience sur l’allocation de mémoire et Degré de parallélisme (DOP).

Comprendre la rétroaction sur l’estimation de cardinalité (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 contraintes et des réécritures logiques de requêtes pour déterminer la cardinalité.

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 du CE.

Mise en œuvre de la rétroaction sur l’estimation de cardinalité (CE)

Le retour d’expérience de l’estimation de cardinalité (CE) apprend quelles hypothèses du modèle CE sont optimales au fil du temps, puis applique 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 à l’aide des 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’indicateurs du 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 rétroaction sur l’estimation de cardinalité (CE)

Le retour d’expérience sur l’estimation de la cardinalité (CE) traite des problèmes de régression perçus résultant d’hypothèses incorrectes du modèle CE lors de l’utilisation du CE par défaut (CE120 ou version ultérieure) et peut utiliser de manière sélective différentes hypothèses de modèle. Les scénarios incluent la corrélation, la contenance de jointure et l’objectif de ligne de l’optimiseur.

Corrélation de la rétroaction sur l’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é (valeur par défaut pour CE120 et les versions ultérieures), où la cardinalité est calculée à l’aide d’une variante du repli exponentiel, en classant les sélectivités du prédicat le plus sélectif au 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 le niveau de compatibilité de la base de données est défini sur 160 et que la corrélation par défaut est utilisée, le retour d’information CE tente d’ajuster la corrélation dans la bonne direction, une étape à la fois, selon que la cardinalité 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 du marquage CE.

Rétroaction sur l’estimation de la cardinalité (CE) pour la contenance des jointures

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.

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

L’exemple suivant utilise le confinement partiel lorsque le niveau de compatibilité de la base de données est défini sur 120 ou une valeur 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 du CE.

Rétroaction sur l’estimation de cardinalité (CE) et l’objectif de lignes 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 schémas de requête amènent l’Optimiseur de requêtes à rechercher un plan qui retournera un nombre plus restreint de lignes afin de réduire les E/S. Si la requête spécifie un nombre cible de lignes (objectif de ligne) qui peut être attendu au moment du runtime à l’aide d’un indicateur de mots de passe TOP, IN ou EXISTS, l’indicateur de requête FAST ou une instruction SET ROWCOUNT, cet objectif de ligne est utilisé dans le cadre du processus d’optimisation des requêtes, 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 le row goal soit une stratégie d’optimisation bénéfique pour certains types de requêtes, si les données ne sont pas réparties uniformément, il se peut que plus de pages soient parcourues que prévu, ce qui rend le row goal inefficace. Le feedback CE peut désactiver le balayage avec objectif de ligne et activer une opération de recherche lorsque cette inefficacité est détectée.

Dans le plan d’exécution, il n’existe aucun attribut spécifique au retour CE, mais un attribut figure pour l’indication Magasin des requêtes. Vérifiez que le QueryStoreStatementHintSource est CE feedback.

Considérations relatives à la rétroaction sur l’estimation de cardinalité (CE)

  • Pour activer la rétroaction sur l’estimation de cardinalité (CE), définissez le niveau de compatibilité sur 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 dans le mode READ_WRITE pour chaque base de données où le feedback CE est utilisé.

  • Pour désactiver les commentaires CE au niveau de la base de données, utilisez la CE_FEEDBACKconfiguration de portée base de données. Par exemple, dans la base de données des utilisateurs :

    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 rétroaction peuvent être suivies à l’aide de la vue de catalogue sys.query_store_plan_feedback.

Si une requête a un plan de requête forcé via Magasin des requêtes, le retour CE n’est pas utilisé 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 sont pas utilisés pour cette requête. Pour plus d’informations, consultez indicateurs de requête et indicateurs du Magasin des requêtes.

À partir de SQL Server 2022 (16.x), lorsque Magasin des requêtes pour les réplicas secondaires est activé, la rétroaction CE n’est pas adaptée aux réplicas secondaires dans les groupes de disponibilité. Actuellement, les retours CE ne profitent qu’aux réplicas primaires. Lors du basculement, le retour d’expérience appliqué aux réplicas principaux ou secondaires est perdu. Le Magasin des requêtes est disponible sur les réplicas secondaires du groupe de disponibilité à partir de SQL Server 2025 (17.x). Pour plus d’informations, consultez Magasin des requêtes pour réplicas secondaires lisibles.

Persistance de la rétroaction sur l’estimation de la cardinalité (CE)

S'applique à : SQL Server 2022 (16.x) et versions ultérieures, base de données Azure SQL, Azure SQL Managed Instance

Le retour d’estimation de cardinalité (CE) peut détecter les cas où l’optimisation des objectifs de lignes doit être conservée de façon persistante, et enregistrer cette modification dans le Magasin des requêtes sous la forme d’une indication Magasin des requêtes. La nouvelle optimisation est utilisée pour les prochaines exécutions de la requête. Les commentaires CE s’appliquent toujours à d’autres scénarios en dehors des modèles de requête liés à l’optimisation des objectifs de ligne, comme indiqué dans les scénarios de commentaires. La rétroaction de la CE gère actuellement des scénarios de sélectivité de prédicat utilisés par le modèle de corrélation de la CE, ainsi que des scénarios de prédicats de jointure gérés par le modèle de contenance de la CE.

Cette fonctionnalité a été introduite dans SQL Server 2022 (16.x) ; cependant, cette amélioration des performances est disponible pour les requêtes qui s’exécutent avec un niveau de compatibilité de base de données de 160 ou supérieur, ou avec l’indicateur QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n défini sur 160 ou plus, et lorsque Magasin des requêtes est activé pour la base de données et est en mode « lecture-écriture ».

Problèmes connus avec le retour d’expérience sur l’estimation de la cardinalité (CE)

Problème Date de la détection Statut Date de 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 observer une forte consommation de mémoire du cache des plans, ainsi que des augmentations inattendues de l’utilisation du processeur lorsque le retour d’information CE est activé. Décembre 2023 Résolu 22 avril 2024 (CU 12)

Détails du problème connu

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), la 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 du cache de plans en cours d’utilisation, qui se rapproche des limites du cache de plans, et l’effacement manuel du cache de plans à l’aide de techniques telles que ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHE ou DBCC FREEPROCCACHE n’apporte aucune amélioration. Ce comportement n’a été observé que par quelques clients.

Ce problème n’affecte pas toutes les charges de travail et dépend du nombre de plans différents générés, ainsi que du nombre de plans pour lesquels la fonctionnalité de retour d’information CE pouvait s’activer. Bien que les commentaires CE analysent les opérateurs de plan afin de détecter des erreurs importantes d’estimation du modèle, il existe un scénario dans lequel un plan référencé peut être déréférencé pendant cette phase d’analyse. Cette situation empêche le plan d’être supprimé de la mémoire avec l’algorithme LRU (dernier récemment utilisé) habituel. Le mécanisme LRU est l’un des moyens par lesquels SQL Server applique des stratégies d’éviction des plans. 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 déréférencés de manière incorrecte, il ne peut pas supprimer ces plans du cache du plan, ce qui entraîne la croissance du cache. Le cache croissant peut commencer à provoquer des compilations supplémentaires qui utilisent finalement davantage d’UC et de mémoire. Pour plus d’informations, consultez Fonctionnement interne du cache des plans.

Symptôme : le nombre d’entrées du cache de plans utilisées et marquées comme « dirty », issues de plans SQL ou de plans d’objet, augmente au fil du temps jusqu’à atteindre 50 000 entrées ou plus. Si vous observez des entrées de cache du 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 est fourni avec SQL Server 2022 (16.x) Mise à jour cumulative 12. Voir KB5033663.

Pour surveiller le nombre d’entrées du cache des plans utilisées par votre système, les exemples suivants peuvent servir d’aperçu à un instant donné du nombre d’entrées du cache des plans existantes. Par exemple, regarder le nombre d’entrées du cache du 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 fournit é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 taux de réussite du cache des plans diminuent, ainsi que le nombre de compilations par rapport au nombre de requêtes batch/s. Les requêtes suivantes permettent de surveiller votre système au fil du temps. Gardez un œil sur le Cache Hit Ratio (baisses inattendues), les objets du cache en cours d’utilisation (augmentations du nombre jusqu’à des niveaux proches de 50 000 sans redescendre) et un taux de Batch Requests/sec inférieur à celui attendu, par rapport à une hausse de Compilations/sec.

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

Si votre système continue à rencontrer les symptômes qui ont été décrits précédemment, après l’application de la mise à jour cumulative 12 KB5033663, la fonctionnalité de retour d’expérience sur la CE peut être désactivée au niveau de la base de données.

Pour récupérer la mémoire du cache du plan 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é de retour d’expérience CE. Pour désactiver les commentaires CE au niveau de la base de données, utilisez la CE_FEEDBACKconfiguration de portée base de données. Par exemple, dans la base de données des utilisateurs :

ALTER DATABASE SCOPED CONFIGURATION
SET CE_FEEDBACK = OFF;

Retour d’expérience et problèmes de création de rapports

Pour tout retour d’expérience ou toute question, veuillez envoyer un e-mail à CEFfeedback@microsoft.com

Contenu connexe

  • Last updated on