Résoudre les problèmes de requêtes qui semblent s’exécuter sans fin dans SQL Server

Cet article fournit des conseils de résolution des problèmes dans lesquels une requête Microsoft SQL Server prend un temps excessif pour se terminer (heures ou jours).

Symptômes

Cet article se concentre sur les requêtes qui semblent s’exécuter ou compiler sans fin. Autrement dit, leur utilisation du processeur continue d’augmenter. Cet article ne s’applique pas aux requêtes qui sont bloquées ou en attente sur une ressource qui n’est jamais publiée. Dans ce cas, l’utilisation du processeur reste constante ou change légèrement.

Important

Si une requête est laissée pour continuer à s’exécuter, elle peut finir par se terminer. Ce processus peut prendre quelques secondes ou plusieurs jours. Dans certaines situations, la requête peut vraiment être infinie, par exemple lorsqu’une boucle WHILE ne se termine pas. Le terme « jamais terminé » est utilisé ici pour décrire la perception d’une requête qui ne se termine pas.

La cause

Les causes courantes des requêtes longues (jamais terminées) sont les suivantes :

Jointures de boucle imbriquée (NL) sur des tables très volumineuses : En raison de la nature des jointures NL, une requête qui joint des tables qui ont beaucoup de lignes peut s’exécuter pendant longtemps. Pour plus d’informations, consultez Jointures.
- Un exemple de jointure NL est l’utilisation de TOP, FASTou EXISTS. Même si une jointure de hachage ou de fusion peut être plus rapide, l’optimiseur ne peut pas utiliser l’un des opérateurs en raison de l’objectif de ligne.
- Un autre exemple de jointure NL est l’utilisation d’un prédicat de jointure d’inégalité dans une requête. Par exemple : SELECT .. FROM tab1 AS a JOIN tab 2 AS b ON a.id > b.id. L’optimiseur ne peut pas utiliser de jointures de fusion ou de hachage ici.
Statistiques obsolètes : Les requêtes qui sélectionnent un plan en fonction des statistiques obsolètes peuvent être non optimales et prendre beaucoup de temps pour s’exécuter.
Boucles infinies : Les requêtes T-SQL qui utilisent des boucles WHILE peuvent être incorrectement écrites. Le code résultant ne quitte jamais la boucle et s’exécute sans fin. Ces requêtes ne se terminent vraiment jamais. Ils s’exécutent jusqu’à ce qu’ils soient tués manuellement.
Requêtes complexes qui ont de nombreuses jointures et tables volumineuses : Les requêtes qui impliquent de nombreuses tables jointes ont généralement des plans de requête complexes qui peuvent prendre beaucoup de temps. Ce scénario est courant dans les requêtes analytiques qui ne filtrent pas les lignes et qui impliquent un grand nombre de tables.
Index manquants : Les requêtes peuvent s’exécuter considérablement plus rapidement si des index appropriés sont utilisés sur des tables. Les index permettent de sélectionner un sous-ensemble des données pour fournir un accès plus rapide.

Solution

Étape 1 : Découvrir des requêtes sans fin

Recherchez une requête sans fin qui s’exécute sur le système. Vous devez déterminer si une requête a un temps d’exécution long, un temps d’attente long (bloqué sur un goulot d’étranglement) ou une longue durée de compilation.

1.1 Exécuter un diagnostic

Exécutez la requête de diagnostic suivante sur votre instance SQL Server où la requête sans fin est active :

DECLARE @cntr INT = 0

WHILE (@cntr < 3)
BEGIN
    SELECT TOP 10 s.session_id,
                    r.status,
                    CAST(r.cpu_time / (1000 * 60.0) AS DECIMAL(10,2)) AS cpu_time_minutes,
                    CAST(r.total_elapsed_time / (1000 * 60.0) AS DECIMAL(10,2)) AS elapsed_minutes,
                    r.logical_reads,
                    r.wait_time,
                    r.wait_type,
                    r.wait_resource,
                    r.reads,
                    r.writes,
                    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

1.2 Examiner la sortie

Plusieurs scénarios peuvent entraîner l’exécution d’une requête pendant un certain temps : une longue exécution, une longue attente et une compilation longue. Pour plus d’informations sur la raison pour laquelle une requête peut s’exécuter lentement, consultez Exécution et Attente : pourquoi les requêtes sont-elles lentes ?

Temps d’exécution long

Les étapes de résolution des problèmes décrites dans cet article s’appliquent lorsque vous recevez une sortie similaire à la suivante, où le temps processeur augmente proportionnellement au temps écoulé sans temps d’attente significatif.

session_id	statut	cpu_time_minutes	elapsed_time_minutes	logical_reads	wait_time_minutes	wait_type
56	exécution en cours	64.40	23.50	0	0.00	NULL

La requête est en cours d’exécution si elle a :

Temps processeur croissant
État ou runningrunnable
Temps d’attente minimal ou zéro
Aucune wait_type

Dans ce cas, la requête lit des lignes, jointures, traitement des résultats, calcul ou mise en forme. Ces activités sont toutes les actions liées au processeur.

Note

Les modifications ne logical_reads sont pas pertinentes dans ce cas, car certaines requêtes T-SQL liées au processeur, telles que l’exécution de calculs ou d’une WHILE boucle, peuvent ne pas effectuer de lectures logiques du tout.

Si la requête lente répond à ces critères, concentrez-vous sur la réduction de son exécution. En règle générale, la réduction du runtime implique de réduire le nombre de lignes que la requête doit traiter tout au long de sa vie en appliquant des index, en réécritant la requête ou en mettant à jour les statistiques. Pour plus d’informations, consultez la section Résolution .

Temps d’attente long

Cet article n’est pas applicable aux scénarios d’attente longs. Dans un scénario d’attente, vous pouvez recevoir une sortie semblable à l’exemple suivant dans lequel l’utilisation du processeur ne change pas ou change légèrement, car la session attend une ressource :

session_id	statut	cpu_time_minutes	elapsed_time_minutes	logical_reads	wait_time_minutes	wait_type
56	interrompu	0.03	4.20	50	4.10	LCK_M_U

Le type d’attente indique que la session attend une ressource. Un temps d’attente long et un temps d’attente long indiquent que la session attend la plupart de sa vie pour cette ressource. Le temps processeur court indique que peu de temps a été consacré au traitement de la requête.

Pour résoudre les problèmes de requêtes qui sont longues en raison d’attentes, consultez Résoudre les problèmes liés aux requêtes en cours d’exécution lente dans SQL Server.

Temps de compilation long

Dans de rares cas, vous pouvez observer que l’utilisation du processeur augmente en continu au fil du temps, mais n’est pas pilotée par l’exécution de la requête. Au lieu de cela, une compilation excessivement longue (l’analyse et la compilation d’une requête) peut être la cause. Dans ces cas, vérifiez la transaction_name colonne de sortie pour obtenir la valeur sqlsource_transform. Ce nom de transaction indique une compilation.

Étape 2 : Collecter manuellement les journaux de diagnostic

Après avoir déterminé qu’une requête sans fin existe sur le système, vous pouvez collecter les données de plan de la requête pour résoudre les problèmes supplémentaires. Pour collecter les données, utilisez l’une des méthodes suivantes, en fonction de votre version de SQL Server.

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

Capturez le code XML du plan d’exécution de requête estimé.
Passez en revue le plan de requête pour savoir si les données affichent des indications évidentes de ce qui provoque la lenteur. Voici quelques exemples d’indications classiques :
- Analyses de table ou d’index (examinez les lignes estimées)
- Boucles imbriquées pilotées par un jeu de données de table externe énorme
- Boucles imbriquées qui ont une grande branche dans le côté interne de la boucle
- Pools de tables
- Fonctions dans la SELECT liste qui prennent beaucoup de temps pour traiter chaque ligne
Si la requête s’exécute plus rapidement à tout moment, vous pouvez capturer les exécutions « rapides » (plan d’exécution XML réel) pour comparer les résultats.

L’infrastructure de profilage des requêtes légère a été introduite dans Microsoft SQL Server 2014. Il vous permet de capturer des statistiques réelles pendant qu’une requête lente s’exécute. Cette fonctionnalité de résolution des problèmes vous permet d’examiner les opérateurs de requête dans un plan de requête au moment de l’exécution et de comprendre où la plupart du temps est passé dans une requête.

Pour identifier les étapes lentes de la requête nu à l’aide de l’infrastructure de profilage des statistiques d’exécution de requêtes légères v1, procédez comme suit :

Exécutez les commandes suivantes pour activer XEvent query_thread_profile :

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = START

Démarrez la requête sans fin affectée à partir de l’application.

Exécutez la commande suivante plusieurs fois, à environ une minute à part, pour vérifier les statistiques d’exécution du runtime pour les opérateurs du plan de requête :

SELECT CONVERT (varchar(30), getdate(), 126) as runtime,
            qp.session_id,
            convert(nvarchar(48), qp.physical_operator_name) as physical_operator_name,
            qp.row_count,
            qp.estimate_row_count,
            qp.node_id,
            req.cpu_time,
            req.total_elapsed_time,
            substring
            (REPLACE
            (REPLACE
                (SUBSTRING
                (SQLText.text
                , (req.statement_start_offset/2) + 1
                , (
                    (CASE statement_END_offset
                        WHEN -1
                        THEN DATALENGTH(SQLText.text)  
                        ELSE req.statement_END_offset
                        END
                        - req.statement_start_offset)/2) + 1)
            , CHAR(10), ' '), CHAR(13), ' '), 1, 512)  AS active_statement_text
FROM sys.dm_exec_query_profiles qp 
RIGHT OUTER JOIN sys.dm_exec_requests req
    ON qp.session_id = req.session_id
LEFT OUTER JOIN sys.dm_exec_sessions sess
    on req.session_id = sess.session_id
LEFT OUTER JOIN sys.dm_exec_connections conn on conn.session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text (ISNULL (req.sql_handle, conn.most_recent_sql_handle)) as SQLText
WHERE req.session_id <> @@SPID 
    AND sess.is_user_process = 1 
ORDER BY qp.session_id asc, row_count desc 
--this is to prevent massive grants
OPTION (max_grant_percent = 3, MAXDOP 1)

Capturez trois ou quatre instantanés, une minute à part, pour obtenir des données suffisantes pour l’analyse. Plus précisément, vous pouvez comparer les row_count nombres de chaque opérateur au fil du temps pour savoir quelles quantités indiquent une augmentation significative du nombre de lignes (d’un million ou plus).
Dans une nouvelle fenêtre de requête dans SSMS, capturez un plan de requête estimé pour la requête de problème en exécutant les commandes suivantes :
```
SET SHOWPLAN_XML ON
GO
<problem query here>
GO
SET SHOWPLAN_XML OFF
```
Recherchez le même nœud dans le plan de requête estimé à l’aide de l’ID de nœud qui a le nombre de lignes le plus élevé, tel que déterminé par la requête que vous avez exécutée à l’étape 3. Cette étape permet d’identifier quel opérateur dans le plan est la cause principale du temps d’exécution long.

Arrêtez le XEvent en exécutant la commande suivante :

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP

Vous pouvez utiliser l’infrastructure de profilage des statistiques d’exécution des requêtes légères v2 pour capturer les plans de requête en direct qui incluent les nombres de lignes réels. Cette infrastructure de profilage vous permet d’examiner les opérateurs de requête dans un plan de requête au moment de l’exécution et d’apprendre où la plupart du temps est passé dans une requête.

Pour identifier les étapes lentes de la requête, procédez comme suit :

Pour activer l’infrastructure légère, utilisez l’une des méthodes suivantes :

Activez l’indicateur de trace 7412 en exécutant la commande suivante :
```
DBCC TRACEON (7412, -1)
```

Vous pouvez également activer XEvent query_thread_profile en exécutant les commandes suivantes :

CREATE EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
 ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
   sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
   sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
   sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
 EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
 MAX_DISPATCH_LATENCY=30 SECONDS,
 MAX_EVENT_SIZE=0 KB,
 MEMORY_PARTITION_MODE=NONE,
 TRACK_CAUSALITY=OFF,
 STARTUP_STATE=OFF);

ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = START

Démarrez la requête sans fin affectée à partir de l’application.
Pour identifier la Session_id requête sans fin en cours d’exécution, exécutez une commande semblable à la commande suivante :
```
SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
```
Pour examiner le plan de requête et les statistiques réelles dans le plan, exécutez la commande suivante trois ou quatre fois, une minute à part. Veillez à enregistrer le plan de requête à chaque fois afin de pouvoir comparer les plans et découvrir les opérateurs de requête qui consomment la plupart du temps processeur. Plus précisément, vous pouvez comparer le nombre de lignes (nombre réel de lignes) pour chaque opérateur au fil du temps pour voir quels opérateurs affichent une augmentation significative du nombre de lignes (d’un million ou plus). Remplacez <session_id> par la valeur entière que vous avez trouvée à l’étape précédente.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```

Arrêtez le XEvent si vous en avez démarré un ou désactivez l’indicateur de trace :

ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = STOP
-- or
DBCC TRACEOFF (7412, -1)

Vous pouvez utiliser l’infrastructure de profilage des statistiques d’exécution des requêtes légères v3 pour capturer les plans de requête en direct qui incluent les nombres de lignes réels. Cette infrastructure de profilage vous permet d’examiner les opérateurs de requête dans un plan de requête au moment de l’exécution et de comprendre où la plupart du temps est passé dans une requête. Le profilage léger est activé par défaut dans Microsoft SQL Server 2019.

Pour identifier les étapes lentes de la requête, procédez comme suit :

Démarrez la requête sans fin affectée à partir de votre application.
Pour identifier la Session_id requête sans fin en cours d’exécution, exécutez une commande semblable à la commande suivante :
```
SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
```
Exécutez la commande suivante pour examiner le plan de requête et les statistiques réelles dans le plan. Veillez à enregistrer le plan de requête à chaque fois pour pouvoir les comparer et découvrir l’opérateur de requête qui consomme la plupart du temps processeur. Remplacez <session_id> par la valeur entière que vous avez trouvée à l’étape précédente.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```
Sélectionnez le lien XML dans la query_plan colonne. Une fois le plan de requête graphique ouvert dans une nouvelle fenêtre, cliquez dessus avec le bouton droit, puis sélectionnez Enregistrer le plan d’exécution sous.... Répétez ces étapes pour capturer trois ou quatre instantanés, une minute à part, afin d’obtenir suffisamment de données pour l’analyse. Plus précisément, vous pouvez comparer le nombre de lignes (nombre réel de lignes) pour chaque opérateur au fil du temps pour savoir quels opérateurs affichent une augmentation significative du nombre de lignes (par million ou plus).
Note

Si vous n’obtenez aucune sortie, vous pouvez vérifier si l’option sys.dm_exec_query_statistics_xml de base de LAST_QUERY_PLAN_STATSdonnées est désactivée en exécutant la commande suivante :
```
SELECT name, value, value_for_secondary, is_value_default 
FROM sys.database_scoped_configurations
WHERE name = 'LAST_QUERY_PLAN_STATS'
```
Vous pouvez activer les dernières statistiques du plan de requête au niveau de la base de données en exécutant ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON.

Utiliser SQL LogScout pour capturer des requêtes sans fin

Vous pouvez utiliser SQL LogScout pour capturer les journaux pendant l’exécution d’une requête sans fin. Utilisez le scénario de requête sans fin avec la commande suivante :

.\SQL_LogScout.ps1 -Scenario "NeverEndingQuery" -ServerName "SQLInstance"

Note

Ce processus de capture de journal nécessite que la requête longue consomme au moins 60 secondes de temps processeur.

SQL LogScout capture au moins trois plans de requête pour chaque requête consommatrice de processeur élevée. Vous pouvez trouver des noms de fichiers qui ressemblent servername_datetime_NeverEnding_statistics_QueryPlansXml_Startup_sessionId_#.sqlplanà . Vous pouvez utiliser ces fichiers à l’étape suivante lorsque vous passez en revue les plans pour identifier la raison de l’exécution longue des requêtes.

Étape 3 : Passer en revue les plans collectés

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

Comparez les plans d’exécution en procédant comme suit :

Ouvrez un fichier de plan d’exécution de requête précédemment enregistré (.sqlplan).
Cliquez avec le bouton droit dans une zone vide du plan d’exécution, puis sélectionnez Comparer le plan d’exécution.
Choisissez le deuxième fichier de plan de requête que vous souhaitez comparer.
Recherchez des flèches épaisses qui indiquent un grand nombre de lignes qui circulent 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 les deux plans.
Comparez les deuxième et troisième plans pour savoir si le plus grand flux de lignes se produit dans les mêmes opérateurs.

Par exemple:

Étape 4 : Résolution

Vérifiez que les statistiques sont mises à jour pour les tables utilisées dans la requête.
Recherchez les recommandations d’index manquantes dans le plan de requête et appliquez les recommandations d’index manquantes.
Simplifiez la requête :
- Utilisez des prédicats plus sélectifs WHERE pour réduire les données traitées à l’avant.
- Dissoyez-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 longtemps en raison d’un objectif de ligne d’optimiseur.
  - Vous pouvez également utiliser DISABLE_OPTIMIZER_ROWGOAL. Pour plus d’informations, consultez Les objectifs de ligne disparus non autorisés.
- Évitez d’utiliser des expressions de table courantes (CTEs) dans de tels cas, car elles combinent des instructions dans une seule requête volumineuse.
Essayez d’utiliser des indicateurs de requête pour produire un meilleur plan :
- HASH JOIN ou MERGE JOIN indicateur
- Indicateur FORCE ORDER
- Indicateur FORCESEEK
- RECOMPILE
- USE PLAN N'<xml_plan>' (si vous avez un plan de requête rapide que vous pouvez forcer)
Utilisez Magasin des requêtes (QDS) pour forcer un plan connu s’il existe un tel plan et si votre version de SQL Server prend en charge Magasin des requêtes.

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

Rétroaction

Cette page vous a-t-elle été utile ?

Last updated on 2025-08-19