Résoudre les problèmes de requêtes lentes sur SQL Server

Introduction

Cet article explique comment gérer un problème de performances que les applications peuvent utiliser en association avec SQL Server : ralentissement des performances d’une requête spécifique ou d’un groupe de requêtes. Si vous résolvez un problème de performances, mais que vous n’avez pas limité le problème à une requête spécifique ou à un petit groupe de requêtes qui s’exécutent plus lentement que prévu, reportez-vous à la rubrique surveiller et régler les performances avant de poursuivre.

Dans cet article, nous partons du principe que vous avez utilisé l’article 298475 pour limiter l’étendue du problème et que vous avez capturé une trace du générateur de profils SQL avec les événements et les colonnes de données spécifiques qui sont détaillés dans l’article 224587.

Le réglage des requêtes de base de données peut être un effort à facettes multiples. Les sections suivantes présentent les éléments courants à examiner lorsque vous examinez les performances des requêtes.

Version du produit d’origine :   SQL Server
Numéro de la base de connaissances initiale :   243589

Vérifier l’existence des index corrects

L’une des premières vérifications à effectuer lorsque vous rencontrez des temps d’exécution de requête lents est une analyse d’index. Si vous étudiez une seule requête, vous pouvez utiliser l’option analyser la requête dans l’Assistant de paramétrage du moteur de base de données dans l’analyseur de requêtes SQL Server ; Si vous disposez d’une trace SQL Profiler d’une grande charge de travail, vous pouvez utiliser l’Assistant Paramétrage du moteur de base de données. Ces deux méthodes utilisent l’optimiseur de requêtes SQL Server pour déterminer les index qui seraient utiles pour les requêtes spécifiées. Il s’agit d’une méthode efficace pour déterminer si les index corrects existent dans votre base de données.

Pour plus d’informations sur l’utilisation de l’Assistant Paramétrage du moteur de base de données, voir la rubrique « démarrer et utiliser l’Assistant Paramétrage du moteur de base de données » dans la documentation en ligne de SQL Server.

Si vous avez mis à niveau votre application à partir d’une version antérieure de SQL Server, différents index peuvent être plus efficaces dans la nouvelle version de SQL Server en raison des modifications apportées à l’optimiseur et au moteur de stockage. L’Assistant Paramétrage du moteur de base de données vous permet de déterminer si une modification apportée à la stratégie d’indexation améliore les performances.

Supprimer toutes les options de requête, de table et de jointure

Les conseils remplacent l’optimisation des requêtes et peuvent empêcher l’optimiseur de requêtes de choisir le plan d’exécution le plus rapide. En raison des modifications apportées à l’optimiseur, les conseils qui améliorent les performances dans les versions antérieures de SQL Server peuvent être sans effet ou nuire aux performances dans les générations SQL Server ultérieures. En outre, les indicateurs de jointure peuvent entraîner une dégradation des performances en fonction des raisons suivantes :

  • Les indicateurs de jointure empêchent une requête ad hoc d’être éligibles pour le paramétrage automatique et la mise en cache du plan de requête.

  • Lorsque vous utilisez un Conseil de jointure, il implique que vous souhaitez forcer l’ordre de jointure pour toutes les tables de la requête, même si ces jointures n’utilisent pas explicitement une indication.

Si la requête que vous analysez comprend des indications, supprimez-les, puis réévaluez les performances.

Examiner le plan d’exécution

Une fois que vous avez confirmé que les index corrects existent et qu’aucune indication ne limite la capacité de l’optimiseur à générer un plan efficace, vous pouvez examiner le plan d’exécution de la requête. Vous pouvez utiliser l’une des méthodes suivantes pour afficher le plan d’exécution d’une requête :

  • Générateur de profils SQL

    Si vous avez capturé l’événement MISC : Execution plan dans le générateur de profils SQL, il se produit immédiatement avant l’événement StmtCompleted de la requête pour le SPID (System Process ID).

  • Analyseur de requêtes SQL : affichage graphique des offres

    Une fois la requête sélectionnée dans la fenêtre de requête, cliquez sur le menu requête, puis sur Afficher le plan d’exécution estimé.

    Notes

    Si la procédure stockée ou le lot crée et référence des tables temporaires, vous devez utiliser une instruction SET STATISTICs PROFILe ON ou créer des tables temporaires de manière explicite avant d’afficher le plan d’exécution.

  • SHOWPLAN_ALL et SHOWPLAN_TEXT

    Pour recevoir une version texte du plan d’exécution estimé, vous pouvez utiliser les options SET SHOWPLAN_ALL et SET SHOWPLAN_TEXT . Pour plus d’informations, reportez-vous aux rubriques set SHOWPLAN_ALL (t-SQL) et SET SHOWPLAN_TEXT (t-SQL) dans la documentation en ligne de SQL Server.

    Notes

    Si la procédure stockée ou le lot crée et référence des tables temporaires, vous devez utiliser l’option définir les statistiques sur le profil ou créer explicitement les tables temporaires avant d’afficher le plan d’exécution.

  • PROFIL DE STATISTIQUES

    Lorsque vous affichez le plan d’exécution estimé, soit graphiquement, soit à l’aide de SHOWPLAN, la requête n’est pas exécutée. Par conséquent, si vous créez des tables temporaires dans un lot ou une procédure stockée, vous ne pouvez pas afficher les plans d’exécution estimés, car les tables temporaires n’existeront pas. STATISTICs PROFILe exécute d’abord la requête, puis affiche le plan d’exécution réel. Pour plus d’informations, reportez-vous à la rubrique Set Statistics Profile (T-SQL) dans la documentation en ligne de SQL Server. Lorsqu’il est en cours d’exécution dans l’analyseur de requêtes SQL, il apparaît au format graphique sous l’onglet Plan d’exécution dans le volet résultats.

Pour plus d’informations sur la façon d’afficher le plan d’exécution estimé, reportez-vous à la rubrique afficher le plan d’exécution estimé dans la documentation en ligne de SQL Server.

Examiner la sortie Showplan

La sortie Showplan fournit de nombreuses informations sur le plan d’exécution que SQL Server utilise pour une requête particulière. Voici quelques aspects de base du plan d’exécution que vous pouvez afficher pour déterminer si vous utilisez le meilleur plan :

  • Utilisation correcte de l’index

    La sortie Showplan affiche chaque table impliquée dans la requête et le chemin d’accès utilisé pour obtenir des données à partir de celle-ci. Avec le showplan graphique, déplacez le pointeur sur un tableau pour afficher les détails de chaque tableau. Si un index est en cours d’utilisation, vous voyez la recherche d’index ; Si un index n’est pas utilisé, vous pouvez voir la table Scan pour un segment de mémoire ou une analyse d’index en cluster pour une table qui a un index cluster. L’analyse de l’index cluster indique que la table est en cours d’analyse par le biais de l’index cluster, pas que l’index clusterisé est utilisé pour accéder directement aux lignes individuelles.

    Si vous déterminez qu’un index utile existe et qu’il n’est pas utilisé pour la requête, vous pouvez essayer de forcer l’index à l’aide d’un indicateur d’index. Pour plus d’informations sur les conseils d’index, voir la rubrique from (T-SQL) dans la documentation en ligne de SQL Server.

  • Ordre de jointure correct

    La sortie Showplan indique dans quelle commande les tables impliquées dans une requête sont jointes. Pour les jointures de boucle imbriquée, le tableau supérieur qui est affiché est le tableau externe et il doit être le plus petit des deux tables. Pour les jointures de hachage, la table supérieure devient l’entrée de création et doit également être la plus petite des deux tables. Toutefois, Notez que la commande est moins importante, car le processeur de requêtes peut inverser la construction et tester les entrées au moment de l’exécution si elle constate que l’optimiseur a pris une décision incorrecte. Vous pouvez déterminer la table qui renvoie moins de lignes en vérifiant les estimations de nombre de lignes dans la sortie Showplan.

    Si vous estimez que la requête peut tirer parti d’une autre commande de jointure, vous pouvez essayer de forcer la commande de jointure avec un Conseil de jointure. Pour plus d’informations sur les indicateurs de jointure, voir la rubrique from (T-SQL) dans la documentation en ligne de SQL Server.

    Notes

    L’utilisation d’un indicateur de jointure dans une requête de grande taille force implicitement l’ordre de participation pour les autres tables de la requête comme si elle FORCEPLAN avait été définie.

  • Type de participation correct

    SQL Server utilise des jointures de boucles imbriquées, de hachage et de fusion. Si une requête à exécution lente utilise une technique Join sur une autre, vous pouvez essayer de forcer un type de jointure différent. Par exemple, si une requête utilise une jointure par hachage, vous pouvez forcer une jointure par boucles imbriquées à l’aide de l’indicateur de jonction de boucle. Consultez la rubrique « FROM (T-SQL) » dans la documentation en ligne de SQL Server pour plus d’informations sur les conseils de jointure.

    L’utilisation d’un indicateur de jointure dans une requête de grande taille force implicitement le type de jointure pour les autres tables de la requête comme si elle FORCEPLAN avait été définie.

  • Exécution parallèle

    Si vous utilisez un ordinateur multiprocesseur, vous pouvez également vérifier si un plan parallèle est en cours d’utilisation. Si le parallélisme est en cours d’utilisation, vous voyez un événement parallelism (Gather Streams). Si une requête particulière est lente lorsqu’elle utilise un plan parallèle, vous pouvez essayer de forcer un plan non parallèle à l’aide de l’indicateur OPTION (MAXDOP 1). Pour plus d’informations, consultez la rubrique « SELECT (T-SQL) » dans la documentation en ligne de SQL Server.

Pour plus d’informations sur l’utilisation de la sortie du plan d’exécution Showplan, consultez les rubriques suivantes dans la documentation en ligne de SQL Server :

  • Enregistrer un plan d’exécution au format XML

  • Comparer et analyser les plans d’exécution

  • Référence des opérateurs physiques et logiques Showplan

Attention

Étant donné que l’optimiseur de requêtes sélectionne généralement le meilleur plan d’exécution pour une requête, Microsoft vous recommande d’utiliser des indicateurs de jointure, des indicateurs de requête et des indicateurs de table uniquement en dernier ressort, et ce, uniquement si vous êtes un administrateur de base de données expérimenté.