Share via

Résoudre les problèmes de requêtes qui semblent ne jamais se terminer par SQL Server

  • Article

Cet article décrit les étapes de résolution du problème dans lequel vous avez une requête qui semble ne jamais être terminée, ou où l’exécution peut prendre plusieurs heures ou plusieurs jours.

Qu’est-ce qu’une requête sans fin ?

Ce document se concentre sur les requêtes qui continuent à s’exécuter ou à compiler, c’est-à-dire que leur processeur continue d’augmenter. Elle ne s’applique pas aux requêtes bloquées ou en attente d’une ressource qui n’est jamais libérée (le processeur reste constant ou change très peu).

Importante

Si une requête est laissée pour terminer son exécution, elle finira par se terminer. Cela peut prendre quelques secondes ou plusieurs jours.

Le terme sans fin est utilisé pour décrire la perception qu’une requête ne se termine pas alors qu’en fait, la requête finira par se terminer.

Identifier une requête sans fin

Pour identifier si une requête s’exécute en continu ou est bloquée sur un goulot d’étranglement, procédez comme suit :

  1. Exécutez la requête suivante :

    DECLARE @cntr int = 0

WHILE (@cntr < 3)
BEGIN
    SELECT TOP 10 s.session_id,
                    r.status,
                    r.wait_time,
                    r.wait_type,
                    r.wait_resource,
                    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,
                    atrn.name as transaction_name,
                    atrn.transaction_id,
                    atrn.transaction_state
        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
        LEFT JOIN (sys.dm_tran_session_transactions AS stran 
             JOIN sys.dm_tran_active_transactions AS atrn
                ON stran.transaction_id = atrn.transaction_id)
        ON stran.session_id =s.session_id
        WHERE r.session_id != @@SPID
        ORDER BY r.cpu_time DESC

    SET @cntr = @cntr + 1
WAITFOR DELAY '00:00:05'
END

  2. Vérifiez l’exemple de sortie.

    • Les étapes de résolution des problèmes décrites dans cet article s’appliquent spécifiquement lorsque vous remarquez une sortie similaire à la suivante où le processeur augmente proportionnellement au temps écoulé, sans temps d’attente significatifs. Il est important de noter que les modifications logical_reads apportées à ne sont pas pertinentes dans ce cas, car certaines requêtes T-SQL liées au processeur peuvent ne pas effectuer de lectures logiques du tout (par exemple, l’exécution de calculs ou d’une WHILE boucle).

      Session_id status cpu_time logical_reads wait_time wait_type
      56 Exécution 7038 101000 0 NULL
      56 exécutable 12040 301000 0 NULL
      56 Exécution 17020 523000 0 NULL

    • Cet article ne s’applique pas si vous observez un scénario d’attente similaire au suivant où le processeur ne change pas ou change très légèrement, et que la session attend une ressource.

      Session_id status cpu_time logical_reads wait_time wait_type
      56 suspended 0 3 8312 LCK_M_U
      56 suspended 0 3 13318 LCK_M_U
      56 suspended 0 5 18331 LCK_M_U

    Pour plus d’informations, consultez Diagnostiquer les attentes ou les goulots d’étranglement.

Temps de compilation long

Dans de rares cas, vous pouvez observer que le processeur augmente continuellement au fil du temps, mais cela n’est pas piloté par l’exécution des requêtes. Au lieu de cela, elle peut être pilotée par une compilation trop longue (l’analyse et la compilation d’une requête). Dans ce cas, case activée la colonne de sortie transaction_name et recherchez la valeur .sqlsource_transform Ce nom de transaction indique une compilation.

Collecter des données de diagnostic

Pour collecter des données de diagnostic à l’aide de SQL Server Management Studio (SSMS), procédez comme suit :

  1. Capturez le code XML du plan d’exécution de requête estimé .

  2. Passez en revue le plan de requête pour voir s’il existe des indications évidentes d’où peut provenir la lenteur. Voici quelques exemples typiques :

    • Analyses de table ou d’index (examinez les lignes estimées).
    • Boucles imbriquées pilotées par un énorme jeu de données de table externe.
    • Boucles imbriquées avec une grande branche dans le côté interne de la boucle.
    • Spoules de table.
    • Fonctions de la SELECT liste qui prennent beaucoup de temps pour traiter chaque ligne.

  3. Si la requête s’exécute rapidement à tout moment, vous pouvez capturer les exécutions « rapides » Plan d’exécution XML réel à comparer.

Méthode d’examen des plans collectés

Cette section montre comment passer en revue les données collectées. Il utilise les plans de requête XML multiples (à l’aide de l’extension *.sqlplan) collectés dans SQL Server 2016 SP1 et versions ultérieures.

Procédez comme suit pour comparer les plans d’exécution :

  1. Ouvrez un fichier de plan d’exécution de requête précédemment enregistré (.sqlplan).

  2. Cliquez avec le bouton droit dans une zone vide du plan d’exécution, puis sélectionnez Comparer le plan d’exécution.

  3. Choisissez le deuxième fichier de plan de requête que vous souhaitez comparer.

  4. Recherchez les flèches épaisses qui indiquent un grand nombre de lignes circulant entre les opérateurs. Sélectionnez ensuite l’opérateur avant ou après la flèche, puis comparez le nombre de lignes réelles sur deux plans.

  5. Comparez les deuxième et troisième plans pour voir si le plus grand flux de lignes se produit dans les mêmes opérateurs.

    Voici un exemple :

    Comparer les plans de requête dans SSMS.

Résolution

  1. Vérifiez que les statistiques sont mises à jour pour les tables utilisées dans la requête.

  2. Recherchez une recommandation d’index manquant dans le plan de requête et appliquez-en une.

  3. Réécrire la requête dans le but de la simplifier :

    • Utilisez des prédicats plus sélectifs WHERE pour réduire les données traitées à l’avance.
    • Séparez-le.
    • Sélectionnez certaines parties dans des tables temporaires et joignez-les ultérieurement.
    • Supprimez TOP, EXISTSet FAST (T-SQL) dans les requêtes qui s’exécutent pendant très longtemps en raison de l’objectif de ligne de l’optimiseur. Vous pouvez également utiliser l’indicateurDISABLE_OPTIMIZER_ROWGOAL . Pour plus d’informations, consultez Ligne Goals Perdu.
    • Évitez d’utiliser des expressions de table communes (CTE) dans les cas où elles combinent des instructions en une seule requête volumineuse.

  4. Essayez d’utiliser des indicateurs de requête pour produire un meilleur plan :

    • HASH JOIN ou MERGE JOIN indicateur
    • FORCE ORDER Indice
    • FORCESEEK Indice
    • RECOMPILE
    • UTILISEZ PLAN N'<xml_plan>' si vous disposez d’un plan de requête rapide que vous pouvez forcer

  5. Utilisez Magasin des requêtes (QDS) pour forcer un plan connu s’il existe un tel plan et si votre version SQL Server prend en charge Magasin des requêtes.

Diagnostiquer les attentes ou les goulots d’étranglement

Cette section est incluse ici en tant que référence au cas où votre problème ne serait pas une requête de longue durée d’utilisation du processeur. Vous pouvez l’utiliser pour résoudre les problèmes de requêtes qui sont longues en raison d’attentes.

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.