Partager via

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

  • Article

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 différents paramètres ou variables.

  • 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 dans l’application de base de données et 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 s’assurer qu’elles sont identiques de toutes façons, 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 É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

    Note

    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 l’Explorateur Windows, 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 l’Explorateur d’objets, développez EventSessionName des sessions>d’événements étendus>de gestion>, 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 temps de requête peuvent être différents :

    • 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 cardinalité, tandis que la deuxième requête utilise l’étape d’histogramme pour l’estimation de 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 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 set 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 définie 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 l’application pour une comparaison valide entre les deux.

Avertissement

Par défaut, le paramètre ARITHABORT pour SQL Server Management Studio est défini sur ON. Les applications clientes qui configurent ARITHABORT sur OFF peuvent recevoir des plans de requête différents, ce qui complique la résolution des celles dont les performances sont médiocres. Autrement dit, la même requête peut s’exécuter rapidement dans Management Studio, mais lente dans l’application. Pour résoudre les requêtes avec Management Studio utilisez toujours le paramètre ARITHABORT du client.

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

Pour vous assurer que les options SET dans SSMS et 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_connectionen particulier les options_text colonnes et options.