Résoudre les problèmes d’utilisation élevée du processeur dans SQL Server

S'applique à : SQL Server

Cet article fournit des procédures qui permettent d’identifier et de résoudre des problèmes provoqués par une utilisation élevée du processeur sur un ordinateur Microsoft SQL Server. Une utilisation élevée du processeur dans SQL Server peut être due à de nombreuses causes possibles ; en voici les plus courantes :

• Lectures logiques élevées provoquées par des analyses de table ou d’index en raison des conditions suivantes :
  • Statistiques obsolètes
  • Index manquants
  • Problèmes de plan sensible aux paramètres (PSP)
  • Requêtes mal conçues
• Augmentation de la charge de travail

Vous pouvez utiliser les étapes ci-dessous pour résoudre les problèmes d’utilisation élevée du processeur dans SQL Server.

Étape 1 : Vérifier si SQL Server est à l’origine de l’utilisation élevée du processeur

Utilisez l’un des outils suivants pour vérifier si le processus SQL Server contribue réellement à l’utilisation élevée du processeur :

• Gestionnaire des tâches : sous l’onglet Processus, vérifiez si la valeur de la colonne Processeur correspondant à SQL Server Windows NT 64 bits est proche de 100 %.

• Analyseur de performances (perfmon)

  • Compteur : Process/%User Time, % Privileged Time
  • Instance : sqlservr

• Vous pouvez utiliser le script PowerShell suivant pour collecter les données de compteur sur une période de 60 secondes :

  $serverName = $env:COMPUTERNAME
  $Counters = @(
      ("\\$serverName" + "\Process(sqlservr*)\% User Time"), ("\\$serverName" + "\Process(sqlservr*)\% Privileged Time")
  )
  Get-Counter -Counter $Counters -MaxSamples 30 | ForEach {
      $_.CounterSamples | ForEach {
          [pscustomobject]@{
              TimeStamp = $_.TimeStamp
              Path = $_.Path
              Value = ([Math]::Round($_.CookedValue, 3))
          }
          Start-Sleep -s 2
      }
  }
  

  S’il % User Time est toujours supérieur à 90 % (% de temps utilisateur correspond à la somme du temps processeur sur chaque processeur, sa valeur maximale est de 100 % * (aucun processeur)), le processus SQL Server provoque une utilisation élevée du processeur. Toutefois, si c’est % Privileged time qui est constamment supérieur à 90 %, votre antivirus, d’autres pilotes ou un autre composant de système d’exploitation sur l’ordinateur contribuent à l’utilisation élevée du processeur. Contactez votre administrateur système pour analyser la cause racine de ce comportement.

• Tableau de bord des performances : dans SQL Server Management Studio, cliquez avec le bouton droit sur <SQLServerInstance> et sélectionnez Rapport Standard Reports>>Performance Dashboard.

  Le tableau de bord illustre un graphique intitulé Utilisation du processeur système avec un graphique à barres. La couleur plus sombre indique l’utilisation du processeur du moteur SQL Server, tandis que la couleur plus claire représente l’utilisation globale du processeur du système d’exploitation (voir la légende du graphique pour référence). Sélectionnez le bouton d’actualisation circulaire ou F5 pour afficher l’utilisation mise à jour.

Étape 2 : Identifier les requêtes contribuant à l’utilisation du processeur

Si le processus Sqlservr.exe provoque une utilisation élevée du processeur, la raison la plus courante est que des requêtes SQL Server effectuent des analyses de table ou d’index, suivies de tri, d’opérations de hachage et de boucles (opérateur de boucle imbriqué ou WHILE (T-SQL)). Pour avoir une idée des ressources de processeur utilisées par les requêtes, exécutez l’instruction suivante :

DECLARE @init_sum_cpu_time int,
        @utilizedCpuCount int 
--get CPU count used by SQL Server
SELECT @utilizedCpuCount = COUNT( * )
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE' 
--calculate the CPU usage by queries OVER a 5 sec interval 
SELECT @init_sum_cpu_time = SUM(cpu_time) FROM sys.dm_exec_requests
WAITFOR DELAY '00:00:05'
SELECT CONVERT(DECIMAL(5,2), ((SUM(cpu_time) - @init_sum_cpu_time) / (@utilizedCpuCount * 5000.00)) * 100) AS [CPU from Queries as Percent of Total CPU Capacity] 
FROM sys.dm_exec_requests

Pour identifier les requêtes qui sollicitent fortement l’UC, exécutez l’instruction suivante :

SELECT TOP 10 s.session_id,
           r.status,
           r.cpu_time,
           r.logical_reads,
           r.reads,
           r.writes,
           r.total_elapsed_time / (1000 * 60) 'Elaps M',
           SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
           ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.TEXT)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) + 1) AS statement_text,
           COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
           r.command,
           s.login_name,
           s.host_name,
           s.program_name,
           s.last_request_end_time,
           s.login_time,
           r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

Si les requêtes ne sollicitent pas l’UC pour l’instant, vous pouvez exécuter l’instruction suivante pour identifier les requêtes d’historique utilisant le processeur de manière intensive :

SELECT TOP 10  qs.last_execution_time, st.text AS batch_text,
    SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

Étape 3 : Mettre à jour les statistiques

Après avoir identifié les requêtes qui utilisent le plus le processeur, mettez à jour les statistiques des tables utilisées par ces requêtes. Vous pouvez utiliser la procédure stockée système sp_updatestats pour mettre à jour les statistiques de toutes les tables internes et définies par l’utilisateur dans la base de données active. Par exemple :

exec sp_updatestats

Pour plus dʼinformations sur sp_updatestats, consultez l’article sp_updatestats.

Si SQL Server utilise toujours un pourcentage de processeur excessif, passez à l’étape suivante.

Étape 4 : Ajouter les index manquants

Les index manquants peuvent entraîner un ralentissement de l’exécution des requêtes et une utilisation élevée du processeur. Vous pouvez identifier les index manquants puis les créer pour améliorer l’impact sur les performances.

1. Exécutez la requête suivante pour identifier les requêtes qui entraînent une utilisation élevée du processeur et dont au moins un index est manquant dans le plan de requête :

   -- Captures the Total CPU time spent by a query along with the query plan and total executions
   SELECT
       qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,
       q.[text],
       p.query_plan,
       qs_cpu.execution_count,
       q.dbid,
       q.objectid,
       q.encrypted AS text_encrypted
   FROM
       (SELECT TOP 500 qs.plan_handle,
        qs.total_worker_time,
        qs.execution_count FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS qs_cpu
   CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
   CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
   WHERE p.query_plan.exist('declare namespace 
           qplan = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
           //qplan:MissingIndexes')=1
   

2. Passez en revue les plans d’exécution des requêtes identifiées et affinez la requête en apportant les modifications requises. La capture d’écran suivante présente un exemple dans lequel SQL Server pointe un index manquant pour votre requête. Cliquez avec le bouton droit sur la partie Index manquant du plan de requête, puis sélectionnez Détails de l’index manquant pour créer l’index dans une autre fenêtre de SQL Server Management Studio.

   

3. Utilisez la requête suivante pour rechercher les index manquants et appliquer tous les index recommandés qui ont des valeurs de mesure d’amélioration élevées. Commencez par les 5 ou 10 premières recommandations de la sortie avec la valeur improvement_measure la plus élevée. Il s’agit des index dont l’effet positif sur les performances est le plus significatif. Déterminez si vous souhaitez appliquer ces index et vérifiez que des tests de performances sont effectués pour l’application. Continuez ensuite à appliquer les recommandations d’index manquants jusqu’à obtenir les résultats souhaités au niveau des performances d’application. Pour plus d’informations sur ce sujet, consultez l’article Optimiser les index non-cluster avec les suggestions d’index manquants.

   SELECT CONVERT(VARCHAR(30), GETDATE(), 126) AS runtime,
       mig.index_group_handle,
       mid.index_handle,
       CONVERT(DECIMAL(28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
       'CREATE INDEX missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns,
           '') + CASE WHEN mid.equality_columns IS NOT NULL
   AND mid.inequality_columns IS NOT NULL THEN ','
   ELSE ''
   END + ISNULL(mid.inequality_columns,
           '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')',
           '') AS create_index_statement,
       migs.*,
       mid.database_id,
       mid.[object_id]
   FROM sys.dm_db_missing_index_groups mig
   INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
   WHERE CONVERT (DECIMAL (28, 1),
                  migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
   ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
   

Étape 5 : Identifier et résoudre les problèmes sensibles aux paramètres

Vous pouvez utiliser la commande DBCC FREEPROCCACHE pour libérer le cache du plan et vérifier si cela permet de résoudre le problème d’utilisation élevée de l’UC. Si le problème est résolu, il s’agit d’un problème sensible aux paramètres (PSP, également appelé « problème de détection de paramètres »).

Pour atténuer les problèmes de sensibilité aux paramètres, procédez comme suit : Chaque méthode possède ses avantages et inconvénients.

• Utilisez l’indicateur de requête RECOMPILE. Vous pouvez ajouter un indicateur de requête RECOMPILE à une ou plusieurs des requêtes gourmandes en ressources de l’UC identifiées à l’étape 2. Cet indicateur permet d’équilibrer la légère augmentation de l’utilisation de l’UC lors de la compilation en offrant des performances optimales pour chaque exécution de requête. Pour plus d’informations, consultez les sections Paramètres et réutilisation des plans d’exécution, Sensibilité aux paramètres et Indicateur de requête RECOMPILE.

  Voici un exemple de la façon dont vous pouvez appliquer cet indicateur à votre requête.

  SELECT * FROM Person.Person 
  WHERE LastName = 'Wood'
  OPTION (RECOMPILE)
  

• Utilisez l’indicateur de requête OPTIMIZE FOR pour remplacer la valeur de paramètre réelle par une valeur de paramètre standard qui englobe la plupart des valeurs présentes dans les données. En procédant ainsi, vous devez connaître les valeurs de paramètre optimal et les caractéristiques du plan associé. Voici un exemple d’utilisation de cet indicateur dans votre requête.

  DECLARE @LastName Name = 'Frintu'
  SELECT FirstName, LastName FROM Person.Person 
  WHERE LastName = @LastName
  OPTION (OPTIMIZE FOR (@LastName = 'Wood'))
  

• Utilisez l’indicateur de requête OPTIMIZE FOR UNKNOWN pour remplacer la valeur de paramètre réelle par la moyenne du vecteur de densité. Vous pouvez également effectuer cette opération en capturant les valeurs de paramètres entrantes dans des variables locales, puis en utilisant ces variables locales dans des prédicats à la place des paramètres. Pour cette correction, la densité moyenne peut être suffisante pour fournir des performances acceptables.

• Utilisez l’indicateur de requête DISABLE_PARAMETER_SNIFFING pour désactiver entièrement la détection de paramètres. Voici un exemple d’utilisation dans une requête :

  SELECT * FROM Person.Address  
  WHERE City = 'SEATTLE' AND PostalCode = 98104
  OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))
  

• Utilisez l’indicateur de requête KEEPFIXED PLAN pour empêcher les recompilations dans le cache. Cette solution de contournement suppose que le plan courant « suffisant » est celui qui se trouve déjà dans le cache. Vous pouvez également désactiver les mises à jour de statistiques automatiques afin de réduire le risque d’éviction du plan suffisant et de compilation d’un plan insuffisant.

• Utilisez la commande DBCC FREEPROCCACHE comme solution temporaire jusqu’à ce que le code de l’application soit corrigé. Vous pouvez utiliser la commande DBCC FREEPROCCACHE (plan_handle) pour supprimer uniquement le plan à l’origine du problème. Par exemple, pour rechercher les plans de requête qui font référence au tableau Person.Person dans AdventureWorks, vous pouvez utiliser cette requête pour rechercher le handle de la requête. Vous pouvez ensuite libérer le plan de requête spécifique du cache à l’aide de la commande DBCC FREEPROCCACHE (plan_handle) qui est produit dans la deuxième colonne des résultats de la requête.

  SELECT text, 'DBCC FREEPROCCACHE (0x' + CONVERT(VARCHAR (512), plan_handle, 2) + ')' AS dbcc_freeproc_command FROM sys.dm_exec_cached_plans
  CROSS APPLY sys.dm_exec_query_plan(plan_handle)
  CROSS APPLY sys.dm_exec_sql_text(plan_handle)
  WHERE text LIKE '%person.person%'
  

Étape 6 : Identifier et résoudre les problèmes d’utilisation d’un argument de recherche

Un prédicat dans une requête est considéré comme SARGable (search ARGument-able) quand le moteur SQL Server peut utiliser une recherche dans l’index pour accélérer l’exécution de la requête. De nombreuses conceptions de requête empêchent la SARGabilité et conduisent à des analyses de table ou d’index et à une utilisation élevée du processeur. Considérez la requête suivante par rapport à la base de données AdventureWorks, où chaque élément ProductNumber doit être récupéré et se voir appliquer la fonction SUBSTRING(), avant d’être comparé à une valeur de littéral de chaîne. Comme vous pouvez le voir, vous devez d’abord extraire toutes les lignes de la table, puis appliquer la fonction avant de pouvoir effectuer une comparaison. L’extraction de toutes les lignes de la table signifie une analyse de table ou d’index, ce qui entraîne une utilisation plus élevée du processeur.

SELECT ProductID, Name, ProductNumber
FROM [Production].[Product]
WHERE SUBSTRING(ProductNumber, 0, 4) =  'HN-'

L’application d’une fonction ou d’un calcul sur la ou les colonnes du prédicat de recherche rend généralement la requête non SARGable et entraîne une consommation plus élevée du processeur. Les solutions impliquent généralement la réécriture des requêtes d’une manière créative pour les rendre SARGable. Une solution possible à cet exemple est cette réécriture où la fonction est supprimée du prédicat de requête, une autre colonne est recherchée et les mêmes résultats sont obtenus :

SELECT ProductID, Name, ProductNumber
FROM [Production].[Product]
WHERE Name LIKE  'Hex%'

Voici un autre exemple, où un responsable commercial décide d’octroyer une commission de 10 % sur les commandes importantes. Il veut également savoir quelles commandes auront une commission supérieure à 300 $. Pour ce faire, voici la méthode logique, mais non SARGable.

SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * 0.10 > 300

Voici une réécriture possible de la requête, moins intuitive mais SARGable, dans laquelle le calcul est déplacé de l’autre côté du prédicat.

SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice > 300/0.10

La capacité d’utiliser un argument de recherche s’applique non seulement aux clauses WHERE, mais aussi aux clauses JOINs, HAVING, GROUP BY et ORDER BY. Souvent, les requêtes ne sont pas SARGable à cause des fonctions CONVERT(), CAST(), ISNULL(), COALESCE() utilisées dans les clauses WHERE ou JOIN qui conduisent à l’analyse des colonnes. Dans les cas de conversion de type de données (CONVERT ou CAST), la solution peut consister à vérifier que vous comparez les mêmes types de données. Voici un exemple où la colonne T1.ProdID est explicitement convertie en type de données INT dans une clause JOIN. La conversion rend inutile l’utilisation d’un index sur la colonne de jointure. Le même problème se produit lors de la conversion implicite, où les types de données sont différents et où SQL Server convertit l’un d’entre eux pour effectuer la jointure.

SELECT T1.ProdID, T1.ProdDesc
FROM T1 JOIN T2 
ON CONVERT(int, T1.ProdID) = T2.ProductID
WHERE t2.ProductID BETWEEN 200 AND 300

Pour éviter une analyse de la table T1, vous pouvez modifier le type de données sous-jacent de la colonne ProdID après une planification et une conception appropriées, puis joindre les deux colonnes sans utiliser la fonction CONVERT ON T1.ProdID = T2.ProductID.

Une autre solution consiste à créer une colonne calculée dans T1 qui utilise la même fonction CONVERT(), puis à créer un index sur celle-ci. Cela permettra à l’optimiseur de requête d’utiliser cet index sans que vous ayez besoin de modifier votre requête.

ALTER TABLE dbo.T1  ADD IntProdID AS CONVERT (INT, ProdID);
CREATE INDEX IndProdID_int ON dbo.T1 (IntProdID);

Dans certains cas, les requêtes ne peuvent pas être réécrites facilement afin de les rendre SARGable. Dans ces situations, vérifiez si la colonne calculée avec un index dessus peut vous aider. Vous pouvez également garder la requête en l’état, mais sachez que cela peut entraîner une utilisation plus élevée du processeur.

Étape 7 : Désactiver le traçage intensif

Recherchez une trace SQL ou un traçage XEvent qui affecte les performances de SQL Server et provoque une utilisation élevée du processeur. Par exemple, l’utilisation des événements suivants peut entraîner une utilisation élevée du processeur si vous tracez une activité SQL Server importante :

• Événements XML de plan de requête (query_plan_profile, query_post_compilation_showplan, query_post_execution_plan_profile, query_post_execution_showplan, query_pre_execution_showplan)
• Événements au niveau de l’instruction (sql_statement_completed, sql_statement_starting, sp_statement_starting, sp_statement_completed)
• Événements de connexion et de déconnexion (login, process_login_finish, login_event, logout)
• Événements de verrouillage (lock_acquired, lock_cancel, lock_released)
• Événements d’attente (wait_info, wait_info_external)
• Événements d’audit SQL (en fonction du groupe audité et de l’activité SQL Server dans ce groupe)

Exécutez les requêtes suivantes pour identifier les traces XEvent ou du serveur actives :

PRINT '--Profiler trace summary--'
SELECT traceid, property, CONVERT(VARCHAR(1024), value) AS value FROM::fn_trace_getinfo(
    default)
GO
PRINT '--Trace event details--'
SELECT trace_id,
    status,
    CASE WHEN row_number = 1 THEN path ELSE NULL end AS path,
    CASE WHEN row_number = 1 THEN max_size ELSE NULL end AS max_size,
    CASE WHEN row_number = 1 THEN start_time ELSE NULL end AS start_time,
    CASE WHEN row_number = 1 THEN stop_time ELSE NULL end AS stop_time,
    max_files,
    is_rowset,
    is_rollover,
    is_shutdown,
    is_default,
    buffer_count,
    buffer_size,
    last_event_time,
    event_count,
    trace_event_id,
    trace_event_name,
    trace_column_id,
    trace_column_name,
    expensive_event
FROM
    (SELECT t.id AS trace_id,
     row_number() over(PARTITION BY t.id order by te.trace_event_id, tc.trace_column_id) AS row_number,
     t.status,
     t.path,
     t.max_size,
     t.start_time,
     t.stop_time,
     t.max_files,
     t.is_rowset,
     t.is_rollover,
     t.is_shutdown,
     t.is_default,
     t.buffer_count,
     t.buffer_size,
     t.last_event_time,
     t.event_count,
     te.trace_event_id,
     te.name AS trace_event_name,
     tc.trace_column_id,
     tc.name AS trace_column_name,
     CASE WHEN te.trace_event_id in (23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) THEN CAST(1 as bit) ELSE CAST(0 AS BIT) END AS expensive_event FROM sys.traces t CROSS APPLY::fn_trace_geteventinfo(t.id) AS e JOIN sys.trace_events te ON te.trace_event_id = e.eventid JOIN sys.trace_columns tc ON e.columnid = trace_column_id) AS x
GO
PRINT '--XEvent Session Details--'
SELECT sess.NAME 'session_name', event_name, xe_event_name, trace_event_id,
    CASE WHEN xemap.trace_event_id IN(23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) 
    THEN Cast(1 AS BIT)
ELSE Cast(0 AS BIT)
END AS expensive_event
FROM sys.dm_xe_sessions sess
JOIN sys.dm_xe_session_events evt
ON sess.address = evt.event_session_address
INNER JOIN sys.trace_xe_event_map xemap
ON evt.event_name = xemap.xe_event_name
GO

Étape 8 : Corriger l’utilisation élevée du processeur causée par la contention de blocage de spinlock

Pour résoudre l’utilisation élevée courante du processeur causée par la contention de verrouillage de spinlock, consultez les sections suivantes.

contention de verrouillage SOS_CACHESTORE

Si votre instance SQL Server rencontre des contentions de blocages volumineux SOS_CACHESTORE ou que vous remarquez que vos plans de requête sont souvent supprimés sur les charges de travail de requête non planifiées, consultez l’article suivant et activez l’indicateur T174 de trace à l’aide de la DBCC TRACEON (174, -1) commande :

CORRECTIF : la contention de verrouillage tournant SOS_CACHESTORE sur le cache de plan SQL Server ad hoc entraîne une utilisation élevée du processeur dans SQL Server.

Si l’état d’utilisation élevée du processeur est résolu à l’aide de T174, activez-le en tant que paramètre de démarrage à l’aide du Gestionnaire de configuration SQL Server.

Utilisation élevée aléatoire du processeur en raison de SOS_BLOCKALLOCPARTIALLIST contention de verrouillage de spinlock sur des machines à mémoire volumineuse

Si votre instance SQL Server rencontre une utilisation aléatoire élevée du processeur en raison SOS_BLOCKALLOCPARTIALLIST de la contention de blocage, nous vous recommandons d’appliquer la mise à jour cumulative 21 pour SQL Server 2019. Pour plus d’informations sur la résolution du problème, consultez la référence de bogue 2410400 et DBCC DROPCLEANBUFFERS qui fournit une atténuation temporaire.

Utilisation élevée du processeur en raison d’une contention de blocage sur XVB_list sur des machines haut de gamme

Si votre instance SQL Server rencontre un scénario d’UC élevé provoqué par la contention de blocage sur le XVB_LIST blocage sur les machines à configuration élevée (systèmes haut de gamme avec un grand nombre de processeurs de génération plus récents), activez l’indicateur de trace TF8102 avec TF8101.

Étape 9 : Configurer la machine virtuelle

Si vous utilisez une machine virtuelle, veillez à ne pas suraprovisionner les processeurs et à ce qu’ils soient configurés correctement. Pour plus d’informations, consultez l’article Troubleshooting ESX/ESXi virtual machine performance issues (2001003) (en anglais uniquement).

Étape 10 : Augmenter l’échelle du système pour utiliser plus de processeurs

Si les instances de requête individuelles utilisent un faible pourcentage de processeur, mais que la charge de travail globale de toutes les requêtes ensemble entraîne une utilisation élevée du processeur, envisagez d’effectuer un scale-up de votre ordinateur en ajoutant d’autres processeurs. Utilisez la requête ci-dessous pour connaître le nombre de requêtes qui ont dépassé un certain seuil d’utilisation moyenne et maximale du processeur par exécution et qui ont été exécutées de nombreuses fois sur le système (veillez à modifier les valeurs des deux variables en fonction de votre environnement) :

-- Shows queries where Max and average CPU time exceeds 200 ms and executed more than 1000 times
DECLARE @cputime_threshold_microsec INT = 200*1000
DECLARE @execution_count INT = 1000
SELECT qs.total_worker_time/1000 total_cpu_time_ms,
       qs.max_worker_time/1000 max_cpu_time_ms,
       (qs.total_worker_time/1000)/execution_count average_cpu_time_ms,
       qs.execution_count,
       q.[text]
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
WHERE (qs.total_worker_time/execution_count > @cputime_threshold_microsec
        OR qs.max_worker_time > @cputime_threshold_microsec )
        AND execution_count > @execution_count
ORDER BY  qs.total_worker_time DESC 

Voir aussi

• Des allocations élevées de processeur ou de mémoire peuvent se produire avec des requêtes qui utilisent une boucle imbriqué optimisée ou un tri par lots
• Mises à jour et options de configuration recommandées pour SQL Server avec des charges de travail hautes performances
• Mises à jour et options de configuration recommandées pour SQL Server 2017 et 2016 avec des charges de travail hautes performances