Pour faire suite à un post sur développez concernant l’existence d’outils de scripting d’objets en dehors de ceux proposés par SSMS, j’ai décidé de regarder d’un peu plus près le sujet et d’en créer un (getMsDDLmt) pour voir ce que ça donne. Afin de suivre les explications du code, les sources sont disponibles sur SourceForge.
Je vais juste détailler un peu les points qui sortent de l’ordinaire dans le programme. Il s’appuie comme la plupart des outils de ce genre sur SMO et C#.
Comment générer le script d’un objet en SMO:
L’objet au sommet de la hiérarchie des objets SMO pour ce qui nous intéresse est Database:
Microsoft.SqlServer.Management.Smo.Database
|__ Users
|__ Schemas
|__ Role
|__ Table
|__ View
|__ StoredProcedure
|__ UserDefinedFunction
|__ etc...
Chaque sous-objet de la hiérarchie de Database possède une méthode script() qu’il suffit d’invoquer, et qui renvoie une StringCollection. Il suffit d’afficher chaque item de la collection pour obtenir le script de tous les objets. Par exemple pour sortir toutes les UDF:
Server Srv = new Server(".");
Database Db = Srv.Databases["AdventureWorks"];
UserDefinedFunctionCollection _func = Db.UserDefinedFunctions;
foreach (UserDefinedFunction _f in _func)
{
StringCollection _fscr = _f.Script();
foreach (string _line in _fscr)
Console.WriteLine(_line);
}
Console.WriteLine("GO");
Vous remarquerez qu’il n’y a pas explicitement de connexion, elle est réalisée dans le constructeur de Srv. Une fois que la base est instanciée, on récupère notre collection d’UDF, et pour chaque fonction on applique la fonction script(). Comme script() renvoie elle aussi une collection, on est encore obligé de boucler pour afficher chaque ligne.
Bien que relativement simple sur le papier, ça n’est pas sans poser de gros soucis dans la réalité:
– Il faut faire attention de filtrer les objets systèmes car il n’y a pas de distinction entre une vue système et une vue utilisateur dans la classe View, ou la classe StoredProcedure.
– La méthode Script() ne peut pas être appelée si l’objet a été encrypté (par exemple une fonction ou une procédure stockée).
– Dans un programme simple, la génération du DDL est sérialisée: elle n’est effectuée que par un seul thread.
– Ca pollue le plan cache avec des tonnes de plans ad hoc qui sont sans cesse recompilés.
Résultat, la génération du DDL par la méthode classique est affreusement lente:
- Compilation des plans ad hoc: c’est le le premier facteur en termes de temps de réponse. Une manière simple de le voir est de lancer la génération deux fois de suite. La seconde fois, le script complet sera généré beaucoup plus vite. Pour pallier ce problème, pas beaucoup de solutions: passer la base en PARAMETERIZATION = FORCED pour que l’optimiseur choisisse de paramétriser aussi les plans non éligibles par défaut (avec des jointures, etc…) peut être extrêmement efficace le temps de la génération (28 secondes en FORCED contre 1 minute 30 en SIMPLE pour sortir AdventureWorks sur mon laptop avec un seul thread), mais l’impact peut quand même être important sur une base de production, car l’option s’applique à toute la base. Fort heureusement elle est dynamique et peut être retirée rapidement à la fin de la génération.
- Le parcours de la liste des objets à scripter: parce que pour toutes les procédures stockées, fonctions, vues il faut vérifier si l’objet n’est pas un objet système (avec isSystemObject) et si l’objet n’est pas encrypté (avec isEncrypted). Il existe une astuce qui a été révélée par Bill Graziano pour accélérer un peu ce processus en pré-fetchant ces propriétés, mais globalement les gains restent très moyens. Le plus rapide pour générer la liste des objets à filtrer reste encore d’écrire une requête qui le ferait, au moins pour filtrer les objets système, et de placer le tout dans un DataTable ADO.NET. C’est la méthode qui a été retenue pour getMsDDLmt.
- La sérialisation du processus: par défaut, seul un thread ferait tout le travail, sans tirer parti des multiples cores que l’on trouve aujourd’hui même sur des portables. La solution est de créer un programme qui utilise de multiples threads pour générer le DDL des objets les plus couramment rencontrés (tables, fonctions, procédures stockées, vues, etc…) en parallèle.
Le programme getMsDDLmt:
L’objectif est d’extraire sur la console le DDL de tous les objets d’une base en particulier. L’utilisateur peut rediriger la sortie de la console ensuite dans un fichier. Il prend en argument un fichier de configuration au format XML très simple qui ressemble à ceci:
<?xml version='1.0'?>
<Host>
<Instance>.</Instance>
<Database>AdventureWorks</Database>
</Host>Le point représente l’instance locale, si vous avez une instance nommée il faudra renseigner son nom complet. L’authentification est supposée en mode intégrée, la raison pour laquelle on ne passe pas de user, de mot de passe, etc…
Un appel au programme s’effectue donc comme ceci:
DOS>getMsDDLmt -I myConfigFile.xml > maBase.sql
Les étapes critiques étant la constitution de la liste d’objets et la partie multithreading, je vais surtout m’attarder sur ces deux points principaux.
1ère étape: prise de l’environnement et des variables principales ( Main() ):
C’est le véritable début, une fois les paramètres récupérés depuis le fichier de config XML, il faut tenter d’initier la connexion à l’instance et à la base. Quelques tests basiques sont effectués pour trapper une instance qui n’existe pas ou qui ne serait pas démarrée, par exemple. Tout cela a lieu dans le point d’entrée principal du programme:
// Validation de la connexion et de la base try { Srv = new Server(_srv); if (Srv.Databases.Contains(_dbname)) { Db = Srv.Databases[_dbname]; } else { Console.WriteLine("La base " + _dbname + " n'a pas été trouvée"); return; } } catch(Exception exsql) { Console.WriteLine("Une erreur est survenue dans le module: "+exsql.Source+" : "+exsql.Message); Console.WriteLine("Vérifiez le nom de l'instance dans le fichier "+ _xmlcfg +" et vérifiez que celle-ci est bien démarrée"); return; } // Test de la version de SQL Server. Supporté 9+ if (Srv.Information.Version.Major < 9) { Console.WriteLine("Version de SQL Server supportée à partir de SQL Server 2005 et plus"); Usage(); return; } // Revision 1.1: l'appel à la méthode Script() sur un objet encrypté retourne une exception non gérée de type PropertyCannotBeRetrievedException. // On fetche donc la propriété dès le départ pour pouvoir la tester plus rapidement. Srv.SetDefaultInitFields(typeof(StoredProcedure), "IsEncrypted"); Srv.SetDefaultInitFields(typeof(View), "IsEncrypted"); Srv.SetDefaultInitFields(typeof(UserDefinedFunction), "IsEncrypted"); // Récupération de la liste des objet et alimentation du DataTable. _DT_ObjList = getUsrObjList();
La partie intéressante est celle qui concerne la Revision 1.1. C’est la mise en place du workaround de Bill Graziano, pour permettre de pré-fetcher la propriété isEncrypted pour les procédures, fonctions et vues, et d’y accéder plus rapidement ensuite. La dernière ligne affecte au DataTable global _DT_ObjList la liste des objets à scripter, en utilisant la fonction getUsrObjList().
2ième étape: lister les objets à scripter par les threads ( getUsrObjList() ):
La liste va constituer le panier dans lequel les worker threads vont venir piocher les objets à scripter. Elle est constituée d’une requête qui va placer l’object_id et le type d’objet dans un objet DataTable:
/* ================================================================================================================ * Peuplement de La liste des objets à scripter par le Main Thread. * * Le plus efficace revient à récupérer directement la liste des objets user dans un DataTable, * plutôt que de boucler sur chaque collection.On ne fait qu'un seul appel à la base pour tout * récupérer d'un coup.On ordonne par type asc pour que les vues (V) soient créées après les * tables (U) * */ static DataTable getUsrObjList() { String _sqlText = @"select object_id, type from " + _dbname + @".sys.objects where isnull(OBJECTPROPERTY(object_id,'IsMSShipped'),0) = 0 and type in ('P','FN','R','SN','U','V') and name not in ('sysdiagrams','sp_upgraddiagrams','sp_helpdiagrams', 'sp_helpdiagramdefinition','sp_creatediagram','sp_renamediagram', 'sp_alterdiagram','sp_dropdiagram','fn_diagramobjects') order by type asc"; DataTable _DT_ObjList = new DataTable(); String _cnStr = "server=" + _srv + ";trusted_connection=yes"; SqlConnection _cnx = new SqlConnection(_cnStr); SqlDataAdapter _sqlDataAdapt = new SqlDataAdapter(_sqlText, _cnx); try { _cnx.Open(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } try { _sqlDataAdapt.Fill(_DT_ObjList); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } return _DT_ObjList; }
On effectue déjà pas mal de filtres, en ne retenant pas les objets liés aux diagrammes dans SSMS, et les objets systèmes. Malheureusement il n’existe pas de propriété pour identifier les fonctions ou procédures qui auraient été encryptées, donc ont sera obligé de le faire au moment de la génération. On ne recherche que les objets de type Procédures stockées (P), UDF (FN), règles (R), synonymes (SN), tables (U) et vues (V). Les indexes, triggers, et contraintes check seront générées lors du passage sur la table donc ils ne sont pas mentionnés explicitement dans la liste.
Les autres objets de la base (la base elle-même, les users, roles, schémas, fonctions et schémas de partition, etc…) seront sortis par le thread principal car ils ne présentent pas de difficulté particulière. On choisit également d’extraire les contraintes de clés étrangères après la génération des tables pour éviter les problématiques d’ordonnancement des tables dans le script (pour éviter qu’une contrainte ne soit écrite par un thread avant que la table à laquelle elle fait référence ne soit présente).
Le tout est placé dans un objet DataTable _DT_ObjList, qui est retourné au Main(), pour être ensuite parcouru par chaque worker thread.
3ième étape: création des worker threads et début de script des objets ( initWorker() et scriptThis() ).
Avant de rentrer dans le code, il faut peut être expliquer un peu la notion de programmation avec plusieurs threads. L’avantage d’une telle philosophie est de pouvoir tirer pleinement parti des ressources de la machine. Le problème, c’est qu’à un moment ou à un autre, les threads vont entrer en concurrence sur l’aquisition d’une ressource partagée. Dans notre cas, il s’agit de _DT_ObjList, la liste des objets à scripter. Il n’est pas question que deux threads parcourent la liste en même temps et décident de scripter le même objet. Il va donc falloir trouver un moyen de les synchroniser.
Il existe de nombreuses méthodes pour synchroniser des threads entre eux. On distingue en gros deux grandes familles, les constructions utilisateur (comme les spinlocks ou les sections critiques en C++) et les kernel objects, qui sont des objets spécialement conçus pour ça, mais qui ont l’inconvénient de ne pas être manipulables directement dans l’espace d’adressage du programme utilisateur , et donc de nécessiter une transition vers le mode kernel à chaque fois qu’il sont invoqués. Toutefois, la méthode la plus simple pour synchroniser des threads en C# est d’utiliser la méthode lock() sur un kernel object, c’est donc celle que nous allons utiliser.
Concernant le nombre de threads à créer, j’ai décidé d’observer la règle suivante: on créé 1 worker thread pour deux CPU, pour ne pas saturer complètement la machine sur laquelle on effectue l’extraction. La valeur est arrondie au supérieur. Donc si on a 1 ou 2 CPU, on ne créera qu’un seul worker thread, si on a 3 ou 4 CPU, on en créera 2, 5 ou 6 on en créera 3, etc…
int _wtcnt = 1;
int _corecnt = System.Environment.ProcessorCount;
if (_corecnt == 1)
{
_wtcnt = 1;
}
else
{
if ((_corecnt % 2) != 0)
{
_wtcnt = (_corecnt / 2) + 1;
}
else
{
_wtcnt = (_corecnt / 2);
}
}Ensuite on dispatche les worker threads toutes les 1 ms. Le worker va passer par son point d’entrée ( initWorker() ) et immédiatement bloquer sur le kernel object. En appelant Join(), le main thread va attendre que les worker threads lui signalent la fin de la génération des objets, pour qu’il puisse continuer avec les autres objets résiduels (foreign keys, fonctions et schémas de partition, etc…) .
// Dispatch des wt toutes les 1 ms Thread[] _T_wt = new Thread[_wtcnt]; for (int i = 0; i < _wtcnt; i++) { _T_wt[i] = new Thread(new ThreadStart(initWorker)); _T_wt[i].Start(); Thread.Sleep(1); } // Le main Thread attend la fin de l'exécution de chaque wt for (int i = 0; i < _wtcnt; i++) { if (_T_wt[i] != null) { _T_wt[i].Join(); } }
Chaque worker thread va dans le même temps bloquer sur le kernel object, et le premier qui obtient la ressource peut commencer à dépiler le DataTable pendant que les autres worker threads attendent:
/* ================================================================================================================= * Point d'entrée des wt. * * Chaque wt bloque sur le kernel object et dépile une ligne du DataTable à chaque fois qu'il obtient la ressource. * Chaque wt opére tant qu'il reste des lignes dans le DataTable. * */ static void initWorker() { Server srv = new Server(_srv); Database _curdb = srv.Databases[_dbname]; DataRow _DR; int _objid = 0; string _type; while (_DT_ObjList.Rows.Count > 0) { // le wt courant bloque sur le kernel object pour obtenir la ligne courante lock (_DTlocker) { // On accède à chaque fois à la dernière ligne, on la stocke et on la supprime pour // qu'elle ne soit plus visible aux autres wt. _DR = _DT_ObjList.Rows[0]; _objid = ((int)_DR["object_id"]); _type = ((string)_DR["type"]).Trim(); _DR.Delete(); _DR.AcceptChanges(); } // execution du script de l'objet courant try { scriptThis(_objid, _type, _curdb); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } } }
Il accède toujours à la dernière ligne car à chaque fois qu’il en fetche une, il la supprime immédiatement après pour que le worker suivant puisse lire la ligne suivante (qui devient à son tour la première dans la liste). Une fois le kernel object relâché, il a stocké les informations nécessaires (object_id et type d’objet) et peut appeler scriptThis() pour scripter l’objet courant. Pendant ce temps, un autre worker a obtenu la ressource et peut dépiler son objet, etc… jusqu’à ce qu’il n’y ait plus d’éléments dans le DataTable. Allons voir maintenant ce que fait scriptThis():
/* ========================================================================================================= * Script de l'objet par appel de sa méthode SMO Script() * * Types remontés par le DataTable: * 'P','FN','R','SN', 'U','V' * Revision 1.1: Attention aux objets encryptés, la méthode Script() n'est pas invocable. */ static bool scriptThis(int _objid, string _type, Database _curdb) { switch (_type) { case "P": // PROCEDURES STOCKEES ----------------------------------------------------- StoredProcedure _sp = _curdb.StoredProcedures.ItemById(_objid); if (_sp.IsEncrypted == false) { StringCollection _spscr = _curdb.StoredProcedures.ItemById(_objid).Script(); foreach (string _line in _spscr) Console.WriteLine(_line); } Console.WriteLine("GO"); break; case "FN": // UDF ---------------------------------------------------------------------- UserDefinedFunction _f = _curdb.UserDefinedFunctions.ItemById(_objid); if (_f.IsEncrypted == false) { StringCollection _fscr = _f.Script(); foreach (string _line in _fscr) Console.WriteLine(_line); } Console.WriteLine("GO"); break; case "R": // RULES ------------------------------------------------------------------- StringCollection _ruscr = _curdb.Rules.ItemById(_objid).Script(); foreach (string _line in _ruscr) Console.WriteLine(_line); Console.WriteLine("GO"); break; case "SN": // SYNONYMS ----------------------------------------------------------------- StringCollection _synscr = _curdb.Synonyms.ItemById(_objid).Script(); foreach (string _line in _synscr) Console.WriteLine(_line); Console.WriteLine("GO"); break; case "U": // TABLES UTILISATEUR + CONTRAINTES + TRIGGERS + INDEXES ------------------- Table _t = _curdb.Tables.ItemById(_objid); StringCollection _tablescr = _curdb.Tables.ItemById(_objid).Script(); foreach (string _line in _tablescr) Console.WriteLine(_line); IndexCollection _indexes = _t.Indexes; foreach (Index _i in _indexes) { StringCollection _iscr = _i.Script(); foreach (string _line in _iscr) Console.WriteLine(_line); } CheckCollection _checks = _t.Checks; foreach (Check _c in _checks) { StringCollection _cscr = _c.Script(); foreach (string _line in _cscr) Console.WriteLine(_line); } TriggerCollection _triggers = _t.Triggers; foreach (Trigger _trg in _triggers) { StringCollection _trgscr = _trg.Script(); foreach (string _line in _trgscr) Console.WriteLine(_line); } // Pour les contraintes par défaut c'est un peu différent, il faut // passer par chaque colonne de la table, et faire attention si la // colonne ne contient pas de contrainte par défaut ( != null) foreach (Column _col in _t.Columns) { if (_col.DefaultConstraint != null) { StringCollection _def = _col.DefaultConstraint.Script(); foreach (string _line in _def) Console.WriteLine(_line); } } Console.WriteLine("GO"); break; case "V": // VUES UTILISATEUR ----------------------------------------------------- View _v = _curdb.Views.ItemById(_objid); if (_v.IsEncrypted == false) { StringCollection _vscr = _v.Script(); foreach (string _line in _vscr) Console.WriteLine(_line); } Console.WriteLine("GO"); break; default: Console.WriteLine("Objet {0} non supporté.", _type); break; } return true; }
L’objet est récupéré en passant son object_id à la méthode ItemById() à chaque fois. Et en fonction du type, on applique la méthode correspondante avec le principe expliqué au début de cet article. Lorsque le thread a terminé la génération, il retourne bloquer à nouveau sur le kernel object pour lire la ligne suivante. On remarquera le filtrage avec isEncrypted pour les procédures, les fonctions et les vues.
Conclusion:
Au final, le gain de passer en multithread est de plus de 30% rien que sur mon laptop avec 2 threads au lieu d’un seul. En plus en activant PARAMETERIZATION = FORCED, le temps de génération pour AdventureWorks tombe de 1 minute 30 à 20 secondes. A la prochaine !
David B.
Continuez votre lecture sur le blog :
- Scripting et SMO (suite): scripter les objets directement en T-SQL (David Baffaleuf) [SQL Server]
- Regénérer le DDL des indexes FULL TEXT (David Baffaleuf) [SQL Server]
- Mythe: SQL Server associe un thread à chaque connexion (David Baffaleuf) [SQL Server]
- Question bête: Faut-il créer des plans de maintenance avec Reconstruction ET Réorganisation ? (Benjamin VESAN) [SQL Server]
- Production SQL Server: Réorganisation des objets (Benjamin VESAN) [SQL Server]