SMO et C# : Reorg Rebuild Index

Dans tous les plans de maintenance des bases de données, on retrouve une étape importante : le reorg rebuild index. En effet, au fil des écritures dans une base de données, un index se fragmente et devient donc de moins en moins performant. Il convient donc de les réorganiser ou de les reconstruire régulièrement. On peut bien évidemment réaliser cette tache en T-SQL ou via un plan de maintenance standard, mais on peut également l’exécuter depuis une application .net grâce à SMO.

Prérequis

Pour les débutants, je vous propose de consulter cet article pour apprendre à créer un projet C# + SMO.

Connexion à l’instance

Je passe rapidement la première étape qui sert à se connecter à une instance SQL Server. Pour la démo, je me connecte à mon instance locale.

string _instance = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instance);
sc.Connect();
smo.Server myServer = new smo.Server(sc);

Reorg Rebuild Index simple en C# avec SMO

Voici comment recréer tous les indexs de toutes les bases de données :

string _instance = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instance);
sc.Connect();
smo.Server myServer = new smo.Server(sc);

foreach (smo.Database myDb in myServer.Databases)
{
   // Pour chaque base Accessible
   // (ne pas tenter si la base est offline ou
   // en cours de restauration par exemple)
   if (myDb.IsAccessible)
   {
      foreach (smo.Table myTable in myDb.Tables)
      {
         foreach (smo.Index myIndex in myTable.Indexes)
         {
            // Pour chaque index de chaque table dans
            // la base de données, je reconstruis l'index
            myIndex.Rebuild();
            //myIndex.Reorganize(); ou Reorganize pour juste défragmenter l'index
         }
      }
   }
}

C’est relativement simple, tous les indexs sont rebuildés mais pour plus d’efficacité, allons plus loin.

Choisir en fonction de la fragmentation

Ces opérations de rebuild ou reorg d’index sont couteuses. Et il ne sert à rien de réorganiser un index d’une table qui n’a pas été modifiée. Il convient donc de vérifier son taux de fragmentation et de décider ensuite quelle est la meilleure action. En principe, si l’index est fragmenté à + de 30% alors je reconstruis l’index. S’il est fragmenté entre 10 et 30%, je le réorganise. Sinon je ne fais rien.

Grâce à la méthode EnumFragmentation, nous pouvons consulter le taux de fragmentation d’un index. C’est équivalent à l’écran dans SSMS ci-dessous.

smo fragmentation

int _seuilReorg = 10;
int _seuilRebuild = 30;
foreach (smo.Database myDb in myServer.Databases)
{
   if (myDb.IsAccessible)
   {
      foreach (smo.Table myTable in myDb.Tables)
      {
         foreach (smo.Index myIndex in myTable.Indexes)
         {
            DataTable dtFragmentation = myIndex.EnumFragmentation(smo.FragmentationOption.Detailed);
            // Consultation de la fragmentation
            // Fast : Calcule les statistiques uniquement sur les pages de niveau parent (- couteux mais + approximatif)
            // sampled : Calcule les statistiques sur des échantillons de données
            // Detailed : Calcule les statistiques sur 100 % des données (+ couteux)
            if (dtFragmentation.Rows.Count > 0)
            {
               double _fragmentation = 0;
               double.TryParse(dtFragmentation.Rows[0]["AverageFragmentation"].ToString(), out _fragmentation);
               if (_fragmentation >= _seuilRebuild) // Supérieur à 30%, je reconstuis
               {
                  myIndex.Rebuild();
               }
               if (_fragmentation >= _seuilReorg && _fragmentation < _seuilRebuild) // entre 10 et 30% je réorganise
               {
                  myIndex.Reorganize();
               }
            }
         }
      }
   }
}

Opérations ONLINE

Par défaut, les indexs sont reconstruits OFFLINE.

SMO Index OfflineSi vous avez de l’activité à ce moment là, les utilisateurs risquent fort d’être lockés. Il faut donc utiliser les opérations ONLINE (uniquement valable en Enterprise Edition). Il suffit d’activer cette propriété :

myIndex.OnlineIndexOperation = true;

smo index online

Notes

Il est possible qu’après avoir mis à jour tous les indexs, vous remarquiez qu’un index est toujours fragmenté. Ceci peut être normal s’il s’agit d’un index contenant moins de 8 pages.

Si vous avez plusieurs partitions, vous pouvez reconstruire partition par partition grâce à la surcharge des méthodes index.Rebuild et index.Reorganize. En effet, une des surcharges de cette méthode permet de passer en paramètre le numéro de la partition à défragmenter. Lien MSDN

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Time limit is exhausted. Please reload CAPTCHA.