Répartition des fichiers de données dans les FileGroups avec SQL Server
La répartition des FileGroups sur plusieurs fichiers est l’une des composantes de l’optimisation des performances SQL Server.
Lors du Setup par exemple, SQL répartit les fichiers de données en fonction du nombre de CPU pour TempDB et définit un File Growth en taille fixe.
(il ne faut jamais définir de File Growth en %)
La répartition permet de gagner en performance sur le parallélisme d’accès aux fichiers, que ce soit en lecture ou en écriture.
Répartir un FileGroup sur plusieurs fichiers permet aussi d’héberger les fichiers sur des disques différents.
Mais, pour tirer le meilleur avantage possible de la répartition, il faut savoir comment cela fonctionne.
Il n’est pas rare, c’est même très souvent le cas, qu’un DBA se voit obligé d’ajouter des fichiers à un Filegroup quand le MDF originel devient trop volumineux et c’est là que la notion de Proportional Fill entre en ligne de compte.
Trois mécanismes entrent en jeu, le Round Robin, le Proportional Fill et le Spin Target.
Etudions donc un cas concret pour comprendre ces trois notions.
Nous allons partir d’une base de données très petite puisque celle-ci ne fait que 10Go, mais il vous faudra extrapoler et imaginer qu’elle fasse plutôt 1To.
Notre base de données fait 10Go avec environ 1.6Go d’espace disponible et n’a pour l’instant qu’un seul fichier de données dans le Filegroup PRIMARY, le MDF originel.
Si notre base de données faisait 1To nous aurions environ 160Go d’espace disponible.
Côté fichiers, notre base de données se présente comme ceci.
Un seul fichier MDF de 10Go et un fichier de LOG.
Nous allons répartir le Filegroup PRIMARY sur 4 fichiers et, par conséquent, ajouter 3 fichiers de données à notre Filegroup.
La base de données étant petite, nous allons ajouter des fichiers NDF de 512Mo avec le même File Growth que le MDF originel soit 128Mo.
Si notre base de données faisait 1To, nous aurions donc ajouté des fichiers de 50Go.
Jusqu’ici, tout semble parfait, les nouvelles données seront à priori réparties sur les 4 fichiers de données, mission accomplie !
Mais la question est : que va-t-il vraiment se passer, comment le Round Robin et le Proportional Fill vont-ils vraiment répartir les nouvelles données ?
La réponse est simple, les nouvelles données ne seront écrites dans les NDF additionnels qu’une fois sur trois, pourquoi ?
Le Spin Target !
Je vais donc stocker dans une table temporaire le résultat de la requête ci-dessous:
Use [Audit_sagex3db];
DBCC showfilestats WITH NO_INFOMSGS;
Puis, j’y ajoute des colonnes pour calculer le Spin Target de chaque fichier.
Au final, cela donnera le résultat ci-dessous.
Comment fonctionnent le Proportional Fill et le Spin Target ?!
Le Round Robin boucle séquentiellement sur les fichiers de données pour y écrire, mais pour autoriser l’écriture dans un fichier, le Proportional Fill doit calculer son Spin Target.
Le calcul du Spin Target est effectué de la manière suivante :
Le Proportional Fill divise l’espace disponible du fichier qui en a le plus parmi tous les fichiers du Filegroup par l’espace disponible dans le fichier dans lequel il souhaite écrire, ce qui donne le Spin Target du fichier.
Si le résultat est égal à 1, l’écriture se fait, si le résultat est supérieur à 1, le fichier est ignoré, son Spin Target lui est affecté et il sera diminué de 1 dans les boucles suivantes.
La boucle continue (Round Robin) et nous passons au fichier suivant avec le même process jusqu’à avoir un fichier avec un Spin Target égal à 1 pour y écrire.
Donc, dans notre boucle d’écriture actuelle, le MDF originel se verra être la cible de 2 écritures sur 3.
Une fois que nos fichiers NDF auront un Spin Target égal à 1, l’écriture se fera et un nouveau Spin Target sera calculé et ainsi de suite.
En résumé, pour avoir une répartition des nouvelles données vraiment équitable, il aurait fallu ajouter des fichiers NDF avec une taille égale à l’espace disponible dans le MDF originel.
Si notre base de données avait fait 1To, nous aurions donc ajouté 3 fichiers de 160Go, et dans le cas présent 3 fichiers de 1.6Go.
Il reste tout de même un dernier détail à régler !
Comment répartir les anciennes données actuellement dans le MDF originel ?
Nous allons voir les différentes possibilités dans l’article suivant…