Sous-Titre : TempDB a explosé

Cas classique : aucune architecture physique ou questionnement sur l’espace disque n’a été fait à l’installation de SQL Server. En production, vos bases grossissent et pas forcément de la meilleure des façons.

Retenez ce mot : CAPACITY PLANNING (en fr-FR : prévision de volumétrie).

C’est une obligation sur une instance de production. Et pas besoin d’aller voir un marabout pour trouver les bons chiffres, souvent, des calculs simples permettent d’obtenir une bonne vision

Ex :

   2M de lignes par semaine
x 1ko la ligne (index et FillFactor pris en compte) 
= 104Go par an.

Il faut ajouter la place pour les backups et autres joyeusetés. Donc à la louche, tous les 1er janvier, allouez directement 250Go (nom de dieu!)

Je m’emporte car ce n’est pas le but de l’article… Admettons que vous soyez DBA ceinture blanche et que vous ne le saviez pas. Après un script bien touffu, vous vous apercevez qu’une de vos bases a grossi anormalement, il va bien falloir réparer.

Mais réparer quoi ? Après tout “c’est fait, c’est fait” comme on dit… Le problème est la fragmentation de votre MDF, NDF, LDF (rayez la mention inutile) sur le disque. Etude de cas avec TempDB (elle aussi fait partie de votre plan de Capacity Planning)

Ma base TempDB est passé de 3Mo (taille par défaut à l’installation de SQL Server) à plus de 2Go lors d’un batch. On peut voir la fragmentation sur disque avec l’outil CONTIG de SysInternals.

CONTIG -a –v <fichier>

Attention, je ne parle pas de fragmentation d’index, je parle là du fichier saucissonné sur le disque.

Mais sans utiliser cet outil, on peut deviner le résultat en regardant le rapport d’utilisation du disque dans SSMS. Ci-dessous, on voit les nombreux évènements d’allocations de place sur le disque.

Pour défragmenter le fichier, 2 solutions :

1. Utiliser Contig.exe (sans le –a) pour réduire le nombre de fragment
2. Recréer un FILE ou un FILEGROUP et vider l’ancien

Je ne détaillerai pas le première solution. C’est la plus simple et celle que je préconise. Mais pour le sport, on va détailler la seconde.

Création d’un fichier dans le même FILEGROUP :

• on crée un nouveau fichier dans le FILEGROUP (de la bonne taille cette fois-ci) histoire d’avoir une allocation d’un bloc

ALTER DATABASE [TEMP]
ADD FILE (  
    NAME = N'F2_FILEGROUP1', FILENAME = N'<chemin>',
    SIZE = 204800KB,
    FILEGROWTH = 102400KB)
TO FILEGROUP [FG1]

• on réduit le fichier fragmenté en le vidant complètement grâce à l’option EMPTYFILE

DBCC SHRINKFILE (N'F1_FILEGROUP1' , EMPTYFILE)

• on supprime le fichier

ALTER DATABASE [TEMP] REMOVE FILE [F1_FILEGROUP1]

Attention, cela ne marche pas sur les fichiers de LOG ni du PRIMARY FILEGROUP (pour cela il faudra faire une opération offline – ie. avoir une coupure de service)

Création d’un nouveau FILEGROUP :

Pour le changement de FILEGROUP, il suffit de déplacer les index en les recréant en précisant le FILEGROUP.

CREATE CLUSTERED INDEX [IX] ON [Table]
( [Col1] ASC )
WITH (DROP_EXISTING = ON) ON [NEW_FILEGROUP]

Evidemment, on évite toutes ces opérations de maintenance (qui peuvent être très couteuses) si on a anticipé tout cela.

Un jour je prendrais le temps de faire un article de fond sur le Capacity Planning (fr-FR: prévision de volumétrie)

Pour conclure, je citerai une de mes citations favorites d’un certain Leonard De Vinci : “Ne pas prévoir, c’est déjà gémir”

PS : débat métaphysique, TempDB est-il féminin ou masculin ? Moi je dirais que c’est un garçon et pourtant c’est une base de données .