36
Chapitre 3. Optimisation du matériel
Buffer
Le cache de données (appelé le buffer, ou buffer pool) maintient en mémoire vive
autant qu’il est possible de pages de données et d’index. La lecture à partir de la
RAM étant environ mille fois plus rapide que sur un disque dur, on en comprend
aisément l’intérêt. Ce cache est géré par un composant de SQLOS nommé le buffer
manager, dont le travail est la lecture des pages du disque vers le buffer, et l’écriture
des pages modifiées du buffer vers le disque.
Lors du démarrage de l’instance, SQL Server calcule et réserve l’espace d’adressage virtuel du buffer (la cible de mémoire, ou memory target). Il s’agit d’espace
réservé, mais la mémoire physique ne sera prise que selon les besoins. La mémoire
réellement utilisée est donc inférieure à l’espace réservé tant que les données demandées par les requêtes ne l’auront pas rempli (cette période avant remplissage est
appelée le ramp-up). Vous pouvez voir la mémoire cible et la mémoire réellement
utilisée (en pages de 8 Ko) dans les colonnes bpool_commit_target et
bpool_committed de la vue de gestion dynamique sys.dm_os_sys_info :
SELECT bpool_commit_target, bpool_committed FROM sys.dm_os_sys_info;
Une commande DBCC permet de vider les pages du buffer : DBCC DROPCLEANBUFFERS.
Comme son nom l’indique, seules les pages propres (non modifiées) sont supprimées, les
autres attendent un point de contrôle pour être écrites sur le disque. Vous pouvez donc
vous assurer de vider le plus de pages possibles en lançant d’abord la commande
CHECKPOINT :
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
Lorsque SQL Server a « validé » de la mémoire physique pour le buffer, il ne la
rend plus au système. Depuis le moniteur système ou le gestionnaire de tâche (colonnes Memory Usage et Virtual Memory Size), vous verrez le processus sqlserver.exe
prendre de plus en plus de mémoire virtuelle au fur et à mesure de l’exécution des
requêtes, et ne jamais la rendre. Faisons-en la démonstration de l’intérieur de SQL
Server.
/* nous créons premièrement des instructions de
SELECT pour chaque table d'AdventureWorks */
SELECT 'SELECT * FROM [' + TABLE_CATALOG + '].[' +
TABLE_SCHEMA + '].[' + TABLE_NAME + '];'
FROM AdventureWorks.INFORMATION_SCHEMA.TABLES;
/* pages validées du buffer */
SELECT bpool_committed, bpool_commit_target FROM sys.dm_os_sys_info;
-- nous avons copié ici le résultat de la génération de code
SELECT * FROM [AdventureWorks].[Production].[ProductProductPhoto];
/* […] */
SELECT * FROM [AdventureWorks].[Production].[ProductPhoto];
GO
-- pages validées du buffer
Chapitre 3. Optimisation du matériel
Buffer
Le cache de données (appelé le buffer, ou buffer pool) maintient en mémoire vive
autant qu’il est possible de pages de données et d’index. La lecture à partir de la
RAM étant environ mille fois plus rapide que sur un disque dur, on en comprend
aisément l’intérêt. Ce cache est géré par un composant de SQLOS nommé le buffer
manager, dont le travail est la lecture des pages du disque vers le buffer, et l’écriture
des pages modifiées du buffer vers le disque.
Lors du démarrage de l’instance, SQL Server calcule et réserve l’espace d’adressage virtuel du buffer (la cible de mémoire, ou memory target). Il s’agit d’espace
réservé, mais la mémoire physique ne sera prise que selon les besoins. La mémoire
réellement utilisée est donc inférieure à l’espace réservé tant que les données demandées par les requêtes ne l’auront pas rempli (cette période avant remplissage est
appelée le ramp-up). Vous pouvez voir la mémoire cible et la mémoire réellement
utilisée (en pages de 8 Ko) dans les colonnes bpool_commit_target et
bpool_committed de la vue de gestion dynamique sys.dm_os_sys_info :
SELECT bpool_commit_target, bpool_committed FROM sys.dm_os_sys_info;
Une commande DBCC permet de vider les pages du buffer : DBCC DROPCLEANBUFFERS.
Comme son nom l’indique, seules les pages propres (non modifiées) sont supprimées, les
autres attendent un point de contrôle pour être écrites sur le disque. Vous pouvez donc
vous assurer de vider le plus de pages possibles en lançant d’abord la commande
CHECKPOINT :
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
Lorsque SQL Server a « validé » de la mémoire physique pour le buffer, il ne la
rend plus au système. Depuis le moniteur système ou le gestionnaire de tâche (colonnes Memory Usage et Virtual Memory Size), vous verrez le processus sqlserver.exe
prendre de plus en plus de mémoire virtuelle au fur et à mesure de l’exécution des
requêtes, et ne jamais la rendre. Faisons-en la démonstration de l’intérieur de SQL
Server.
/* nous créons premièrement des instructions de
SELECT pour chaque table d'AdventureWorks */
SELECT 'SELECT * FROM [' + TABLE_CATALOG + '].[' +
TABLE_SCHEMA + '].[' + TABLE_NAME + '];'
FROM AdventureWorks.INFORMATION_SCHEMA.TABLES;
/* pages validées du buffer */
SELECT bpool_committed, bpool_commit_target FROM sys.dm_os_sys_info;
-- nous avons copié ici le résultat de la génération de code
SELECT * FROM [AdventureWorks].[Production].[ProductProductPhoto];
/* […] */
SELECT * FROM [AdventureWorks].[Production].[ProductPhoto];
GO
-- pages validées du buffer