Toute modification de données se fait d’abord sur les pages de la base de données qui sont en mémoire.
Toute page qui subit une modification mais qui n’a pas encore été copiée sur disque dur est alors qualifiée de « sale », ou « dirty » dans la littérature.

En effet, SQL Server, comme la plupart des SGBDR, travaille exclusivement en mémoire vive, pour la simple raison que son accès est au moins 1000 fois plus rapide qu’un accès aux disques durs, si performants qu’ils soient.

Mais alors comment sont persistées ces changements sur les disques durs qui supportent la base de données ?

Comme les trois as de la gâchette du célèbre film sont à la recherche d’un chargement d’or disparu, ces trois processus flairent avec précision, quand on les « sonne », les pages de données qui ont été modifiées pour optimiser le fonctionnement du moteur de base de données.

Techniquement, chaque page de la base de données qui est chargée en RAM contient un compteur qui représente le nombre de fois où la page a été accédée, et un drapeau qui indique si la page est sale.
A intervalles de temps réguliers, le processus LazyWriter et Checkpoint scanne la RAM et décrémente le compteur.
A chaque fois que la page est accédée, ce compteur est incrémenté.
Lorsque la valeur de ce compteur est à zéro, SQL Server vérifie si la page est « sale ».
Si c’est le cas, les changements de la page sont écrits sur disque et la page est libérée.
Sinon, la page est directement libérée.

=> Le Bon : Lazywriter

Pourquoi « Le Bon » ? Parce que son travail est de garantir un certain nombre de pages libres en RAM, prêtes à recevoir des données.

Avec cette méthode donc, les pages les plus fréquemment accédées restent en mémoire, alors que celles qui le sont moins sont libérées, et permettent l’accès à d’autres pages de la base de données.

Imaginons un moment que ce processus n’existe pas.
Alors si la taille de ma base de données est plus grande que la taille de la RAM disponible sur le serveur qui supporte la base de données, on ne pourrait plus accéder à aucune table une fois que la RAM serait peuplée par les tables dernièrement accédées.

C’est d’ailleurs tout à l’image de l’utilisation d’une base de données : en général, les dernières données accédées sont celles qui sont le plus souvent réutilisées peu après.
Celles qui en revanche on été écrites il y un moment sont beaucoup moins manipulées.

S’il y a donc beaucoup de pages libres, ce processus aura peu de travail, car il est conçu pour conserver un minimum de pages toujours libres en RAM.
En revanche, si ce processus est exécuté très souvent, cela peut traduire une pression mémoire.
Pour visualiser les exécutions de ce processus, on peut utiliser le compteur de performance SQLServer:Buffer Manager / Lazy writes/sec dans le Moniteur de Performances.

=> La Brute: Checkpoint

Checkpoint écrit aussi les pages « sales » de la RAM sur disque, mais son but n’est pas de garantir un minimum de pages toujours libres en RAM.

Il est exécuté :

Рpar d̩faut toutes les minutes
– lors de l’exécution d’une instruction ALTER DATABASE
– lors de l’arrêt du service SQL Server

En effet, il est programmé pour garantir un temps de rétablissement de la base de données aussi court que possible.
Le rétablissement d’une base de données, durant lequel la base de données est inaccessible, se fait par la lecture complète du fichier du journal des transactions, à la recherche de transactions qui auraient été validées mais pas écrites sur disque lors de l’arrêt de la base de données.
Cette étape est appelée « REDO ».
Ce processus peut donc être très long s’il s’agit de bases de données OLTP supportant un fort trafic.

Or ce que l’on recherche, c’est qu’une telle base de données ne soit jamais inaccessible, ou au pire qu’elle le soit pour une durée aussi courte que possible.
Pour garantir un rétablissement en une minute (valeur par défaut de l’option de configuration recovery interval (min), ce processus écrit toutes les pages « sales » sur disque et ajoute une entrée dans le fichier du journal des transactions pour indiquer que tous les changements avant cette entrée sont maintenant persistés dans le fichier du journal des transactions.

Il faut noter que Checkpoint écrit toutes les pages « sales » sur disque, pas seulement celles des transactions validées. C’est pour cela qu’il est « La Brute ».
Cela signifie donc que la page peut être « sale » pour une transaction qui va peut-être être annulée.
En fait, quand l’annulation de la transaction aura lieu, la page sera de nouveau marquée « sale », et écrite plus tard sur disque.

Enfin, si la base de données à été arrêtée après que les pages aient été écrites sur disque, mais avant que la transaction ait été validée, une partie du processus de rétablissement annulera une telle transaction. Cette étape est appelée « UNDO ».

On peut bien évidemment changer la valeur de l’option de configuration recovery interval (min), mais cela n’est pas conseillé.
En effet, si l’on augmente ce temps, évidemment ce processus s’exécutera moins souvent.
Mais quand il s’exécutera, il aura plus de pages à écrire sur disque, ce qui peut se transformer en contention disque.

Pour visualiser les exécutions de ce processus, on peut utiliser le compteur de performance SQLServer:Buffer Manager / Checkpoint pages/sec dans le Moniteur de Performances.

=> Le Truand : Ghost Cleanup

Pareillement à LazyWriter et Checkpoint, Ghost Cleanup est un processus d’arrière-plan.
Son rôle est de supprimer les clés fantômes, c’est-à-dire une valeur de clé d’un index qui vient d’être supprimée.
En effet, la maintenance d’un index sur une instruction DELETE ne supprime pas physiquement la valeur de clé : elle le marque comme supprimé; il est donc fantôme : c’est « Le Truand ».

Exposé en tant que tel, on peut le voir comme un défaut, mais il s’agit en fait d’une optimisation.
Cela permet en effet aux transactions DELETE de s’exécuter plus vite, mais aussi d’être annulées plus vite (il n’est pas besoin de réinsérer la ligne).

C’est donc Ghost Cleanup qui va ensuite supprimer physiquement les valeurs de clé de l’index marquées comme fantôme, ce qui a donc pour effet de grouper les entrées/sorties disque.

=> Comment voir ces processus à l’aide d’une requête ?

En exécutant la requête suivante :

SELECT *
FROM sys.dm_exec_requests
WHERE command = ‘LAZY WRITER’
OR command = ‘CHECKPOINT’
OR command = ‘GHOST CLEANUP’

ElSüket