defrag

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

SMO Créer une base de données en C#

Beaucoup d’applications ont besoin de leur base de données pour fonctionner. Après l’installation d’un logiciel, l’application doit donc créer sa propre base. C’est le cas de Kankuru par exemple. Après son installation, j’utilise SMO pour créer la base de données vide (mais ensuite, contrairement à cet article, j’utilise des scripts SQL pour créer les objets). Dans ce tutoriel, je vais vous montrer comment on peut se passer du moindre script T-SQL pour créer une base de données et ses objets (une table) en utilisant 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

Oui pour créer une base de données, il faut avant tout être connecté à 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);

Création de la base de données

Une fois connecté à l’instance, je vais créer une base de données. A ce stade, ce ne sera qu’une enveloppe vide.

smo.Database myDb = new smo.Database(myServer, "TestSMO"); // TestSMO est le nom de la base que je vais créer
myDb.Collation = "French_CI_AS"; // Un exemple de collation
myDb.RecoveryModel = smo.RecoveryModel.Simple; // Il y a aussi BulkLogged et FULL
myDb.CompatibilityLevel = smo.CompatibilityLevel.Version110; // Pour SQL Server 2012

// FileGroup et Fichiers
smo.FileGroup myFileGroup = new smo.FileGroup(myDb, "PRIMARY", false); // True pour un FileStream
myDb.FileGroups.Add(myFileGroup);

// Fichier de données
smo.DataFile myDataFile = new smo.DataFile(myFileGroup, "TestSMO_data");
myDataFile.FileName = myServer.MasterDBPath + "\TestSMO_data.mdf"; // je crée le fichier dans le répertoire par défaut de l'instance mais vous pouvez mettre un chemin complet
myDataFile.Size = 500 * 1024.0; // 500Mo
myDataFile.GrowthType = smo.FileGrowthType.Percent; // None pour pas de croissance et KB si accroissement en taille fixe
myDataFile.Growth = 12; // 12%
myDataFile.IsPrimaryFile = true; // Uniquement sur le fichier primaire hein !
myFileGroup.Files.Add(myDataFile); // j'ajoute le fichier au bon filegroup

//Fichier de transactions
smo.LogFile myLogFile = new smo.LogFile(myDb, "TestSMO_log");
myLogFile.FileName = myServer.MasterDBLogPath + "\TestSMO_log.ldf"; // je crée le fichier dans le répertoire par défaut de l'instance mais vous pouvez mettre un chemin complet
myLogFile.Size = 50 * 1024.0; // 50Mo
myLogFile.GrowthType = smo.FileGrowthType.KB; // None pour pas de croissance et Percent si accroissement en pourcentage
myLogFile.Growth = 20 * 1024.0; // 20 Mo
myDb.LogFiles.Add(myLogFile); // j'ajoute le fichier à ma base (et non pas au filegroup comme le fichier de données)

myDb.Create(); // création de la base, jusqu'ici rien n'avait été créé sur l'instance.

smo_create_database

Création de la table

Une fois ma base créée, je peux y insérer des objets. Par exemple, je vais créer une table avec 2 colonnes dont une clef primaire auto-incrémentée.

// Déclaration de la table
smo.Table myTable = new smo.Table(myDb, "TableTest1", "dbo");
// Première colonne de la table. C'est une PK auto incrémentée
smo.Column myCol1 = new smo.Column(myTable, "id", smo.DataType.Int);
myCol1.Nullable = false;
myTable.Columns.Add(myCol1);

// Auto Incrément
myCol1.Identity = true;
myCol1.IdentityIncrement = 1;

// Définition de la PK
smo.Index myPk = new smo.Index(myTable, "IXPk");
myPk.IsClustered = true;
myPk.IsUnique = true;
myPk.IndexKeyType = smo.IndexKeyType.DriPrimaryKey;
myPk.IndexedColumns.Add(new smo.IndexedColumn(myPk, myCol1.Name));
myTable.Indexes.Add(myPk);

// Deuxième colonne de la table. C'est une chaine de caractère
smo.Column myCol2 = new smo.Column(myTable, "nom", smo.DataType.VarChar(200));
myCol2.Nullable = true;
myTable.Columns.Add(myCol2);

// Création de la table
myTable.Create();

smo Create Table

Script Complet

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using smoCommon = Microsoft.SqlServer.Management.Common;
using smo = Microsoft.SqlServer.Management.Smo;

namespace DemoCreateDatabase
{
class Program
{
static void Main(string[] args)
{
string _instance = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instance);
sc.Connect();
smo.Server myServer = new smo.Server(sc);

smo.Database myDb = new smo.Database(myServer, "TestSMO"); // TestSMO est le nom de la base que je vais créer
myDb.Collation = "French_CI_AS"; // Un exemple de collation
myDb.RecoveryModel = smo.RecoveryModel.Simple; // Il y a aussi BulkLogged et FULL
myDb.CompatibilityLevel = smo.CompatibilityLevel.Version110; // Pour SQL Server 2012

// FileGroup et Fichiers
smo.FileGroup myFileGroup = new smo.FileGroup(myDb, "PRIMARY", false); // True pour un FileStream
myDb.FileGroups.Add(myFileGroup);

// Fichier de données
smo.DataFile myDataFile = new smo.DataFile(myFileGroup, "TestSMO_data");
myDataFile.FileName = myServer.MasterDBPath + "\TestSMO_data.mdf"; // je crée le fichier dans le répertoire par défaut de l'instance mais vous pouvez mettre un chemin complet
myDataFile.Size = 500 * 1024.0; // 50Mo
myDataFile.GrowthType = smo.FileGrowthType.Percent; // None pour pas de croissance et KB si accroissement en taille fixe
myDataFile.Growth = 12; // 12%
myDataFile.IsPrimaryFile = true; // Uniquement sur le fichier primaire hein !
myFileGroup.Files.Add(myDataFile); // j'ajoute le fichier au bon filegroup

//Fichier de transactions
smo.LogFile myLogFile = new smo.LogFile(myDb, "TestSMO_log");
myLogFile.FileName = myServer.MasterDBLogPath + "\TestSMO_log.ldf"; // je crée le fichier dans le répertoire par défaut de l'instance mais vous pouvez mettre un chemin complet
myLogFile.Size = 50 * 1024.0; // 50Mo
myLogFile.GrowthType = smo.FileGrowthType.KB; // None pour pas de croissance et Percent si accroissement en pourcentage
myLogFile.Growth = 20 * 1024.0; // 20 Mo
myDb.LogFiles.Add(myLogFile); // j'ajoute le fichier à ma base (et non pas au filegroup comme le fichier de données)

myDb.Create(); // création de la base, jusqu'ici rien n'avait été créé sur l'instance.

// Déclaration de la table
smo.Table myTable = new smo.Table(myDb, "TableTest1", "dbo");
// Première colonne de la table. C'est une PK auto incrémentée
smo.Column myCol1 = new smo.Column(myTable, "id", smo.DataType.Int);
myCol1.Nullable = false;
myTable.Columns.Add(myCol1);

// Auto Incrément
myCol1.Identity = true;
myCol1.IdentityIncrement = 1;

// Définition de la PK
smo.Index myPk = new smo.Index(myTable, "IXPk");
myPk.IsClustered = true;
myPk.IsUnique = true;
myPk.IndexKeyType = smo.IndexKeyType.DriPrimaryKey;
myPk.IndexedColumns.Add(new smo.IndexedColumn(myPk, myCol1.Name));
myTable.Indexes.Add(myPk);

// Deuxième colonne de la table. C'est une chaine de caractère
smo.Column myCol2 = new smo.Column(myTable, "nom", smo.DataType.VarChar(200));
myCol2.Nullable = true;
myTable.Columns.Add(myCol2);

// Création de la table
myTable.Create();

sc.Disconnect();
}
}
}

Conclusion

Je n’ai abordé qu’une toute petite partie des fonctionnalités de SMO mais je pense qu’en terme de création d’objets, il n’y a pas de limite.
Bien évidemment, si vous utilisez SMO pour créer une base de données, pensez à tester les exceptions !