[SQL Server] Gestion de fichiers de données : création et gestion de filegroups et de fichiers multiples

Ce billet traite de quelques éléments de gestion de fichiers de données relatifs aux filegroups et aux fichiers multiples. Des concepts théories seront notamment abordés afin de mieux comprendre leur fonctionnement et utilité.

Notion de filegroups

Les filegroups sont des groupes de fichiers contenant des fichiers de données d’une base de données. Il existe 2 types de filegroups : PRIMARY (groupe de fichier primaire créé automatiquement par SQL Server à chaque création d’une base de données) et USER-DEFINED (groupe de fichier(s) secondaire(s) définit par l’utilisateur).

Notons bien que :

  • Toutes les pages systèmes d’une base de données sont allouées dans le filegroup primaire.
  • Un fichier de données ne peut pas appartenir à plusieurs filegroups.
  • Chaque base de données ne possède qu’un seul filegroup par défaut à la fois. Si, lors de la création d’une base de données, aucun filegroup par défaut n’est spécifié, alors SQL Server choisit PRIMARY comme filegroup par défaut.
  • Toutes les pages d’un objet (table, index,…) appartenant à un filegroup spécifique seront allouées dans ce dernier.
  • Un filegroup ne concerne pas de fichiers de journal de transactions.

Fonctionnement de l’allocation d’extents au sein d’un filegroup par le moteur SQL

Contrairement à des idées reçues, les fichiers de données issus d’un même filegroup ne sont pas remplis en même temps. En fait, SQL Server travaille en utilisant une approche basée sur le remplissage proportionnel (proportional fill). Ainsi, si, par exemple, il est amené à insérer des données au sein d’une table concernée par un filegroup constitué de plusieurs fichiers de données, SQL Server répartit lesdites données dans les différents fichiers du filegroup traité de façon à ce que lesdits fichiers puissent arriver à saturation à peu près en même temps.

Pour mieux étayer cette logique, supposons que l’on possède 2 fichiers de données F1 et F2 ayant respectivement 100 et 200 Mo d’espace restants. Si :

  • Des données de 300 Mo doivent être insérées : les 2 fichiers fourniront tous leurs extents libres (100 et 200 Mo respectivement).
  • Des données de 100 Mo doivent être insérées: le fichier F2 fournira 100 Mo de façon à ce que F1 et F2 ait sensiblement le même niveau de saturation.

En cas de saturation: via l’utilisation d’un algorithme de type round-robin (« A chacun son tour »), chaque fichier se voit allouer, un par un (et donc, à la suite) des extents en fonction de la valeur de croissance (filegrowth).

A noter tout de même que :

  • Cette situation n’intervient que quand tous les fichiers d’un filegroup à traiter sont pleins.
  • En admettant qu’il y ait N fichiers (dans l’ordre): F1, F2,.., FN, SQL Server remplit d’abord le fichier F1, puis si celui-ci, lors de l’insertion de données, (re)devient plein, il alloue de l’espace au fichier suivant F2, puis si celui-ci, à son tour, (re)devient plein, il alloue de l’espace au fichier suivant F3, et ainsi de suite.

Autre exemple parlant : admettons qu’il y ait 2 fichiers de données F1 et F2 pleins au sein d’un filegroup, avec filegrowth égal à 200 Mo pour chacun:

  • Si 200 Mo de données doivent être insérées, 200 Mo d’extents seront alloués à F1 et les 200 Mo de nouvelles données intégralement insérées dans ce fichier (*);
  • Si 300 Mo de données doivent être insérées, 200 Mo d’extents seront alloués à F1, puis à F2, puis les 200 Mo de données seront réparties au sein des 2 fichiers de façon à ce que leur espace volumétrique restant soit approximativement identique. Cela signifie, ici, que 150 Mo seront allouées à chaque fichier ce qui laissera 50 Mo de disponible pour chacun.
Au vu de la manière dont SQL Server alloue des extents aux fichiers de données d’un filegroup, il est recommandé de donner la même valeur de filegrowth pour chaque fichier concerné.

Principaux avantages et inconvénients de l’utilisation de filegroups et de fichiers multiples

La création de filegroups et/ou de fichiers multiples peut notamment se justifier dans le cas où les performances en I/O ne sont pas toujours au rendez-vous.

Ainsi, afin d’avoir des performances optimum en I/O, il est possible de créer plusieurs filegroups constitués, chacun :

  • De fichiers dédiés aux indexes.
  • De fichiers dédiés aux tables en lecture seule.
  • De fichiers dédiés aux tables en lecture/écriture.
  • Etc…

En plaçant chaque filegroup dans un LUN dédié, on assure l’amélioration des performances en I/O. En effet, avoir des filegroups sur des LUNs séparés permet d’isoler les activités I/O dédiées de façon à pouvoir éviter, pour chaque filegroup, des conflits en I/O avec d’autres activités spécifiques à d’autres filegroup.

Par exemple, en plaçant un filegroup contenant une table volumineuse dans une partition dédiée, on évite que les grosses opérations associées à ladite table impactent significativement les autres tables moins volumineuses placées sur d’autres partitions. De plus, si, par exemple, dans le filegroup de la table volumineuse il y a, supposons, 3 fichiers de données multiples, chaque fois que SQL Server traitera ladite table, il utilisera 3 threads parallèles afin d’accélérer les traitements, les données de la table étant accédées par 3 threads simultanément.

Cette approche permet également de se prémunir, dans la mesure du possible, de tout risque potentiel de contention PFS durant l’allocation d’extents (i.e., une base de données peut observer des problèmes de performances si un trop grand nombre d’extents, pour répondre aux demandes d’espace supplémentaire, doivent être alloués à un même fichier occupé par un autre thread).

L’autre avantage non-négligeable de l’utilisation de filegroups multiples est la possibilité d’assouplir la gestion de structures de données spécifiques. Par exemple :

  • Supposons que l’on possède une table très volumineuse mais que l’on ne souhaite pas sauvegarde son contenu durant une sauvegarde complète de base de données (cas d’une grosse table d’archives, par exemple), il est possible de l’isoler dans un filegroup spécifique, afin d’effectuer une sauvegarde partielle de la base de données en excluant le filegroup concerné.
  • Si une sauvegarde complète s’avère trop coûteuse en temps, et qu’il est en de même concernant l’utilisation d’une sauvegarde différentielle, il est possible de procéder, à des moments fixes de la semaine, à des sauvegardes de filegroups spécifiques. Imaginons qu’il existe 3 filegroups contenant chacune des tables trop volumineuses, on pourrait procéder à la sauvegarde du filegroup 1 le vendredi, du filegroup 2 le samedi, du filegroup 3 le dimanche, etc…
  • Si l’on souhaite ne permettre à une table particulière de n’être accédée qu’en lecture seule, il est possible de la placer dans un filegroup spécifique accessible qu’en mode READ ONLY.
  • Si une table spécifique vient de subir une corruption de pages ou des suppressions de lignes accidentelles, la restauration d’une version précédente de son filegroup spécifique, sans impact sur les autres tables, peut assurer un retour-arrière particulièrement souple.
  • Réduction du RTO (Recovery Time Objective, ou temps qu’une base de données doit avoir pour redevenir opérationnelle) en cas de crash. Ainsi, on peut procéder à la restauration des données (situées sur un ou plusieurs filegroups spécifiques) les plus importantes et les rendre accessibles aux utilisateurs, et procéder par la suite à la restauration des autres données moins importantes sans impacter les accès. Cette technique de restauration partielle permet de travailler plus rapidement sur une base de données alors que celle-ci est en cours de restauration. Elle concerne l’édition Enterprise (ou Developer) de SQL Server, et la version 2005 au minimum.
Remarquons tout de même qu’il convient de trouver un juste milieu concernant le nombre de (fichiers de) filegroups à créer. En effet, un trop grand nombre de (fichiers de) filegroups peut paradoxalement engendrer des problèmes de performances en I/O du fait de risques potentiels de la présence d’un grand nombre de threads parallèles en attente.
De plus, admettons qu’une base de données doit faire face à un très (trop) grand nombre d’insertions au sein d’un filegroup contenant trop de fichiers, et comme SQL Server remplit les fichiers de données de façon proportionnelle via une approche round-robin, cela peut potentiellement prendre beaucoup de temps.
Il est donc recommandé d’utiliser entre 0,25 et 1 fichier de données par CPU par filegroups ce qui constitue un bon compromis[1]. Et en outre, un bon indicateur, au sein du perfmon (Performance Monitor) permet de s’assurer qu’il y ait assez de filegroups pour ne pas perturber le serveur : Avg. Disk Queue Length. Quand ce compteur est constamment supérieur à 3 pour n’importe quel disque concerné, il devient souhaitable de revoir le nombre de filegroups à la baisse, ou d’ajouter des disques robustes supplémentaires.
Autre détail important à ne pas négliger : les temps de récupération d’une base de données (lors du démarrage d’une base de données ou le lancement d’un point de contrôle ou même la modification d’un fichier de données). Un trop grand nombre de fichiers (ou de filegroups) peut potentiellement prolonger la durée de récupération de la base de données, surtout s’il y a un certain nombre de fichiers volumineux sachant que chaque fichier est traité séquentiellement durant la récupération d’une base de données.

Exemple pratique de création de filegroups

La création de filegroups est une opération très simple. Supposons que l’on veuille créer un filegroup
FG_BIGTAB (pour stocker la plus grosse table de la base de données), il suffira d’exécuter la requête suivante (dans la base MaBase préalablement créée) :

USE MaBase
GO
ALTER DATABASE MaBase ADD FILEGROUP FG_BIGTAB
GO

Et pour l’ajout d’un fichier de données MaBase_BigTab (qu’on peut bien sûr placer sur une partition dédiée) dans le nouveau filegroup :

ALTER DATABASE MaBase
ADD FILE (
     NAME = MaBase_BigTab,
     FILENAME='V:\SQL Data\MaBase\FG_BIGTAB\MaBase_BigTab.ndf',SIZE = 500MB,
     MAXSIZE =UNLIMITED,FILEGROWTH = 512MB
)
TO FILEGROUP FG_BIGTAB
GO

Si l’on souhaite déplacer notre table la plus volumineuse appelée BiggestTable, il suffira de coder la requête suivante qui va permettre de créer l’index en cluster de ladite table dans le filegroup
FG_BIGTAB (en effet, tout index en cluster représente la table dans laquelle il a été créé) :

USE MaBase
GO
CREATE CLUSTERED INDEX IDX_BiggestTableID ON dbo.BiggestTable(BiggestTableID) WITH (DROP_EXISTING = ON) ON FG_BIGTAB
GO

Si l’on souhaite migrer BiggestTable (que l’on suppose, cette fois-ci, non-indexée par un index en cluster) vers le nouveau filegroup, mais sans index en cluster, il est possible de d’abord créer ledit index sur le filegroup (ce qui, à ce moment-là, va migrer la table), puis de le supprimer.


Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s