Retour d'expérience sur l'estimation de la 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’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 à 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, le retour d’expérience sur la CE peut identifier les opérateurs de plan où 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).

Comprenez le retour d’expérience sur l’estimation de la 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 de CE.

Implémentation du retour d’expérience d’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 par les variantes de modèle disponibles dans le retour d’expérience sur la 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 retour d’expérience sur l’estimation de la 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 non-imbrication et l’objectif de ligne Optimiseur.

Corrélation du retour d’expérience sur l’estimation de la 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, le retour d’expérience sur la CE tente de déplacer la corrélation vers la direction correcte à la fois en fonction de la sous-estimation ou de la surestimation du 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.

Non-imbrication du retour d’expérience sur l’estimation de la cardinalité (CE)

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’autonomie simple (valeur par défaut pour CE120 et versions supérieures) suppose qu’il n’existe 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é de jointure est factorisée.

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.

Retour d’expérience sur 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 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 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 qu’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 au retour d’expérience sur la CE, mais il y aura un attribut répertorié pour l’indicateur de Magasin des requêtes. Recherchez le QueryStoreStatementHintSource pour être CE feedback.

Considérations relatives au retour d’expérience d’estimation de cardinalité (CE)

  • Pour activer le retour d’expérience sur l’estimation de cardinalité (CE), activez le niveau de compatibilité de la base de données à 160 pour la base de données à laquelle vous vous connectez lors de l’exécution de la requête. La Magasin des requêtes doit être activé et en mode READ_WRITE pour chaque base de données où le retour d’expérience sur la CE est utilisé.

  • Pour désactiver le retour d’expérience sur la CE au niveau de la base de données, utilisez la CE_FEEDBACKconfiguration étendue à la base de données. Par exemple, à partir de 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 sur le retour d’expérience 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.

À partir de SQL Server 2022 (16.x), lorsque le Magasin des requêtes pour les réplicas secondaires est activé, le retour d’expérience sur le CE ne prend pas en charge les réplicas pour les réplicas secondaires dans les groupes de disponibilité. Actuellement, le retour d’expérience sur la CE bénéficie uniquement des réplicas principaux. Lors du basculement, le retour d’expérience appliqué aux réplicas principaux ou secondaires est perdu. Pour plus d’informations, consultez Magasin des requêtes pour réplicas secondaires.

Persistance relative au retour d’expérience sur l’estimation de la cardinalité (CE)

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

Le retour d’expérience sur l’estimation de cardinalité (CE) peut 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. Le retour d’expérience sur la CE conserve 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 retour d’expérience. Le retour d’expérience sur 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 et joint des scénarios de prédicat gérés par le modèle d’autonomie de la CE.

Cette caractéristique a été introduite dans SQL Server 2022 (16.x) . cependant, 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 postérieur, ou l’indicateur QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n de 160 et postérieur, et quand le magasin des requêtes est activé pour la base de données et est dans un état « 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 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 le retour d’expérience sur la CE est activé. Décembre 2023 (Mise à jour de mai 2024) A une solution de contournement

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 du plan 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 FREESYSTEMCACHE ou 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 retour d’expérience sur la CE. Pendant la période pendant laquelle le retour d’expérience sur la CE analyse les opérateurs de plan où des mauvaises estimations 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é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, voir Éléments internes du cache du plan.

Symptôme : le nombre d’entrées de cache du plan en cours d’utilisation et marquées 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 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 a été fourni avec SQL Server 2022 (16.x) Mise à jour cumulative 12. Voir KB5033663.

Pour surveiller le nombre d’entrées de cache du 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 du plan qui existent. 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 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 du plan diminuent, ainsi que le nombre de compilations par rapport au nombre de requêtes de lots/sec. Les requêtes suivantes peuvent être utilisées pour surveiller votre système au fil du temps. Gardez un œil sur le Cache Hit Ratio (baisses inattendues), les objets mis en cache en cours d’utilisation (augmentations du nombre à des niveaux approchant de 50 000 sans diminuer) et un ratio inférieur aux Requêtes Batch/sec attendues par rapport à une augmentation des 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 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 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 une fois la fonctionnalité de retour d’expérience sur la CE désactivée. Pour désactiver le retour d’expérience sur la CE au niveau de la base de données, utilisez la CE_FEEDBACKconfiguration étendue à la base de données. Par exemple, à partir de la base de données utilisateur :

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