Parmi les compteurs de performance qu’expose SQL Server, il en existe quelques-uns qui ne laissent aucun doute sur les performances courantes d’une instance SQL Server. Page Life Expectancy est l’un d’entre eux, puisqu’il nous renseigne sur la durée de vie moyenne d’une page de données en RAM, en secondes.

L’ensemble des opérations de manipulation de données se faisant exclusivement en mémoire par tout SGBDR digne de ce nom, on comprend aisément que plus la valeur exposée par ce compteur est élevée, plus les performances de l’instance sont correctes. En effet, comme un accès en RAM est en pratique au moins 1000 fois plus rapide qu’un accès à des disques mécaniques, plus on peut traiter de données en RAM, moins on est exposé à des problèmes de performances.

Cela étant, les serveurs physiques actuels disposant de plusieurs sockets pour accueillir leurs CPUs, ils sont tous une implémentation de NUMA (Non-Uniform Memory Access), dans lequel chaque processeur dispose d’un bus mémoire dédié. On appelle nÅ“ud NUMA l’ensemble composé par l’espace mémoire et le processeur situés sur le même bus. On désigne par nÅ“ud NUMA distant un nÅ“ud NUMA distinct de celui sur lequel la requête s’exécute.

Si par le passé on pouvait donc se référer directement à la valeur de ce compteur, puisque les serveur ne disposaient que d’un seul socket, qu’en est-il avec les architectures multi-socket ? Comment suivre cette valeur sous SQL Server ? Quelle valeur de ce compteur peut servir de seuil d’alerte ?

Je vous propose la réponse à ces questions dans cet article !

Page Life Expectancy (PLE), qu’est-ce que c’est ?

Comme nous l’avons vu en introduction, ce compteur de performance nous indique, en secondes, la durée moyenne pendant laquelle une page reste stockée dans en RAM : c’est ce que l’on appelle plus communément le buffer pool.

Comme les opérations de lecture et d’écriture se font exclusivement en RAM, plus une page de données reste longtemps dans le buffer pool, plus l’instance SQL Server étudiée fonctionne proprement.

Au contraire, si la valeur de ce compteur est faible de façon constante, cela signifie que SQL Server est constamment obligé d’importer les pages stockées sur disque en RAM, avant de réaliser l’opération qu’on lui a soumis. Cela signifie généralement que la majeure partie de la charge de travail que l’on soumet à SQL Server lit une quantité de données qui excède celle de la RAM disponible sur le serveur, ce qui peut par exemple être du tout simplement à une configuration incorrecte de l’option d’instance max server memory (MB), à une indexation pauvre, à un plan d’exécution sous-optimal, …
Il est à noter que si l’on observe des valeurs faibles de façon sporadique, pour une durée de quelques secondes, cela est plus proche de signifier qu’une requête produit un grand nombre d’entrées/sorties. Une trace SQL Profiler combinée à un fichier du Moniteur de Performances permettra de déterminer la requête coupable (dans SQL Profiler, choisir File > Import Performance Data pour corréler un fichier .blg avec un fichier de trace).

Une valeur élevée pour PLE n’équivaut pas à une pastille verte quand à l’état des performances de l’instance SQL Server auditée. Il est nécessaire de corréler la valeur de ce compteur avec celle du compteur Buffer Cache Hit Ratio (BCHR): ce dernier exprime par un pourcentage la proportion de pages trouvées directement en RAM par rapport au nombre de pages importées à partir des disques. Ici encore, on saisit facilement que plus on est proche de 100%, plus les performances sont optimales. PLE peut avoir une valeur décente et BCHR être faible : cela signifie que les disques sont largement sollicités par la charge de travail, et qu’ils parviennent à fournir les pages de données suffisamment rapidement à SQL Server pour qu’il puisse opérer sans que l’on note une véritable dégradation des performances. Dans ce cas, un audit des requêtes grandes consommatrices d’I/O et une optimisation sont nécessaires. Je vous invite à un approfondissement sur le blog de Mikedavem.

Finalement, lorsque l’un ou l’autre de ces compteurs montre une valeur faible, nous sommes en présence d’une charge de travail qui fait subir au serveur une pression mémoire qu’il est urgent d’investiguer. C’est à partir de ces deux compteurs que l’on va décider de s’intéresser ou non à d’autres compteurs de performance (comme les queues disque, le nombre d’opération checkpoints et lazywites par seconde), …

Où trouver ce compteur de performance ?

On trouve ce compteur de performance dans le Moniteur de Performances, sous la catégorie SQL Server:Buffer Manager, de même que BCHR :

Néanmoins, en fouillant un peu plus, on trouve le groupe SQL Server:Buffer Node :

On est alors libre de suivre toutes les nodes, c’est à dire les nÅ“uds NUMA de façon individuelle, mais il nous est d’abord nécessaire de savoir sur quel nÅ“ud NUMA ladite requête s’exécute. Ceci prouve que la moyenne des PLE de chaque nÅ“ud NUMA, exposée par le compteur SQL Server:Buffer Manager / Page Life Expectancy masque cet état de fait.

Implicitement, SQL Server va donc affecter l’exécution d’une requête au planificateur qui est le plus susceptible de disposer, dans l’espace mémoire qu’il adresse, toutes les données dont la requête a besoin. Ceci est dû au fait que l’accès à un nÅ“ud NUMA distant est coûteux en ressources CPU.
L’affectation de la quantité de mémoire se fait de manière triviale : SQL Server lit la valeur de l’option de configuration max server memory (KB), et affecte à chaque nÅ“ud NUMA cette quantité de mémoire divisée par le nombre de nÅ“uds NUMA du serveur. Si ladite option n’est pas configurée, SQL Server produit le même calcul avec la quantité de RAM disponible sur le serveur.

Comment détecter un pression mémoire sur un nœud NUMA ?

Peu après la sortie commerciale de SQL Server 2005, Microsoft a publié un livre blanc dans lequel il était préconisé qu’une valeur de 300 pour PLE était une situation à éviter pour une instance SQL Server supportant une chage de travail OLTP. Malheureusement, cette recommandation s’est répandue et est restée comme un standard officieux, alors qu’il est totalement obsolète : en effet à cette époque là, peu de serveur disposaient de plusieurs dizaines de Go de RAM, et on était au point de basculement entre les architectures CPU 32 bits et 64 bits. 300 secondes signifie qu’en 5 minutes, SQL Server lit autant de pages depuis les disque que de mémoire qui lui est alloué. Si l’on applique cela à un serveur qui dispose de 128 Go de RAM, il devient évident que cette valeur seuil n’est plus appropriée. On peut considérer qu’à cette époque, les serveurs de base de données disposaient en moyenne de 8Go de RAM. On peut appliquer une règle de trois sur les configurations actuelles pour détecter une pression mémoire.

On peut exposer la différence entre les nÅ“uds NUMA à l’aide de la vue de gestion dynamique (DMV) sys.dm_os_schedulers : celle-ci expose au travers de la colonne parent_node_id le nÅ“ud NUMA auquel un planificateur d’exécution de requête accepte ces dernières. Le lot de requêtes suivant aide à mieux comprendre ce qui se passe sur un serveur servant une instance SQL Server :

Ceci donne par exemple :

On voit bien ici que la PLE est bien plus faible sur le nÅ“ud NUMA 0, et qu’elle est pas totalement identique sur les autres nÅ“uds.
On peut donc penser à mettre en place une alerte lorsque la PLE d’une des noeuds NUMA passe en-dessous de la valeur calculée par le script ci-dessus.
Malheureusement les alertes proposées par SQL Server Agent ne permettent de prendre en compte que la moyenne des PLE de tous les nÅ“uds NUMA, c’est à dire le compteur de performance SQL Server:Buffer Manager / Page Life Expectancy.
On peut cependant exécuter à intervalles réguliers et fréquents une procédure stockée qui vérifie la PLE de chaque noeud NUMA, à l’aide d’un job de l’Agent SQL Server.

Bon monitoring à tous !

ElSüket