Résoudre les erreurs de délai d’expiration des requêtes

Article
07/03/2023

Symptômes

Supposons qu’une application interroge les données d’une base de données SQL Server. Si la requête ne renvoie aucune donnée dans la valeur de délai d’expiration configurée (généralement 30 secondes), l’application annule la requête et génère l’un des messages d’erreur suivants :

Délai d’attente expiré. Le délai d’expiration s’est produit avant la fin de l’opération ou le serveur ne répond pas. L’instruction a été interrompue.
System.Data.SqlClient.SqlException : délai d’attente expiré. Le délai d’expiration s’est produit avant la fin de l’opération ou le serveur ne répond pas.

Explication

Ces erreurs se produisent côté application. L’application définit une valeur de délai d’expiration et, si le délai d’attente est atteint, elle annule la requête. Côté SQL Server, une annulation de requête du côté client provoque un événement Attention, erreur 3617 (MSSQLSERVER_3617). Si la valeur du délai d’attente côté application est définie sur 0 (aucune limite de temps), le Moteur de base de données exécute la requête jusqu’à ce qu’elle soit terminée.

Dans .NET Framework System.Data.SqlClient, la valeur du délai d’expiration est définie sur la propriété CommandTimeout.
Dans l’API ODBC, elle est définie par le biais de l’attribut SQL_ATTR_QUERY_TIMEOUT dans la fonction SQLSetStmtAttr.
Dans l’API Java Database Connectivity (JDBC), elle est définie par le biais de la méthode setQueryTimeout.
Dans OLEDB, elle est définie par le biais de la propriété DBPROP_COMMANDTIMEOUT sur la structure DBPROP.
Dans VBA (Excel), elle est définie par le biais de la propriété ADODB.Command.CommandTimeout.

Le délai d’expiration de la requête est différent d’une propriété de délai de connexion. Cette dernière contrôle la durée d’attente d’une connexion réussie et n’est pas impliquée dans l’exécution de la requête. Pour plus d’informations, consultez la section Le délai d’expiration de la requête est différent du délai de connexion.

Étapes de résolution des problèmes

La raison la plus courante des délais d’expiration des requêtes est, de loin, la sous-performance des requêtes. Cela signifie que la requête s’exécute plus longtemps que la valeur de délai d’expiration de requête prédéfinie. Accélérer l’exécution de la requête est le premier objectif recommandé de votre résolution des problèmes. Voici comment consulter les requêtes :

Utilisez Événements étendus ou SQL Trace pour identifier les requêtes qui provoquent les erreurs de délai d’expiration. Vous pouvez suivre l’événement Attention avec les événements étendus sql_batch_completed et rpc_completed, et les mettre en corrélation sur le même session_id. Si vous constatez qu’un événement terminé est immédiatement suivi d’un événement d’attention et que la durée de l’événement terminé correspond approximativement au paramètre de délai d’attente, vous avez identifié la requête. Voici un exemple :

Remarque

Dans cet exemple, la requête SELECT s’est exécutée pendant près de 30 secondes exactement, puis s’est interrompue. L’événement Attention qui a le même ID de session indique que la requête a été annulée par l’application.

Nom	Session_id	Sql_text	Durée (microsecondes)	Timestamp
sql_batch_started	54	Sélectionner … from Customers WHERE cid = 192937	NULL	2021-09-30 09:50:25.0000
sql_batch_completed	54	Sélectionner … from Customers WHERE cid = 192937	29999981	2021-09-30 09:50:55.0000
Attention	54	Sélectionner … from Customers WHERE cid = 192937	40000	2021-09-30 09:50:55.0400

Exécutez et testez les requêtes dans SQLCMD ou dans SQL Server Management Studio (SSMS).
Si les requêtes sont également lentes dans SQLCMD et SSMS, résolvez les problèmes de performances et améliorez ces dernières. Pour plus d’informations, consultez Résoudre les problèmes de requêtes lentes dans SQL Server

Remarque

Dans SQLCMD et SSMS, la valeur du délai d’expiration est définie sur 0 (aucune limite de temps) et les requêtes peuvent être testées et examinées.
Si les requêtes sont rapides dans SQLCMD et SSMS, mais lentes côté application, modifiez les requêtes pour utiliser les mêmes options SET que celles utilisées dans SQLCMD et SSMS. Comparez les options SET en collectant un suivi d’événements étendus (événements de connexion avec collect_options_text) et consultez la colonne options_text. Voici un exemple :
```
ALTER EVENT SESSION [setOptions] ON SERVER 
ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1) 
    ACTION(package0.event_sequence,package0.last_error,sqlos.system_thread_id,sqlserver.context_info,sqlserver.session_id,sqlserver.sql_text)), 
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(sqlos.system_thread_id,sqlserver.context_info,sqlserver.sql_text))
```
Pour plus d’informations, consultez l’article Résoudre les problèmes de différence de performances des requêtes entre l’application de base de données et SSMS.

Vérifiez si le paramètre CommandTimeout est inférieur à la durée de requête attendue. Si le paramètre de l’utilisateur est correct et que les délais d’expiration persistent, cela est dû à un problème de performances des requêtes. Voici un exemple de code ADO.NET avec une valeur de délai d’expiration définie sur 10 secondes :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace ConsoleApplication6
{
    class Program
    {
        static void Main()
        {
            string ConnectionString = "Data Source=.\sql2019;Integrated Security=SSPI;Initial Catalog=tempdb;";
            string queryString = "exec test";

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(queryString, connection);

                // Setting command timeout to 10 seconds
                command.CommandTimeout = 10;
                //command.ExecuteNonQuery();
                try {
                    command.ExecuteNonQuery();
                }
                catch (SqlException e) {
                    Console.WriteLine("Got expected SqlException due to command timeout ");
                    Console.WriteLine(e);
                }
            }
        }
    }
}

Le délai d’expiration de la requête est différent du délai de connexion

Un délai d’expiration de requête est différent d’un délai de connexion. Le dépassement du délai de connexion se produit lorsque la connexion initiale au serveur d’une base de données atteint un délai d’expiration prédéfini. À ce stade, aucune requête n’a été envoyée au serveur. Ces messages sont des exemples d’erreur de délai d’attente de connexion ou de connexion :

Le délai d’attente pour la connexion a expiré. Le délai d’attente s’est écoulé lors de la tentative de consommation de l’accusé de réception de la poignée de main de préconnexion. Cela peut être dû à un échec de la poignée de main de préconnexion ou au fait que le serveur n’a pas pu répondre dans les temps. Le temps écoulé lors de la tentative de connexion à ce serveur était de [Préconnexion] initialisation = 23 ; poignée de main = 14979 ;
Délai d’attente expiré. Le délai d’expiration s’est produit avant la fin de l’opération ou le serveur ne répond pas. System.ComponentModel.Win32Exception (0x80004005) : dépassement du délai d’attente.

La valeur du délai d’expiration de la connexion est un paramètre côté client et est généralement définie sur 15 secondes. Pour plus d’informations sur la résolution des problèmes liés au délai de connexion, consultez la section Résoudre les problèmes de délai de connexion. Pour la résolution des problèmes liés au délai d’expiration des requêtes, regardez cette vidéo.

Partager via