Fragmentation

Et le Capacity Planning, bordel !

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>

image

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.

image

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 Sourire.

SQL Internals Viewer et SQL 2008 R2

Il existe un outil très intéressant sur CodePlex pour analyser visuellement la fragmentation interne de vos fichiers MDF et NDF.

Internals Viewer for SQL Server

image

Cet outil gratuit se présente sous la forme d’un addin à Management Studio (SSMS). Il détaille toutes les pages par index.

Sauf qu’entre SQL Server 2008 et 2008 R2, il y a eu des modifications dans les API entrainant une incompatibilité (breaking change en anglais). Si vous le téléchargez, vous aurez une erreur au démarrage (No such interface supported).

Pour le faire fonctionner (sans revenir à la version précédente de Management Studio), je vous invite à télécharger le code et supprimer ces quelques lignes :

 

/*
 * Legacy Code (SQL 2005 et 2008) 
 */
//IObjectExplorerEventProvider provider = ServiceCache.GetObjectExplorer().GetService(typeof(IObjectExplorerEventProvider)) as IObjectExplorerEventProvider; 
//provider.NodesRefreshed += new NodesChangedEventHandler(Provider_NodesRefreshed); 
//provider.NodesAdded += new NodesChangedEventHandler(Provider_NodesRefreshed); 
//provider.BufferedNodesAdded += new NodesChangedEventHandler(Provider_NodesRefreshed); 

 

Il vous faudra également mettre à jour quelques références (SmoEnum qui n’existe plus par exemple ou bien le chemin vers les assemblies si vous êtes en x64). Redéployez et le tour est joué.

Evidemment, ce n’est pas la solution la plus académique mais c’est pragmatique en attendant un vrai support des Addins dans SSMS.

 

Merci à Jonathan Kehayias (SQL 2008 R2 Breaks SSMS Addins) même si ça n’a pas tout résolu Sourire.

Merci à mes partenaires de tarot qui ont supporté ma séance de débogage pendant nos parties.

Fragmentation et plan de maintenance

Malheureusement, tout le monde ne le sait pas toujours mais une base de données se fragmente avec le temps. Je vous renvoie à un précédent post : http://blog.djeepy1.net/2008/08/13/indexation-gerer-la-fragmentation/ pour les détails.

Le billet du jour est lié aux plans de maintenance “automatique”. En effet, je vois souvent des SI dans lesquels l’administrateur ou l’équipe de développement sont fiers de m’annoncer qu’ils ont mis des plans de maintenance en place pour gérer la fragmentation.

C’est bien sauf que, si la base est conséquente, ces maintenances automatiques peuvent faire plus de tort que de bien.

En effet, le composant Rebuild Index Task, dans sa configuration par défaut reconstruit TOUS les index de TOUTES les tables.

image

Le problème est que si vous avez des tables importantes (plusieurs millions de lignes et plusieurs Go de données), reconstruire l’index CLUSTERED peut être une opération lourde :

  • Remplissage du LOG (si vous êtes en RECOVERY FULL)
  • Lock de la table (si vous êtes en Standard Edition)
  • Opération longue

D’autant plus que la tâche génère un script SQL par ordre alphabetique des index et non dans un ordre logique. On reconstruirait normalement les CLUSTERED puis les NON-CLUSTERED.

Bref, j’ai été confronté à des situations où cette tâche engendrait plus de problèmes qu’elle n’en résolvait. Par exemple, la tâche qui dure jusqu’au petit matin, se superposant à un autre plan de maintenance (le backup), dégradant les performances d’un job de synchro, etc.

La solution

La solution consiste à faire un job un peu plus complexe. L’idée est de se baser sur la vue système nous donnant les infos de fragmentation : sys.dm_db_index_physical_stats. Déjà, on peut filtrer sur les index les plus fragmentés (> 25/30% pour moi).

On croise ensuite avec les infos de l’index (type CLUSTERED ou non) et les infos de volumétrie (nombre de lignes) que l’on trouve respectivement dans sys.indexes et sys.partitions.

A partir de ces informations, je peux donc obtenir dynamiquement les instructions à jouer.

SELECT 
    Schema_Name(o.schema_id) as SchemaName,
    OBJECT_NAME(stat.object_id) as TableName,
    ix.name, stat.index_id, 
    avg_fragmentation_in_percent, ix.fill_factor, rows,
    'Alter Index ['+ ix.name
        + '] on [' + Schema_Name(o.schema_id) 
        + '].[' + OBJECT_NAME(stat.object_id) 
        + '] Rebuild' as Statement
FROM sys.dm_db_index_physical_stats(
    DB_ID('AdventureWorks'),
    NULL,
    NULL, NULL, NULL) stat
  inner join sys.indexes ix 
    on ix.index_id = stat.index_id
    and ix.object_id = stat.object_id
  inner join sys.partitions p
    on ix.index_id = p.index_id
    and ix.object_id = p.object_id
  inner join sys.objects o on stat.object_id = o.object_id
where avg_fragmentation_in_percent > 25
order by index_id, avg_fragmentation_in_percent desc

 

Je prends toutefois ces informations avec précaution. En effet, j’essaie d’avoir une idée de la durée de chaque reconstruction en fonction de la volumétrie. Ceci pour faire ma planification au mieux. Par exemple si en testant (eh oui, le test reste primordial) je m’aperçois que la reconstruction de tel index prends 35 minutes, je l’isole et le planifie à part, à un moment où cela ne va pas gêner les autres tâches.

Pour cela, il suffit de bien connaître sa base et ce qui s’y passe.

 

Epilogue

Malgré toute la pédagogie que nous pouvons faire lors de formation, consulting, projets, etc., les développeurs ne sont pas encore assez vigilants aux problématiques de base de données et pensent qu’un produit comme SQL Server se maintient tout seul à un haut niveau de performance. Je prends souvent la métophore avec l’automobile. Cela fait quelques siècles que ça existe et pourtant vous allez toujours au garage régulièrement ;-).

Evidemment, loin de moi l’idée d’entretenir le clivage DBA/développeurs. Mon métier de consultant me place bien souvent au milieu et je privilégie la pédagogie.

Donc la règle à retenir : SQL Server est un produit puissant mais complexe, faites-vous aider par des spécialistes.