[SQL Server] Gestion de fichiers de données : contrôle de la croissance d’un fichier de données

Ce billet traite de l’importance du contrôle de la croissance d’un fichier de données dans le cadre de la gestion de fichiers de données.

Taille initiale, taille maximale

La finalité d’une base de données est de (continuer à) grossir. Appliquer une taille maximale trop limitée à ne pas dépasser à une base de données est la meilleure façon d’augmenter les risques de contention.

Et quand il y a contention, différentes opérations plus ou moins vitales ne peuvent plus s’exécuter. On peut notamment citer les opérations nécessitant de l’espace disque supplémentaire (de façon permanente ou temporaire) :

  • Les opérations de réindexation.
  • Les sauvegardes.
  • L’ajout ou mise-à-jour de données (quand cela fait dépasser la taille maximale de la base).
  • Etc…
Il est donc souvent préférable de soit accorder une taille maximale illimitée (i.e., la taille maximale de la base dépendra désormais exclusivement de l’espace disque disponible du ou des disques où sont stockés ses fichiers) à condition d’avoir les bons outils de monitoring ou, si pour diverses raisons une taille maximale est souhaitée, une valeur de taille maximale suffisamment grande pour limiter, au maximum, les risques de contention.

Il faut savoir que la meilleure façon de contrôler la taille de la base de données est non pas de spécifier une taille maximale à celle-ci, mais plutôt de contrôler son filegrowth (voir plus loin).

Une autre façon de gérer efficacement la taille d’un fichier de données est de réduire, dans la mesure du possible, les activités en I/O générées par l’allocation de nouveaux extents de données, en cas de demande d’espace supplémentaire par une base SQL Server, en faisant preuve de proactivité en attribuant une taille minimale (ou initiale) suffisamment grande. Déterminer à l’avance la taille d’une base de données est un exercice difficile mais nécessitera plusieurs facteurs à prendre en compte, à commencer par le profil d’une base de données (i.e., est-elle destinée, à court, moyen ou long-terme, à recevoir plusieurs centaines de Go de données ?…).

De ce fait, une belle technique de performance tuning serait idéalement de connaître à l’avance la taille estimée d’une base de données dans X mois, voire années, puis de lui donner une taille initiale (ou minimale) égale à cette taille estimée[1] de façon à ce qu’elle n’ait plus (ou peu) l’occasion de se voir allouer de nouveaux extents pendant un bon moment[2].

Choix du filegrowth

Estimer la taille d’une base de données est certes une gymnastique intellectuelle pas toujours facile à réaliser. Mais toutefois, une bonne pratique consiste à lui donner un paramètre de filegrowth (incrément ou croissance de fichier) ni trop large (réduction trop rapide de l’espace disque, sinon), ni trop petit (risque de fragmentation de disques, par exemple).

Par défaut, l’instance SQL Server spécifie une valeur de paramètre de filegrowth de 1 Mo pour le(s) fichier(s) de données (et 10% pour le fichier de logs), et cela pour chaque base de données nouvelle créée.

Néanmoins, au bout d’un certain temps, et afin de mieux contrôler la croissance d’une base de données, il devient tout de même impératif de modifier les paramètres de filegrowth d’une base de données. Et garder une unité de croissance plutôt en Mo, et non en % pour un meilleur contrôle de la taille des extents à allouer. En effet, toute valeur en pourcentage est susceptible de faire grossir beaucoup plus vite une base de données qu’en Mo.

Un filegrowth plus petit peut multiplier les risques de contention et de fragmentation de disques, à cause du trop grand nombre d’extents à allouer pour répondre aux problèmes de volumétrie. Ainsi, un fichier MDF de, disons, 20 Go, ayant un filegrowth de 1 Mo (valeur par défaut) peut rencontrer de gros problèmes de performances dans le cas où un nouveau bloc de données (chunk) de plus d’1 Mo doit régulièrement être inséré.

Idéalement, la valeur du filegrowth de chaque fichier de données doit être, dans la mesure du possible, au minimum entre 100 et 200 MB et celle du fichier de logs aux alentours de la moitié de la taille de filegrowth des fichiers de données de la base de données.
Quoiqu’il advienne, il est important de retenir une chose : le choix de la valeur idéale pour le paramètre de filegrowth d’un MDF/NDF (et d’un LDF) n’est guère sujet à une quelconque règle empirique vu que la décision dépend de différents facteurs (i.e., le disque accepterait-il une croissance rapide ou lente ? Quid du filegrowth des autres bases de données cohabitant sur le même serveur ? Croissance attendue à court, moyen et long-terme ?…). Il appartient à chacun de pouvoir s’assurer que la valeur soit suffisamment adéquate pour assurer une bonne croissance contrôlée d’une base de données.
On rappelle (voir plus loin) que le fait de pré-allouer une taille de base de données suffisamment grande de façon à réduire la fréquence des autogrowths est une bien meilleure gestion de la volumétrie d’une base de données.

Imaginons que l’on veuille créer une base de données MaBase avec un fichier de données unique appelé MaBase_Data.mdf pour taille de départ de 500 Mo et taille maximale illimitée, on peut procéder comme suit :

  • Dans le cas de la création d’une base de données MaBase :
USE master
GO
CREATE DATABASE MaBase ON
(NAME = MaBase_Data, FILENAME='D:\SQL Data\MaBase_Data.mdf',
 SIZE = 500,
 MAXSIZE =UNLIMITED,
 FILEGROWTH = 250)
LOG ON
(NAME = MaBase_Log,
 FILENAME='L:\SQL Logs\MaBase\MaBase_Log.ldf',
 SIZE = 250MB,
 MAXSIZE =UNLIMITED,
 FILEGROWTH = 125MB )
GO
  • Dans le cas de la modification du fichier MaBase_Data ayant déjà été créé :
USE master
GO
ALTER DATABASE MaBase
MODIFY FILE
(NAME = MaBase_Data,
 FILENAME='D:\SQL Data\MaBase_Data.mdf',
 SIZE = 500,
 MAXSIZE =UNLIMITED,
 FILEGROWTH = 250 )
GO
Notons que l’activation de l’autogrowth (option de croissance automatique par défaut de chaque base de données) est généralement une bonne pratique. Mais toutefois, il est important de ne pas sous-estimer certaines situations au cours desquelles une telle option peut s’avérer calamiteuse (i.e., multiplication de délais d’attente suite à la croissance automatique d’un gros lot d’extents, fragmentation physique du fichier concerné car valeur de filegrowth trop petite, etc…)[3]. Tout cela renvoie à la nécessité de bien définir la valeur de filegrowth de la base de données concernée selon la volumétrie des données à supporter…

Afin de limiter l’utilisation de l’autogrowth par SQL Server, il serait judicieux de fournir une taille de base de données suffisamment large (mais cohérente) aux fichiers de données (on parle, bien sûr, de la taille minimale/initiale).

Réduction de la taille d’un fichier de données (shrink)

Bien que cela puisse réduire la taille des bases de données concernées, son utilisation est à bannir (voir un prochain billet où une démonstration pratique sera faite), sauf si l’objectif de l’utilisation desdites bases est de les rendre malade.

Le shrink est une opération de compactage qui fait plus de mal que de bien. En effet, elle s’y prend en déplaçant, au fur et à mesure, et dans la mesure du possible, les pages de données les plus « grosses » (après les avoir identifiées grâce aux pages GAM), une par une, vers le début du fichier de données traité de la base concernée ce qui va libérer de l’espace à la fin dudit fichier, espace qui, ensuite, sera réduit. Et ainsi de suite, et ainsi de suite…

Cela aura des effets pervers sur la base de données puisque, en plus d’augmenter la consommation de ressources CPU, le shrink provoque diverses conséquences peu acceptables, à savoir :
  • L’opération de shrink génère beaucoup d’activités I/O à cause de la quantité de pages à déplacer au sein d’un fichier de données.
  • L’opération de shrink est une opération lente (tous les pointeurs d’une page/de lignes doivent être corrigés après déplacement, sachant que l’opération de shrink est une opération mono-thread, ce qui peut augmenter la consommation CPU).
  • Fragmentation interne (souvent très forte) des indexes du fait de la désorganisation des pages (on parle de fragmentation logique).
  • Fragmentation des disques et fichiers concernés (on parle de fragmentation physique).
  • Lenteurs de certaines opérations censées être rapides (démarrage de l’instance, transactions,…) non seulement à cause des problèmes de fragmentation, mais également à cause de la difficulté du moteur SQL à (ré)allouer rapidement de l’espace supplémentaire dans le cas où un grand nombre de nouvelles pages de données supplémentaires doit être stocké suite à des transactions post-shrink.
  • L’opération de shrink est une opération fully-logged. Cela signifie qu’elle est pleinement journalisée dans le fichier LDF de la base de données traitée.

Il faut tout de même noter que le shrink de fichiers de logs LDF est différent (et surtout moins problématique) de celui du fichier de données MDF/NDF du fait que la réduction de la taille des logs n’a aucune incidence sur les données. Il est donc de bon ton de ne pas confondre ces 2 types de shrink dont les impacts sur les performances d’une base de données ne sont pas les mêmes[4].

Il existe 2 types de commandes de shrink de fichiers de données : DBCC SHRINKDATABASE qui effectue un shrink séquentiel (i.e., une série séquencée de DBCC SHRINKFILE) de l’ensemble des fichiers de données d’une base de données et DBCC SHRINKFILE qui permet d’effectuer un shrink de fichier de données.

Si, pour diverses raisons exceptionnelles et excusables (disque saturé et forte quantité d’espace disponible au sein d’une base de données à libérer temporairement ou bien, suppression d’une petite quantité d’espace libérée par la troncation d’une table) le shrink de fichier de données est souhaité, il est possible de procéder vi le lancement de la requête T-SQL suivante[5] :

USE MaBase
GO
DBCC SHRINKFILE (MaBase_Data, 450)
GO

L’exemple ci-dessus réduit la taille du fichier de données MaBase_Data de la base MaBase à 450 Mo. De ce fait, si sa taille pré-shrink était de 500 Mo, alors 50 Mo d’espace sera supprimé dans la mesure du possible.

Remarquons que si l’option EMPTYFILE est utilisée à la place de la valeur de réduction (ici, 450), SQL Server déplacera le contenu du fichier cible vers d’autres fichiers du même filegroup avant de marquer ledit fichier comme candidat à la suppression (à noter que si la suppression d’un fichier de données est souhaitée, la commande « ALTER DATABASE MaBase REMOVE FILE MaBase_Data » est à utiliser).

Un autre réflexe plus « safe » serait :

  • Soit d’utiliser l’option TRUNCATEONLY, bien qu’elle soit susceptible de ne pas pleinement satisfaire aux besoins. En effet, cette option permet de libérer de l’espace libre situé en fin de fichier (sans garantie que la quantité d’espace libre en fin de fichier corresponde à la quantité d’espace de l’ensemble du fichier que l’on souhaite libérer)[6]:
USE MaBase
GO
DBCC SHRINKFILE (MaBase_Data, TRUNCATEONLY)
GO
  • Soit de déplacer le contenu d’un fichier à « shrinker » vers un nouveau fichier (en lui donnant une taille initiale au moins 1,5 fois supérieure au contenu des pages de données du fichier original) situé sur un autre filegroup, puis « shrinkez » le fichier original (ou supprimez-le s’il ne s’agit pas du fichier principal de la base de données traitée). Cette opération a l’avantage de ne pas générer de problèmes de fragmentations (et donc la nécessité d’effectuer une réindexation), mais toutefois, elle oblige d’avoir des ressources disques suffisantes.

    Le déplacement peut se faire en utilisant la syntaxe suivante :

USE MaBase
GO
CREATE CLUSTERED INDEX IDX_MaTable
WITH (DROP_EXISTING = ON)
ON New_Filegroup
GO

Notre exemple ci-dessus part du principe qu’il n’y a qu’une seule table, MaTable, hébergée au sein d’un fichier de données à « shrinker » (dans le cas échéant, effectuer autant de déplacements que de tables ou autres objets (vues, indexes non-clusters, etc…) à déplacer et appartenant au fichier de données à « shrinker ») et que cette table possède un index en cluster IDX_MaTable. L’index en cluster étant la table elle-même, le déplacement de celle-ci se fait donc en reconstruisant son index en cluster vers le fichier cible.

Dans le cas d’une table en heap, il faudra donc procéder comme suit :

ALTER TABLE MaTable DROP CONSTRAINT PK_MaTable WITH (MOVE TO New_Filegroup)
GO
ALTER TABLE MaTable ADD CONSTRAINT PK_MaTable PRIMARY KEY(MaTable_ID)
GO

Grosso-modo: suppression de la contrainte de clé primaire de la table avec MOVE TO vers le nouveau filegroup (ici, appelé New_Filegroup). Puis sitôt le déplacement fait, recréation de la contrainte de clé primaire au sein de la table déplacée.

Peu importe le type de shrink de base (ou de fichier) de données effectué (à part si l’option TRUNCATEONLY est utilisée, ou qu’il y a déplacement), il est important de procéder par la suite à une reconstruction d’indexes ou bien à une réorganisation d’indexes suivie d’une mise-à-jour des statistiques.

[1]On peut estimer « arbitrairement » la taille d’une base sur plusieurs années en effectuant la somme des tailles de l’ensemble des tables de la base multipliée par un coefficient d’incertitude (pouvant varier entre 1,5 et 2,5). [2]Vous pouvez lire l’article de Frédéric Brouard (aka SQLPro) : http://blog.developpez.com/sqlpro/p5859/ms-sql-server/fragmentation-physique-des-fichiers-et-t/ sur la fragmentation physique des fichiers et leurs performances d’accès.[3]Pour plus de détails : http://support.microsoft.com/kb/315512.

[4]Paul Randal (ancien manager de la SQL Team) combat un mythe persistant qui affirme que le shrink de bases de données n’affecte pas les performances sur le lien suivant: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(930)-data-file-shrink-does-not-affect-performance.aspx.

[5]Le nom du fichier de données peut être remplacé par son identifiant (identifiable via sp_helpfile, par exemple).

[6]Il est néanmoins possible, au préalable, de déplacer les pages allouées, situées en fin de fichier, vers les pages non-allouées du début du fichier afin de libérer de l’espace en fin de fichier : DBCC SHRINKFILE(MaBase_Data,NOTRUNCATE). Cette opération reste, tout de même, très impactante sur la santé d’un index du fait de la désorganisation de ses pages.

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