Diminution des performances des requêtes après la mise à niveau de SQL Server 2012 ou version antérieure vers la version 2014 ou ultérieure
Après avoir mis à niveau SQL Server de 2012 ou d’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 certaines de vos requêtes s’exécutent plus lentement que dans la version précédente. Bien qu’il existe de nombreuses causes et facteurs contributifs possibles, l’une des causes relativement courantes est les modifications apportées au modèle d’estimation de cardinalité (CE) après la mise à niveau. Des changements importants ont été apportés aux modèles CE à partir de SQL Server 2014.
Cet article décrit les étapes de résolution des problèmes de performances des requêtes qui se produisent lors de l’utilisation de l’instance CE par défaut, mais qui ne se produisent pas lors de l’utilisation de l’instance CE héritée.
Remarque
Si toutes les requêtes s’exécutent plus lentement après la mise à niveau, les étapes de résolution des problèmes introduites dans cet article ne sont probablement pas applicables à votre situation.
Résolution des problèmes : Identifier si les modifications de l’EC sont le problème et en déterminer la raison
Étape 1 : Identifier si la ce par défaut est utilisée
- Choisissez une requête qui s’exécute plus lentement après la mise à niveau.
- Exécutez la requête et collectez le plan d’exécution.
- À partir du plan d’exécution Fenêtre Propriétés, case activée CardinalityEstimationModelVersion.
- La valeur 70 indique le ce hérité et la valeur 120 ou supérieure indique l’utilisation de la ce par défaut.
Si le ce hérité est utilisé, les modifications de ce ne sont pas la cause du problème de performances. Si la ce par défaut est utilisée, passez à l’étape suivante.
Étape 2 : Déterminer 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 l’utilisation de la ce par défaut, passez à l’étape suivante. Si les performances ne s’améliorent pas, les modifications de ce n’en sont pas la cause.
Étape 3 : Découvrir pourquoi la requête fonctionne mieux avec l’élément 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 4137, 9472 et 4139 correspondants pour tester la requête. Déterminez les indicateurs ou indicateurs de trace qui ont un impact positif sur 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.
Naturellement, il n’est pas toujours possible de réécrire des requêtes, mais en particulier 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 ce.
Utilisez des 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 ce par défaut. 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 au forçage d’un plan (l’option la plus robuste).
Forcez un bon plan.
Il s’agit d’une option favorable qui peut être utilisée pour cibler des requêtes spécifiques. Le forçage d’un plan peut être effectué à l’aide d’un repère de plan ou DSQ. QDS est généralement plus facile à utiliser.
Utilisez la configuration à l’échelle de la base de données pour forcer l’entrée ce héritée.
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. Pourtant, cela est parfois nécessaire lorsqu’une approche ciblée n’est pas réalisable. C’est certainement l’option la plus simple à implémenter.
Utilisez l’indicateur de trace 9841 pour forcer l’ce hérité globalement. Pour ce faire, utilisez DBCC TRACEON ou définissez l’indicateur de trace en tant que paramètre de démarrage.
Il s’agit de l’approche la moins ciblée et ne doit être utilisée que comme atténuation temporaire lorsque vous ne parvenez pas à appliquer les autres options.
Options permettant d’activer l’option CE héritée
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_ESTIMATIONpour 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 la configuration délimitée ou le niveau de compatibilité
Pour SQL Server 2016 et versions ultérieures, modifiez la configuration étendue à 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 au niveau de la base de données disponible pour SQL Server 2014. Notez que cette modification n’a pas seulement un impact sur l’ENVIRONNEMENT. 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 » qu’il contient.
ALTER DATABASE <YourDatabase> SET COMPATIBILITY_LEVEL = 110 -- set it to SQL Server 2012 level
Remarque
Cette modification affecte toutes les requêtes exécutées dans le contexte de la base de données pour laquelle la configuration est modifiée, sauf si un indicateur de trace ou un indicateur de requête de substitution est utilisé. Les requêtes qui fonctionnent mieux en raison de la ce par défaut peuvent régresser.
Niveau serveur : Utiliser l’indicateur de trace
Utilisez l’indicateur de trace 9481 pour forcer le ce hérité à l’échelle du serveur :
--Turn on
DBCC TRACEON(9481, -1)
--Validate
DBCC TRACESTATUS
Remarque
Cette modification affecte toutes les requêtes exécutées dans le contexte du SQL Server instance, sauf si un indicateur de trace ou un indicateur de requête de substitution est utilisé. Les requêtes qui fonctionnent mieux en raison de la ce par défaut peuvent régresser.
Foire 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 la 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é supérieur 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 plus pour SQL Server et Azure SQL Base de données.
Q2 : Je n’ai pas le temps de tester les modifications de CE. Que puis-je faire dans ce cas ?
Pour les applications et charges de travail préexistantes, nous vous déconseillons de passer à la ce par défaut tant que des tests de régression suffisants n’ont pas été effectués. Si vous avez encore des doutes, nous vous recommandons de continuer à mettre à niveau SQL Server et à passer au dernier niveau de compatibilité disponible. Par mesure de précaution, activez également l’indicateur de trace 9481 pour SQL Server 2014 ou configurez la configuration ON LEGACY_CARDINALITY_ESTIMATION étendue à la base de données pour SQL Server 2016 et versions ultérieures jusqu’à ce que vous ayez la possibilité de tester.
Q3 : Y a-t-il des inconvénients liés à l’utilisation permanente du CE hérité ?
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 minutieux, nous vous recommandons de passer à une version ce plus récente pour tirer parti des correctifs CE les plus récents. Il existe une probabilité élevée de modifications de plan de requête lors du passage de l’environnement d’exécution de l’environnement d’exécution hérité. Par conséquent, 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.
Importante
Le ce par défaut étant le chemin de code main qui recevra un investissement futur et une couverture de test plus approfondie à long terme, n’envisagez donc pas d’utiliser la CE héritée indéfiniment.
Q4 : J’ai des milliers de bases de données et je ne veux pas activer manuellement LEGACY_CARDINALITY_ESTIMATION pour chacune d’elles. Existe-t-il une autre méthode ?
Pour SQL Server 2014, activez l’indicateur de trace 9481 pour utiliser le ce hérité 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 à 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 Base de données, 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 le CE hérité m’empêchera-t-elle d’accéder aux nouvelles fonctionnalités ?
Même si LEGACY_CARDINALITY_ESTIMATION est activé, vous aurez toujours accès aux dernières fonctionnalités incluses dans la version de SQL Server et au niveau de compatibilité de 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 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 l’instance ce héritée sera-t-elle hors support ?
Nous n’avons pas l’intention d’arrêter la prise en charge de l’ancienne 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 ce.
Q7 : Je n’ai que quelques requêtes qui régressent avec la ce par défaut, mais la plupart des performances des requêtes sont identiques, voire améliorées. Que dois-je faire ?
Une alternative plus granulaire à l’indicateur de trace de l’étendue serveur 9481 ou à la configuration LEGACY_CARDINALITY_ESTIMATION étendue à la base de données est l’utilisation de la construction USE HINT délimitée aux requêtes. Pour plus d’informations, consultez use hint query hint argument in SQL Server 2016 et USE HINT.
Remarque
Il existe également une QUERYTRACEON option avec l’indicateur de trace 9481, mais vous devez envisager d’utiliser à la USE HINT place, car il est sémantiquement plus propre et ne nécessite pas d’autorisations spéciales.
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 Niveau de la requête : utiliser l’indicateur de requête ou l’option QUERYTRACEON.
Sinon, s’il n’y a qu’une seule requête qui pose problème avec la ce par défaut, vous pouvez forcer un plan CE hérité stocké dans Magasin des requêtes ou l’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é à un dépassement ou à une sous-estimation significative lors de l’utilisation de la ce par défaut, le problème sera-t-il résolu dans le produit ?
La ce est un problème complexe, et les algorithmes s’appuient sur les données 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 des colonnes, la distribution uniforme des données, l’autonomie, etc.).
Étant donné les combinaisons illimitées de schéma client, de données et de charges de travail, il est presque impossible de choisir des modèles qui fonctionnent pour tous les cas. Bien que certaines modifications apportées à la ce par défaut puissent contenir des bogues (comme n’importe quel autre logiciel) et peuvent être corrigées, d’autres problèmes sont causés par une modification du modèle.
Les modifications apportées aux versions CE, en particulier de 70 à 120, incluent de nombreux choix différents pour les modèles utilisés. Par exemple, lors de l’estimation des filtres, supposons un certain niveau de corrélation entre les prédicats, car, dans la pratique, une telle corrélation existe fréquemment, et le modèle CE 70 sous-estimerait les résultats dans de tels cas. Bien que ces modifications aient été testées pour de nombreuses charges de travail et améliorées de nombreuses requêtes, pour certaines autres requêtes, la ce héritée correspondait mieux et, par conséquent, avec la ce par défaut, des régressions des performances peuvent être observées.
Malheureusement, il n’est pas considéré comme un bogue. Dans de telles situations, utilisez une solution de contournement telle que le réglage de la requête, comme vous deviez le faire avec l’environnement CE hérité si les performances de la requête ne sont pas acceptables, ou forcer 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 changements de cardinalité dans la ce par défaut et l’impact sur les performances des requêtes ?
Pour plus d’informations, consultez Optimisation de vos plans de requête avec l’estimateur de cardinalité SQL Server 2014 et lisez la section « Qu’est-ce qui a changé dans SQL Server 2014 ? ».
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour