Gérer, Superviser et Optimiser

2. Surveillance et réglage des performances

Le but de la surveillance des bases de données est d'évaluer le fonctionnement d'un
serveur. Une surveillance efficace implique la prise d'instantanés 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.
L'évaluation continue des performances de la base de données vous permet de réduire
les temps de réponse et accélère le débit, ce qui optimise les performances. Un trafic
réseau efficace, des E/S disque et l'utilisation de l'UC sont essentiels pour maximiser les
performances. Vous devez analyser soigneusement les besoins de l'application,
comprendre la structure logique et physique des données, évaluer l'utilisation de la base
de données et négocier les compromis entre des utilisations conflictuelles telles que le
traitement transactionnel en ligne par rapport à l'aide à la décision.

Surveillance et paramétrage des bases de données à des fins de performances

Microsoft SQL Server et le système d’exploitation Microsoft Windows fournissent des

utilitaires pour afficher la condition actuelle de la base de données et suivre les
performances à mesure que les conditions changent. Il existe différents outils et
techniques que vous pouvez utiliser pour surveiller Microsoft SQL Server. La supervision
de SQL Server vous aide à :
• 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
configurée de manière adéquate et teste les applications ou 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 problèmes ou déboguer des composants d’application, comme des
procédures stockées.
 Surveillance dans un environnement dynamique

La modification des conditions aboutit à un changement des performances. Dans vos

évaluations, vous pouvez voir les changements de performances au fur et à mesure que
le nombre d'utilisateurs augmente, que les accès des utilisateurs et les méthodes de
connexion changent, que la base de données se remplit, que les applications clientes
changent, que les données des applications changent, que les requêtes deviennent plus
complexes et que le trafic réseau augmente. Grâce aux outils permettant de surveiller les
performances, vous pouvez associer certaines modifications des performances à des
modifications de conditions et des requêtes complexes. Exemples :
• en surveillant les temps de réponse des requêtes les plus fréquentes, vous pouvez
déterminer s'il faut modifier, soit les requêtes, soit les index des tables ;
• En surveillant les requêtes Transact-SQL à mesure qu’elles sont exécutées, vous pouvez
déterminer si les requêtes sont écrites correctement et en produisant les résultats
attendus.
• En surveillant les utilisateurs qui tentent de se connecter à une instance de SQL Server,
vous pouvez déterminer si la sécurité est configurée de manière adéquate et teste les
applications ou les systèmes de développement.
Le temps de réponse est la durée requise pour le renvoi de la première ligne de
l'ensemble de résultats à l’utilisateur sous forme de confirmation visuelle qu’une requête
est en cours de traitement. Le débit mesure le nombre total de requêtes gérées par le
serveur pendant une période donnée.
La demande des ressources du serveur croît proportionnellement au nombre
d'utilisateurs, augmentant ainsi le temps de réponse et, par conséquent, diminuant le
débit global.

Tâches de surveillance et de paramétrage des performances

Rubrique Tâche
Surveiller les composants SQL Étapes requises pour superviser tout composant
Server de SQL Server, tel que le Moniteur d’activité, les
Événements étendus, les Vues et fonctions de
gestion dynamique, et ainsi de suite.
Outils de surveillance et de Liste les outils de supervision et de paramétrage
réglage des performances disponibles avec SQL Server, tels que les
Statistiques des requêtes actives et l’Assistant
Paramétrage du moteur de base de données.
Mise à niveau des bases de Maintenez la stabilité des performances de charge
données à l’aide de l’Assistant de travail durant la mise à niveau vers un niveau
Paramétrage de requête de compatibilité de base de données plus récent.
Surveillance des performances à Utiliser le magasin de requêtes pour capturer
l'aide du magasin de requêtes automatiquement l’historique des requêtes, des
plans et des statistiques d’exécution et les
conserver à des fins de consultation.
Établir un niveau de référence Comment établir un niveau de référence des
des performances performances.
Isoler les problèmes de Isoler les problèmes de performance des bases de
performance données.
Identifier les goulots Surveiller et suivre les performances du serveur
d’étranglement afin d’identifier les goulots d’étranglement.
Utiliser des vues de gestion Traite de la méthodologie et des scripts utilisés
dynamique pour déterminer les pour obtenir des informations sur les
statistiques d’utilisation et les performances des requêtes.
performances des vues
Analyse des performances et Utilisez les outils de surveillance des performances
surveillance de l'activité du et des activités SQL Server et Windows.
serveur
Superviser l’utilisation des Utilisation du Moniteur système (également
ressources appelé perfmon) pour mesurer les performances
de SQL Server à l’aide de compteurs de
performances.
Surveiller les composants 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
d’applications accédant à SQL Server peuvent même changer, mais SQL Server gère
automatiquement les ressources au niveau du système, telles que la mémoire et l’espace
disque, afin de réduire le besoin d’un réglage manuel complet au niveau du 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 n’importe quel composant de 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 SQL Server efficacement, vous devez clairement identifier votre raison 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é

Après avoir déterminé pourquoi vous surveillez, vous devez sélectionner les outils
appropriés pour ce type de surveillance. Le système d’exploitation Windows et SQL
Server fournissent un ensemble complet d’outils pour surveiller les serveurs dans des
environnements nécessitant beaucoup de 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 Analyseur de performances (Windows) qui vous permet de collecter et
d’afficher des données en temps réel sur les activités telles que la mémoire, le
disque et l’utilisation du processeur.
• Journaux et alertes de performance ;
• Gestionnaire des tâches
SQL Server fournit les outils suivants pour surveiller les composants de SQL Server :
• Aperçu des événements étendus
• Trace SQL
• SQL Server Profiler
• SQL Server Distributed Replay
• Moniteur d’activité
• Plan d’affichage graphique SQL Server Management Studio
• Procédures stockées système
• Commandes DBCC (Database Console Commands)
• Vues de gestion dynamique système
• Quelles sont les fonctions de base de données SQL ?
• Indicateurs de trace

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 prochaine version de Microsoft 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.

Identifier les composants à surveiller

La troisième étape de surveillance d’une instance de SQL Server consiste à identifier les
composants que vous surveillez. Par exemple, si vous utilisez SQL Server Profiler 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 métriques
que vous avez 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.
7. 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.
8. Surveiller (capturer) les événements.
Dès qu’elle est activée, la surveillance active capture les données de l’application
spécifiée, de l’instance de SQL Server ou du système d’exploitation. 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.
9. 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 important lorsque vous créez une base de
référence de 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.
Les données d’événement trace SQL peuvent même être relues à l’aide de l’utilitaire
Distributed Replay ou de SQL Server Profiler. SQL Server Profiler autorise
l’enregistrement des données d’événement dans un fichier ou une table SQL Server.
10. 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é. Management Studio XEvent Profiler fournit déjà des modèles prêts à être
utilisés.
11. 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énement étendue capturée peut être rechargée dans SQL
Server Management Studio pour l’affichage et l’analyseLes données de trace SQL
peuvent être rechargées dans SQL Server Profiler pour l’affichage et l’analyse.
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 une trace capturée à
partir d’événements étendus ou de SQL Server Profiler et formuler des recommandations
d’index en fonction des résultats.
12. 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
fonctionnalité est disponible uniquement avec l’utilitaire Distributed Replay ou 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.
Tableau de bord Performances
SQL Server Management Studio version 17.2 et versions ultérieures inclut le tableau de
bord des performances. Ce tableau de bord a été conçu pour fournir visuellement un
aperçu rapide de l’état des performances de SQL Server (à partir de SQL Server 2008
(10.0.x)) et d’Azure SQL Database Managed Instance.
Le tableau de bord des performances permet d’identifier rapidement si SQL Server ou
Azure SQL Database rencontre un goulot d’étranglement des performances. Si un
goulot d’étranglement est trouvé, capturez facilement des données de diagnostic
supplémentaires pouvant être nécessaires pour résoudre le problème. Voici certains
problèmes de performances courants que le tableau de bord Performances peut aider à
identifier :
• Goulots d'étranglement au niveau de l'unité centrale (et identification des requêtes
sollicitant le plus l'unité centrale)
• Goulots d’étranglement E/S (et identification des requêtes effectuant le plus d’E/S)
• Recommandations d’index générées par l’optimiseur de requête (index absents)
• Blocage
• Conflit de ressources (y compris une contention de verrous)
Le tableau de bord des performances permet également d’identifier les requêtes
coûteuses qui ont pu être exécutées précédemment, et plusieurs métriques sont
disponibles pour définir un coût élevé : processeur, écritures logiques, lectures logiques,
durée, lectures physiques et temps CLR.
Le tableau de bord Performances comprend les sections et sous-rapports suivants :
• Utilisation de l’UC du système
• Demandes actuellement en attente
• Activité actuelle
• Demandes de l’utilisateur
• Sessions utilisateur
• Taux d'accès au cache
• Informations historiques
• Attend
• Verrous internes
• Statistiques d’E/S
• Requêtes coûteuses
• Informations diverses
• Traces actives
• Sessions Xevent actives
• Bases de données
• Index absents

Pour afficher le tableau de bord Performances

Pour afficher le tableau de bord des performances, cliquez avec le bouton droit sur le
nom de l’instance SQL Server dans l’Explorateur d’objets, sélectionnez Rapports,
Rapports standard, puis cliquez sur Tableau de bord des performances.

Le tableau de bord des performances s’affiche sous la forme d’un nouvel onglet. Voici
un exemple où un goulot d’étranglement du processeur est clairement présent :
Autorisations

Sur SQL Server, nécessite VIEW SERVER STATE et ALTER TRACE dispose d’autorisations. Sur
Azure SQL Database, nécessite l’autorisation VIEW DATABASE STATE dans la base de
données.
Outils de surveillance et de réglage des performances

Microsoft SQL Server fournit un ensemble complet d’outils pour la surveillance des
événements dans SQL Server et pour l’optimisation de la conception de la base de
données physique. Le choix de l'outil dépend du type de surveillance ou de
paramétrage à effectuer et des événements spécifiques à contrôler.
Voici les outils de supervision et de réglage SQL Server :

Outil Description
Fonctions Les fonctions intégrées affichent des statistiques d’instantané sur l’activité
intégrées SQL Server depuis le démarrage du serveur ; ces statistiques sont stockées
(Transact dans des compteurs SQL Server prédéfinis. Par exemple, @@CPU_BUSY
-SQL) contient la durée pendant laquelle l’UC a exécuté du code SQL Server ;
@@CONNECTIONS contient le nombre de connexions SQL Server ou de
tentatives de connexions; et @@PACKET_ERRORS contient le nombre de
paquets réseau qui se produisent sur les connexions SQL Server.
DBCC Les instructions DBCC (Database Console Command) vous permettent de
(Transact contrôler les statistiques de performances et la cohérence logique et
-SQL) physique d'une base de données.
Assistant L’Assistant Paramétrage du moteur de base de données analyse les effets
Paramétr de performances des instructions Transact-SQL exécutées sur des bases
age du de données que vous souhaitez paramétrer. Il fournit des
moteur recommandations pour ajouter, supprimer ou modifier des index, des vues
de base indexées et un partitionnement.
de
données
(DTA)
Assistant L’Assistant Expérimentation de base de données (DEA) est une nouvelle
Expérime solution de test A/B pour SQL Server. Il aidera à évaluer une version ciblée
ntation du moteur de base de données SQL Server pour une charge de travail
de base donnée. Lors de la mise à niveau d’une version antérieure de SQL Server (à
de partir de SQL Server 2005 (9.x)) vers une version plus récente de SQL
Server, DEA pourra fournir des métriques d’analyse comparative.
données
(DEA)
Journaux Le journal des événements d’application Windows fournit une image
d’activité globale des événements qui se produisent sur les systèmes d’exploitation
d’erreurs Windows Server et Windows dans son ensemble, ainsi que les événements
dans SQL Server, SQL Server Agent et la recherche en texte intégral. Il
contient des informations sur les événements dans SQL Server qui ne sont
pas disponibles ailleurs. Vous pouvez utiliser les informations dans le
journal des erreurs pour résoudre les problèmes liés à SQL Server.
Événeme Les événements étendus sont un système léger d'analyse des
nts performances qui utilise très peu de ressources de performances. Les
étendus événements étendus fournissent trois interfaces utilisateur graphiques
(Assistant Nouvelle session, Nouvelle session et XE Profiler) permettant de
créer, de modifier, d’afficher et d’analyser vos données de session.
Fonctions Les vues de gestion dynamique relatives à l’exécution vous permettent de
et vues vérifier les informations liées à l’exécution.
de
gestion
dynamiq
ue
associées
à
l’exécutio
n
(Transact
-SQL)
Statistiqu Affiche les statistiques en temps réel sur les étapes d’exécution des
es des requêtes. Puisque ces données sont accessibles pendant l’exécution des
requêtes requêtes, ces statistiques d’exécution sont extrêmement utiles pour
actives résoudre les problèmes de performances de requêtes.
Analyser Le Moniteur système surveille principalement l'utilisation des ressources,
l'utilisatio notamment le nombre de demandes de pages en cours au gestionnaire
n des de tampons, ce qui vous permet de contrôler les performances et l'activité
ressource du serveur à l'aide d'objets et de compteurs prédéfinis, ou de compteurs
s définis par l'utilisateur pour surveiller les événements. Le Moniteur
(Moniteu système (l’Analyseur de performances dans Microsoft Windows NT 4.0)
r recueille le nombre et le taux et non pas les données concernant les
système) événements (par exemple, l'utilisation de la mémoire, le nombre de
transactions actives, le nombre de verrous bloqués, ou l'activité de l'UC).
Vous pouvez définir des seuils pour des compteurs spécifiques de manière
à générer des alertes pour avertir les opérateurs.

Le Moniteur système fonctionne sur les systèmes d'exploitation Microsoft

Windows Server et Windows. Il peut surveiller (à distance ou localement)
une instance de SQL Server sur Windows NT 4.0 ou version ultérieure.

La principale différence entre SQL Server Profiler et System Monitor est

que SQL Server Profiler surveille les événements du moteur de base de
données, tandis que System Monitor surveille l’utilisation des ressources
associée aux processus serveur.
Ouvrir le Le moniteur d’activité dans SQL Server Management Studio est utile pour
Moniteur les vues ad hoc de l’activité actuelle et affiche graphiquement des
d’activité informations sur :
(SQL
Server - Processus s’exécutant sur une instance de SQL Server
Manage - les processus bloqués
ment - les verrous
Studio) - l’activité utilisateur
Tableau Le tableau de bord des performances dans SQL Server Management
de bord Studio permet d’identifier rapidement s’il existe un goulot d’étranglement
Performa actuel des performances dans SQL Server.
nces
Assistant La fonctionnalité Assistant Paramétrage des requêtes (QTA) guide les
Paramétr utilisateurs par le biais du flux de travail recommandé pour maintenir la
age de stabilité des performances pendant les mises à niveau vers des versions
requêtes plus récentes de SQL Server, comme indiqué dans la section Conserver la
stabilité des performances pendant la mise à niveau vers des scénarios
d’utilisation du magasin de requêtes plus récents.
Magasin La fonctionnalité Magasin des requêtes fournit des informations sur le
de choix de plan de requête et sur les performances. Elle simplifie la
requêtes résolution des problèmes de performances en vous permettant de trouver
rapidement les différences de performances provoquées par des
changements de plan de requête. Le magasin de requête capture
automatiquement l'historique des requêtes, des plans et des statistiques
d'exécution et les conserve à des fins de révision. Elle sépare les données
en périodes, ce qui vous permet de voir les modèles d'utilisation de base
de données et de comprendre à quel moment les changements de plan
de requête ont eu lieu sur le serveur.
Trace Procédures stockées Transact-SQL qui créent, filtrent et définissent le
SQL suivi :

sp_trace_create (Transact-SQL)
sp_trace_generateevent (Transact-SQL)
sp_trace_setevent (Transact-SQL)
sp_trace_setfilter (Transact-SQL)
sp_trace_setstatus (Transact-SQL)
SQL Microsoft SQL Server Distributed Replay peut utiliser plusieurs ordinateurs
Server pour relire les données de trace, en simulant une charge de travail
Distribute stratégique.
d Replay
sp_trace_ SQL Server Profiler suit les événements de processus du moteur, tels que
setfilter le démarrage d’un lot ou d’une transaction, ce qui vous permet de
(Transact surveiller l’activité du serveur et de la base de données (par exemple, des
-SQL) blocages, des erreurs irrécupérables ou une activité de connexion). Vous
pouvez capturer des données SQL Server Profiler dans une table SQL
Server ou un fichier pour une analyse ultérieure, et vous pouvez
également relire les événements capturés sur SQL Server étape par étape
pour voir exactement ce qui s’est passé.
Procédur Les procédures stockées système SQL Server suivantes offrent une
es alternative puissante pour de nombreuses tâches de surveillance :
stockées
système sp_who (Transact-SQL) :
 (Transact Signale des informations d’instantané sur les utilisateurs et processus SQL
-SQL) Server actuels, notamment l’instruction en cours d’exécution et si
l’instruction est bloquée.

sp_lock (Transact-SQL) :
Renvoie des informations d'instantané sur les verrous, y compris l'ID de
l'objet, l'ID d'index, le type de verrou et le type de ressource auquel
s'applique le verrou.

sp_spaceused (Transact-SQL) :
Affiche une estimation de l'espace disque actuellement utilisé par une
table (ou une base de données entière).

sp_monitor (Transact-SQL) :
Affiche des statistiques, notamment l’utilisation de l’UC, l’utilisation des
E/S et la durée d’inactivité depuis la dernière exécution de sp_monitor .
Indicateu Les indicateurs de trace affichent des informations sur une activité
rs de spécifique sur le serveur ; ils permettent de diagnostiquer les problèmes
trace ou les causes agissant sur les performances (par exemple, chaînes de
(Transact blocage).
-SQL)

Choix d'un outil de surveillance

Le choix d'un outil de surveillance dépend de l'événement ou de l'activité à surveiller.

Événement ou Évén SQL Distribut Mon Moni Tra Journ Tableau

activité eme Server ed iteu teur ns aux de bord
nts Profiler Replay r d'act act des Perform
éten syst ivité - erreu ances
dus ème SQ rs
L
Analyse de Oui Oui Oui
tendances
Relecture des Oui Oui
événements (depuis (depuis
capturés un plusieurs
ordinateu ordinateu
r unique) rs)
Surveillance ad Oui 1 Oui Oui Oui Oui Oui
hoc
Génération Oui
d'alertes
Interface Oui Oui Oui Oui Oui Oui
graphique
Utilisation dans Oui Oui2 Oui
une application
personnalisée
 Statistiques des requêtes dynamiques
QL Server Management Studio permet d’afficher le plan d’exécution en direct d’une
requête active. Ce plan de requête active fournit des aperçus en temps réel sur le
processus d’exécution des requêtes à mesure que les contrôles passent d’un opérateur
de plan de requête à un autre. Le plan de requête active affiche la progression globale
de la requête ainsi que des statistiques d’exécution de niveau opérateur telles que le
nombre de lignes produites, le temps écoulé, la progression de l’opérateur, etc. Vous
pouvez accéder à ces données en temps réel sans avoir à attendre l’exécution de la
requête ; ces statistiques d’exécution se révèlent donc extrêmement utiles pour résoudre
les problèmes de performances de requêtes.

Pour afficher les statistiques des requêtes actives pour une requête

13. Pour afficher le plan d’exécution des requêtes actives, accédez au menu Outils et cliquez
sur l’icône Inclure les statistiques des requêtes actives.

Vous pouvez également afficher l’accès au plan d’exécution de requête en direct en

cliquant avec le bouton droit sur une requête sélectionnée dans Management Studio,
puis en cliquant sur Inclure des statistiques de requête dynamique.
14. Exécutez maintenant la requête. Le plan de requête active affiche la progression globale
de la requête et les statistiques d’exécution (par exemple, temps écoulé, progression,
etc.) pour les opérateurs du plan de requête. Les informations relatives à la progression
de la requête et les statistiques d’exécution sont régulièrement mises à jour tout au long
de l’exécution de la requête. Vous pouvez utiliser ces informations pour comprendre le
processus global d’exécution de la requête, déboguer les longues requêtes, les requêtes
qui s’exécutent indéfiniment et les requêtes qui entraînent un dépassement tempdb, ou
encore résoudre les problèmes de délai d’attente.
Pour afficher les statistiques des requêtes actives pour n’importe quelle requête

Vous pouvez également cliquer avec le bouton droit sur n’importe quelle requête dans
la table Processus ou Requêtes coûteuses actives du Moniteur d’activité pour
accéder au plan d’exécution actif.
Notes

L’infrastructure de profil de statistiques doit être activée pour que les statistiques de
requêtes actives puissent capturer des informations sur la progression des requêtes. En
fonction de la version, la surcharge peut être significative.

Autorisations

Nécessite une autorisation SHOWPLAN au niveau de la base de données pour l’écriture de

données dans la page de résultats Statistiques des requêtes actives et nécessite les
autorisations nécessaires pour l’exécution de la requête. Sur SQL Server, vous devez
disposer de l’autorisation au niveau VIEW SERVER STATE du serveur pour afficher les
statistiques actives.
Sur les niveaux SQL Database Premium, vous devez disposer de l’autorisation VIEW
DATABASE STATE dans la base de données pour afficher les statistiques actives. Sur les
niveaux Standard et De base de SQL Database, nécessite l’administrateur du serveur ou
le compte d’administrateur Microsoft Entra pour afficher les statistiques actives.
Moniteur d'activité
Le Moniteur d’activité dans SQL Server Management Studio (SSMS) affiche des
informations sur les processus SQL Server et la façon dont ces processus affectent
l’instance actuelle de SQL Server.

Le moniteur d’activité est une fenêtre de document à onglets avec les volets extensibles
et réductibles suivants : Vue d’ensemble, processus, attentes des ressources, E/S de
fichier de données, requêtes coûteuses récentes et requêtes coûteuses actives.
Lorsqu'un volet est développé, le Moniteur d'activité interroge l'instance pour obtenir
des informations. Lorsqu'un volet est réduit, toutes les activités d'interrogation cessent
pour ce volet. Vous pouvez développer un ou plusieurs volets en même temps pour
afficher différents types d’activité sur l’instance.

Ouvrir le moniteur d’activité dans SSMS (SQL Server Management Studio)

Activity Monitor exécute des requêtes sur l’instance surveillée pour obtenir des
informations sur les volets d’affichage du Moniteur d’activité. Si l’intervalle
d’actualisation est défini sur une valeur inférieure à 10 secondes, le temps pour exécuter
ces requêtes peut affecter les performances du serveur.

Autorisations

Pour afficher l’activité courante, vous devez avoir l’autorisation VIEW SERVER STATE.
Pour afficher la section E/S du fichier de données du Moniteur d'activité, vous devez
disposer de l'autorisation CREATE DATABASE, ALTER ANY DATABASE ou VIEW ANY
DEFINITION en plus de VIEW SERVER STATE.
Pour TUER (KILL) un processus, l’utilisateur doit être membre des rôles serveur fixes
sysadmin ou processadmin.

Ouvrir le Moniteur d’activité

Explorateur d’objets

Cliquez avec le bouton droit sur l’objet de niveau supérieur pour une connexion SQL
Server, puis sélectionnez Moniteur d’activité.
 Barre d’outils

Dans la barre d’outils standard, sélectionnez l’icône Moniteur d’activité. Elle se trouve
au centre, à la droite des boutons d’annulation/de rétablissement. Pour faciliter sa
recherche, pointez sur chaque icône jusqu’à ce que vous trouviez le Moniteur d’activité.
Terminez la boîte de dialogue Connecter au serveur si vous n’êtes pas déjà connecté à
une instance de SQL Server que vous souhaitez surveiller.

Lancer le moniteur d’activité et l’explorateur d’objets au démarrage

15. Dans le menu Outils , sélectionnez Options.

16. Dans la boîte de dialogue Options , développez Environnement, puis sélectionnez
Démarrage.
17. Dans la liste déroulante Au démarrage , sélectionnez Ouvrir l’Explorateur d’objets et
le Moniteur d’activité.
18. Cliquez sur OK.
 Définir l’intervalle d’actualisation du Moniteur d’activité

19. Ouvrez le Moniteur d'activité.

20. Cliquez avec le bouton droit sur Vue d’ensemble, sélectionnez Intervalle
d’actualisation, puis choisissez l’intervalle dans lequel le Moniteur d’activité doit obtenir
de nouvelles informations sur l’instance.
 Aperçu des événements étendus
L’architecture Des événements étendus (XEvents) permet aux utilisateurs de collecter
autant ou moins de données que nécessaire pour surveiller, identifier ou résoudre les
problèmes de performances dans SQL Server, Azure SQL Database et Azure SQL
Managed Instance. Les événements étendus sont hautement configurables, légers et mis
à l’échelle correctement.

Avantages des événements étendus

Les événements étendus sont un système de surveillance des performances léger qui
utilise des ressources système minimales tout en fournissant une vue détaillée et
détaillée du moteur de base de données. SQL Server Management Studio fournit une
interface utilisateur graphique permettant aux événements étendus de créer, de
modifier et de supprimer des sessions d’événements et d’afficher et d’analyser les
données de session.

Démarrage rapide : Événements étendus

Créer une session d’événements dans SSMS

Lorsque vous créez une session Événements étendus, vous indiquez au système :
• Quels événements vous intéressez
• Comment vous souhaitez que le système signale les données à vous
La démonstration ouvre la boîte de dialogue Nouvelle session , montre comment
utiliser ses quatre pages, nommées :
• Général
• Événements
• Stockage des données
• Avancé
21. Connecter à une instance du moteur de base de données. Les événements étendus sont
pris en charge à partir de SQL Server 2014, dans Azure SQL Database et Azure SQL
Managed Instance.
22. Dans l’Explorateur d’objets, sélectionnez Événements étendus de gestion>.
23. Cliquez avec le bouton droit sur le dossier Sessions , puis sélectionnez Nouvelle
session. La boîte de dialogue Nouvelle session est préférable à l’Assistant Nouvelle
session, bien que les deux soient similaires.
24. En haut à gauche, sélectionnez la page Général . YourSessionTapez ensuite, un nom
dans la zone de texte Nom de session. Ne sélectionnez pas encore OK , car vous devez
toujours entrer des détails sur d’autres pages.

25. En haut à gauche, sélectionnez la page Événements .

26. Dans la zone bibliothèque d’événements, dans la liste déroulante, choisissez
uniquement les noms d’événements.
o Tapez sql_statement_ dans la zone de texte. Cette option filtre la liste pour afficher
uniquement les événements portant sql_statement_ le nom.
o Faites défiler et sélectionnez l’événement nommé sql_statement_completed.
o Sélectionnez le bouton flèche > droite pour déplacer l’événement vers la zone
Événements sélectionnés .
27. Dans la page Événements , sélectionnez le bouton Configurer en haut à droite. La
zone Options de configuration des événements s’ouvre pour les événements
sélectionnés.
28. Sélectionnez l’onglet Filtre (prédicat). Ensuite, sélectionnez Sélectionner ici pour
ajouter une clause. Nous configurons ce filtre (également appelé prédicat) pour
capturer toutes les SELECT instructions qui ont une HAVING clause.
29. Dans la liste déroulante Champ , choisissez sqlserver.sql_text.
o Pour l’opérateur, choisissez like_i_sql_unicode_string. Ici, i dans le nom de
l’opérateur signifie case-i nsensible.
o Pour Valeur, tapez %SELECT%HAVING%. Ici, les signes de pourcentage remplacent
n’importe quelle chaîne de caractères.
30. En haut à gauche, sélectionnez la page Data Stockage.
31. Dans la zone Cibles , sélectionnez Sélectionner ici pour ajouter une cible.
o Dans la liste déroulante Type , choisissez event_file. Cela signifie que les
données d’événement sont stockées dans un fichier que nous pouvons ouvrir et
 afficher ultérieurement.

32. Dans la zone Propriétés , tapez le chemin d’accès complet et le nom de fichier dans la
zone de texte Nom de fichier sur la zone de texte du serveur . Vous pouvez également
utiliser le bouton Parcourir . L’extension de nom de fichier doit être xel. Dans notre
exemple, nous avons utilisé C:\Temp\YourSession_Target.xel
33. En haut à gauche, sélectionnez la page Avancé . Réduisez la latence de répartition
maximale à 3 secondes.
34. Sélectionnez le bouton OK en bas pour créer cette session d’événements.
35. De retour dans l’Explorateur d’objets, ouvrez ou actualisez le dossier Sessions et
consultez le nouveau nœud pour YourSession. La session n’est pas encore démarrée.
Vous la démarrez plus tard.
Afficher les données de session d’événements dans SSMS

Il existe plusieurs fonctionnalités avancées dans l’interface utilisateur de SSMS que vous
pouvez utiliser pour afficher les données capturées par une session d’événements.

Afficher les données cibles

Dans l’Explorateur d’objets SSMS, vous pouvez cliquer avec le bouton droit sur le nœud
cible qui se trouve sous votre nœud de session d’événements. Dans le menu contextuel,
sélectionnez Afficher les données cibles. SSMS affiche les données.
L’affichage n’est pas mis à jour à mesure que de nouveaux événements se produisent
dans une session. Mais vous pouvez à nouveau sélectionner Afficher les données
cibles.
Regarder les données en direct

Dans l’ Explorateur d’objetsde SSMS, vous pouvez cliquer avec le bouton droit sur le
nœud de votre session d’événements. Dans le menu contextuel, sélectionnez Watch
Live Data. SSMS affiche les données entrantes au fur et à mesure qu’elles arrivent en
temps réel.
Termes et concepts dans les événements étendus

Le tableau suivant répertorie les termes utilisés pour les événements étendus et décrit
leurs significations.
Terme Description
event Construction centrée autour d’un ou plusieurs événements, associées à des
session
éléments comme des actions et des cibles. L’instruction CREATE EVENT SESSION
crée chaque session d’événements. Vous pouvez ALTER démarrer une session
d’événements et l’arrêter à l’écran.

Une session d’événements est parfois simplement appelée session. Quand

le contexte le précise, il s’agit d’une session d’événements.

Des détails supplémentaires sur les sessions d’événements sont décrits

dans : sessions d’événements étendus.
event Occurrence spécifique dans le système qui est surveillée par une session
d’événements active.

Par exemple, l’événement sql_statement_completed représente le moment où une

 instruction T-SQL donnée se termine. L’événement peut signaler sa durée et
d’autres données.
target Élément qui reçoit les données de sortie d’un événement capturé. La cible
vous affiche les données.

Les exemples incluent la event_file cible utilisée précédemment dans ce

démarrage rapide et la ring_buffer cible qui conserve les événements les plus
récents en mémoire.

Tout type de cible peut être utilisé pour n’importe quelle session
d’événements.
action Champ connu de l’événement. Les données issues du champ sont envoyées
à la cible. Le champ d’action est étroitement lié au filtre de prédicat.
predicateo Test de données d’un champ d’événement, utilisé pour que seul un sous-
u filtre ensemble intéressant d’occurrences d’événements soit envoyé à la cible.

Par exemple, un filtre peut inclure uniquement les sql_statement_completed

occurrences d’événements où l’instruction T-SQL contenait la chaîne HAVING.
package Qualificateur de nom associé à chaque élément dans un ensemble
d’éléments qui tournent autour d’un cœur d’événements.

Par exemple, un package peut avoir des événements sur le texte T-SQL. Un
événement peut être à propos de tous les T-SQL dans un lot. Dans le même
temps, un autre événement plus précis concerne des instructions T-SQL
individuelles. De plus, pour n’importe quelle instruction T-SQL, il existe
started et completed des événements.

Les champs appropriés aux événements sont également inclus dans le

package avec les événements. La plupart des cibles sont package0 dans et sont
utilisées avec des événements de nombreux autres packages.

Scénarios d’événements étendus et détails d’utilisation

Il existe de nombreux scénarios d’utilisation d’événements étendus pour surveiller et

résoudre les problèmes du moteur de base de données et des charges de travail de
requête. Les articles suivants fournissent des exemples utilisant des scénarios liés au
verrouillage :
• Trouver les objets comportant le plus de verrous
• Ce scénario utilise la cible d’histogramme , qui traite les données d’événement brutes
avant de les afficher dans un formulaire résumé (compartimenté).
• Déterminer quelles requêtes détiennent des verrous
• Ce scénario utilise la cible pair_matching , où la paire d’événements est
sqlserver.lock_acquire et sqlserver.lock_release.

Rechercher des événements à l’aide de l’interface utilisateur de SSMS

Une autre option permettant de trouver des événements par nom consiste à utiliser la
boîte de dialogue Nouvelle bibliothèque d’événements d’événements > de > session
qui s’affiche dans une capture d’écran précédente. Vous pouvez taper un nom
d’événement partiel et rechercher tous les noms d’événements correspondants.

Afficher les données d’événement dans SQL Server Management Studio

Ouvrir l’interface utilisateur de l’observateur d’événements SSMS

L’interface utilisateur de l’observateur d’événements SSMS est accessible de l’une des

manières suivantes :
• Fichier>d’ouverture>de fichier de menu principal, puis recherchez un fichier xel
• Cliquez avec le bouton droit sur les événements étendus dans la gestion de
l’Explorateur > d’objets
• Menu Événements étendus et barre d’outils Événements étendus
• Clics droit dans le volet à onglets qui affiche les données cibles

Afficher les données event_file dans SSMS

Il existe différentes façons d’afficher les event_file données cibles dans l’interface
utilisateur de SSMS. Lorsque vous spécifiez une event_file cible, vous définissez son
chemin d’accès et son nom de fichier, y compris l’extension xel .
 • Chaque fois que la session d’événements est démarrée, les événements étendus
ajoutent un suffixe numérique au nom de fichier pour rendre le nom de fichier
unique.
• Exemple : Checkpoint_Begins_ES_0_131103935140400000.xel
• xel les fichiers sont des fichiers binaires. Ils ne doivent pas être ouverts dans un
éditeur de texte tel que Bloc-notes Windows.
• Utilisez la boîte de dialogue File>Open>Merge Extended Event Files pour ouvrir
plusieurs fichiers d’événements dans l’interface utilisateur de l’observateur
d’événements SSMS.
SSMS peut afficher des données pour la plupart des cibles d’événements étendus. Mais
les affichages sont différents pour les différentes cibles. Par exemple :
• event_file : les données d’une cible event_file s’affichent dans l’observateur
d’événements, avec filtrage, agrégation et autres fonctionnalités disponibles.
• ring_buffer : les données d’une cible de mémoire tampon en anneau sont affichées
en tant que XML.
SSMS ne peut pas afficher les données de la etw_classic_sync_target cible.

Ouvrir un fichier xel à l’aide > d’un fichier ouvert > de fichier

Vous pouvez ouvrir un fichier unique xel à partir du menu principal de SSMS, à l’aide du
fichier ouvert>de fichier.> Vous pouvez également faire glisser-déplacer un xel fichier
dans SSMS.

Afficher les données cibles

L’option Afficher les données cibles affiche les données qui ont été capturées jusque-
là. Dans l’Explorateur d’objets, vous pouvez développer les nœuds, puis cliquer avec le
bouton droit :
• Gestion>Événements étendus>Sessions>[votre-session]>[votre-nœud-
cible]>Afficher les données cibles.
Les données cibles sont affichées dans un volet à onglets dans SSMS comme suit :
Regarder les données en direct
Lorsque votre session d’événements est en cours d’exécution, vous pouvez regarder les
données d’événement en temps réel, car elles sont reçues par la cible.
• Gestion>Événements étendus>Sessions>[votre-session]>Surveiller les
données actives.
L’affichage des données est mis à jour à intervalles réguliers que vous pouvez spécifier
au niveau du paramètre Latence maximale de répartition dans :
• Événements étendus>Sessions>[votre-session]>Propriétés>Avancé>Latence
maximale de répartition

Afficher les données d’événement avec la fonction sys.fn_xe_file_target_read_file()

La fonction sys.fn_xe_file_target_read_file() retourne un ensemble de lignes avec chaque

ligne représentant un événement capturé. Les données d’événement sont retournées au
format XML. XQuery peut être utilisé pour présenter des données d’événement
relationnellement.

Exporter des données cibles

Une fois que vous avez des données d’événement affichées dans l’observateur
d’événements SSMS, vous pouvez l’exporter dans différents formats en procédant
comme suit :
 36. Sélectionnez Événements étendus dans le menu principal.
37. Sélectionnez Exporter vers, puis choisissez un format.

Manipuler les données dans l’affichage

Au-delà de la simple consultation des données telles quelles, l’interface utilisateur de

SSMS vous permet de manipuler les données de différentes manières.

Menus contextuels dans l’affichage des données

Les menus contextuels proposés dans l’affichage de données varient en fonction de

l’endroit où vous cliquez avec le bouton droit.

Cliquez avec le bouton droit sur une cellule de données

La capture d’écran suivante montre le menu de contenu que vous obtenez quand vous
cliquez avec le bouton droit dans une cellule de l’affichage de données. La capture
d’écran montre également le menu Copier développé.
Cliquez avec le bouton droit sur un en-tête de colonne
La capture d’écran suivante montre le menu contextuel qui s’affiche après un clic droit
dans l’en-tête timestamp .
Choisir des colonnes, fusionner des colonnes

L’option Choisir des colonnes vous permet de contrôler les colonnes de données
affichées. Vous pouvez trouver l’élément de menu Choisir les colonnes à plusieurs
endroits :
• dans le menu Événements étendus ;
• Dans la barre d’outils Événements étendus .
• dans le menu contextuel d’un en-tête de l’affichage de données.
Lorsque vous sélectionnez Choisir des colonnes, la boîte de dialogue du même nom
s’affiche.

Fusionner des colonnes

La boîte de dialogue Choisir des colonnes comporte une section consacrée à la fusion
de plusieurs colonnes en une, à des fins d’affichage et d’exportation de données.

Filtres

Les filtres de l’observateur d’événements SSMS peuvent :

 • Restreindre les données retournées par la colonne timestamp
• Filtrer par valeur de colonne
La relation entre le filtre de temps et le filtre de colonnes est une valeur booléenne AND.

Groupe et agrégation

Regrouper des lignes par la mise en correspondance des valeurs d’une colonne donnée
est la première étape de l’agrégation synthétique de données.

Regroupement
Dans la barre d’outils Événements étendus , le bouton Regroupement démarre une
boîte de dialogue que vous pouvez utiliser pour regrouper les données affichées par
une colonne donnée. La capture d’écran suivante montre une boîte de dialogue qui
permet d’effectuer un regroupement en fonction de la colonne nom.
Une fois le regroupement terminé, l’affichage a une nouvelle apparence, comme indiqué
ci-dessous.
Agrégation
Une fois que les données affichées ont été regroupées, vous pouvez poursuivre en
agrégeant les données dans d’autres colonnes. La capture d’écran suivante illustre
l’agrégation des données regroupées par nombre ( count).

Une fois l’agrégation terminée, l’affichage a une nouvelle apparence, comme indiqué ci-
dessous.
Afficher le plan de requête d’exécution

L’événement query_post_execution_showplan vous permet de voir le plan de requête réel

(avec les statistiques d’exécution incluses) dans l’interface utilisateur de SSMS. Lorsque
le volet Détails est visible, vous pouvez voir le plan de requête graphique sous l’onglet
Plan de requête. En pointant sur un nœud sur le plan de requête, vous pouvez voir une
liste de noms de propriétés et leurs valeurs pour le nœud de plan.
 Utilisation de SELECT et JOIN dans les vues système pour les événements étendus
dans SQL Server

A. Informations fondamentales

Il existe deux ensembles de vues système pour les événements étendus :

Affichages catalogue :
• Ces vues stockent des informations sur la définition de chaque session d’événements
créée par CREATE EVENT SESSION, ou par un équivalent dans l’interface utilisateur de
SSMS. Ces vues ignorent si une session a démarré.
• Par exemple, si l’ Explorateur d’objets de SSMS montre qu’aucune session
d’événements n’est définie, une instruction SELECT dans la vue
sys.server_event_session_targets retourne zéro ligne.
• Le préfixe de nom est :
• sys.server_event_session* est le préfixe de nom sur SQL Server.
• sys.database_event_session* est le préfixe de nom sur SQL Database.

Vues de gestion dynamiques (DMV) :

• Elles stockent des informations sur l’ activité en cours des sessions d’événements en
cours d’exécution. Ces DMV en savent peu sur la définition des sessions.
• Même si toutes les sessions d’événements sont actuellement arrêtées, une instruction
SELECT à partir de la vue sys.dm_xe_packages retournera toujours des lignes, car
différents packages sont chargés dans la mémoire active au démarrage du serveur.
• Pour la même raison, sys.dm_xe_objectssys.dm_xe_object_columns would also still
return rows.
• Le préfixe de nom pour les DMV d’événements étendus est le suivant :
• sys.dm_xe_* est le préfixe de nom sur SQL Server.
• sys.dm_xe_database_* est généralement le préfixe de nom sur SQL Database.

Autorisations :
Pour sélectionner (avec SELECT) à partir des vues système, l’autorisation suivante est
nécessaire :
 • VIEW SERVER STATE si vous utilisez Microsoft SQL Server.
• VIEW DATABASE STATE si vous utilisez Azure SQL Database.

B. Affichages catalogue

Cette section met en correspondance et en corrélation trois différentes perspectives

technologiques sur la même session d’événements définie. La session a été définie et est
visible dans l’ Explorateur d’objets de SQL Server Management Studio (SSMS.exe),
mais elle n’est pas en cours d’exécution.
Chaque mois, il est préférable d’ installer la dernière mise à jour de SSMS, afin d’éviter
toute erreur inattendue.
La documentation de référence sur les vues de catalogue pour les événements étendus
se trouve dans les vues de catalogue d’événements étendus (Transact-SQL).

B.1 Perspective de l’interface utilisateur de SSMS

Dans SSMS, dans l’ Explorateur d’objets, vous pouvez démarrer la boîte de dialogue
Nouvelle session en développant Gestion>Événements étendus, puis en cliquant avec
le bouton droit sur Sessions>Nouvelle session.
Dans la grande boîte de dialogue Nouvelle session , dans la première section intitulée
Général, nous constatons que l’option Démarrer la session d’événements au
démarrage du serveura été sélectionnée.
Ensuite, dans la section Événements, nous constatons que l’événement lock_deadlock
a été choisi. Pour cet événement, nous voyons que trois Actions ont été sélectionnées.
Cela signifie que le bouton Configurer a été enfoncé. C’est pourquoi il est maintenant
grisé.
Ensuite, toujours dans la section Événements>Configurer, nous constatons que
resource_type a la valeur PAGE. Cela signifie que les données d’événement ne seront
pas envoyées du moteur d’événements à la cible si la valeur de resource_type est autre
que PAGE.
Nous constatons la présence de filtres de prédicat supplémentaires pour le nom de base
de données et pour un compteur.
Ensuite, dans la section Stockage de données, nous constatons qu’event_file a été
choisi comme cible. En outre, nous constatons que l’option Activer la substitution de
fichier a été sélectionnée.
Pour finir, dans la section Avancé, nous constatons que la valeur de Latence maximale
de répartition a été réduite à quatre secondes.
Déterminer quelles requêtes détiennent des verrous

Les administrateurs de base de données ont souvent besoin d’identifier la source des
verrous qui entravent les performances d’une base de données.
Par exemple, vous soupçonnez qu’un problème de performances sur votre serveur est
provoqué par des verrous. Lorsque vous interrogez sys.dm_exec_requests, vous trouvez
plusieurs sessions en mode suspendu avec un type d'attente qui indique que la
ressource attendue est un verrou.
 Vous interrogez sys.dm_tran_locks et les résultats indiquent que de nombreux verrous
sont en attente, mais que les sessions auxquelles ces verrous ont été accordés ne font
pas l’objet de requêtes actives dans sys.dm_exec_requests.
Cet exemple illustre une méthode permettant de déterminer la requête qui a pris le
verrou, le plan de la requête et la pile Transact-SQL au moment où le verrou a été pris.
Cet exemple illustre également comment la cible d'appariement est utilisée dans une
session d'événements étendus.
Pour accomplir cette tâche, vous devez utiliser l’Éditeur de requête dans SQL Server
Management Studio pour effectuer la procédure suivante.

Pour déterminer quelles requêtes détiennent des verrous

38. Dans l'éditeur de requêtes, émettez les instructions suivantes.

-- Perform cleanup.
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE
name='FindBlockers')
DROP EVENT SESSION FindBlockers ON SERVER
GO
-- Use dynamic SQL to create the event session and allow creating
a -- predicate on the AdventureWorks database id.
--
DECLARE @dbid int

SELECT @dbid = db_id('AdventureWorks')

IF @dbid IS NULL
BEGIN
RAISERROR('AdventureWorks is not installed. Install
AdventureWorks before proceeding', 17, 1)
RETURN
END

DECLARE @sql nvarchar(1024)

SET @sql = '
CREATE EVENT SESSION FindBlockers ON SERVER
 ADD EVENT sqlserver.lock_acquired
(action
( sqlserver.sql_text, sqlserver.database_id,
sqlserver.tsql_stack,
sqlserver.plan_handle, sqlserver.session_id)
WHERE ( database_id=' + cast(@dbid as nvarchar) + ' AND
resource_0!=0)
),
ADD EVENT sqlserver.lock_released
(WHERE ( database_id=' + cast(@dbid as nvarchar) + ' AND
resource_0!=0 ))
ADD TARGET package0.pair_matching
( SET begin_event=''sqlserver.lock_acquired'',
begin_matching_columns=''database_id, resource_0,
resource_1, resource_2, transaction_id, mode'',
end_event=''sqlserver.lock_released'',
end_matching_columns=''database_id, resource_0,
resource_1, resource_2, transaction_id, mode'',
respond_to_memory_pressure=1)
WITH (max_dispatch_latency = 1 seconds)'

EXEC (@sql)
--
-- Create the metadata for the event session
-- Start the event session
--
ALTER EVENT SESSION FindBlockers ON SERVER
STATE = START

39. Après exécution d'une charge de travail sur le serveur, émettez les instructions suivantes
dans l’éditeur de requêtes pour déterminer quelles requêtes détiennent encore des
verrous.
--
-- The pair matching targets report current unpaired events using
-- the sys.dm_xe_session_targets dynamic management view (DMV)
-- in XML format.
-- The following query retrieves the data from the DMV and stores
-- key data in a temporary table to speed subsequent access and
-- retrieval.
--
SELECT
objlocks.value('(action[@name="session_id"]/value)[1]', 'int')
AS session_id,
objlocks.value('(data[@name="database_id"]/value)[1]', 'int')
AS database_id,
objlocks.value('(data[@name="resource_type"]/text)[1]',
'nvarchar(50)' )
AS resource_type,
objlocks.value('(data[@name="resource_0"]/value)[1]',
'bigint')
AS resource_0,
objlocks.value('(data[@name="resource_1"]/value)[1]',
'bigint')
AS resource_1,
objlocks.value('(data[@name="resource_2"]/value)[1]',
'bigint')
AS resource_2,
objlocks.value('(data[@name="mode"]/text)[1]', 'nvarchar(50)')
AS mode,
objlocks.value('(action[@name="sql_text"]/value)[1]',
'varchar(MAX)')
AS sql_text,
CAST(objlocks.value('(action[@name="plan_handle"]/value)[1]',
'varchar(MAX)') AS xml)
AS plan_handle,
CAST(objlocks.value('(action[@name="tsql_stack"]/value)[1]',
'varchar(MAX)') AS xml)
AS tsql_stack
INTO #unmatched_locks
FROM (
SELECT CAST(xest.target_data as xml)
lockinfo
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address =
xest.event_session_address
WHERE xest.target_name = 'pair_matching' AND xes.name =
'FindBlockers'
) heldlocks
 CROSS APPLY lockinfo.nodes('//event[@name="lock_acquired"]') AS
T(objlocks)

--
-- Join the data acquired from the pairing target with other
-- DMVs to return provide additional information about blockers
--
SELECT ul.*
FROM #unmatched_locks ul
INNER JOIN sys.dm_tran_locks tl ON ul.database_id =
tl.resource_database_id AND ul.resource_type = tl.resource_type
WHERE resource_0 IS NOT NULL
AND session_id IN
(SELECT blocking_session_id FROM sys.dm_exec_requests
WHERE blocking_session_id != 0)
AND tl.request_status='wait'
AND REPLACE(ul.mode, 'LCK_M_', '' ) = tl.request_mode

40. Après avoir identifié les problèmes, supprimez les éventuelles tables temporaires et la
session d'événement.

DROP TABLE #unmatched_locks

DROP EVENT SESSION FindBlockers ON SERVER

Pourquoi utiliser XEvent Profiler ?

Contrairement à SQL Profiler, XEvent Profiler est directement intégré à SSMS et s’appuie
sur la technologie d’événements étendus scalable du moteur SQL. Cette fonctionnalité
permet un accès rapide à une vue de diffusion en continu en direct des événements de
diagnostic sur SQL Server. Cette vue peut être personnalisée et ces personnalisations
peuvent être partagées avec d’autres utilisateurs SSMS sous forme de
fichier .viewsettings. La session créée par XE Profiler est moins intrusive pour sql Server
en cours d’exécution qu’une trace SQL similaire lors de l’utilisation de SQL Profiler. Cette
 session peut également être personnalisée par l’utilisateur, à l’aide de l’interface
utilisateur des propriétés de session XE existante ou de Transact-SQL.

Mise en route

Pour accéder à XEvent Profiler, procédez comme suit :

41. Ouvrez SQL Server Management Studio.
42. Connectez-vous à une instance du Moteur de base de données SQL Server ou à un hôte
local.
43. Dans l’Explorateur d’objets, recherchez l’élément de menu XE Profiler et développez-le
en cliquant sur le signe « + ».

44. Double-cliquez sur Standard si vous souhaitez afficher tous les événements de cette
session. Cliquez sur T-SQL si vous souhaitez voir les instructions SQL journalisées. Si une
session n’est pas encore créée, une session est créée pour vous.
45. Vous pouvez maintenant afficher les événements capturés par la session.
 Superviser le niveau de performance avec le Magasin des requêtes
La fonctionnalité Magasin des requêtes vous fournit des aperçus sur le choix et les
performances du plan de requête pour SQL Server, Azure SQL Database, Azure SQL
Managed Instance et Azure Synapse Analytics. Le Magasin des requêtes simplifie la
résolution des problèmes de performances en vous permettant de trouver rapidement
les différences de performances provoquées par des changements de plan de requête.
Le magasin de requête capture automatiquement l’historique des requêtes, des plans et
des statistiques d’exécution et les conserve à des fins de révision. Elle sépare les
données en périodes, ce qui vous permet de voir les modèles d'utilisation de base de
données et de comprendre à quel moment les changements de plan de requête ont eu
lieu sur le serveur.

Activer le magasin des requêtes

Utiliser la page Magasin des requêtes dans SQL Server Management Studio

46. Dans l’Explorateur d’objets, faites un clic droit sur une base de données, puis
sélectionnez Propriétés.
47. Dans la boîte de dialogue Propriétés de la base de données , sélectionnez la page
Magasin de requêtes .
48. Dans la zone Mode d’opération (demandé) , sélectionnez Lecture Écriture.

Utiliser la fonctionnalité Requêtes régressées

Après avoir activé le magasin des requêtes, actualisez la partie de la base de données du
volet de l’Explorateur d’objets pour ajouter la section Magasin des requêtes .
Notes
Pour Azure Synapse Analytics, les vues du Magasin des requêtes sont disponibles sous
Vues système dans la partie base de données du volet Explorateur d’objets.
Sélectionnez Requêtes régressées pour ouvrir le volet Requêtes régressées dans SQL
Server Management Studio. Le volet Requêtes régressées affiche les requêtes et les
plans du magasin de requêtes. Utilisez les zones de liste déroulante en haut pour filtrer
les requêtes en fonction de différents critères : Durée (ms) (par défaut), Temps
processeur (ms), Lectures logiques (Ko), Écritures logiques (Ko), Lectures physiques (Ko),
Temps CLR (ms), DOP, Consommation de mémoire (Ko), Nombre de lignes, Mémoire
journal utilisée (Ko), Mémoire de base de données temporaire utilisée (Ko) et Temps
d’attente (ms).
Sélectionnez un plan pour afficher le plan de requête sous forme graphique. Des
boutons sont disponibles pour afficher la requête source, forcer et désactiver
l’application forcée d’un plan de requête, basculer entre les formats de grille et de
graphique, comparer des plans sélectionnés (si plusieurs plans sont sélectionnés) et
actualiser l’affichage.
Pour forcer un plan, sélectionnez une requête et un plan, puis Forcer le plan. Vous
pouvez uniquement forcer des plans qui ont été enregistrés par la fonctionnalité de plan
de requête et sont toujours conservés dans le cache du plan de requête.

Rechercher les requêtes en attente

À compter de SQL Server 2017 (14.x) et Azure SQL Database, les statistiques d’attente
par requête au fil du temps sont disponibles dans Magasin des requêtes.
Dans le Magasin des requêtes, les types d’attente sont combinés en catégories
d’attente. Vous trouverez dans sys.query_store_wait_stats (Transact-SQL) une
correspondance entre les catégories d’attente et les types d’attente.
Sélectionnez Statistiques d’attente de requête pour ouvrir le volet Statistiques
d’attente de requête dans SQL Server Management Studio v18 ou version ultérieure. Le
volet Statistiques d’attente des requêtes contient un graphique à barres qui indique les
principales catégories d’attente dans le Magasin des requêtes. Utilisez la liste déroulante
en haut pour sélectionner un critère d’agrégation pour le temps d’attente : avg, max,
min, std dev et total (valeur par défaut).
Sélectionnez une catégorie d’attente en sélectionnant dans la barre et une vue détaillée
sur la catégorie d’attente sélectionnée s’affiche. Ce nouveau graphique à barres contient
les requêtes qui ont contribué à cette catégorie d’attente.

Utilisez la zone de liste déroulante en haut pour filtrer les requêtes en fonction de
différents critères de temps d’attente pour la catégorie d’attente sélectionnée : avg, max,
min, std dev et total (valeur par défaut). Sélectionnez un plan pour afficher le plan de
requête sous forme graphique. Des boutons permettent d'afficher la requête source, de
forcer un plan de requête et d’annuler son application forcée, ainsi que d'actualiser
l'affichage.
Les catégories d’attente combinent différents types d’attente dans des compartiments
similaires par nature. Différentes catégories d’attente nécessitent une analyse de suivi
différente pour résoudre le problème, mais les types d’attente d’une même catégorie
entraînent des expériences de résolution de problèmes très similaires à condition que la
requête affectée au-dessus des attentes soit l’élément manquant de la plupart de ces
expériences.
Voici quelques exemples vous permettant d’obtenir plus d’insights sur votre charge de
travail avant et après l’introduction des catégories d’attente dans le Magasin des
requêtes :

Expérience Nouvelle Action

précédente expérience
Attentes élevées de Attentes Recherchez les principales requêtes
RESOURCE_SEMAPH élevées de consommatrices de mémoire dans le Magasin
ORE par base de mémoire dans des requêtes. Ces requêtes retardent
données le Magasin probablement davantage la progression des
des requêtes requêtes affectées. Utilisez l’indicateur de
pour des requête MAX_GRANT_PERCENT pour ces
requêtes requêtes ou pour les requêtes concernées.
spécifiques
Attentes élevées de Attentes Vérifiez les textes de requêtes pour les
LCK_M_X par base de élevées de requêtes affectées et identifiez les entités
données verrouillage cibles. Recherchez dans le Magasin des
dans le requêtes d’autres requêtes modifiant la même
Magasin des entité, qui sont fréquemment exécutées et/ou
requêtes pour ont une durée importante. Après avoir
des requêtes identifié ces requêtes, envisagez de changer
spécifiques la logique d’application pour améliorer l’accès
concurrentiel, ou utilisez un niveau d’isolation
moins restrictif.
Attentes élevées de Attentes Recherchez les requêtes comportant un grand
PAGEIOLATCH_SH élevées d’E/S nombre de lectures physiques dans le
par base de données de mémoire Magasin des requêtes. Si elles correspondent
tampon dans aux requêtes avec des attentes élevées d’E/S,
le Magasin introduisez un index sur l’entité sous-jacente
des requêtes pour faire des recherches au lieu d’analyses et
pour des ainsi réduire la surcharge d’E/S des requêtes.
requêtes
spécifiques
Attentes élevées de Attentes Recherchez les requêtes les plus
SOS_SCHEDULER_YIE élevées du consommatrices de processeur dans le
LD par base de processeur Magasin des requêtes. Parmi elles, identifiez
données dans le celles pour lesquelles la tendance de
Magasin des processeur élevé correspond aux attentes
requêtes pour élevées de processeur pour les requêtes
des requêtes concernées. Concentrez-vous sur
spécifiques l’optimisation de ces requêtes : il peut y avoir
une régression de plan ou peut-être un index
manquant.