Diminution des performances des requêtes après la mise à niveau de SQL Server 2012 ou antérieur vers 2014 ou ultérieur

Après avoir mis à niveau SQL Server de 2012 ou une version antérieure vers 2014 ou une version ultérieure, vous pouvez rencontrer le problème suivant : la plupart des requêtes d’origine s’exécutent correctement, mais quelques-unes de vos requêtes s’exécutent plus lentement que dans la version précédente. Bien qu’il existe de nombreuses causes possibles et facteurs de contribution, une cause relativement courante est les changements dans le modèle d’estimation de cardinalité (CE) après la mise à niveau. Des modifications significatives ont été introduites dans les modèles CE à partir de SQL Server 2014.

Cet article fournit des étapes de dépannage et des résolutions pour les problèmes de performances des requêtes qui se produisent lors de l’utilisation de la clé d’administration par défaut, mais qui ne se produisent pas lors de l’utilisation de la version CE héritée.

Résolution des problèmes : identifier si les modifications ce sont le problème et déterminer la raison

Étape 1 : Identifier si le ce par défaut est utilisé

1. Choisissez une requête qui s’exécute plus lentement après la mise à niveau.
2. Exécutez la requête et collectez le plan d’exécution.
3. À partir du plan d’exécution Fenêtre Propriétés, vérifiez CardinalityEstimationModelVersion.
4. La valeur 70 indique la valeur CE héritée, et la valeur 120 ou supérieure indique l’utilisation du ce par défaut.

Si le ce hérité est utilisé, les modifications CE ne sont pas la cause du problème de performances. Si la valeur CE par défaut est utilisée, passez à l’étape suivante.

Étape 2 : Identifier si l’optimiseur de requête peut générer un meilleur plan à l’aide du ce hérité

Exécutez la requête avec le ce hérité. S’il fonctionne mieux que d’utiliser le ce par défaut, passez à l’étape suivante. Si les performances ne s’améliorent pas, les modifications CE ne sont pas la cause.

Étape 3 : Découvrez pourquoi la requête s’effectue mieux avec le ce hérité

Testez les différents indicateurs de requête liés à CE pour votre requête. Pour SQL Server 2014, utilisez les indicateurs de trace correspondants 4137, 9472 et 4139 pour tester la requête. Déterminez quels indicateurs ou indicateurs de trace impactent positivement les performances en fonction de ces tests.

Résolution

Pour résoudre le problème, essayez l’une des méthodes suivantes :

• Optimisez la requête.

  De façon compréhensible, il n’est pas toujours possible de réécrire des requêtes, mais surtout lorsqu’il n’y a que quelques requêtes qui peuvent être réécrites, cette approche doit être le premier choix. Les requêtes écrites de manière optimale fonctionnent mieux, quelles que soient les versions de CE.

• Utilisez les indicateurs de requête identifiés à l’étape 3.

  Cette approche ciblée permet à d’autres charges de travail de tirer parti des hypothèses et améliorations par défaut de CE. En outre, il s’agit d’une option plus robuste que la création d’un repère de plan. Et il ne nécessite pas de Magasin des requêtes (QDS), contrairement à forcer un plan (option la plus robuste).

• Forcez un bon plan.

  Il s’agit d’une option favorable et peut être utilisée pour cibler des requêtes spécifiques. Forcer un plan peut être effectué à l’aide d’un guide de plan ou de QDS. QDS est généralement plus facile à utiliser.

• Utilisez la configuration dans l’étendue de la base de données pour forcer l’ancienne version d’accès à la base de données.

  Il s’agit d’une approche moins recommandée, car il s’agit d’un paramètre à l’échelle de la base de données et s’applique à toutes les requêtes sur cette base de données. Toutefois, il est parfois nécessaire lorsqu’une approche ciblée n’est pas réalisable. Il s’agit certainement de l’option la plus simple à implémenter.

• Utilisez l’indicateur de trace 9841 pour forcer l’héritage de la clé d’exploitation globale. Pour ce faire, utilisez DBCC TRACEON ou définissez l’indicateur de trace comme paramètre de démarrage.

  Il s’agit de l’approche la moins ciblée et ne doit être utilisée qu’en tant qu’atténuation temporaire lorsque vous ne parvenez pas à appliquer l’une des autres options.

Options permettant d’activer le ce hérité

Niveau de requête : Utiliser l’indicateur de requête ou l’option QUERYTRACEON

• Pour SQL Server 2016 SP1 et versions ultérieures, utilisez un indicateur FORCE_LEGACY_CARDINALITY_ESTIMATION pour votre requête, par exemple :

  SELECT * FROM Table1
  WHERE Col1 = 10
  OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
  

• Activez l’indicateur de trace 9481 pour forcer un plan CE hérité. Voici un exemple :

  SELECT * FROM Table1
  WHERE Col1 = 10
  OPTION (QUERYTRACEON 9481)
  

Niveau de base de données : Définir une configuration délimitée ou un niveau de compatibilité

• Pour SQL Server 2016 et versions ultérieures, modifiez la configuration délimitée à la base de données :

    --Force a specific database to use legacy CE
    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
  
    -- Validate what databases use legacy CE
    SELECT name, value
        FROM sys.database_scoped_configurations 
    WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
  

• Modifiez le niveau de compatibilité de la base de données. Il s’agit de la seule option de niveau base de données disponible pour SQL Server 2014. Notez que ce changement a un impact plus important que le CE. Pour déterminer l’impact des modifications de niveau de compatibilité, accédez au niveau de compatibilité ALTER DATABASE (Transact-SQL) et examinez les tables « Différences » dans celle-ci.

  ALTER DATABASE <YourDatabase>
  SET COMPATIBILITY_LEVEL = 110  -- set it to SQL Server 2012 level
  

Niveau du serveur : Utiliser l’indicateur de trace

Utilisez l’indicateur de trace 9481 pour forcer l’ancien serveur à l’échelle du serveur :

--Turn on 
DBCC TRACEON(9481, -1)
--Validate
DBCC TRACESTATUS

Forum aux questions

Q1 : Je suis intéressé par la mise à niveau vers une version plus récente de SQL Server, et je m’inquiète des régressions de performances de l’estimateur de cardinalité. Quelle planification de mise à niveau est recommandée pour réduire les problèmes ?

Pour les bases de données préexistantes s’exécutant à des niveaux de compatibilité inférieurs, le flux de travail recommandé pour la mise à niveau du processeur de requêtes vers un niveau de compatibilité plus élevé est détaillé dans Modifier le mode de compatibilité de la base de données et utiliser les scénarios d’utilisation Magasin des requêtes et Magasin des requêtes. La méthodologie introduite dans l’article s’applique aux déplacements vers 130 ou versions ultérieures pour SQL Server et Azure SQL Database.

Q2 : Je n’ai pas le temps de tester les modifications CE. Que puis-je faire dans ce cas ?

Pour les applications et charges de travail préexistantes, nous vous déconseillons de passer à la version CE par défaut tant que le test de régression suffisant n’a pas été effectué. Si vous avez toujours des doutes, nous vous recommandons de mettre à niveau SQL Server et de passer au dernier niveau de compatibilité disponible. Par précaution, activez également l’indicateur de trace 9481 pour SQL Server 2014 ou configurez la configuration ON étendue à la base de données LEGACY_CARDINALITY_ESTIMATION pour SQL Server 2016 et versions ultérieures jusqu’à ce que vous ayez l’occasion de tester.

Q3 : Existe-t-il des inconvénients liés à l’utilisation permanente de l’ancienne version de la ce ?

Les améliorations et correctifs futurs liés à l’estimateur de cardinalité sont centrés sur les versions plus récentes. La version 70 est un état intermédiaire acceptable. Toutefois, après un test prudent, nous vous recommandons de passer à une version CE plus récente pour bénéficier des derniers correctifs CE. Il existe une probabilité élevée de modifications du plan de requête lors du passage de l’ancienne version DE, donc testez avant d’apporter des modifications aux systèmes de production. Les modifications peuvent améliorer les performances des requêtes dans de nombreux cas, mais dans certains cas, les performances des requêtes peuvent se dégrader.

Q4 : J’ai des milliers de bases de données et ne souhaite pas activer manuellement LEGACY_CARDINALITY_ESTIMATION pour chacun d’eux. Existe-t-il une autre méthode ?

Pour SQL Server 2014, activez l’indicateur de trace 9481 pour utiliser la version CE héritée pour toutes les bases de données, quel que soit le niveau de compatibilité. Pour SQL Server 2016 et versions ultérieures, exécutez la requête suivante pour itérer dans les bases de données. Le paramètre est activé même lorsque la base de données est restaurée ou attachée dans un autre serveur.

SELECT [name], 0 AS [isdone]
INTO #tmpDatabases
FROM master.sys.databases WITH (NOLOCK)
WHERE database_id > 4 AND source_database_id IS NULL AND is_read_only = 0

DECLARE @dbname sysname, @sqlcmd NVARCHAR(500);

WHILE (SELECT COUNT([name]) FROM #tmpDatabases WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [name] FROM #tmpDatabases WHERE isdone = 0

    SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + '; 
        IF (SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''LEGACY_CARDINALITY_ESTIMATION'') = 0
        ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;'
 
    BEGIN TRY
        EXECUTE sp_executesql @sqlcmd
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState, ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    UPDATE #tmpDatabases
    SET isdone = 1
    WHERE [name] = @dbname
END;

Pour Azure SQL Database, vous pouvez créer un ticket de support pour que cet indicateur de trace soit activé au niveau de l’abonnement, mais pas au niveau du serveur.

Q5 : L’exécution avec la version CE héritée m’empêchera-t-elle d’accéder aux nouvelles fonctionnalités ?

Même avec LEGACY_CARDINALITY_ESTIMATION activé, vous aurez toujours accès aux fonctionnalités les plus récentes incluses avec la version de SQL Server et le niveau de compatibilité de la base de données associé. Par exemple, une base de données avec LEGACY_CARDINALITY_ESTIMATION activée s’exécutant au niveau de compatibilité de la base de données 140 sur SQL Server 2017 peut toujours tirer parti de la famille de fonctionnalités de traitement des requêtes adaptatives.

Q6 : Quand est-ce que l’ancienne version de ce sera prise en charge ?

Nous n’avons pas l’intention d’arrêter la prise en charge de l’ancien CE à ce stade. Toutefois, les améliorations et correctifs futurs liés à l’estimateur de cardinalité sont centrés sur les versions plus récentes de la CE.

Q7 : Je n’ai que quelques requêtes régressant avec la valeur CE par défaut, mais la plupart des performances des requêtes sont identiques ou même améliorées. Que dois-je faire ?

Une alternative plus granulaire à l’indicateur de trace de l’étendue du serveur 9481 ou la configuration étendue à la base de données LEGACY_CARDINALITY_ESTIMATION est l’utilisation de la construction USE HINT délimitée par la requête. Pour plus d’informations, consultez l’argument d’indicateur de requête USE HINT dans SQL Server 2016 et USE HINT.

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION vous permet de définir le modèle CE de l’optimiseur de requête sur la version 70, quel que soit le niveau de compatibilité de la base de données. Consultez le niveau de requête : Utiliser l’indicateur de requête ou l’option QUERYTRACEON.

Vous pouvez également forcer un plan CE hérité stocké dans Magasin des requêtes ou utiliser FORCE_LEGACY_CARDINALITY_ESTIMATION conjointement avec un repère de plan.

Q8 : Si les performances des requêtes ont régressé en raison d’un changement de plan lié à des dépassements ou sous-estimations significatifs lors de l’utilisation de la valeur CE par défaut, le problème sera-t-il résolu dans le produit ?

CE est un problème complexe, et les algorithmes s’appuient sur les données moins parfaites disponibles pour les estimations, telles que les statistiques pour les tables et les index. Il n’existe aucune information pour certaines constructions hors modèle telles que les fonctions table et les modèles basés sur de nombreuses hypothèses (telles que la corrélation ou l’indépendance des prédicats et colonnes, la distribution uniforme des données, l’isolement, etc.).

Étant donné les combinaisons illimitées de schéma, de données et de charges de travail client, il est presque impossible de choisir des modèles qui fonctionnent pour tous les cas. Bien que certaines modifications apportées à la version CE par défaut contiennent des bogues (comme n’importe quel autre logiciel) et peuvent être corrigées, d’autres problèmes sont causés par une modification de modèle.

Les modifications apportées aux versions CE, en particulier entre 70 et 120, incluent de nombreux choix différents pour les modèles utilisés. Par exemple, lors de l’estimation des filtres, supposons qu’un certain niveau de corrélation entre les prédicats est supposé que, dans la pratique, cette corrélation existe fréquemment et que le modèle CE 70 sous-estime les résultats dans de tels cas. Bien que ces modifications aient été testées pour de nombreuses charges de travail et amélioré de nombreuses requêtes, pour d’autres requêtes, le ce hérité était une meilleure correspondance, et par conséquent avec la valeur CE par défaut, les régressions de performances peuvent être observées.

Malheureusement, il n’est pas considéré comme un bogue. Dans de tels cas, utilisez une solution de contournement telle que l’optimisation de la requête, comme vous l’avez nécessaire pour ce faire avec l’ancienne version de l’instance si les performances de la requête ne sont pas acceptables, ou forçant un modèle CE précédent ou un plan d’exécution spécifique.

Q9 : Existe-t-il une ressource pour en savoir plus sur les modifications de cardinalité dans le ce par défaut et l’impact sur les performances des requêtes ?

Consultez l’optimisation de vos plans de requête avec l’estimateur de cardinalité SQL Server 2014 pour plus d’informations et lisez la section « Qu’est-ce qui a changé dans SQL Server 2014 ? ».