Un petit billet sur un compteur que vous connaissez certainement tous et qui permet en autre de détecter une pression mémoire sur une instance SQL Server. Seulement en faisant un audit des compteurs de surveillance d’un de mes clients, je me suis aperçu qu’il n’utilisait que ce dernier pour détecter la présence ou non une pression mémoire sur l’ensemble des instances de son parc. Je lui ai donc expliqué que l’utilisation unique de ce compteur ne permettait pas à tous les coups de détecter un problème d’utilisation mémoire. Je vous propose de voir pourquoi dans ce billet.

Tout d’abord commençons par regarder la définition de cet compteur :

« Percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server. »

Ce compteur représente donc le pourcentage de pages qui seront satisfaites directement depuis le cache de données SQL Server sans avoir à les récupérer sur disque. On peut donc facilement comprendre que la diminution de ce ratio peut signifier qu’il existe un problème potentiel de pression mémoire puisque les accès aux pages de données seront feront d’abord sur disque et ensuite dans le cache de données. Si ce compteur est directement représentatif d’un problème de mémoire alors pourquoi la surveillance unique de ce dernier ne suffit-elle pas ? Pour répondre à cette question faisons un test :

Créons une base de données avec une table ayant une volumétrie supérieure à la taille du cache alloué à SQL Server pour garantir que SQL Server devra effectuer des lectures physiques pour récupérer les pages de données nécessaires à nos requêtes. Si l’on suit la définition de notre compteur Buffer Cache Hit Ratio nous devrions voir une diminution du ration pendant notre test. J’ai limité volontairement la taille du cache de données à 1Go et j’ai créé une table avec une taille légèrement supérieure à 3Go.

EXEC sp_configure N’show advanced options’ , N’1′
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure N’max server memory (MB)’ , N’1024′
GO
RECONFIGURE WITH OVERRIDE
GO

CREATE DATABASE test_buffer_cache_hit_ratio;
GO

USE test_buffer_cache_hit_ratio;
GO

CREATE TABLE dbo.test
(
id INT IDENTITY(1,1),
col1 CHAR(1000),
col2 CHAR(1000),
col3 CHAR(1000)
);
GO

INSERT dbo.test (col1, col2, col3) VALUES (REPLICATE(‘T’, 1000), REPLICATE(‘T’, 1000), REPLICATE(‘T’, 1000))
GO 818998

Vérifions la taille de notre table :

EXEC sp_spaceused ‘dbo.test';
GO

qui donne une table de 3199 Mo :

 

Pendant notre test je vous propose d’ajouter d’autres compteurs qui nous seront utiles pour démontrer que l’utilisation unique du compteur BCHR n’est pas judicieuse.

• Page Life Expectancy : qui représenta la durée de vie en seconde d’une page dans le cache de données SQL Server sans être référencée
• Page reads/sec : qui représente le nombre de lectures physique par seconde effectuée par SQL Server. Il faut comprendre ici que la ou les pages concernés ne sont pas lues depuis le cache de données. Autant dire que cette valeur doit être la plus petite possible.
• Free pages : qui représente le nombre de pages libres disponible pour une allocation immédiate dans le cache de données 

J’ai également rajouté un compteur lié à l’utilisation des disques à savoir :

• Disks Read/sec : qui représente le nombre d’IO en lecture par seconde

 

D’autres compteurs sont présents dans la démonstration mais seuls ceux cités plus haut nous servirons dans le cadre du présent billet. La situation primaire est la suivante :

Une situation plutôt pérenne avec un BCHR de 100%, une durée de vie de page dans le cache  596 minutes  et un nombre conséquent de pages libres dans le buffer. Pour lancer nos requêtes j’utiliserai un outil plutôt sympathique pour notre exercice : SQLQueryStress.

1ère phase : Avec SQLQueryStress je lance une requête sans impact majeure (sur la base AdventureWorks) sur le cache avec suffisamment d’itération (10000) pour que celle puisse durer le temps de notre test. Au préalable j’aurai vidé le cache de données SQL Server.

2ème phase : Je lance une seconde requête qui va venir remplir notre cache de données en interrogeant les données de notre table dbo.test précédemment créé.

 

 

et voici les résultats :

Un peu d’explication est nécessaire parmi tout ces lignes de monitoring. A l’étape 1 on voit un pic de diminution du buffer cache hit ratio associé à une montée soudaine des lectures de pages sur disque. Cette première phase est tout à fait normal car ayant vider le cache des données au préalable SQL Server est obligé d’aller chercher la première fois les données sur disque. On constate d’ailleurs que la valeur du cache hit ratio se stabilise par la suite et qu’aucune lecture des pages n’est faite sur disque.

A l’étape 2 j’augmente la charge en exécutant la 2ème requête qui interroge notre fameuse table dbo.test. On constate presque instantanément que le nombre de lectures disques augmente (compteurs Disk Read/sec et Page read/sec). En revanche on se ne voit pas de changement radical de valeur pour le BCHR.

A l’étape 2 – P1, la durée de vie d’une page dans le cache de données chute drastiquement. En effet, comme la table dbo.test ne tient pas en mémoire SQL Server est obligé de vider les pages du cache de données pour satisfaire l’exécution simultanée de nos 2 requêtes. Mais encore une fois on ne voit aucun changement au niveau du BCHR.

A l’étape 2 – P2 on constate que les lectures sur disque sont nombreuses, que l’espérance de vie d’une page dans le cache des données est proche de zéro mais que le BCHR est toujours proche de 100%

On vient donc ici de démontrer que la lecture seule du BCHR n’est pas suffisante pour détecter une pression mémoire. Alors la question est pourquoi ? O_O … O_O … Tout simplement parce que nous avons oublié que SQL Server était capable de faire de la lecture anticipée et que celle-ci va donc lire plus de données que nécessaire pour tenter de satisfaire la lecture des prochaines données qui seront du coup déjà en cache. Comme nous l’avons vu un peu plus haute la valeur du BCHR détermine le taux d’accès de pages de données déjà en cache par rapport à ceux effectués sur disque. C’est le cas ici car la lecture anticipée permet comme son nom l’indique d’anticiper les prochaines lectures de pages dans le cache de données. C’est la raison pour laquelle la valeur de compteur ne bouge pratiquement pas ici. Sans cette lecture anticipée on aurait une valeur de compteur beaucoup plus variable. Peut-on le prouver ? Bien sûr. Pour cela il suffit de désactiver l’utilisation de la lecture anticipée sur notre instance SQL Server en activant le trace flag 652.

DBCC TRACEON(652, -1);
GO

On réalise à nouveau les mêmes tests que précédemment et voici le résultat :

A l’étape 1 on retrouve la même chose que tout à l’heure. Le cache étant vide au départ une première lecture des données de la première requête donne lieu à des accès sur disque pour remonter les pages dans le cache des données SQL Server.

A l’étape 2 on lance notre requête de sélection sur la table dbo.test. Ici c’est une tout autre histoire. On voit que le nombre de lectures physiques augmente comme tout à l’heure mais que la valeur du BCHR diminue drastiquement également. En effet, la lecture anticipée n’est plus là pour satisfaire les prochaine lectures de pages directement dans le cache de données. Du coup on se retrouve à aller chercher plus souvent les pages de données sur disque que dans le cache de données.

A l’étape 2 – P1 P2 on retrouve la même situation que tout à l’heure. L’espérance de vie d’une page de données chute puisque SQL Server est obligé de vider le cache au fur et à mesure pour remonter les nouvelles pages qui devront être accédées pendant l’exécution de nos 2 requêtes.

 

Nous avons donc vu que la valeur du BHCR ne permettait pas à elle seule de détecter une pression mémoire sur une instance SQL Server. Je dirai même que lorsque la valeur de ce compteur commence à chuter plus que de raison il est déjà trop tard. Maintenant il est question ici d’environnements OLTP comme vous l’avez sans doute deviner. Il n’est pas rare de voir la valeur de compteur beaucoup plus basse dans des environnements OLAP avec des entrepôts de données. J’espère en tout cas que ce billet vous aura convaincu de ne pas utiliser uniquement la valeur du BCHR dans vos outils de monitoring.

Bonne surveillance !!

David BARBARIN (Mikedavem)
MVP SQL Server