Auditer les performances d'une instance SQL Server
Le 01/08/2024
Le temps passe, les choses évolues, et SOSQL aussi.
Avec désormais plus de 90 audits il était temps d’offrir une expérience plus riche et interactive.
En effet, il en est fini des .pdf trop statiques et réducteurs.
Vous pourrez désormais partager le compte rendu interactif avec tous les membres de votre équipe pour que chacun puisse faire le focus sur ses priorités.
Découvrez la nouvelle version des audits de performance dans une démo privée.
Tous les détails sont dans le Post suivant Audit de performance Sql Server
A ce jour (03/02/2023), j’ai réalisé en trois ans plus de 60 audits de performance sur des instances SQL Server de toutes versions (2008 à 2022).
Je n’utilise jamais d’outils tiers, je préfère et de loin utiliser mes scripts SQL pour deux raisons simples.
1- Je sais exactement ce qu’ils font, je peux donc m’engager sur une clause de confidentialité, aucune donnée autre que technique n’est collectée.
2- Je sais exactement l’impact qu’ils peuvent avoir, ce qui me permet de garantir qu’il sera vraiment minime et sans effets de bord.
Il serait contreproductif de tuer une instance SQL juste pour l’auditer.
Vous trouverez ci-dessous les détails sur les informations que proposent mes audits.
Certaines données seront bien sûr anonymisées.
Table des matières
Le paramétrage du serveur
Il y a certains paramètres à vérifier au niveau du serveur de même qu’au niveau des services.
– Droits et types des comptes de service (Local, Domaine, MSA).
– Les paramètres de démarrage du service et les Trace Flags.
– La mémoire, les cpu et le nombre de Sockets (surtout pour SQL Standard).
– La répartition des disques.
Etc…
Le paramétrage d'une instance SQL
Au niveau de l’instance SQL, il y a plusieurs paramètres à vérifier.
– La mémoire allouée.
– L’affectation CPU.
– Le MaxDop.
– Le Cost Threshold.
– Les paramètres de sécurité.
– Les paramètres de base de données.
– La surface d’exposition.
Etc…
Le contenu du Cache Plan
L’analyse du contenu du Cache Plan nous donne des informations pertinentes.
Le taux de requêtes Ad Hoc, Prepared, de Proc, View et parfois même des informations sur les triggers et le Parse Tree.
Cela nous permet de décider s’il faut optimiser l’instance pour les Workflow Ad Hoc ou bien passer en paramétrage forcé au niveau d’une base de données.
L’optimisation de l’instance pour les Workflow Ad Hoc a un impact sur toutes les bases de données et sur le stockage des plans en cache.
Si l’option est activée, ce ne sont plus des plans complets qui sont stockés mais juste des Stub (extraits de plan).
Nous avons aussi des informations pour paramétrer le Cost Threshold (seuil de coût de parallélisme).
Un seuil trop bas permet de paralléliser sur de petites requêtes qui ne le nécessitent pas et engendre un surcoût CPU inutile voire contreproductif (Threads).
Il vaut mieux paralléliser sur de grosses requêtes qui tirent avantage du parallélisme.
Le MaxDop (degré maximum de parallélisme) est lié au nombre de CPU et de nœuds NUMA.
L'utilisation CPU
L’utilisation CPU nous permet de savoir quelles sont les heures où il y a des pics de consommation CPU et si ces pics sont dus à SQL Server ou à des process externes.
La latence sur les I/O
L’information sur les temps de latence en lecture/écriture nous permet de connaitre les heures de pointe en I/O.
Elle nous permet aussi d’identifier la latence éventuelle par disque et types de fichiers (Rows = data, et Log = log).
Utilisation des ressources par base de données
Les moyennes affichées sont calculées au prorata de la consommation de l’instance.
Par exemple, si l’instance consomme 20% de CPU, une bases de données peut, elle, consommer 80% de ces 20%.
Au final, cela permet d’identifier les bases de données à vérifier/optimiser en priorité ou tout simplement les plus consommatrices de ressources.
Le nombre de transactions par seconde
Ces graphiques nous permettent de connaitre l’intensité du trafic par heure sur l’ensemble de la période de capture.
Cela permet par exemple de modifier la planification des plans de maintenance ou d’identifier si ce sont eux qui sont à l’origine du trafic ou tout autre traitement.
Le nombre d'exécutions de requêtes par plage de durée
Cette vision globale nous donne une bonne image du travail d’optimisation des requêtes qui se profile.
90% des problèmes sont généralement liés au codage et/ou au modèle.
Dans le cas présent, il est clairement nécessaire d’approfondir et d’identifier ce qu’il se passe au niveau des bases de données.
Les infos précédemment récoltées nous permettent aussi de nous orienter vers les potentielles BDD à optimiser.
Il y a de fortes probabilités que les bases de données qui engendrent le plus gros trafic soient aussi à la source de ces résultats.
Les statistiques d'utilisation des index
Avoir une vision de l’utilisation et donc de la pertinence de l’indexation par base de données est une source d’optimisation importante.
Trop d’index tue l’index, et des index inutilisés ont un coût en INSERT, UPDATE, DELETE.
1- Efficient, signifie que les index répondent aux demandes
2- SCANS>SEEKS, signifie que les index ne répondent pas bien aux demandes et qu’ils font plus souvent l’objet d’un SCAN (Analyse complète) que d’un SEEK(Recherche sur la clé).
3- UNSUSED, signifie que les index ne sont à priori jamais utilisés en recherche même si cela peut être tout à fait normal en fonction du type de ces index.
Par exemple, il arrive qu’une clé technique ou une PK ne soit pas une clé de recherche.
4- UPDATES>READS, signifie que ces index ont un cout supérieur en mise à jour par rapport à leur usage en recherche.
Ce graphique donne juste une image de la situation mais un fichier Excel est fourni avec les détails par base de données, table, index.
C’est pour moi une source importante d’optimisation.
Cerise sur le gâteau, sont fournis aussi les exports de tous les index manquants identifiés au format SQLPLAN que l’on peut ouvrir directement dans SSMS.
Les VLF's Virtual Log Files
Les VLF’s sont compliqués à argumenter et surtout à corriger.
Il faut prendre en compte l’initial size, le File Growth et en plus selon les versions SQL il y a des Bug’s sur le File growth, notamment sur 2014/2016 ou les File Growth en Go sont mal gérés au niveau VLF.
Ce que je peux affirmer, c’est que trop de VLF pose problème et que trop peu de VLF produit le même effet.
Cela reste du cas par cas, dans la mesure où il est possible de changer les choses, ce qui n’est pas toujours le cas.
Il est généralement admis que quelques centaines ça va, quelques milliers ça ne va plus, mais aucun seuil n’est fourni par Microsoft à ma connaissance.
*Règle N°1, je n’affirme jamais quelque chose dont je ne suis pas sûr.
La surface d'exposition
La surface d’exposition concerne une vingtaine de points, rien que pour ce qui est récupérable par requête.
Il y a d’autres aspects comme le fait de cacher une instance, Etc.
Mais tout n’est pas applicable, cacher une instance ne fonctionne pas en environnement Cluster.
Interdire le Remote Access idem, si vous utilisez des serveurs liés.
Tout lister ici rendrait la page illisible, alors je mets juste la capture d’une infime partie.
Pour conclure, je dirais que même, si tout n’y est pas, vous avez une bonne vision de l’importance d’un audit de performance.
Un bon audit doit comprendre deux volets :
1-Le constat
2-Les actions à mener pour améliorer les performances et/ou la sécurité, avec une priorisation.
L’un ne va pas sans l’autre, un simple constat n’aide en rien et pour moi n’a aucun sens.