Comment identifier les requêtes SQL d'exécution lente dans SQL Server ?
Les requêtes à exécution lente sont l'un des problèmes les plus courants dans toutes les organisations traitant d'énormes quantités de données. Et le problème le plus difficile, est de savoir comment trouver les requêtes lentes et déterminer quelle est la cause réelle du problème de performances. Heureusement, la solution, dans la plupart des cas, est simple. Heureusement, il existe certains outils et techniques qu'un développeur ou un DBA devrait toujours utiliser (au moins) pour avoir une idée juste des requêtes lentes.
Moniteur d'activité (Activity Monitor)
Comme pour tout type de travail dans le monde, des outils spéciaux sont nécessaires. Le processus d'identification des requêtes à exécution lente n'est pas différent. Le premier outil, est un outil intégré dans SQL Server Management Studio; appelé «Moniteur d'activité» ou «Activity Monitor». Vous pouvez l'afficher en cliquant avec le bouton droit sur le nom de l'instance dans SQL Server Management Studio et en sélectionnant «Moniteur d'activité» ou «Activity Monitor». Le moniteur d'activité indique quelles sont les activités actuelles et récentes dans votre instance SQL Server.
Le moniteur d'activité affiche les graphiques des temps de traitement, des tâches en attente et des demandes de lots. En général, plus le nombre de comptages est bas, meilleures sont les performances. Dans les grandes organisations avec une charge énorme, il peut y avoir un grand nombre de demandes par lots avec des temps de traitement élevés, mais cela n'indique pas nécessairement un problème de performances.
Après «Vue d'ensemble» ou «Overview», vous devez vous concentrer sur les processus, ce qui vous donne accès à tous les processus en cours d'exécution dans votre instance et examine plus en détail le nombre de processus en attente, bloqués ou bloqués. De cette façon, vous pouvez vous faire une idée si vos requêtes sont lentes en raison d'une attente spécifique ou si les requêtes prenant du temps sont bloquées par d'autres processus. Dans cette vue, vous pouvez faire un clic droit sur n'importe quel processus et cliquer sur Details pour afficher le TSQL réel en cours d'exécution pour cette session.
Les requêtes bloquées sont celles étant effectivement suspendues en raison de tout autre processus travaillant sur les ressources dont dépend le processus. Donc, si vous trouvez des requêtes étant bloquées par d'autres processus, vérifiez simplement le bloqueur de racine provoquant tout le blocage en regardant la colonne Bloqué par ou Blocked By. Essayez de ne considérer que cette requête, pas tous les processus bloqués.
Les requêtes attendant une ressource spécifique vous donnent des informations sur la ressource d'attente afin que vous puissiez vérifier le type d'attente et essayer de trouver la solution à ce problème. Certaines des statistiques d'attente les plus courantes sont répertoriées dans la section complète des types d'attente SQL Server de SQL Shack. Parcourez là en détail pour d'autres actions. Les requêtes actives et récentes coûteuses vous donneront des informations sur les requêtes ayant un processeur élevé, des lectures logiques ou un temps écoulé élevé.
Vous pouvez accéder à chaque section pour les requêtes coûteuses actuelles ou récentes. Triez-les par Temps écoulé ou Elapsed time, Lecture logique ou Logical Read et Temps CPU ou CPU Time un par un et vérifiez le plan d'exécution. Dans le plan d'exécution, vous serez en mesure de découvrir pourquoi ces requêtes coûteuses prenaient un temps excessif afin que vous puissiez prendre les mesures appropriées pour les résoudre.
Magazin des requêtes (Query Store)
L'outil suivant est le «Magazin des requêtes» ou «Query Store». Il est utile et pourrait vous sauver la vie dans une situation où vous avez été appelé au milieu de la nuit pour vérifier pourquoi SQL Server était lent 1 heure plus tôt. En règle générale, avant SQL Server 2016, sans application tierce ni solution personnalisée, vous ne pouvez pas consulter l'historique de l'exécution des requêtes. Ainsi, le «Magazin des requêtes» ou «Query Store» offre de nombreuses fonctionnalités à valeur ajoutée à cet égard. Si vous disposez de SQL Server 2016 ou version ultérieure, vous devez d'abord l'activer dans les propriétés de votre base de données. On peut utiliser la requête SQL suivante pour activer l'option :
ALTER DATABASE [mabasededonnees] SET QUERY_STORE = ON;
Après avoir activé le «Magazin des requêtes» ou «Query Store», vous aurez les propriétés de votre base de données comme indiqué dans la capture d'écran ci-dessous :
Une fois que vous avez activé le «Magazin des requêtes» ou «Query Store», vous pouvez développer les objets de base de données et accéder aux «Principales requêtes consommatrices de ressources» ou «Top Resource Consuming Queries», comme illustré dans la capture d'écran ci-dessous :
Il est préférable de laissez un jour ou deux au «Magazin des requêtes» ou «Query Store» pour capturer la charge de production afin de pouvoir travailler facilement dessus avec une charge réelle.