Blog ENI : Toute la veille numérique !
-25€ dès 75€ sur les livres en ligne, vidéos... avec le code FUSEE25. J'en profite !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. SQL Server 2019
  3. Outils pour l'optimisation
Extrait - SQL Server 2019 Apprendre à administrer une base de données transactionnelle avec SQL Server Management Studio
Extraits du livre
SQL Server 2019 Apprendre à administrer une base de données transactionnelle avec SQL Server Management Studio
4 avis
Revenir à la page d'achat du livre

Outils pour l'optimisation

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...

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. Le modèle Standard (par défaut) est adapté dans la plupart des cas :

images/10ri03.PNG

Dès que l’on...

É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.

images/10ri06.PNG

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.

images/10ri07.PNG

Depuis la page Événements, il est possible de sélectionner les événements....

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.

images/10ri14.PNG

Les principaux objets spécifiques à SQL Server sont :

  • 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...

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...

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...