Name: SQL Server 2022 Apprendre à administrer un serveur de base de données
Rating: 5 (1 reviews)

Le plan d’exécution d’une requête

Les requêtes, procédures et déclencheurs sont analysés par SQL Server et l’optimiseur de requête va stocker le plan d’exécution dans la mémoire de SQL Server, plus exactement dans la zone mémoire intitulée mémoire cache du plan. Il est possible d’analyser cette version compilée de la requête pour mieux comprendre les choix faits par l’optimiseur de requête et réagir pour permettre une exécution plus rapide de la requête. Ce qui peut se traduire par une nouvelle écriture de la requête, l’ajout d’index, la mise à jour des statistiques...

L’optimisation des requêtes n’est pas le seul point à prendre en compte pour résoudre les problèmes de performances, mais ce n’est pas non plus un élément à négliger. En effet, se focaliser sur des problèmes mémoire lorsque la requête est mal écrite peut masquer momentanément les mauvais temps de réponse, mais le problème se produira de nouveau lorsque le volume de données augmentera.

Il n’est pas possible d’afficher le plan d’exécution d’un déclencheur ou d’une procédure stockée.

Pour visualiser le plan d’exécution sous SQL Server Management Studio, il existe deux options :

Afficher le plan d’exécution estimé : le script Transact SQL n’est pas exécuté, le plan d’exécution affiché est issu de l’analyse de la requête par l’optimiseur de requête.
Inclure le plan d’exécution réel : le script Transact SQL est exécuté et le plan d’exécution utilisé est affiché....

Le générateur de profils

Pour capturer l’activité du serveur et ainsi être capable d’analyser la charge de travail soumise à un serveur SQL, deux outils sont proposés : le générateur de profils (ancien outil marqué déprécié par Microsoft, mais très ergonomique et intégré à d’autres outils SQL Server) et les événements étendus qui permettent de lancer des sessions en arrière-plan.

Le générateur de profils est aux requêtes ce qu’un outil comme WireShark est au réseau : au lieu de capturer des trames réseau, il capture des requêtes SQL. Ces captures peuvent être paramétrées pour indiquer les types de requêtes à enregistrer ainsi que les informations nécessaires (comme les filtres de WireShark). Ceci peut être réalisé à partir de modèles prédéfinis.

Le générateur de profils peut être lancé à partir de Management Studio dans le menu Outils. Il est nécessaire ensuite de se connecter à une instance de SQL Server (comme pour Management Studio). Les paramètres d’une nouvelle trace sont ensuite demandés.

Aucun modèle n’existe par défaut pour SQL Server 2022, il faut donc choisir les évènements à enregistrer et éventuellement créer un modèle pour les prochaines utilisation. Les évènements intéressants et correspondant au modèle standard des anciennes versions sont RPC:Completed et SQL:BatchCompleted :

Dès que l’on clique sur le bouton Exécuter, l’enregistrement de l’activité commence :

Pour arrêter l’enregistrement de la trace, il faut aller dans le menu Fichier...

Événements étendus

1. La création de session

Les événements étendus sont amenés à remplacer le générateur de profils, ainsi les nouveautés n’apparaîtront pas dans ce dernier. Un avantage des événements étendus est de pouvoir lancer une session d’analyse en arrière-plan (pas besoin de session interactive comme pour le générateur de profils).

Pour capturer l’activité, il est tout d’abord nécessaire de définir une nouvelle session depuis le nœud Gestion - Événements étendus - Sessions.

La création d’une nouvelle session peut être réalisée à partir de l’assistant ou bien en paramétrant directement la session. L’assistant permet de construire la session à partir d’un modèle dont certains sont équivalents à ceux du générateur de profils, alors que le passage par la boîte de dialogue permet de réaliser sa session en définissant les différentes options dans l’ordre que l’on souhaite. Les modifications de session passent toujours par la boîte de dialogue des propriétés quel que soit le mode de création choisi.

Depuis la page Événements, il est possible de sélectionner les événements. Dans le cas où un modèle est utilisé, alors des événements sont prédéfinis pour être analysés.

Exemple

Les événements définis par défaut depuis le modèle Standard

Enfin, la page Stockage de données est importante car elle permet de définir l’emplacement où ces données de type événement vont être enregistrées.

Dans...

L’analyseur de performances (moniteur système)

Il s’agit de l’analyseur de performances de Windows, auquel de nombreux compteurs ont été ajoutés lors de l’installation de SQL Server.

Voici les principaux objets spécifiques à SQL Server :

Agent de réplication : surveiller les agents de réplication en cours d’exécution.
Base de données : surveiller l’utilisation de la base de données, comme la quantité d’espace journal disponible ou le nombre de transactions actives.
Capture instantanée : surveiller la capture instantanée des réplications.
Distribution de réplication : surveiller le nombre de commandes et de transactions lues à partir de la base de données distribution.
Fusion de réplication : surveiller l’exécution de chaque fusion qui déplace les modifications de données, soit de l’abonné vers l’éditeur, soit l’inverse.
Gestionnaire de cache : permet de surveiller la façon dont SQL Server utilise la mémoire pour stocker des objets (procédures stockées...).
Gestionnaire de tampon : permet de surveiller la façon dont SQL Server utilise la mémoire pour stocker des pages de données.
Gestionnaire mémoire : surveiller l’utilisation globale de la mémoire.
Lecteur du journal des transactions : surveiller l’agent de lecture du journal des transactions.
Méthodes d’accès : surveiller l’accès aux pages logiques.
Réservé à l’utilisateur : la définition des compteurs (10 au maximum) est à la charge de l’utilisateur.
Statistiques générales : surveiller l’activité générale du serveur.
Statistiques...

L’optimisation de la mémoire

Par défaut, SQL Server gère automatiquement et dynamiquement la quantité de mémoire qui lui est nécessaire. Cette option doit être conservée dans la majorité des cas. Il est pourtant possible de figer les quantités de mémoire minimum, maximum et la taille du jeu de travail.

L’Analyseur de performances va permettre de surveiller l’utilisation de la mémoire afin de s’assurer que le serveur dispose de suffisamment de mémoire. Le compteur utilisé est Gestionnaire de tampons : Durée non référencée par page d’extension (en anglais Buffer Manager : Page life expectancy). Ce compteur indique la durée moyenne de présence dans le cache des pages de données. Ainsi, plus ce compteur est élevé, plus le pourcentage de lecture de page de données en mémoire (à la place du fichier sur disque) est élevé, donc meilleures sont les performances. Une valeur de quelques minutes sera donc trop faible car à peine les pages seront arrivées dans le cache qu’elles seront remplacées par des nouvelles pages en mémoire. Elles devront donc être lues à partir du disque dur.

Attention à l’interprétation de ce compteur : une lecture en début de journée peut s’avérer non représentative de la réalité si des travaux manipulant des données ont tournés pendant la nuit. Dans ce cas, les données concernées par ces travaux ont « pollué » le cache de données. Il faut dans ce cas faire plusieurs lectures de ce compteur à différents moments de la journée.

L’assistant Paramétrage du moteur de base de données

L’assistant Paramétrage du moteur de base de données a pour objectif de proposer la création d’index et de statistiques en confrontant l’organisation actuelle avec une charge de travail.

Il est possible de demander l’exécution de cet outil en ligne de commande avec dta.exe.

La charge de travail correspond soit à une trace capturée au préalable par le générateur de profil de SQL Server (fichier .trc), soit à un script Transact SQL.

À partir de cette charge de travail, l’outil va éventuellement proposer une réorganisation du schéma logique en ajoutant des index supplémentaires, en partitionnant certaines tables ou bien encore en proposant la création de vues indexées. Les propositions faites par l’assistant ont pour objectif de réduire le coût estimé par l’optimiseur de requête pour la charge de travail analysée.

Lors de l’analyse d’une charge de travail, il est nécessaire de paramétrer trois éléments :

Nommer de façon unique l’analyse.
Référencer vers un fichier ou une table contenant une charge de travail.
Sélectionner la ou les bases qui vont être concernées par cette analyse.

1. Initialisation de l’assistant de paramétrage

Toutes les informations relatives au paramétrage vont être stockées dans la base msdb. Pour cela, lors de la première exécution de l’assistant de paramétrage, une structure va être définie dans msdb. Seul un utilisateur disposant des droits d’administrateur du système (sysadmin) peut mener à bien cette tâche. Par la suite, l’utilisateur devra au moins être membre du rôle...

Vues/fonctions de gestion dynamique (DMV/DMF)

Il existe quelques vues ou fonctions qui peuvent être utiles dans l’optimisation d’un serveur SQL Server :

sys.dm_db_index_usage_stats : liste l’utilisation des index. Un index inutilisé pourra donc éventuellement être supprimé.
sys.dm_db_index_physical_stats : indique la fragmentation des index. Cette information peut être utile afin de prévoir les opérations de maintenance sur les index.

Outils pour l'optimisation