Résoudre les problèmes de différence de performances des requêtes entre l’application de base de données et SSMS

Lorsque vous exécutez une requête dans une application de base de données, elle s’exécute plus lentement que la même requête dans une application comme SQL Server Management Studio (SSMS), Azure Data Studio ou SQLCMD.

Ce problème peut survenir pour l’une des raisons suivantes :

• Les requêtes utilisent des paramètres ou des variables différents.

• Les requêtes sont envoyées au serveur sur différents réseaux ou il existe une différence dans la façon dont les applications traitent les données.

• Les options SET de l’application de base de données et de SSMS sont différentes.

Pour résoudre le problème, procédez comme suit :

Étape 1 : Vérifier que les requêtes sont envoyées avec les mêmes paramètres ou variables

Pour comparer ces requêtes et vérifier qu’elles sont identiques dans tous les sens, procédez comme suit :

1. Ouvrez votre SSMS et connectez-le au moteur de base de données que vous utilisez.

2. Exécutez les commandes suivantes pour créer une session d’événements étendus :

   CREATE EVENT SESSION <EventSessionName> ON SERVER
   ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)),
   ADD EVENT sqlserver.login(SET collect_options_text=(1)
       ACTION(sqlserver.client_app_name)),
   ADD EVENT sqlserver.rpc_completed,
   ADD EVENT sqlserver.sp_statement_completed(
       ACTION(sqlserver.client_app_name)),
   ADD EVENT sqlserver.sql_batch_completed(
       ACTION(sqlserver.client_app_name)),
   ADD EVENT sqlserver.sql_statement_completed(
       ACTION(sqlserver.client_app_name))
   ADD TARGET package0.event_file(SET filename=N'<FilePath>')
   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)
   GO
   

   Remarque

   Remplacez les espaces réservés <EventSessionName> et <FilePath> par ceux que vous souhaitez créer.

3. Exécutez les commandes suivantes pour démarrer la session EventSessionName :

   ALTER EVENT SESSION <EventSessionName> ON SERVER
   STATE = START
   

4. Exécutez vos requêtes pour reproduire le problème.

5. Utilisez l’une des méthodes suivantes pour analyser les données collectées :

   • Ouvrez Windows Explorer, recherchez le fichier .xel cible, puis double-cliquez dessus. Le fichier est ouvert dans une autre fenêtre SSMS que vous pouvez utiliser pour afficher et analyser.

   • Dans Explorateur d'objets, développezSessions>d’événements étendus>de gestion>EventSessionName, cliquez avec le bouton droit sur package0.event_file, puis sélectionnez Afficher les données cibles....

   • Recherchez l’emplacement des fichiers .xel et lisez ce fichier à l’aide de la fonction sys.fn_xe_file_target_read_file.

6. Comparez l’instruction Field en vérifiant les événements suivants :

   • sp_statement_completed
   • sql_batch_completed
   • sql_statement_completed
   • rpc_completed

Pour plus d’informations sur les requêtes identiques, consultez les exemples suivants :

• Si les procédures stockées ou les fonctions ont des valeurs de paramètre différentes, les heures de requête peuvent être différentes :

  • SpUserProc @p1 = 100

  • SpUserProc @p1 = 270

• Les requêtes suivantes sont différentes. La première requête utilise la densité moyenne de l’histogramme pour l’estimation de la cardinalité, tandis que la deuxième requête utilise l’étape d’histogramme pour l’estimation de la cardinalité :

  • declare @variable1 = 123
    select * from table where c1 = @variable1
    

  • select * from table where c1 = 123
    

Pour la même raison que ci-dessus, la comparaison de l’exécution d’une procédure stockée à l’exécution de la requête ad hoc équivalente (à l’aide de variables locales) peut être différente. Les instructions identiques doivent être comparées.

Étape 2 : Mesurer le temps d’exécution sur le serveur

Pour une comparaison précise des durées de requête, vous pouvez exclure le temps de latence réseau ou le temps de traitement des données spécifique à l’application. Utilisez l’une des méthodes suivantes pour mesurer uniquement le temps d’exécution sur le SQL Server :

• Exécutez votre requête à l’aide de SET STATISTICS TIME :

  SET STATISTICS TIME ON
  <YourQuery>
  SET STATISTICS TIME OFF
  

• Utilisez le XEvent de l’étape 1 pour examiner la durée ou le temps écoulé d’une requête (classe SQL:StmtCompletedd’événements , SQL:BatchCompletedou RPC:Completed).

Dans certains cas, la différence de temps entre les requêtes peut être due à l’exécution d’une application dans un autre réseau ou à l’application elle-même. Lorsque vous comparez l’exécution sur le serveur, vous comparez la durée d’exécution des requêtes sur le serveur.

Étape 3 : Vérifier les options SET pour chaque connexion

Il existe des options SET qui affectent le plan de requête, ce qui signifie qu’elles peuvent modifier le choix du plan de requête. Par conséquent, si une application de base de données utilise des options de jeu différentes de SSMS, chaque option de jeu peut obtenir un plan de requête différent. Par exemple, ARITHABORT, NUMERIC_ROUNDABORT, ROWCOUNT, FORCEPLAN et ANSI_NULLS. La différence la plus courante observée entre les applications SSMS et .NET est l’option SET ARITHABORT . Par défaut, l’option est définie sur ON dans SSMS, mais sur OFF dans la plupart des applications de base de données. En fonction des besoins de votre application, définissez ARITHABORT sur le même paramètre dans SSMS et dans l’application pour une comparaison valide entre les deux.

Avertissement

Le paramètre ARITHABORT par défaut pour SQL Server Management Studio est ON. Les applications clientes qui définissent ARITHABORT sur OFF peuvent recevoir différents plans de requête, ce qui rend difficile la résolution des requêtes peu performantes. Autrement dit, la même requête peut s’exécuter rapidement dans Management Studio, mais lente dans l’application. Lors de la résolution des problèmes de requêtes avec Management Studio, faites toujours correspondre le paramètre ARITHABORT du client.

Pour obtenir la liste de toutes les options affectant le plan, consultez Définir des options.

Pour vous assurer que les options SET de SSMS et de l’application sont identiques pour pouvoir effectuer une comparaison valide, procédez comme suit :

1. Utilisez les données collectées à l’étape 1.

2. Comparez les options de jeu en vérifiant les événements login et existing_connection, en particulier les colonnes d’options options_text et .