Résoudre les problèmes d’une requête qui montre une différence de performances significative entre deux serveurs

Article
06/20/2023

S’applique à : SQL Server

Cet article décrit les étapes de résolution des problèmes de performances où une requête s’exécute plus lentement sur un serveur que sur un autre serveur.

Symptômes

Supposons qu’il existe deux serveurs avec SQL Server installés. L’une des instances SQL Server contient une copie d’une base de données dans l’autre SQL Server instance. Lorsque vous exécutez une requête sur les bases de données sur les deux serveurs, la requête s’exécute plus lentement sur un serveur que sur l’autre.

Les étapes suivantes peuvent vous aider à résoudre ce problème.

Étape 1 : Déterminer s’il s’agit d’un problème courant avec plusieurs requêtes

Utilisez l’une des deux méthodes suivantes pour comparer les performances de deux requêtes ou plus sur les deux serveurs :

Testez manuellement les requêtes sur les deux serveurs :
1. Choisissez plusieurs requêtes pour le test avec la priorité accordée aux requêtes qui sont :
  - Beaucoup plus rapide sur un serveur que sur l’autre.
  - Important pour l’utilisateur/l’application.
  - Fréquemment exécuté ou conçu pour reproduire le problème à la demande.
  - Suffisamment longtemps pour capturer les données qui y sont contenues (par exemple, au lieu d’une requête de 5 millisecondes, choisissez une requête de 10 secondes).
2. Exécutez les requêtes sur les deux serveurs.
3. Comparez le temps écoulé (durée) sur deux serveurs pour chaque requête.
Analysez les données de performances avec SQL Nexus.
1. Collectez les données PSSDiag/SQLdiag ou SQL LogScout pour les requêtes sur les deux serveurs.
2. Importez les fichiers de données collectés avec SQL Nexus et comparez les requêtes des deux serveurs. Pour plus d’informations, consultez Comparaison des performances entre deux collections de journaux (lentes et rapides, par exemple).

Scénario 1 : Une seule requête s’exécute différemment sur les deux serveurs

Si une seule requête s’exécute différemment, le problème est probablement spécifique à la requête individuelle plutôt qu’à l’environnement. Dans ce cas, passez à l’Étape 2 : Collecter des données et déterminer le type de problème de performances.

Scénario 2 : Plusieurs requêtes s’exécutent différemment sur les deux serveurs

Si plusieurs requêtes s’exécutent plus lentement sur un serveur que sur l’autre, la cause la plus probable est les différences de serveur ou d’environnement de données. Accédez à Diagnostiquer les différences d’environnement et vérifiez si la comparaison entre les deux serveurs est valide.

Étape 2 : Collecter des données et déterminer le type de problème de performances

Collecter le temps écoulé, le temps processeur et les lectures logiques

Pour collecter le temps écoulé et le temps processeur de la requête sur les deux serveurs, utilisez l’une des méthodes suivantes qui correspondent le mieux à votre situation :

Pour les instructions en cours d’exécution, case activée total_elapsed_time et cpu_time colonnes dans sys.dm_exec_requests. Exécutez la requête suivante pour obtenir les données :

SELECT 
    req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.cpu_time AS cpu_time_ms
    , req.total_elapsed_time - req.cpu_time AS wait_time
    , req.logical_reads
    , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)  
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
      1, 512)  AS statement_text  
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;

Pour les exécutions antérieures de la requête, case activée last_elapsed_time et last_worker_time colonnes dans sys.dm_exec_query_stats. Exécutez la requête suivante pour obtenir les données :

SELECT t.text,
     (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
     (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
     ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time,
     qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
     qs.total_logical_writes / qs.execution_count AS avg_writes,
     (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions
FROM sys.dm_exec_query_stats qs
     CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE t.text like '<Your Query>%'
-- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped.
ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC

Remarque

Si avg_wait_time affiche une valeur négative, il s’agit d’une requête parallèle.

Si vous pouvez exécuter la requête à la demande dans SQL Server Management Studio (SSMS) ou Azure Data Studio, exécutez-la avec SET STATISTICS TIMEON et SET STATISTICS IOON.

SET STATISTICS TIME ON
SET STATISTICS IO ON
<YourQuery>
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Ensuite, à partir de Messages, vous verrez le temps processeur, le temps écoulé et les lectures logiques comme suit :

  Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 460 ms,  elapsed time = 470 ms.

Si vous pouvez collecter un plan de requête, case activée les données des propriétés du plan d’exécution.
1. Exécutez la requête avec Inclure le plan d’exécution réel activé.
2. Sélectionnez l’opérateur le plus à gauche dans Plan d’exécution.
3. Dans Propriétés, développez la propriété QueryTimeStats .
4. Vérifiez Le temps écoulé et le temps processeur.

Comparez le temps écoulé et le temps processeur de la requête pour déterminer le type de problème pour les deux serveurs.

Type 1 : lié au processeur (exécuteur)

Si le temps processeur est proche, égal ou supérieur au temps écoulé, vous pouvez le traiter comme une requête liée au processeur. Par exemple, si le temps écoulé est de 3 000 millisecondes (ms) et que le temps processeur est de 2900 ms, cela signifie que la majeure partie du temps écoulé est consacrée au processeur. Ensuite, nous pouvons dis-le est une requête liée au processeur.

Exemples de requêtes en cours d’exécution (liées au processeur) :

Temps écoulé (ms)	Temps processeur (ms)	Lectures (logique)
3200	3000	300000
1080	1000	20

Les lectures logiques ( lecture des pages de données/d’index dans le cache) sont le plus souvent les moteurs de l’utilisation du processeur dans SQL Server. Il peut y avoir des scénarios où l’utilisation du processeur provient d’autres sources : une boucle while (dans T-SQL ou un autre code comme des objets XProcs ou SQL CRL). Le deuxième exemple du tableau illustre un tel scénario, où la majorité du processeur ne provient pas de lectures.

Remarque

Si le temps processeur est supérieur à la durée, cela indique qu’une requête parallèle est exécutée . plusieurs threads utilisent le processeur en même temps. Pour plus d’informations, consultez Requêtes parallèles - exécuteur ou serveur.

Type 2 : En attente d’un goulot d’étranglement (serveur)

Une requête attend un goulot d’étranglement si le temps écoulé est considérablement supérieur au temps processeur. Le temps écoulé inclut le temps d’exécution de la requête sur le processeur (temps processeur) et le temps d’attente de la libération d’une ressource (temps d’attente). Par exemple, si le temps écoulé est de 2 000 ms et que le temps processeur est de 300 ms, le temps d’attente est de 1700 ms (2000 - 300 = 1700). Pour plus d’informations, consultez Types d’attente.

Exemples de requêtes en attente :

Temps écoulé (ms)	Temps processeur (ms)	Lectures (logique)
2000	300	28000
10080	700	80000

Requêtes parallèles : exécuteur ou serveur

Les requêtes parallèles peuvent utiliser plus de temps processeur que la durée totale. L’objectif du parallélisme est de permettre à plusieurs threads d’exécuter simultanément des parties d’une requête. En une seconde d’horloge, une requête peut utiliser huit secondes de temps processeur en exécutant huit threads parallèles. Par conséquent, il devient difficile de déterminer une requête liée au processeur ou en attente en fonction du temps écoulé et de la différence de temps processeur. Toutefois, en règle générale, suivez les principes énumérés dans les deux sections ci-dessus. Le résumé est le suivant :

Si le temps écoulé est beaucoup plus élevé que le temps processeur, considérez-le comme un serveur.
Si le temps processeur est beaucoup plus élevé que le temps écoulé, considérez-le comme un exécuteur.

Exemples de requêtes parallèles :

Temps écoulé (ms)	Temps processeur (ms)	Lectures (logique)
1200	8100	850000
3080	12300	1500000

Étape 3 : Comparer les données des deux serveurs, déterminer le scénario et résoudre le problème

Supposons qu’il existe deux machines nommées Server1 et Server2. Et la requête s’exécute plus lentement sur Server1 que sur Server2. Comparez les temps des deux serveurs, puis suivez les actions du scénario qui correspond le mieux au vôtre dans les sections suivantes.

Scénario 1 : La requête sur Server1 utilise plus de temps processeur, et les lectures logiques sont plus élevées sur Server1 que sur Server2

Si le temps processeur sur Server1 est beaucoup plus élevé que sur Server2 et que le temps écoulé correspond étroitement au temps processeur sur les deux serveurs, il n’y a pas d’attente ou de goulot d’étranglement majeur. L’augmentation du temps processeur sur Server1 est probablement due à une augmentation des lectures logiques. Une modification significative des lectures logiques indique généralement une différence dans les plans de requête. Par exemple :

Serveur	Temps écoulé (ms)	Temps processeur (ms)	Lectures (logique)
Serveur1	3100	3000	300000
Serveur2	1100	1000	90200

Action : Vérifier les plans d’exécution et les environnements

Comparez les plans d’exécution de la requête sur les deux serveurs. Pour ce faire, utilisez l’une des deux méthodes :
- Comparez visuellement les plans d’exécution. Pour plus d’informations, consultez Afficher un plan d’exécution réel.
- Enregistrez les plans d’exécution et comparez-les à l’aide de SQL Server Management Studio fonctionnalité de comparaison de plans.
Comparer les environnements. Différents environnements peuvent entraîner des différences de plan de requête ou des différences directes dans l’utilisation du processeur. Les environnements incluent les versions du serveur, les paramètres de configuration de base de données ou de serveur, les indicateurs de trace, le nombre de processeurs ou la vitesse d’horloge, et machine virtuelle par rapport à machine physique. Pour plus d’informations, consultez Diagnostiquer les différences de plan de requête .

Scénario 2 : La requête est un serveur sur Server1, mais pas sur Server2

Si les temps processeur de la requête sur les deux serveurs sont similaires, mais que le temps écoulé sur Server1 est beaucoup plus élevé que sur Server2, la requête sur Server1 passe beaucoup plus de temps à attendre un goulot d’étranglement. Par exemple :

Serveur	Temps écoulé (ms)	Temps processeur (ms)	Lectures (logique)
Serveur1	4500	1000	90200
Serveur2	1100	1000	90200

Temps d’attente sur Server1 : 4500 - 1000 = 3500 ms
Temps d’attente sur Server2 : 1100 - 1000 = 100 ms

Action : Vérifier les types d’attente sur Server1

Identifiez et éliminez le goulot d’étranglement sur Server1. Les attentes de blocage (attente de verrou), d’attente de verrous, d’attente d’E/S disque, d’attente de réseau et d’attente de mémoire sont des exemples d’attentes. Pour résoudre les problèmes courants de goulot d’étranglement, passez à Diagnostiquer les attentes ou les goulots d’étranglement.

Scénario 3 : Les requêtes sur les deux serveurs sont des serveurs, mais les types d’attente ou les heures sont différents

Par exemple :

Serveur	Temps écoulé (ms)	Temps processeur (ms)	Lectures (logique)
Serveur1	8000	1000	90200
Serveur2	3000	1000	90200

Temps d’attente sur Server1 : 8000 - 1000 = 7 000 ms
Temps d’attente sur Server2 : 3000 - 1000 = 2 000 ms

Dans ce cas, les temps processeur sont similaires sur les deux serveurs, ce qui indique que les plans de requête sont probablement les mêmes. Les requêtes s’exécutent également sur les deux serveurs s’ils n’attendent pas les goulots d’étranglement. Les différences de durée proviennent donc des différents temps d’attente. Par exemple, la requête attend les verrous sur Server1 pendant 7 000 ms, tandis qu’elle attend les E/S sur Server2 pendant 2 000 ms.

Action : Vérifier les types d’attente sur les deux serveurs

Traitez chaque goulot d’étranglement d’attente individuellement sur chaque serveur et accélérez les exécutions sur les deux serveurs. La résolution de ce problème nécessite beaucoup de travail, car vous devez éliminer les goulots d’étranglement sur les deux serveurs et rendre les performances comparables. Pour résoudre les problèmes courants de goulot d’étranglement, passez à Diagnostiquer les attentes ou les goulots d’étranglement.

Scénario 4 : La requête sur Server1 utilise plus de temps processeur que sur Server2, mais les lectures logiques sont proches

Par exemple :

Serveur	Temps écoulé (ms)	Temps processeur (ms)	Lectures (logique)
Serveur1	3000	3000	90200
Serveur2	1000	1000	90200

Si les données correspondent aux conditions suivantes :

Le temps processeur sur Server1 est beaucoup plus élevé que sur Server2.
Le temps écoulé correspond étroitement au temps processeur sur chaque serveur, ce qui indique qu’il n’y a pas d’attente.
Les lectures logiques, généralement le pilote le plus élevé du temps processeur, sont similaires sur les deux serveurs.

Ensuite, le temps processeur supplémentaire provient d’autres activités liées au processeur. Ce scénario est le plus rare de tous les scénarios.

Causes : Suivi, fonctions définies par l’utilisateur et intégration du CLR

Ce problème peut être dû aux causes suivantes :

XEvents/SQL Server le suivi, en particulier avec le filtrage sur les colonnes de texte (nom de base de données, nom de connexion, texte de requête, etc.). Si le suivi est activé sur un serveur, mais pas sur l’autre, cela peut être la raison de la différence.
Fonctions définies par l’utilisateur (UDF) ou autre code T-SQL qui effectue des opérations liées au processeur. Cela est généralement la cause lorsque d’autres conditions sont différentes sur Server1 et Server2, telles que la taille des données, la vitesse de l’horloge du processeur ou le plan d’alimentation.
SQL Server l’intégration du CLR ou des procédures stockées étendues (XPs) qui peuvent piloter le processeur, mais n’effectuent pas de lectures logiques. Les différences dans les DLL peuvent entraîner des temps de processeur différents.
Différence dans SQL Server fonctionnalité liée au processeur (par exemple, code de manipulation de chaîne).

Action : Vérifier les traces et les requêtes

Vérifiez les traces sur les deux serveurs pour les éléments suivants :
1. Si une trace est activée sur Server1, mais pas sur Server2.
2. Si une trace est activée, désactivez-la et réexécutez la requête sur Server1.
3. Si la requête s’exécute plus rapidement cette fois, activez la trace en arrière, mais supprimez les filtres de texte, le cas échéant.
Vérifiez si la requête utilise des fonctions définies par l’utilisateur qui effectuent des manipulations de chaînes ou un traitement étendu sur les colonnes de données de la SELECT liste.
Vérifiez si la requête contient des boucles, des récursivités de fonction ou des imbrications.

Diagnostiquer les différences d’environnement

Vérifiez les questions suivantes et déterminez si la comparaison entre les deux serveurs est valide.

Les deux instances SQL Server sont-elles de la même version ou de la même build ?

Si ce n’est pas le cas, certains correctifs peuvent être à l’origine des différences. Exécutez la requête suivante pour obtenir des informations de version sur les deux serveurs :
```
SELECT @@VERSION
```
La quantité de mémoire physique est-elle similaire sur les deux serveurs ?

Si un serveur a 64 Go de mémoire tandis que l’autre a 256 Go de mémoire, cela représenterait une différence significative. Avec plus de mémoire disponible pour mettre en cache les pages de données/index et les plans de requête, la requête peut être optimisée différemment en fonction de la disponibilité des ressources matérielles.
Les configurations matérielles liées au processeur sont-elles similaires sur les deux serveurs ? Par exemple :
- Le nombre de processeurs varie d’une machine à l’autre (24 processeurs sur une machine contre 96 processeurs sur l’autre).
- Modes de gestion de l’alimentation : équilibrés par rapport aux performances élevées.
- Machine virtuelle (VM) ou machine physique (nue).
- Hyper-V et VMware : différence de configuration.
- Différence de vitesse d’horloge (vitesse d’horloge inférieure par rapport à la vitesse d’horloge supérieure). Par exemple, 2 GHz contre 3,5 GHz peuvent faire la différence. Pour obtenir la vitesse d’horloge sur un serveur, exécutez la commande PowerShell suivante :
```
Get-CimInstance Win32_Processor | Select-Object -Expand MaxClockSpeed
```
Utilisez l’une des deux méthodes suivantes pour tester la vitesse du processeur des serveurs. S’ils ne produisent pas de résultats comparables, le problème est en dehors de SQL Server. Il peut s’agir d’une différence de plan d’alimentation, d’un nombre réduit de processeurs, d’un problème de logiciel de machine virtuelle ou d’une différence de vitesse d’horloge.
- Exécutez le script PowerShell suivant sur les deux serveurs et comparez les sorties.
```
$bf = [System.DateTime]::Now
for ($i = 0; $i -le 20000000; $i++) {}
$af = [System.DateTime]::Now
Write-Host ($af - $bf).Milliseconds " milliseconds"
Write-Host ($af - $bf).Seconds " Seconds"
```
- Exécutez le code Transact-SQL suivant sur les deux serveurs et comparez les sorties.
```
SET NOCOUNT ON 
DECLARE @spins INT = 0
DECLARE @start_time DATETIME = GETDATE(), @time_millisecond INT

WHILE (@spins < 20000000)
BEGIN
   SET @spins = @spins +1
END

SELECT @time_millisecond = DATEDIFF(millisecond, @start_time, getdate())

SELECT @spins Spins, @time_millisecond Time_ms,  @spins / @time_millisecond Spins_Per_ms
```

Diagnostiquer les attentes ou les goulots d’étranglement

Pour optimiser une requête en attente de goulots d’étranglement, identifiez la durée d’attente et l’emplacement du goulot d’étranglement (le type d’attente). Une fois le type d’attente confirmé, réduisez le temps d’attente ou éliminez complètement l’attente.

Pour calculer le temps d’attente approximatif, soustrayez le temps processeur (temps de travail) du temps écoulé d’une requête. En règle générale, le temps processeur correspond au temps d’exécution réel, et la partie restante de la durée de vie de la requête est en attente.

Exemples de calcul de la durée d’attente approximative :

Temps écoulé (ms)	Temps processeur (ms)	Temps d’attente (ms)
3200	3000	200
7080	1000	6080

Identifier le goulot d’étranglement ou l’attente

Pour identifier les requêtes historiques en attente longue (par exemple, >20 % du temps d’attente total est un temps d’attente), exécutez la requête suivante. Cette requête utilise des statistiques de performances pour les plans de requête mis en cache depuis le début de SQL Server.

SELECT t.text,
         qs.total_elapsed_time / qs.execution_count
         AS avg_elapsed_time,
         qs.total_worker_time / qs.execution_count
         AS avg_cpu_time,
         (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count
         AS avg_wait_time,
         qs.total_logical_reads / qs.execution_count
         AS avg_logical_reads,
         qs.total_logical_writes / qs.execution_count
         AS avg_writes,
         qs.total_elapsed_time
         AS cumulative_elapsed_time
FROM sys.dm_exec_query_stats qs
         CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time
         > 0.2
ORDER BY qs.total_elapsed_time / qs.execution_count DESC

Pour identifier les requêtes en cours d’exécution avec des attentes supérieures à 500 ms, exécutez la requête suivante :

SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms
FROM sys.dm_exec_requests r 
   JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id 
WHERE wait_time > 500
AND is_user_process = 1

Si vous pouvez collecter un plan de requête, case activée waitStats à partir des propriétés du plan d’exécution dans SSMS :
1. Exécutez la requête avec Inclure le plan d’exécution réel activé.
2. Cliquez avec le bouton droit sur l’opérateur le plus à gauche dans l’onglet Plan d’exécution
3. Sélectionnez Propriétés , puis propriété WaitStats .
4. Vérifiez les valeurs WaitTimeMs et WaitType.
Si vous êtes familiarisé avec les scénarios PSSDiag/SQLdiag ou SQL LogScout LightPerf/GeneralPerf, envisagez d’utiliser l’un d’eux pour collecter des statistiques de performances et identifier les requêtes en attente sur votre SQL Server instance. Vous pouvez importer les fichiers de données collectés et analyser les données de performances avec SQL Nexus.

Références permettant d’éliminer ou de réduire les attentes

Les causes et les résolutions de chaque type d’attente varient. Il n’existe aucune méthode générale pour résoudre tous les types d’attente. Voici des articles pour résoudre les problèmes courants de type d’attente :

Pour obtenir une description de nombreux types d’attente et de ce qu’ils indiquent, consultez le tableau dans Types d’attente.

Diagnostiquer les différences de plan de requête

Voici quelques causes courantes des différences dans les plans de requête :

Différences de taille des données ou de valeurs de données

La même base de données est-elle utilisée sur les deux serveurs, à l’aide de la même sauvegarde de base de données ? Les données ont-elles été modifiées sur un serveur par rapport à l’autre ? Les différences de données peuvent entraîner des plans de requête différents. Par exemple, joindre la table T1 (1000 lignes) à la table T2 (2 000 000 lignes) diffère de la jointure de la table T1 (100 lignes) avec la table T2 (2 000 000 lignes). Le type et la vitesse de l’opération JOIN peuvent être considérablement différents.
Différences en matière de statistiques

Les statistiques ont-elles été mises à jour sur une base de données et non sur l’autre ? Les statistiques ont-ils été mises à jour avec un taux d’échantillonnage différent (par exemple, 30 % par rapport à une analyse complète de 100 %) ? Veillez à mettre à jour les statistiques des deux côtés avec le même taux d’échantillonnage.
Différences de niveau de compatibilité de base de données

Vérifiez si les niveaux de compatibilité des bases de données sont différents entre les deux serveurs. Pour obtenir le niveau de compatibilité de la base de données, exécutez la requête suivante :
```
SELECT name, compatibility_level
FROM sys.databases
WHERE name = '<YourDatabase>'
```
Différences de version/build du serveur

Les versions ou builds de SQL Server sont-elles différentes entre les deux serveurs ? Par exemple, un serveur SQL Server version 2014 et l’autre SQL Server version 2016 ? Il peut y avoir des modifications de produit qui peuvent entraîner des modifications dans la façon dont un plan de requête est sélectionné. Veillez à comparer la même version et la même build de SQL Server.
```
SELECT ServerProperty('ProductVersion')
```
Différences de version de l’estimateur de cardinalité (CE)

Vérifiez si l’estimateur de cardinalité hérité est activé au niveau de la base de données. Pour plus d’informations sur CE, consultez Estimation de la cardinalité (SQL Server).
```
SELECT name, value, is_value_default
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION'
```
Correctifs logiciels de l’optimiseur activés/désactivés

Si les correctifs logiciels de l’optimiseur de requête sont activés sur un serveur, mais désactivés sur l’autre, différents plans de requête peuvent être générés. Pour plus d’informations, consultez SQL Server modèle de maintenance de l’indicateur de trace de correctif logiciel de l’optimiseur de requête 4199.

Pour obtenir l’état des correctifs logiciels de l’optimiseur de requête, exécutez la requête suivante :
```
-- Check at server level for TF 4199
DBCC TRACESTATUS (-1)
-- Check at database level
USE <YourDatabase>
SELECT name, value, is_value_default 
FROM sys.database_scoped_configurations
WHERE name = 'QUERY_OPTIMIZER_HOTFIXES'
```
Différences entre les indicateurs de trace

Certains indicateurs de trace affectent la sélection du plan de requête. Vérifiez s’il existe des indicateurs de trace activés sur un serveur qui ne sont pas activés sur l’autre. Exécutez la requête suivante sur les deux serveurs et comparez les résultats :
```
-- Check at server level for trace flags
DBCC TRACESTATUS (-1)
```
Différences matérielles (nombre de processeurs, taille de la mémoire)

Pour obtenir les informations matérielles, exécutez la requête suivante :
```
SELECT cpu_count, physical_memory_kb/1024/1024 PhysicalMemory_GB 
FROM sys.dm_os_sys_info
```

Différences matérielles selon l’optimiseur de requête

Vérifiez le OptimizerHardwareDependentProperties d’un plan de requête et vérifiez si les différences matérielles sont considérées comme significatives pour différents plans.

WITH xmlnamespaces(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
  txt.text,
  t.OptHardw.value('@EstimatedAvailableMemoryGrant', 'INT') AS EstimatedAvailableMemoryGrant , 
  t.OptHardw.value('@EstimatedPagesCached', 'INT') AS EstimatedPagesCached, 
  t.OptHardw.value('@EstimatedAvailableDegreeOfParallelism', 'INT') AS EstimatedAvailDegreeOfParallelism,
  t.OptHardw.value('@MaxCompileMemory', 'INT') AS MaxCompileMemory
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//OptimizerHardwareDependentProperties') AS t(OptHardw)
CROSS APPLY sys.dm_exec_sql_text (CP.plan_handle) txt
WHERE text Like '%<Part of Your Query>%'

Délai d’expiration de l’optimiseur

Existe-t-il un problème de délai d’expiration de l’optimiseur ? L’optimiseur de requête peut arrêter l’évaluation des options de plan si la requête en cours d’exécution est trop complexe. Lorsqu’il s’arrête, il choisit le plan avec le coût le plus bas disponible à ce moment-là. Cela peut conduire à ce qui semble être un choix de plan arbitraire sur un serveur par rapport à un autre.
Options SET

Certaines options SET affectent le plan, telles que SET ARITHABORT. Pour plus d’informations, consultez OPTIONS SET.
Différences entre les indicateurs de requête

Une requête utilise-t-elle des indicateurs de requête et l’autre pas ? Vérifiez manuellement le texte de la requête pour établir la présence d’indicateurs de requête.
Plans sensibles aux paramètres (problème de détection de paramètre)

Testez-vous la requête avec exactement les mêmes valeurs de paramètre ? Si ce n’est pas le cas, vous pouvez commencer par là. Le plan a-t-il été compilé précédemment sur un serveur en fonction d’une valeur de paramètre différente ? Testez les deux requêtes à l’aide de l’indicateur de requête RECOMPILE pour vous assurer qu’aucune réutilisation de plan n’a lieu. Pour plus d’informations, consultez Examiner et résoudre les problèmes sensibles aux paramètres.

Différentes options de base de données/paramètres de configuration délimités

Les mêmes options de base de données ou paramètres de configuration délimités sont-ils utilisés sur les deux serveurs ? Certaines options de base de données peuvent influencer les choix de plan. Par exemple, la compatibilité de la base de données, la ce héritée par rapport à la ce par défaut et la détection de paramètres. Exécutez la requête suivante à partir d’un serveur pour comparer les options de base de données utilisées sur les deux serveurs :

-- On Server1 add a linked server to Server2 
EXEC master.dbo.sp_addlinkedserver @server = N'Server2', @srvproduct=N'SQL Server'

-- Run a join between the two servers to compare settings side by side
SELECT 
   s1.name AS srv1_config_name, 
   s2.name AS srv2_config_name,
   s1.value_in_use AS srv1_value_in_use, 
   s2.value_in_use AS srv2_value_in_use, 
   Variance = CASE WHEN ISNULL(s1.value_in_use, '##') != ISNULL(s2.value_in_use,'##') THEN 'Different' ELSE '' END
FROM sys.configurations s1 
FULL OUTER JOIN [server2].master.sys.configurations s2 ON s1.name = s2.name


SELECT 
   s1.name AS srv1_config_name,
   s2.name AS srv2_config_name,
   s1.value srv1_value_in_use,
   s2.value srv2_value_in_use,
   s1.is_value_default,
   s2.is_value_default,
   Variance = CASE WHEN ISNULL(s1.value, '##') != ISNULL(s2.value, '##') THEN 'Different' ELSE '' END
FROM sys.database_scoped_configurations s1
FULL OUTER JOIN [server2].master.sys.database_scoped_configurations s2 ON s1.name = s2.name

Repères de plan

Des repères de plan sont-ils utilisés pour vos requêtes sur un serveur, mais pas sur l’autre ? Exécutez la requête suivante pour établir les différences :
```
SELECT * FROM sys.plan_guides
```

Share via