Résoudre les problèmes de lenteur des requêtes dans SQL Server

  • Article

Version du produit d’origine : SQL Server
Numéro de la base de connaissances d’origine : 243589

Introduction

Cet article explique comment gérer un problème de performances que les applications de base de données peuvent rencontrer lors de l’utilisation de SQL Server : le ralentissement des performances d’une requête ou d’un groupe de requêtes spécifique. La méthodologie suivante vous aidera à cerner la cause du problème de requêtes lentes et à vous diriger vers la résolution.

Rechercher des requêtes lentes

Pour établir que vous rencontrez des problèmes de performances de requête sur votre SQL Server instance, commencez par examiner les requêtes en fonction de leur temps d’exécution (temps écoulé). Vérifiez si le temps dépasse un seuil que vous avez défini (en millisecondes) en fonction d’une base de référence de performances établie. Par exemple, dans un environnement de test de contrainte, vous avez peut-être établi un seuil pour que votre charge de travail ne dépasse pas 300 ms, et vous pouvez utiliser ce seuil. Ensuite, vous pouvez identifier toutes les requêtes qui dépassent ce seuil, en vous concentrant sur chaque requête individuelle et sa durée de référence des performances prédéfinie. En fin de compte, les utilisateurs professionnels se soucient de la durée globale des requêtes de base de données ; par conséquent, le main se concentre sur la durée d’exécution. D’autres métriques telles que le temps processeur et les lectures logiques sont collectées pour vous aider à affiner l’examen.

  • 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.

      Capture d’écran de la fenêtre de propriétés du plan d’exécution SQL Server avec la propriété QueryTimeStats développée.

Exécution ou attente : pourquoi les requêtes sont-elles lentes ?

Si vous trouvez des requêtes qui dépassent votre seuil prédéfini, examinez pourquoi elles peuvent être lentes. La cause des problèmes de performances peut être regroupée en deux catégories, en cours d’exécution ou en attente :

  • EN ATTENTE : les requêtes peuvent être lentes, car elles attendent un goulot d’étranglement pendant une longue période. Consultez la liste détaillée des goulots d’étranglement dans les types d’attente.

  • EN COURS D’EXÉCUTION : les requêtes peuvent être lentes, car elles s’exécutent (en cours d’exécution) pendant une longue période. En d’autres termes, ces requêtes utilisent activement des ressources processeur.

Une requête peut être en cours d’exécution pendant un certain temps et attendre un certain temps dans sa durée de vie (durée). Toutefois, votre objectif est de déterminer quelle est la catégorie dominante qui contribue à son temps écoulé depuis longtemps. Par conséquent, la première tâche consiste à déterminer dans quelle catégorie les requêtes se trouvent. C’est simple : si une requête n’est pas en cours d’exécution, elle attend. Dans l’idéal, une requête passe la plupart de son temps écoulé dans un état d’exécution et très peu de temps à attendre les ressources. En outre, dans le meilleur scénario, une requête s’exécute dans ou en dessous d’une base de référence prédéterminée. Comparez le temps écoulé et le temps processeur de la requête pour déterminer le type de problème.

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

Représentation visuelle de haut niveau de la méthodologie

La capture d’écran montre une représentation visuelle générale de la méthodologie de résolution des requêtes lentes.

Diagnostiquer et résoudre les requêtes en attente

Si vous avez établi que vos requêtes d’intérêt sont des serveurs, l’étape suivante consiste à vous concentrer sur la résolution des problèmes de goulot d’étranglement. Sinon, passez à l’étape 4 : Diagnostiquer et résoudre les requêtes en cours d’exécution.

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 et résoudre les requêtes en cours d’exécution

Si le temps processeur (worker) est très proche de la durée totale écoulée, la requête passe la majeure partie de sa durée de vie à s’exécuter. En règle générale, lorsque le moteur SQL Server entraîne une utilisation élevée du processeur, l’utilisation élevée du processeur provient des requêtes qui tirent un grand nombre de lectures logiques (la raison la plus courante).

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

Méthodes courantes pour résoudre les requêtes longues liées au processeur