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 !

SMO et C# : sauvegarder vos bases de données

Pour sauvegarder les bases SQL Server, on utilise généralement un plan de maintenance ou un script T-SQL mais on peut également réaliser cette opération en C# (ou powershell) avec SMO.

Dans un précédent article, j’expliquais comment créer un projet SMO dans Visual Studio. Pour les sauvegardes, Il faut ajouter la DLL Microsoft.SqlServer.SmoExtended.

La logique du script ci-dessous est relativement simple. Je me connecte à une instance SQL Server et pour chaque base de données, j’effectue une sauvegarde FULL.

static void Main(string[] args)
{
   string _instance = "localhost";
   string _repertoireSauvegarde = @"C:temptest";

   string _horodatage = DateTime.Now.ToString("yyyyMMdd_hhmmss");
   smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instance);
   sc.Connect();
   smo.Server myServer = new smo.Server(sc);
   foreach (smo.Database myDb in myServer.Databases)
   {
      if (myDb.Name != "tempdb")
      {
         smo.Backup myBackup = new smo.Backup();
         myBackup.Database = myDb.Name;

         // Définit le type de sauvegarde à effectuer  (base ou log)
         myBackup.Action = smo.BackupActionType.Database;

         // Sauvegarde FULL = false, Sauvegarde DIFF = true
         myBackup.Incremental = false;

         // Activation de la compression de la sauvegarde
         myBackup.CompressionOption = smo.BackupCompressionOptions.Default;

         // Ajout du device. Ici il s'agit d'un fichier mais on pourrait envisager une sauvegarde sur bande
         myBackup.Devices.AddDevice(_repertoireSauvegarde + myDb.Name + "_" + _horodatage + ".bak", smo.DeviceType.File);
         try
         {
            myBackup.SqlBackup(myServer);
            Console.WriteLine(myDb.Name + " sauvegardée à " + DateTime.Now.ToString());
         }
         catch (Exception ex)
         {
            Console.WriteLine(ex.Message);
         }
      }
   }
   sc.Disconnect();
}

SMO sauvegarder SQL Server

Pour une sauvegarde différentielle, il suffit d’activer la propriété Incremental à true. Pour une sauvegarde du journal de transaction, il faut modifier la propriété Action à smo.BackupActionType.Log.

Dans la bibliothèque SMO, on retrouve toutes les options disponibles dans l’interface de sauvegarde de SQL Server Management Studio. Pourquoi ? Parce que SSMS utilise lui aussi ces bibliothèques. En voici la preuve, si je tente une sauvegarde dans un répertoire dans lequel je n’ai aucun droit, lisez le message d’erreur que j’ai souligné.

preuve_ssms

Sur le site MSDN, vous trouverez une liste exhaustive de toutes les propriétés, méthodes et évènements concernant les sauvegardes. Bon codage !

Débuter avec SMO et C# : performances SetDefaultInitFields

Bonjour,

Dans l’article précédent, je vous ai montré comment ouvrir une connexion à une instance SQL Server avec SMO et C# puis à lister le nom des bases de données. Avant d’aller plus loin dans l’apprentissage de SMO, il y a une notion très importante à retenir pour avoir de bonnes performances : SetDefaultInitFields.

Si je lance une trace SQL lors de l’exécution du programme qui liste le nom des bases de données, je vais voir que 5 requêtes ont été exécutées. Les 4 premières sont propre à l’utilisation de SMO, il a besoin d’obtenir des informations sur l’instance et la base avant de générer sa requête. Celle qui nous intéresse est la dernière qui sert à lister le nom des bases de données :


SELECT
dtb.name AS [Name]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

SQL Profiler et SMO

Jusqu’ici, la requête générée est plutôt propre.

Lister la collation de chaque base de données

Je vais un peu modifier le code afin d’ajouter la collation de chaque base de données


string _instanceName = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instanceName);
sc.Connect();
smo.Server monServeur = new smo.Server(sc);
foreach (smo.Database maBase in monServeur.Databases)
{
    Console.WriteLine(maBase.Name + " : " + maBase.Collation);
}
sc.Disconnect();

SMO Affichage des bases et des collations

Analyse de la trace SQL Profiler

Voici le résultat de la trace SQL Profiler. Comme vous pouvez le constater, nous sommes loin d’un traitement ensembliste et efficace ! Il y a une seule requête pour récupérer le nom de toutes les bases de données mais ensuite une requête par base de données pour récupérer la collation !

SQL Profiler SMO sans SetDefaultInitFields

Comment rendre le traitement ensembliste ?

Il existe une méthode pour améliorer la génération de l’écriture de la requête. Il s’agit de SetDefaultInitFields. Cette méthode permet de préciser à SMO qu’il doit récupérer certaines ou toutes les propriétés en une seule étape.

Je vais maintenant modifier mon code afin d’utiliser cette méthode pour récupérer également la collation :


string _instanceName = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instanceName);
sc.Connect();
smo.Server monServeur = new smo.Server(sc);
monServeur.SetDefaultInitFields(typeof(smo.Database), "Collation");
foreach (smo.Database maBase in monServeur.Databases)
{
 Console.WriteLine(maBase.Name + " : " + maBase.Collation);
}
sc.Disconnect();

Et voici donc le résultat de la trace SQL Profiler.
SQL Profiler SMO avec SetDefaultInitFields

Il n’y a plus qu’une seule requête pour récupérer le nom et la collation :


SELECT
dtb.name AS [Name],
dtb.collation_name AS [Collation],
dtb.name AS [DatabaseName2]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

Conclusions

Je vous ai montré cette méthode pour les bases de données, mais elle est également valable pour tous les autres objets SMO (les tables, les procédures stockées, les vues, etc…). Imaginez les gains de performance sur une base de données à plusieurs milliers de procédures stockées !

Attention tout de même : si vous écrivez mal la ou les propriétés dans la méthode SetDefaultInitFields, vous n’aurez pas d’erreur à la compilation puisque c’est une stringCollection. L’erreur surviendrait à l’exécution.

Débuter avec SMO et C# pour piloter SQL Server

SMO est un ensemble de bibliothèques fournies par Microsoft pour automatiser le pilotage des bases de données SQL Server. Je m’en sers beaucoup dans le développement de Kankuru ou dans mon quotidien de DBA. Dans Kankuru par exemple, je les utilise pour afficher la liste des bases de données d’une instance ou la liste des tables d’une base de données.

Je trouve ces librairies très pratique bien que parfois plus lent que du code T-SQL. En effet, l’utilisation de SMO revient souvent à faire du traitement unitaire plutôt que du traitement ensembliste. Je reviendrai dans un futur article sur ces problèmes de performance.

Dans cet article, je vais vous montrer un exemple de base pour apprendre à vous connecter à une instance.

Prérequis

Bien que l’on puisse utiliser ces bibliothèques avec Powershell, pour ma part je préfère le C#. Je vais donc utiliser Visual Studio 2012 Express Edition for Windows Desktop.

Vous trouverez les dll SMO dans le répertoire d’installation de SQL Server (sur mon PC : C:Program FilesMicrosoft SQL Server110SDKAssemblies).

Préparation du projet

Dans Visual Studio, je vais créer une application console. Mais bien évidemment, vous pouvez l’utiliser dans une application WPF, Windows Forms ou ASP.Net.

Création du projet d'application console

Une fois le projet créé, il faut importer les DLL requises. A minima, vous aurez besoin de :

  • Microsoft.SqlServer.Smo.dll
  • Microsoft.SqlServer.ConnectionInfo.dll
  • Microsoft.SqlServer.Management.Sdk.Sfc.dll

Ajouter les références

Connexion à une instance avec SMO

Il faut tout d’abord commencer par importer les bibliothèques :

using smoCommon = Microsoft.SqlServer.Management.Common;
using smo = Microsoft.SqlServer.Management.Smo;

Ensuite, on peut se connecter à l’instance. Ci-dessous, un simple test de connexion puis déconnexion.

NB : Cet exemple utilise l’authentification Windows. Votre utilisateur Windows doit avoir des droits sur l’instance.

Si vous n’arrivez pas à exécuter ceci, pas la peine d’aller plus loin ! Vérifier le nom de votre instance (serveurinstance)

string _instanceName = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instanceName);
sc.Connect();
sc.Disconnect();

Afficher le nom des bases de données

Une fois connecté à l’instance, on peut facilement récupérer la liste de toutes les bases de données:


string _instanceName = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instanceName);
sc.Connect();
smo.Server monServeur = new smo.Server(sc);
foreach (smo.Database maBase in monServeur.Databases)
{
 Console.WriteLine(maBase.Name);
}
sc.Disconnect();

Afficher la liste des bases de données avec SMO

Dans un prochain article, je rentrerai plus dans le détail de l’utilisation de SMO. Je vous montrerai comment scripter des objets par exemple.