Surveiller les composants SQL Server

  • Article

S’applique à :SQL Server

La surveillance est importante, car SQL Server fournit un service dans un environnement dynamique. Les données dans l'application sont fluctuantes. Le type d'accès requis par les utilisateurs peut changer. Le mode de connexion des utilisateurs change. Les types des applications accédant à SQL Server peuvent même changer, mais SQL Server gère automatiquement les ressources de niveau système, telles que la mémoire et l'espace disque, pour minimiser les paramétrages manuels nécessaires au niveau système. La surveillance permet aux administrateurs d'identifier les tendances de performances afin de déterminer si des modifications s'imposent.

Pour surveiller efficacement un composant SQL Server :

  1. Déterminer vos objectifs en matière de surveillance.
  2. Sélectionner l'outil approprié.
  3. Identifier les composants à surveiller.
  4. Sélectionner les éléments de mesure pour ces composants.
  5. Surveiller le serveur.
  6. Analyser les données.

Chacune de ces étapes est décrite ci-après.

Déterminer vos objectifs en matière de surveillance

Pour surveiller efficacement SQL Server, vous devez identifier clairement les motifs de la surveillance. Ces motifs peuvent être les suivants :

  • Établir un niveau de référence des performances.
  • Identifier les fluctuations de performances dans le temps.
  • Diagnostiquer des problèmes de performances spécifiques.
  • Identifier les composants ou processus à optimiser.
  • Comparer les effets de différentes applications clientes sur les performances.
  • Auditer l'activité des utilisateurs.
  • Tester un serveur sous différentes charges.
  • Tester l'architecture d'une base de données.
  • Tester les programmes de maintenance.
  • Tester les plans de sauvegarde et de restauration.
  • Déterminer le moment où il convient de modifier votre configuration matérielle.

Sélectionner l'outil approprié

Une fois que vous avez identifié les motifs de la surveillance, vous devez sélectionner les outils correspondant à ce type de surveillance. Le système d'exploitation Windows et SQL Server comportent un jeu complet d'outils permettant de surveiller les serveurs dans des environnements riches en transactions. Ces outils révèlent clairement la condition d'une instance du moteur de base de données SQL Server ou d'une instance de SQL Server Analysis Services.

Windows fournit les outils suivants pour la surveillance d'applications s'exécutant sur un serveur :

  • Démarrez Performance Monitor (Windows), qui permet de collecter et d'afficher des données en temps réel sur des activités, telles que l'utilisation de la mémoire, du disque et du processeur.
  • Journaux et alertes de performance ;
  • Gestionnaire des tâches

Pour plus d'informations sur les outils Windows ou Windows Server, consultez la documentation Windows.

SQL Server fournit les outils suivants pour surveiller les composants de SQL Server :

Important

Trace SQL et SQL Server Profiler sont dépréciés. L’espace de noms Microsoft.SqlServer.Management.Trace qui contient les objets Trace et Replay Microsoft SQL Server est également déconseillé.

Cette fonctionnalité sera supprimée dans une version future de SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.

Utilisez plutôt des événements étendus. Pour plus d’informations sur l’aperçu des événements étendus, consultez Démarrage rapide : événements étendus et Utiliser SSMS XEvent Profiler.

Remarque

Le Générateur de profils SQL pour les charges de travail Analysis Services n'est PAS déprécié et continuera à être pris en charge.

Pour des informations sur la surveillance de SQL Server, voir Outils de surveillance et de réglage des performances.

Identifier les composants à surveiller

La troisième étape dans la surveillance d'une instance de SQL Server consiste à identifier les composants à surveiller. Par exemple, si vous utilisez le Générateur de profils SQL pour tracer un serveur, vous pouvez définir la trace pour collecter des données sur des événements spécifiques. Vous pouvez également exclure des événements qui ne s'appliquent pas à votre situation.

Sélectionner les éléments de mesure pour les composants surveillés

Après l'identification des composants à surveiller, déterminez les éléments de mesure à utiliser pour la surveillance. Par exemple, après avoir sélectionné les événements à inclure dans une trace, vous pouvez choisir d'inclure uniquement des données spécifiques concernant ces événements. La limitation de la trace aux données pertinentes permet de réduire la quantité de ressources système requise pour effectuer le suivi.

Surveiller le serveur

Pour surveiller le serveur, exécutez l'outil de surveillance que vous avez configuré pour collecter des données. Par exemple, après avoir défini une trace, vous pouvez l’exécuter pour recueillir des données concernant les événements qui se sont produits sur le serveur.

Analyser les données

Une fois le suivi terminé, analysez les données pour vérifier si vous avez atteint votre objectif de surveillance. Si ce n'est pas le cas, modifiez les composants ou les indicateurs utilisés pour surveiller le serveur.

Le processus de capture de données d’événement et de leur exploitation est décrit ci-dessous.

  1. Appliquer des filtres pour limiter les données d'événement recueillies.

    Le fait de limiter les données d'événement permet de s'attacher uniquement aux événements pertinents par rapport au scénario de surveillance en place. Par exemple, si vous souhaitez surveiller les requêtes lentes, vous pouvez utiliser un filtre afin de ne vous intéresser qu’aux requêtes dont l'exécution par l'application sur une base de données particulière prend plus de 30 secondes.

    Pour plus d'informations sur le filtrage des traces d'événements étendus, consultez Démarrage rapide : Événements étendus.

    Pour plus d'informations sur le filtrage de Trace SQL, consultez Définir un filtre de trace (Transact-SQL) et Filtrer des événements dans une trace (Générateur de profils SQL).

  2. Surveiller (capturer) les événements.

    Dès qu'elle est activée, la surveillance active capture des données à partir de l'application, de l'instance de SQL Server ou du système d'exploitation spécifié. Par exemple, lorsque l'activité du disque est analysée à l'aide du Moniteur système, ce dernier capture les données d'événement, notamment les lectures et les écritures sur le disque et les affiche sur l'écran. Pour plus d’informations, consultez Monitorer l’utilisation des ressources (Analyseur de performances).

  3. Enregistrer les données d'événement capturées.

    L’enregistrement des données d’événement capturées vous permet de les analyser ultérieurement. Les données d’événement capturées sont enregistrées dans un fichier pouvant être rechargé dans l’outil qui l’a créé à l’origine pour analyse. L'enregistrement des données d'événement capturées est essentiel lors de la création d'une base de référence des performances. Les données du niveau de référence des performances sont enregistrées et utilisées lors de la comparaison des données d'événement récemment capturées afin de déterminer si les performances sont optimales.

    Les événement étendus permettent d’enregistrer des données d’événements dans un fichier d’événements, un compteur d’événements, un histogramme et une mémoire tampon en anneau. Pour plus d'informations, consultez Cibles des Événements étendus.

    Les données d'événements de Trace SQL peuvent même être relues à l'aide de l'utilitaire Distributed Replay ou du Générateur de profils SQL. Le Générateur de profils SQL permet d'enregistrer les données d'événement dans un fichier ou une table SQL Server. Pour plus d’informations, consultez Modèles et autorisations du générateur de SQL Server Profiler.

  4. Créer des modèles de trace contenant les paramètres spécifiés pour capturer les événements.

    Ces modèles contiennent des spécifications concernant les événements proprement dits, les données d’événement et les filtres utilisés pour la capture de données. Ils permettent de surveiller ultérieurement un ensemble spécifique d'événements sans avoir à redéfinir les événements, les données d'événement et les filtres. Par exemple, si vous voulez souvent surveiller le nombre d'interblocages et les utilisateurs impliqués dans ces interblocages, vous pouvez créer un fichier définissant ces événements, données d'événement et filtres d'événement, puis enregistrer le modèle et appliquer le filtre la prochaine fois que vous voudrez surveiller les interblocages.

    Une définition de session d’événements étendus est un modèle qui peut être scripté et réutilisé. Pour créer et gérer les sessions, consultez Gérer les sessions d’événements dans l’Explorateur d’objets. Le Management Studio XEvent Profiler fournit déjà des modèles prêts à l'emploi. Pour plus d’informations, consultez Utiliser SSMS XEvent Profiler.

    Le Générateur de profils SQL utilise les modèles de trace à cet effet. Pour plus d'informations, consultez Définir les valeurs par défaut des définitions de trace (Générateur de profils SQL) et Créer un modèle de trace (Générateur de profils SQL).

    Conseil

    Une définition de Trace SQL peut être convertie en session d’événements étendus. Pour plus d’informations, consultez Convertir un script Trace SQL existant en session d’événements étendus.

  5. Analyser les données d'événement capturées.

    Les données d'événement capturées sont chargées dans l'application qui les a capturées afin d’être analysées.

    Par exemple, une trace d'événements étendus capturée peut être rechargée dans SQL Server Management Studio à des fins de consultation et d'analyse. Pour plus d'informations, consultez Afficher les données d'événements dans SQL Server Management Studio.

    Les données de Trace SQL peuvent être rechargées dans le Générateur de profils SQL à des fins de consultation et d'analyse. Pour plus d’informations, consultez Afficher et analyser des traces avec SQL Server Profiler.

    L'analyse des données d'événement implique l'identification des événements et de leur cause. Ces informations vous permettent d'effectuer des modifications susceptibles d'améliorer les performances, telles que l'ajout de mémoire, la modification d'index, la correction de problèmes de code avec des procédures stockées et des instructions Transact-SQL, en fonction du type d'analyse effectuée. Par exemple, vous pouvez utiliser l'Assistant Paramétrage du moteur de base de données pour analyser une trace capturée à partir d'événements étendus ou du Générateur de profils SQL et créer des recommandations d'index en fonction des résultats.

  6. Relire les données d’événement capturées (facultatif).

    La relecture d'événements permet d'établir une copie de test de l'environnement de base de données à partir duquel les données ont été capturées, puis de répéter les événements capturés tels qu'ils se sont initialement produits sur le système réel. Cette fonction n'est disponible qu'avec l'utilitaire Distributed Replay ou le Générateur de profils SQL. Ces événements peuvent être relus à la vitesse à laquelle ils se sont produits, aussi rapidement que possible (pour contraindre le système) ou, plus vraisemblablement, pas à pas (pour analyser le système après chaque événement). L'analyse des événements exacts dans un environnement de test empêche tout effet nuisible sur le système de production. Pour plus d’informations, consultez Relire des traces.