Vue d’ensemble de la surveillance et du paramétrage des performances
La surveillance des bases de données a pour objectif d'évaluer le fonctionnement d'un serveur. Une surveillance efficace implique la prise de captures instantanées périodiques des performances actuelles afin d'isoler les processus à l’origine des problèmes, ainsi que la collecte de données en continu pour suivre de près les tendances des performances. Microsoft SQL Server et le système d'exploitation Microsoft Windows fournissent des utilitaires qui permettent de contrôler les conditions actuelles de la base de données et de suivre l'évolution des performances en fonction de l’évolution de ces conditions.
La surveillance de SQL Server offre les possibilités suivantes :
Déterminer si vous pouvez améliorer les performances. Par exemple, en surveillant les temps de réponse des requêtes les plus fréquentes, vous pouvez déterminer s'il faut modifier les requêtes ou les index des tables.
Évaluer l'activité des utilisateurs. Par exemple, en surveillant les utilisateurs qui tentent de se connecter à une instance de SQL Server, vous pouvez déterminer si la sécurité est correctement configurée et tester les applications et les systèmes de développement. Par exemple, en surveillant les requêtes SQL au fur et à mesure de leur exécution, vous pouvez déterminer si elles sont correctement rédigées et si elles produisent les résultats attendus.
Résoudre les éventuels problèmes ou déboguer des composants d'application, comme des procédures stockées.
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, de manière à minimiser les paramétrages manuels nécessaires au niveau système. La surveillance permet toutefois aux administrateurs d'identifier les tendances de performances afin de déterminer si des modifications s'imposent.
Pour que la surveillance d'un composant de SQL Server soit efficace, vous devez effectuer les opérations suivantes :
Déterminer vos objectifs en matière de surveillance.
Sélectionner l'outil approprié.
Identifier les composants à surveiller.
Sélectionner les éléments de mesure pour ces composants.
Surveiller le serveur.
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 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 appropriés. 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 :
Moniteur système, 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 de performance et alertes.
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 la surveillance des composants de SQL Server :
Trace SQL
SQL Server Profiler
Moniteur d'activité de SQL Server Management Studio
Plan d’exécution de SQL Server Management Studio
Procédures stockées
Commandes DBCC (Database Console Commands)
Fonctions intégrées
Indicateurs de trace
Pour plus d'informations sur les outils de surveillance de SQL Server, consultez Outils d'analyse et de paramétrage 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 SQL Server Profiler pour tracer un serveur, vous définir la trace de sorte à collecter des données concernant 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 éléments de mesure 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.
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, consultez Procédure : définir un filtre de trace (Transact-SQL) et Procédure : Filtrage des événements dans une trace (Générateur de profils SQL Server).
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 à l'écran. Pour plus d'informations, consultez Surveillance de l'utilisation des ressources (Moniteur système).
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, voire de les relire avec SQL Server Profiler. 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. SQL Server Profiler permet d'enregistrer les données d'événement dans une table SQL Server. L'enregistrement des données d'événement capturées est essentiel lors de la création d'un niveau 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 valeurs capturées afin de déterminer si les performances sont optimales. Pour plus d'informations, consultez Utilisation du Générateur de profils SQL Server.
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 de blocages et les utilisateurs impliqués dans ces blocages, 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 blocages. SQL Server Profiler utilise les modèles de trace à cet effet. Pour plus d'informations, consultez Procédure : Définition des paramètres par défaut de trace (Générateur de profils SQL Server) et Procédure : créer un modèle de trace (Générateur de profils SQL Server).
Analyser les données d'événement capturées.
Les données d'événement capturées et enregistrées sont chargées dans l'application qui les a capturées afin d’être analysées. Par exemple, une trace capturée depuis SQL Server Profiler peut y être rechargée en vue d’un affichage et d'une analyse. Pour plus d'informations, consultez Affichage et analyse des traces avec le Générateur de profils SQL Server.
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 Moteur de base de données pour analyser automatiquement une trace capturée depuis SQL Server Profiler et créer des recommandations d'index en fonction des résultats. Pour plus d'informations, consultez Paramétrage du modèle physique de la base de données.
Relire les données d'événement capturées.
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 et de répéter les événements capturés tels qu'ils se sont initialement produits sur le système réel. Cette possibilité n'est offerte que dans SQL Server Profiler. 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 Relecture de traces.