Sauf que pour pouvoir ajouter la signature de 2 bits au début de chaque secteur, SQL Server doit poser un latch exclusif sur la page (PAGELATCH_EX) alors que le checksum ne pose qu’un latch d’update (PAGELATCH_UP) , qui est plus permissif notamment avec les latches en lecture (PAGELATCH_SH). Checksum n’a pas besoin d’un latch exclusif car le résultat de la somme de contrôle va dans l’entête de la page, pas dans les données. Malin !

Donc utiliser des checksums provoque moins d’attente au niveau des pages dans le buffer pool que TPD.

* cf article précédent pour plus de détails sur ce que font TPD et Checksums

Continuez votre lecture sur le blog :

• Règles d’installation de base (épisode 1) (David BAFFALEUF) [SQL Server]
• Création d’un Dataguard physique (Guillaume DEFENDINI) [Oracle]
• Mythe: SQL Server associe un thread à chaque connexion (David BAFFALEUF) [SQL Server]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Scripting et SMO (suite): scripter les objets directement en T-SQL (David BAFFALEUF) [SQL Server]

10 sections principales sur 5 jours avec une dizaine de TP pour couvrir l’essentiel de ce que doit savoir le DBA SQL Server en production. Nous avons ajouté des démos sur Resource Governor, le Change Data Capture, ainsi qu’une étude de cas de requête consommatrice. Nous travaillons actuellement sur le nouveau simulateur et les nouveaux scénarios que nous allons préparer. On mettra quelques captures d’ici quelques jours pour vous montrer à quoi ça va ressembler. Cette formation est assurée par Louis et moi-même en intra et en inter-entreprise. Vous pouvez nous contacter pour les détails techniques.

Pour les infos commerciales, planning, tarifs, etc… vous pouvez contacter la hotline commerciale au 0820 620 400 ou télécharger la plaquette ici.

Le découpage plan / jour sera le suivant:

Partie I: Architecture et généralités (Jour1)

Pour ceux qui utilisent déjà SQL Server ce sera une révision, et pour les non-initiés une découverte du produit. La première partie permet une remise à plat des connaissances nécessaires pour aller plus loin dans le cours.

I.1 Généralités:

- Description du package: différents outils (SSRS, SSIS, SSBIS, SSAS, etc…), des différents SKU (Enterprise, Standard, Express, etc…).
- Quelques rappels sur les notions de base: tables, vues, procédures stockées, triggers, curseurs, etc…
- Rappels sur les indexes: indexes cluster, non cluster, vues indexées, …
- Rappels sur la concurrence d’accès: mode d’isolation et verrouillage.

I.2: Architecture SQL Server:

- Processus, zones mémoire, bases systèmes, fichier ERRORLOG, connectivité, stockage, …

I.3: Architecture d’une base de données:

- Fichiers de données, groupes de fichiers, allocation, IN-ROW, OFF-ROW, LOB….
- Journal de transactions.
- Options de bases de données, création, modification, suppression….

Partie II: Bonnes pratiques d’installation.(Jour1)

Plutôt que de livrer un pas à pas tout bête, nous avons essayé de mettre en avant la préparation qui est à la base de tout succès.
- Discussion autour du thème machine virtuelle / physique, question des licences, définition et préparation des axes, comptes de services, collation, mode d’authentification, dimensionnement des bases…
- Installation automatisée et pas à pas, maintenance: changement d’option, arrêt démarrage, DAC…
-> TP.

Partie III: Maintien en conditions opérationnelles (Jour2 / Jour3)

C’est le cœur de la formation. On va passer en revue les tâches que le DBA doit mettre en place, et comment il peut les automatiser.

III.1: Backups / Restaurations:

- Modes de récupération des transactions, mediasets…
- Différents types de backups: complet, de journal de transaction, tail-log, différentiel.
- Différents types de restaurations: après un crash, sur une base différente, restauration de pages.
- Vérification des sauvegardes.
- Impact du Bulk Logged.
-> TP.

III.2: Contrôles de cohérence (DBCC CHECKDB):

- Pourquoi effectuer des contrôles de cohérence.
- Comment interpréter les résultats.
- Quoi faire en cas d’erreurs remontées par DBCC CHECKDB.

III.3: Fragmentation

- Origines de la fragmentation logique, impact sur les performances.
- Reconstruction des indexes.

III.4: Collecte des statistiques

- Contenu des statistiques.
- Collecte automatique vs collecte manuelle.

III.5: Gestion de la volumétrie:

- SHRINK sur les fichiers de données / journaux
- Conseils sur la réduction de fichiers.
-> TP.

III.6: Automatisation des tâches de maintenance .

- SQL Agent, plans de maintenance.
- Notification via Database Mail.
- Gestion des alertes.
- Collecter les données (Data Collector).
-> TP

Partie IV: Demandes de travaux: (Jour3)

- Import / export de données (BCP, BULK INSERT, SSIS) -> TP
-  Détacher /rattacher une base -> TP
- Database snapshot -> TP
- Serveurs liés.

Partie V: Outils d’administration: (Jour4)

- SQLCMD
- DAC
- Resource Governor -> DEMO

Partie V: Sécurité: (Jour4)

- Définition des risques.
- Accès, authentification, et ACLs -> TP
- Chiffrement, protection des données.
- Techniques d’audit: Policies, trace par défaut, rapports  SSMS, audit C2, Change Data Capture -> DEMO

Partie VI: Cas de corruptions de données (Jour4)

- Importance des sauvegardes, outils, recovery, etc…
- Cas de corruption sur une base système en SQL Server 2005/2008.
- Problème de recovery sur une base système en SQL Server 2005/2008.
- Problème sur un fichier de données de base utilisateur.
- Problème de recovery sur une base utilisateur.
-> TP

Partie VII: VLDB (Very Large Data Bases): (Jour4)

- Définition de la problématique des VLDB.
- Stockage des données étendues (OFF-ROW, LOB, FILESTREAM)
- Compression des données (page, ligne, colonnes sparse).
- Techniques de backup par groupes de fichiers.
- Partitionnement de tables.
- Glissement de partitions.

Partie VIII: Problématiques de migration (Jour4)

- Cycles de vie de SQL Server.
- Obsolescence des fonctionnalités.
- SQL Upgrade Advisor.
- Méthodologies de migration.
- Le problème DTS.

Partie IX: Performances: (Jour5)

IX.1: Utilisation des ressources par SQL Server:

- CPU: SQLOS, planification, paramètres appliqués à la CPU (max worker threads, affinités, priorité, etc…)
- Mémoire: 32 bits et 64 bits, AWE, page verrouillées en mémoire, Buffer POOL, PLAN CACHE,  etc…
- Disques: Scatter/Gather IOs, IO asynchrones, IO non bufferisées.
- Tempdb.
- Les antivirus et les bases de données.

IX.2: Optimisation des requêtes:

- Le pipeline de la compilation.
- Phases d’optimisation prises en charge par l’optimiseur.
- Recompilations.
- Lecture d’un plan d’exécution et principaux opérateurs.
- Types de jointures (NLJ, MJ, HJ)
- Couverture d’une requête.
- Etude de cas -> DEMO

IX.3: Les outils de mesure:

- Vues basiques de SSMS.
- Outils Windows: TM, Perfmon, ETW, etc…
- Compteurs perfmon types pour windows et SQL Server;
- SQL Trace et profiler -> TP
- DTA.
- DMVs.
- Outils divers: SQLDIAG, SQL Nexus, RML Utilities, Performance Dashboard SQL 2005, rapports SSMS.
- Extended events (XE).
- set statistics …

IX.4: Démarche de résolution d’un problème:

- Analyse du problème à chaud (mode ‘pompier’): outils, techniques et astuces…
- Analyse du problème à postériori avec et sans traces.

Partie X: Haute disponibilité et réplication: (Jour5)

X.1: Cluster SQL Server.

- Notions de cluster simple-instance / multi-instance.
- Problèmes liés aux clusters.

X.2: Log Shipping:

- Mise en place du log shipping.
- Problèmes liés au log shipping.

X.3: Database Mirroring:

- Différentes topologies.
- Mise en place du DB mirroring.
- Problèmes liés au Database Mirroring.
-> TP

X.4: Réplication:

- Topologies de réplication.
- Différents acteurs de la réplication transactionnelle.
- Maintenance de la réplication.
- Problèmes liés à la réplication.
-> TP

- Comparatif des différentes solutions de HA.

Partie XI: Q&A

A suivre la formation spéciale optimisation de requêtes. A+

David B.Continuez votre lecture sur le blog :

None Found

Les bouquins

En général les stagiaires rechignent toujours autant à se mettre à l’anglais, et c’est bien dommage car la plupart du temps la doc est en anglais et n’est souvent pas traduite du tout. Pour les bouquins en français sur SQL Server je ne saurais que trop recommander de lire des livres écrits par des français car les traductions sont parfois hasardeuses. Personnellement je ne connais que celui de Rudi Bruchez, c’est le seul qui rentre un peu dans les détails.

Mon exemplaire de Guru’s Guide for SQL Server Architecture and Internals de Ken Henderson est tellement noirci de notes et de commentaires qu’il n’est presque plus lisible. A chaque relecture je suis soufflé par le niveau de détail et la qualité des exemples qu’il apporte pour démontrer la façon dont le moteur est construit. Jusqu’ici, personne n’a réussi à arriver au quart du dixième de la cheville de ce qu’il montre. Toute personne désirant vraiment plonger dans le moteur devrait l’avoir sur son bureau.

SQL Server 2005 Practical Troubleshooting Guide a été rédigé sous la baguette de mister Henderson encore lui, par des membres de l’équipe de développement de SQL Server (Slava Oks, Santeri Voutilainen, Sameer Teijani, Cesar Galindo-Legaria,  etc…) ainsi que des membres du support (Bob Ward, Bart Duncan, Cindy Gross). Chacun parle de son domaine d’expertise, Santeri de la partie Waits, Slava de SQLOS et de la gestion de la mémoire, Sameer du scheduling, Cindy Gross du cluster, et Bob Ward nous livre deux chapitres magistraux sur le débogage, la lecture de minidumps, des tonnes d’informations qu’on ne trouve nulle part ailleurs sur le net. Là encore un must-read pour tous ceux qui souhaitent approfondir le sujet.

SQL Server 2008 Internals: Le dernier en date vraiment intéressant, rédigé par les stars du moment (Randal / Tripp / Delaney / Machanic / Cunningham). Là encore, chaque personne parle de ce qu’il connaît le mieux, Kim Tripp des indexes, Paul Randal de DBCC CHECKDB,  Conor Cunningham de l’optimiseur et Adam Machanic de la partie tracing. Kalen Delaney se charge d’envoyer tout le reste comme à son habitude avec une extrême clarté. C’est le moins profond des trois, mais on se fait quand même des bonnes migraines.

Au delà de ces trois là, je ne vois pas ce qu’il pourrait y avoir d’autre à raconter sur le moteur. Peut être les livres d’Allan Hirt sur le cluster SQL Server, d’Itzik Ben-Gan sur Transact-SQL et celui d’Hillary Cotter sur la réplication, il faudrait qu’il en réécrive un sur la version 2008 un jour.

Je n’ai pas encore lu le bouquin auquel ont participé John Kehayias, James Rowland Jones et Brent Ozar, si quelqu’un veut donner son avis dans les commentaires.

Les blogs:

Le problème des blogs, c’est qu’il y en a tellement qu’on ne peut pas tous les suivre, et que les plus intéressants sont aussi souvent les plus actifs.

Les blogs en français:

- Mikedavem.
- Frédéric Brouard.
- Christian Robert.
- Rudi Bruchez.
- SQLPac.

Les blogs US:

- PSS.
- SQL Server Support Team.
- SQL Server Storage Engine Team
- SQL Server Relationnal Engine team.
- SQLCLR Support team.
- Micheal Wories (SQLPS)
- SQLSKILLS.
- Craig Freedman.
- Liste étendue.

Sur Windows:

- Windows Internals, 5th edition, de Mark Russinovich et Dave Solomon.
- Le blog de Mark Russinovich.

Les videos:

- Les interviews des développeurs et architectes  Windows sur Channel 9:
- Dave Probert. (Kernel)
- Arun Kishan. (Scheduling & Dispatcher lock)
- Neal Christiansen (Filter Driver Manager)
- Molly Brown. (Cache Manager)
- Nar Ganapathy.  (IO Manager)
- Mark Russinovich. (Windows 7 kernel)
- Rob Short. (Vista Kernel)
- Landy Wang. (Memory Manager)

- Videos sur SQL Server :
- Interview de Dave Campbell sur Channel9 (SQL Azure).
- Interview de Jim Gray en 2005.
- Euan Garden fait la visite des bureaux du building  35  -  Part I – II - III -IV.
- Engagez-vous qu’y disaient.

Il existait aussi une tonne de vidéos de la TechEd 2008 sur Spotlight malheureusement elles ont toutes été retirées.

A+ David B.Continuez votre lecture sur le blog :

• Fragmentation sur des tables stockées en S-GAM (David BAFFALEUF) [SQL Server]
• Un trigger fait-il parti d’une transaction ? (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]
• Scripting et SMO (suite): scripter les objets directement en T-SQL (David BAFFALEUF) [SQL Server]
• Mythe: SQL Server associe un thread à chaque connexion (David BAFFALEUF) [SQL Server]

Soit une table ‘FILLIALE‘ avec la définition suivante:

FILLIALE {
        [ID_FILLIALE] [int] NOT NULL,
	[RAISOC] [varchar](50) NULL,
	[ADRESSE] [varchar](100) NULL,
	[CODEPOSTAL] [char](5) NULL,
	[VILLE] [varchar](50) NULL,
	[ID_GERANT] [int] NULL
}

Elle contient 240 lignes, une clé primaire PK_FILLIALE sur ID_FILLIALE. La mesure du taux de fragmentation de l’index cluster remonte 66,666666..7 % . L’intitulé du TP indique d’utiliser ALTER INDEX REBUILD pour la fragmentation  supérieure à 30%, ce serait donc le cas ici. Sauf qu’un ALTER INDEX REBUILD ne change rien à cette valeur:

select I.name, F.avg_fragmentation_in_percent from sys.indexes I
cross apply sys.dm_db_index_physical_stats(db_id(),object_id('FILLIALE'),
-1,NULL,DEFAULT) F
where I.object_id = object_id('FILLIALE')

name            avg_fragmentation_in_percent
--------------  ----------------------------
PK_FILLIALE     66,6666666666667

ALTER INDEX PK_FILLIALE ON FILLIALE REBUILD
GO

select I.name, F.avg_fragmentation_in_percent from sys.indexes I
cross apply sys.dm_db_index_physical_stats(db_id(),object_id('FILLIALE'),
-1,NULL,DEFAULT) F
where I.object_id = object_id('FILLIALE')

name            avg_fragmentation_in_percent
--------------  ----------------------------
PK_FILLIALE     66,6666666666667

D’où la question du stagiaire: “mais pourquoi si je lance 20 fois la reconstruction de l’index, la table est toujours fragmentée à 66,6% ?”

Représentation physique de la table FILLIALE:

Petite piqûre de rappel sur le stockage: les données sont stockées dans des pages de 8192 octets, elles mêmes regroupées dans des groupes de 8 pages que l’on appelle extents (on voit aussi allocation_units ou fragments). Deux types d’extents coexistent dans un fichier de données:

• Les extents ‘uniformes’: sont alloués pour les tables dont l’allocation dépasse 8 pages, donc 64+Kb. Ces pages sont gérées dans un plan d’allocation standard qu’on appelle GAM (pour Global Allocation Map).
• Les extents ‘mixtes’: sont alloués pour les tables dont l’allocation initiale est inférieure à 8 pages. Ces pages sont gérées dans un plan d’allocation spécial qu’on appelle S-GAM (pour Short-GAM ou Shared-GAM comme on veut, personne n’a jamais compris ce que S voulait vraiment dire, et chacun a sa version).

Si  on regarde de quelle manière est stockée notre table FILLIALE:

select I.name, F.index_id, F.index_depth, F.index_level, F.avg_fragmentation_in_percent,
F.fragment_count, F.avg_fragment_size_in_pages, F.page_count, record_count
from sys.indexes I
cross apply sys.dm_db_index_physical_stats(db_id(),object_id('FILLIALE'),-1,NULL,'DETAILED') F
where I.object_id = object_id('FILLIALE')

name          index_id    index_depth index_level avg_fragmentation_in_percent fragment_count       (...)
------------- ----------- ----------- ----------- ---------------------------- --------------------
PK_FILLIALE   1           2           0           66,6666666666667             3
PK_FILLIALE   1           2           1           0                            1                    

(...) avg_fragment_size_in_pages page_count           record_count
     -------------------------- -------------------- --------------------
     1                          3                    240
     1                          1                    3

On voit qu’elle est composée de 4 pages: une page avec un index_level à 1 qui désigne la root page de l’index cluster. Elle contient 3 lignes (record_count=3) parce qu’elle pointe vers chacune des pages du niveau inférieur. En dessous (index_level=0), il y a 3 pages qui constituent le niveau feuille de l’index cluster, donc les données. Elles contiennent les 240 lignes de notre table.

Si FILLIALE est composée de 4 pages de données + index, alors elle tombe invariablement dans la catégorie S-GAM. Les valeurs de fragment_count et avg_fragment_size_in_pages sur la première ligne nous le montrent: 3 pages dans trois fragments (on rappelle fragment = extent), donc chacune dans le sien. Histoire de bien se représenter visuellement la façon dont la table est stockée, on peut utiliser un dbcc ind:

dbcc ind('CAPDATA','FILLIALE',-1)

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          (...)
------- ----------- ------ ----------- ----------- ----------- --------------- --------------------
1       79          NULL   NULL        1189579276  1           1               72057594040221696    
1       127         1      79          1189579276  1           1               72057594040221696
1       94          1      79          1189579276  1           1               72057594040221696
1       256         1      79          1189579276  1           1               72057594040221696    
1       257         1      79          1189579276  1           1               72057594040221696    

(...) iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
      -------------------- -------- ---------- ----------- ----------- ----------- -----------
      In-row data          10       NULL       0           0           0           0
      In-row data          1        0          1           94          0           0
      In-row data          1        0          1           257         1           127
      In-row data          2        1          0           0           0           0
      In-row data          1        0          0           0           1           94

La page 1:79 est la page d’allocation de l’objet, on n’en parle donc pas. Pour le reste, on voit que la page 1:256 est la page racine de notre index (index_level=1), et pointe dans l’ordre vers 1:127 -> 1:94 -> 1:257 comme suit:

	/  |1:127|
	     |
|1:256| -- |1:94|
	     |
	\  |1:257|

En couleurs les 3 extents où se trouvent physiquement les pages: 1:94 dans un extent, 1:127 dans un autre, et 1:256 / 1:257 dans un troisième (elles se suivent et 1:256 est la première page du 33ième extent dans le fichier).

Calcul de la fragmentation:

Calculer la fragmentation d’un index revient à calculer le rapport entre le nombre de fragments qu’on a dû lire pour récupérer toutes les pages et le nombre de fragments qu’on aurait dû lire dans le cas idéal. Si on prend une table de 1680 pages, le cas idéal est celui où on ne lit que (1680/8)=210 fragments. Si les 1680 pages se trouvent sur plus de 210 fragments, alors on s’éloigne du 100% de densité et on augmente la valeur de la fragmentation.

Si je reprends le cas de la table FILLIALE, le cas idéal aurait été (3/8) ~= 1  fragment à lire seulement, or on doit en lire toujours 3. Cette information est visible dans DBCC SHOWCONTIG:

dbcc showcontig('FILLIALE','PK_FILLIALE')

DBCC SHOWCONTIG analyse la table 'FILLIALE'...
Table : 'FILLIALE' (1189579276) ; index ID : 1, base de données ID : 5
Analyse du niveau TABLE effectuée.
- Pages analysées................................: 3
- Extensions analysées..............................: 3
- Commutateurs d'extension..............................: 2
- Moyenne des pages par extension........................: 1.0
- Densité d'analyse [meilleure valeur:valeur réelle].......: 33.33% [1:3]
- Fragmentation d'analyse logique..................: 66.67%
- Fragmentation d'analyse d'extension...................: 66.67%
- Moyenne d'octets libres par page.....................: 1877.0
- Densité de page moyenne (complète).....................: 76.81%
Exécution de DBCC terminée. Si DBCC vous a adressé des messages d'erreur, contactez l'administrateur système.

[1:3] signifie qu’on aurait dû ne lire qu’un seul fragment, or on a en lu 3 (extensions analysées = 3), parce qu’on est sur une table où les pages sont dans trois extents. Donc 100-33.33333… = 66.6666…7%

Dommage que SHOWCONTIG doive disparaître à terme, l’affichage me paraissait plus lisible que la DMV qui le remplace. Qui me suit pour une pétition ??

A+ David B.

Continuez votre lecture sur le blog :

• Saines lectures (David BAFFALEUF) [SQL Server]
• Un trigger fait-il parti d’une transaction ? (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]
• Mythe: SQL Server associe un thread à chaque connexion (David BAFFALEUF) [SQL Server]
• Insert et Update en une seule fois avec MERGE (Guillaume DEFENDINI) [SQL Server]
• Modifier PAGE_VERIFY après une migration depuis SQL 2000 (David BAFFALEUF) [SQL Server]

$ df -k
Sys. de fich.        1K-blocs       Occupé     Disponible  Capacité     Monté sur
c:                    40957684     28061160    12896524    69%         /cygdrive/c
e:                   210170360     133011444   77158916    64%         /cygdrive/e
g:                    78153152     19149152    59004000    25%         /cygdrive/g
v:                    61440576     30980608    30459968    51%         /cygdrive/v

Il existe dans SQL Server une procédure stockée étendue xp_fixeddrives qui renvoie une approximation table-valuée du df, mais il y manque des informations importantes comme par exemple la taille du drive:

exec xp_fixeddrives

drive   Mo disponibles
-----   --------------
C	12594
E	75350
G	57621
V	29746

Il nous fallait donc réécrire notre propre xp_fixeddrives en le formattant comme un df (sans l’information du point de montage), sous la forme d’une fonction table valuée en SQLCLR *.

Le source de fn_fixeddrives.dll:

On récupère les propriétés des lecteurs assez simplement en utilisant la méthode GetDrives() de la classe DriveInfo, et on stocke le résultat dans un tableau, que l’on affiche ensuite ligne à ligne.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections;

public partial class cFixedDrives
{
    static DriveInfo[] _t_drvlist;

    [Microsoft.SqlServer.Server.SqlFunction
        (
            FillRowMethodName = "_f_fill",
            TableDefinition = "Drive nvarchar(3), SizeMb bigint, UsedMb bigint, "+
            "AvailableMb bigint, UsePct bigint"
        )
    ]

    // Main
    public static IEnumerable fn_fixeddrives()
    {
        _t_drvlist = DriveInfo.GetDrives();
        ArrayList _t_index = new ArrayList();
        int i = 0;

        foreach (DriveInfo _drv in _t_drvlist)
        {
            if (("Fixed" == _drv.DriveType.ToString()) && (_drv.IsReady))
            {
                _t_index.Add(i);
            }
            i++;
        }

        return _t_index;
    }

    // _fill
    private static void _f_fill(Object _v_Obj, out string _v_drvName,
                                out long _v_totalsizeMb, out long _v_usedsizeMb,
                                out long _v_freesizeMb, out long _v_pctused)
    {
        int _t_row = (int)_v_Obj;

        _v_drvName = _t_drvlist[_t_row].Name;
        _v_totalsizeMb = _t_drvlist[_t_row].TotalSize / 1048576;
        _v_usedsizeMb = (_t_drvlist[_t_row].TotalSize - _t_drvlist[_t_row].TotalFreeSpace)
                          / 1048576;
        _v_freesizeMb = _t_drvlist[_t_row].TotalFreeSpace / 1048576;
        _v_pctused = ((_t_drvlist[_t_row].TotalSize - _t_drvlist[_t_row].TotalFreeSpace)
                        * 100 / _t_drvlist[_t_row].TotalSize);
    }
};

Globalement, pour qu’une fonction SQLCLR puisse retourner une table, elle doit renvoyer un type IEnumerable. L’élément important est dans le rôle de la fonction de traitement des lignes ( _f_fill() ). Cette fonction va être appelée de manière itérative par la fonction principale fn_fixeddrives() à chaque fois qu’une ligne du tableau est fetchée, un peu comme dans un curseur, et va traiter et retourner la valeur de chaque colonne de la ligne. A la fin de l’itération, on obtient une table.

La seule vague astuce consiste à stocker dans un Arraylist les positions dans le DriveInfo[] où les lecteurs sont de type ‘Fixed‘, car les propriétés de taille ou d’espace libre ne sont pas renvoyées pour les autres types de lecteurs (par exemple les lecteurs de DVD), et plutôt que de passer à _f_fill() le tableau DriveInfo[] contenant la liste des drives, on passe juste un petit tableau d’entiers contenant les positions des disques fixes, et on s’en sert comme d’un index pour affecter les colonnes en retour. On utilise un ArrayList à la place d’un int[] parce qu’on ne sait pas au départ combien de lecteurs fixes on aura sur la machine.

A noter le test && (_drv.IsReady) qui permet d’éviter de provoquer une IOException si le drive n’est pas accessible.

Déclaration de l’assembly et de la fonction dans SQL Server:

La méthode GetDrives() est considérée comme UNSAFE, en raison du type d’appels qu’elle effectue vers le kernel pour récupérer les propriétés et les tailles des disques. Pour charger l’assembly dans SQL Server, il faut activer ‘clr enabled‘ et paramétrer la base pour accepter de charger des assemblies UNSAFE:

sp_configure 'clr enabled',1
GO
reconfigure
GP
alter database clrtest set trustworthy on
GO

Enfin, créer l’assembly et la fonction:

use clrtest
GO
CREATE ASSEMBLY fixeddrives from 'E:\CAPDATA\DEV\CLR\fn_fixeddrives\bin\Debug\fn_fixeddrives.dll' WITH PERMISSION_SET = UNSAFE
GO
CREATE FUNCTION fn_fixeddrives ()
RETURNS TABLE ("Drive" nvarchar(3), "Size(Mb)" bigint, "Used(Mb)" bigint, "Available(Mb)" bigint, "Use%" bigint)
AS EXTERNAL NAME fixeddrives.cFixedDrives.fn_fixeddrives
GO

Finalement:

select * from fn_fixeddrives()
GO

Drive   Size(Mb)  Used(Mb)  Available(Mb)    Use%
----    --------  --------  --------------   -----
C:\	39997	  27403	    12593	     68
E:\	205244	  129893    75350	     63
G:\	76321	  18700	    57621	     24
V:\	60000	  30254	    29746	     50

A+ David B.

* Note: A noter qu’un précédent existe et a été publié  par Andy Novick dans mssqltips.com, mais sa fonction ne filtre pas sur les lecteurs DVD ou CD, et elle est obligée de passer par des pseudo-types SQL (sqlint64) pour tenir compte des colonnes nullable.

Continuez votre lecture sur le blog :

• Principes d’une sauvegarde à chaud (David BAFFALEUF) [SQL Server]
• Retrouver une transaction en échec (David BAFFALEUF) [SQL Server]
• Insert et Update en une seule fois avec MERGE (Guillaume DEFENDINI) [SQL Server]

SQLCLR vs COM Automation:

Seulement voilà, on s’y casse un peu les dents car comme le dit Bob Beauchemin, SMO n’est pas supporté dans SQLCLR. Donc à priori, adieu la méthode Script(), et retour au clic-droit …

Cet article propose une alternative qui utilise  COM Automation (ou OLE Automation). C’est une fonctionnalité qui permet de charger un composant COM et d’utiliser ses méthodes grâce à des procédures stockées étendues sp_OA… C’est un peu l’ancêtre de SQLCLR, mais avec moins de restrictions, donc plus de risques à l’utilisation, c’est la raison pour laquelle cette technique est pas mal décriée.

D’ailleurs, en théorie utiliser du SMO avec OLE Automation n’est pas supporté par SQL Server, pour des raisons de sécurité du code notamment, parce que par défaut la DLL externe que l’on souhaite utiliser est chargée dans le process address space du moteur, ce qui augmente le risque d’écraser de la mémoire liée à SQL Server et de crasher le processus tout entier ou de provoquer des fuites mémoire. Il existe tout un tas de recommandations contre l’usage de COM Automation avec SQL Server, et  beaucoup de cas ont été ouverts au support à cause de ça. Il faut à chaque fois que c’est possible lui préférer l’utilisation de SQLCLR. Sauf que dans notre cas, SMO n’étant  pas utilisable dans du SQLCLR, il ne reste que peu d’options.

Une technique acceptable pour utiliser COM Automation sans mettre en péril l’instance est de charger le COM dans un processus séparé et dédié, pour que tout problème de pointeur NULL ou de  garbage collection dans le COM n’affecte pas SQL Server. Par exemple dans un web service accessible par Service Broker comme le propose Simon Sabin, ou dans un serveur COM+ comme nous allons le voir ici. Donc en résumé:

Ecrire le COM object:

COM est un truc énorme et je ne peux pas en parler dans un article. Il faudrait un bon millier de pages pour décrire ce que c’est dans les détails et ce que ça fait, mais disons que l’idée derrière COM est qu’un objet puisse publier ses classes et ses méthodes pour permettre à des applications tierces de pouvoir les utiliser sans pour autant divulguer son code source. Les objets COM existent le plus souvent sous la forme de librairies dynamiques (DLL). Le principe est utilisé massivement dans à peu près toutes les applications Windows MFC ou Console, y compris SQL Server.

Pour pouvoir être réutilisé, un objet COM doit posséder les caractéristiques suivantes:
- Ses classes doivent être publiques et implémenter une interface publique.
- Chaque classe doit posséder un constructeur par défaut (vide).
- La DLL doit être signée.
- La DLL doit être enregistrée sur la machine sur laquelle on va l’utiliser.

Par exemple pour scripter juste une table:

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace getDDL
{
     public interface IgetDDL    // <- 1
     {
         string ddlThis(string _srvname, string _dbname, int _objid);
     }
     public class cgetDDL : IgetDDL     // <- 2
     {
         string ddlstr;

         public cgetDDL() { }     // <- 3

         public string ddlThis(string _srvname, string _dbname, int _objid)     // <- 4
         {           
             Server Srv = new Server(_srvname);
             Database Db = new Database();
             Db = Srv.Databases[_dbname];
             StringCollection _tablescr = Db.Tables.ItemById(_objid).Script();

             foreach (string _line in _tablescr)
                 ddlstr += _line;
             ddlstr += "GO"+'\n';

             IndexCollection _indexes = _t.Indexes;
             foreach (Index _i in _indexes)
             {
                 StringCollection _iscr = _i.Script();
                 foreach (string _line in _iscr)
                     ddlstr += _line + '\n';
             }
             if (_indexes.Count != 0)
                 ddlstr += "GO" + '\n';

             CheckCollection _checks = _t.Checks;
             foreach (Check _c in _checks)
             {
                 StringCollection _cscr = _c.Script();
                 foreach (string _line in _cscr)
                     ddlstr += _line + '\n';
             }
             if (_checks.Count != 0)
                 ddlstr += "GO" + '\n';

             TriggerCollection _triggers = _t.Triggers;
             foreach (Trigger _trg in _triggers)
             {
                 StringCollection _trgscr = _trg.Script();
                 foreach (string _line in _trgscr)
                     ddlstr += _line + '\n';
             }
             if (_triggers.Count != 0)
                 ddlstr += "GO" + '\n';

             foreach (Column _col in _t.Columns)
             {
                 if (_col.DefaultConstraint != null)
                 {
                     StringCollection _def = _col.DefaultConstraint.Script();
                     foreach (string _line in _def)
                         ddlstr += _line + '\n';
                     ddlstr += "GO" + '\n';
                 }
            }

             return ddlstr;
         }
     }
}

–> 1: Déclaration de l’interface publique, qui va publier aux applications externes l’unique méthode de la classe CgetDDL :: ddlThis()
–> 2: Déclaration de la classe publique qui implémente l’interface.
–> 3: Déclaration de son constructeur par défaut.
–> 4: Déclaration de la méthode, qui prend en arguments le nom de l’instance, le nom de la base et l’object_id de la table à scripter. Pour le reste, cf l’article précédent.

Il faudra également modifier la propriété ComVisible de l’assembly pour permettre d’y accéder depuis l’extérieur. Dans assembly.cs:

[assembly: ComVisible(true)]

Enfin, il faut signer la DLL avec un nom fort que l’on pourra générer avec VS:

Chargement du COM dans un serveur COM+:

Une fois la DLL compilée, il va falloir l’enregistrer sur la machine et générer un fichier Type Library (*.tlb) que l’on va utiliser ensuite dans le serveur COM+. Pour enregistrer une dll et générer le tlb, on utilise l’utilitaire regasm qui est fournit avec tous les frameworks .NET (et qui doit se trouver qq part sous C:\Windows\Microsoft .NET\Framework\…):

DOS >regasm /codebase getDDL.dll /tlb:getDDL.tlb
Microsoft (R) .NET Framework Assembly Registration Utility 2.0.50727.3053
Copyright (C) Microsoft Corporation 1998-2004. Tous droits réservés.

Inscription des types réussie
L'assembly a été exporté vers 'C:\DBA\DEV\C#\getDDL\bin\Debug\getDDL.tlb' et la bibliothèque de types a été inscrite avec succès

A partir de là, on va créer un nouveau serveur COM+ en utilisant le service de composants comexp.msc: dans Services des composants -> Ordinateurs -> Poste de travail -> Applications COM+, on créé une nouvelle application serveur vide, démarrée sous un compte windows qui serait reconnu comme sysadmin sur l’instance (par exemple l’admin local, ou tout autre compte windows sysadmin). On dépose ensuite le fichier tlb dans les composants de la nouvelle application COM+, et on vérifie si la méthode est bien exposée:

Enfin on démarre le serveur COM+, et on vérifie qu’un nouveau processus hôte de dll (dllhost.exe) est bien démarré avec le bon compte. On va pouvoir s’intéresser à la partie T-SQL.

Ecriture de la procédure stockée:

Avant toute chose, pour pouvoir autoriser SQL Server à utiliser Ole Automation, il faut l’activer dans les paramètres de configuration:

sp_configure 'Ole Automation Procedures',1;
GO
reconfigure;
GO

Ensuite, on créé la procédure stockée qui va charger le COM et utiliser la méthode ddlThis():

create procedure usp_getDDL (@tablename varchar(128))
AS
BEGIN
     DECLARE @hr int, @obj int;
     DECLARE @Source nvarchar(255), @Desc nvarchar(255);
     DECLARE @DDLTEXT nvarchar(4000);
     DECLARE @dbname sysname, @objid int, @srvname sysname;

     select @srvname = @@servername
     select @dbname= db_name()
     select @objid = object_id(@tablename)
     if (@objid is NULL)
     BEGIN
         print 'Table '+@tablename+' non trouvee dans la base courante'
         RETURN
     END

     execute @hr = sp_OACreate 'getDDL.cgetDDL', @obj OUT, 4          --> context = 4 => COM stocké dans un dllhost.exe, pas dans sqlservr.exe
     if (@hr <> 0)
     BEGIN
         EXEC sp_OAGetErrorInfo @obj,
         @Source OUT,
         @Desc OUT;
         SELECT HR = convert(varbinary(4),@hr),
         Source=@Source,
         Description=@Desc;
         RETURN
     END

     execute @hr = sp_OAMethod @obj, 'ddlThis', @DDLTEXT OUT, @srvname, @dbname, @objid
     if (@hr <> 0)
     BEGIN
         EXEC sp_OAGetErrorInfo @obj,
         @Source OUT,
         @Desc OUT;
         SELECT HR = convert(varbinary(4),@hr),
         Source=@Source,
         Description=@Desc;
         RETURN
     END

     execute @hr = sp_OADestroy @obj

     select @DDLTEXT as 'DDL'
END

La procédure récupère le nom de la table, vérifie si celle-ci existe dans la base courante, et charge le COM dans le dllhost.exe avec sp_OACreate. Le nom du COM object est celui déclaré dans le serveur COM+ . Un autre paramètre d’extrême importance est le dernier passé à sp_OACreate  (4). Il s’agit du contexte de chargement du COM, dans ce cas 4 indique à SQL Server que le composant sera chargé en dehors de son process address space et ne peut avoir accès aux ressources mémoire dans sqlservr.exe (cf MSDN). C’est notre garde-fou et c’est indispensable, car par défaut, la ddl aurait été chargée dans sqlservr.exe et aurait pû causer des dégâts, il est donc très important de procéder de cette manière.

Ensuite la procédure appelle sp_OAMethod pour invoquer la méthode exposée par l’interface du COM, et récupère le DDL dans un nvarchar(4000). A la fin du processus, la référence vers l’objet est détruite avec sp_OADestroy.

A l’appel de la procédure:

DOS>sqlcmd -E
1> use CAPDATA
2> go
Le contexte de la base de données a changé ; il est maintenant 'CAPDATA'.
1> exec usp_getddl 'ANNONCE'
2> go

DDL
------------------------------------------------------------------------------------------------------

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[ANNONCE](
 [ID_ANNONCE] [int] NOT NULL,
 [ID_JOURNAL] [int] NOT NULL,
 [ID_PROPALOUER] [int] NOT NULL,
 [DATETIME_ANNONCE] [datetime] NOT NULL,
 [PRIX] [numeric](6, 2) NULL,
 [NB_PARUTION] [int] NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[ANNONCE] ADD  CONSTRAINT [PK_ANNONCE] PRIMARY KEY CLUSTERED
(
 [ID_ANNONCE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Conclusion:

COM ou Ole Automation est une technique pour faire communiquer SQL Server avec des composants externes. Elle  présente des risques élevés de corruption mémoire lorsqu’on l’utilise avec les paramètres de contexte par défaut, mais lorsqu’on manipule l’objet à travers son propre conteneur, les risques sont limités.

Dans la mesure où SMO n’est pas supporté avec SQLCLR, elle représente une alternative intéressante pour pouvoir interfacer SQL Server avec des fonctions qui ne sont pas disponibles ailleurs.

A+. David B.

Continuez votre lecture sur le blog :

• Modes de récupération et journal de transactions, épisode 2 (David BAFFALEUF) [SQL Server]
• Création d’un Dataguard physique (Guillaume DEFENDINI) [Oracle]
• Règles d’installation de base (épisode 2) (David BAFFALEUF) [SQL Server]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Saines lectures (David BAFFALEUF) [SQL Server]

select os_thread_id from sys.dm_os_threads T
inner join sys.dm_os_workers W on W.worker_address = T.worker_address
inner join sys.dm_os_tasks TA on TA.task_address = W.task_address
inner join sys.dm_exec_requests R on R.session_id = TA.session_id
where R.session_id = @@spid;
GO

Vous verrez qu’il finit par changer, et pourtant la connexion n’a pas été interrompue. L’explication réside dans le modèle de planification adopté par SQL Server depuis 1997 (la 7.0)

Tasks et Workers:

Dans SQL Server, la requête SQL ou l’initialisation de la connexion est une tâche (sys.dm_os_tasks). Le porteur de l’exécution de la tâche est le worker (sys.dm_os_workers). L’idée principale est la suivante: pour pouvoir supporter de nombreuses exécutions de batches SQL, il faut dissocier la tâche (task) de l’exécutant (worker), ce qui permet à un même worker d’exécuter plusieurs tâches successivement. Si vous ouvrez deux connexions sur un système idle, et que dans chaque session vous exécutez la requête ci-dessus, vous avez des chances de tomber sur le même os_thread_id, c’est la preuve que le worker est dissocié de la session ou de la tâche à exécuter. L’objectif est de maintenir un worker en exécution tant qu’il reste des choses à faire.

Organisation des workers:

Les workers sont regroupés par scheduler (UMS ou SQLOS) dans un pool, dont la taille maximale est définie telle que =  (max worker threads / nb of user schedulers). Au cours de l’exécution, les workers sont répartis dans différentes listes:
- worker-list:  les workers y sont stationnés au démarrage de SQL Server. A cet instant là, aucune tâche n’est arrivée au moteur d’exécution.
- runnable-list:  à chaque nouvelle tâche qui arrive, un worker est pris dans la worker-list, lié à la tâche et placé dans la runnable-list. Il est initialisé en attente infinie sur un ‘event‘ privé en utilisant la primitive WaitForSingleObject(). L’event est un objet du noyau windows qui sert comme un drapeau levé ou baissé à signaler à un worker qu’il peut poursuivre son exécution. N’importe quel worker peut prendre n’importe quelle tâche. La runnable-list est l’antichambre de l’exécution.
- waiter-list: lorsqu’une tâche doit attendre la fin d’une opération (la libération d’une ressource comme un latch par exemple), elle est placée en waiter-list. C’est à la charge du worker qui détient la ressource de lui indiquer que la ressource est libérée.
- I/O-list: toutes les tâches en attente de retour d’une I/O asynchrone sont chaînées dans cette liste. C’est le worker qui rend la main qui exécute les routines de completion d’I/Os et qui replace ces workers dans la runnable-list ensuite (cf section suivante).
- timer-list:  gère toutes les tâches en attente de l’expiration d’un timer (comme une trace SQL avec une durée d’exécution…). Comme pour les workers en I/O list, elles sont remises dans la runnable-list par le worker qui rend la main.

Cycle de l’exécution:

Lorsque c’est à son tour, le premier worker dans la runnable-list se voit signaler son event par le worker qui rend la main (= yield), et il peut passer en exécution. A son tour ensuite, il va exécuter sa tâche plus un certain nombre de sub-routines comme par exemple vérifier la liste des I/Os asynchrones en attente et lancer les routines de completion d’I/Os, vérifier la liste des workers en attente d’éveil en timer list, et vérifier l’expiration de certains timers, enfin signaler l’event du worker en tête de runnable-list et se replacer en worker-list en attendant une nouvelle tâche à exécuter.

Si l’instance est peu sollicitée, le même worker peut se retrouver à exécuter plusieurs batches différents à la suite en repassant par la worker-list, la runnable-list puis en exécution. C’est ce qui donne l’impression qu’une connexion  est toujours sur le même thread, alors que c’est juste parce que les autres workers n’ont rien à faire de particulier et restent en worker-list. Il n’y a qu’un seul cas où un nouveau worker est créé lorsqu’une nouvelle connexion arrive, c’est lorsque la worker-list est vide et que tous les workers sont répartis dans les différentes listes waiter, timer, I/O ou runnable. Tant que la valeur de max worker threads n’est pas atteinte, SQL Server créé un nouveau worker et le place avec la tâche de gérer la nouvelle connexion en runnable-list, mais encore une fois, le worker n’est pas lié à la connexion. Une fois revenu en worker-list, il prendra une tâche en provenance d’une autre connexion.

En résumé:

Ce qu’il faut se dire, c’est qu’une instance n’a pas besoin de 1000 threads pour servir 1000 connexions simultanées. De nombreuses connexions restent idle, et il est très rare de devoir manuellement augmenter la valeur de max worker threads même avec une très forte activité et de nombreux utilisateurs. Quelques workers suffisent, et grâce à son système de planification coopératif, SQL Server peut absorber la charge aisément.

En ce sens, la vision que renvoient les DMV sys.dm_exec_sessions et sys.dm_exec_connections est beaucoup plus consistante avec la réalité que ne l’était sysprocesses. Une session en AWAITING COMMAND n’avait pas de thread associé, c’était vaguement une instance de classe chaînée avec d’autres dans une liste.  A partir de SQL Server 2005, seule sys.dm_exec_sessions est liée aux tables de tasks, workers et threads. sys.dm_exec_connections reste à part, ce qui avait tendance à dérouter au début mais qui au final s’avère beaucoup plus logique.

A+ David B.

Continuez votre lecture sur le blog :

• Un trigger fait-il parti d’une transaction ? (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]
• Fragmentation sur des tables stockées en S-GAM (David BAFFALEUF) [SQL Server]
• Saines lectures (David BAFFALEUF) [SQL Server]
• Scripting et SMO (suite): scripter les objets directement en T-SQL (David BAFFALEUF) [SQL Server]
• Règles d’installation de base (épisode 1) (David BAFFALEUF) [SQL Server]

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 :

• Attention aux requêtes en double avec Windev et MySQL ! (Cédric PEINTRE) [MySQL]
• Abonnez-vous au blog de la CapData team ! (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]
• Intérêt de créer des indexes cluster uniques (David BAFFALEUF) [SQL Server]
• Que faire des “[Warning] Aborted connection” avec MySQL ? (Cédric PEINTRE) [MySQL]
• Planification dans Adaptive Server (David BAFFALEUF) [Sybase]

Un post pour vous démontrer que c’est toujours intéressant de lire les documentations et que parfois, les petites lignes en bas de page peuvent révéler des informations importantes.

J’étais récemment chez un client pour un audit de performance dans un environnement Windev / MySQL.
Je ne suis pas un grand spécialiste de Windev, bien qu’ayant déjà réalisé des audits sur ce type d’environnement, je n’avais jamais eu l’occasion d’aller aussi loin dans la compréhension du code applicatif.

Au hasard de l’analyse des requêtes lentes (via le log des requêtes lentes), je me suis aperçu qu’une requête apparaissait systématiquement deux fois consécutivement dans le log.

Après avoir identifié avec mon client le bout de code exécutant cette requête, nous avons travaillé avec le log général de MySQL afin d’essayer de comprendre pourquoi cette requête était lancée deux fois alors qu’elle n’apparaissait qu’une fois dans le code applicatif.

Le code utilisé est le suivant :

SD est une Source de Données
MaReq est une chaîne

####################################################################################

MaReq = "SELECT nom, prenom FROM client WHERE type =  1"

// Exécution de la requête

HExécuteRequêteSQL(SD,  MaConnexionMySQL,  hRequêteSansCorrection,  MaReq)

// Boucle sur le résultat

HLitPremier(SD)

TANTQUE  PAS HEnDehors(SD)

Trace(SD.Nom)

HLitSuivant(SD)

FIN

####################################################################################

Le verdict du log général est sans appel, la requête est bien exécutée deux fois !

Petite parenthèse, la fonction HExécuteRequêteSQL réalise systématiquement une modification de code SQL (Ajout d’un ORDER BY par exemple). Si vous souhaitez conserver votre code SQL original, n’oubliez pas d’ajouter le paramètre hRequêteSansCorrection comme fait ici.

Mais revenons à nos moutons, la mystérieuse requête en double…
Après plusieurs tests, nous avons réussi à identifier la cause de cette double exécution, il s’agissait de la fonction HLitPremier.

En effet, cette fonction exécute une nouvelle fois la requête, certainement pour intégrer d’éventuelles modifications de données entre le moment où l’on exécute la requête et le moment où l’on entre dans la boucle.
Simplement, si la requête est déjà coûteuse, elle l’est d’autant plus si elle est lancée deux fois et la seconde exécution n’est pas forcement nécessaire.
Cela dépend évidemment du contexte applicatif dans lequel vous vous trouvez. Dans le cas présent, ce n’était pas nécessaire.

Nous voici donc en train d’éplucher la documentation Windev (c’est là que vous comprenez mon introduction) à la recherche d’une information capitale dans notre cas : Comment désactiver cette double exécution ?

Et nous n’avons pas mis longtemps (enfin, un peu quand même…) à repérer de petites lignes en bas de la page de la documentation de l’accès natif MySQL pour Windev :

Optimisations des fonctions HyperFileSQL (Réf :  http://doc.pcsoft.fr/fr-FR/?acces-natif-mysql-pour-windev-webdev)

• Les Tables fichiers basées sur des requêtes sont optimisées : il est possible de trier la table en cliquant sur une de ses colonnes.
• Pour éviter de ré-exécuter plusieurs fois la même requête lors du parcours de son résultat, il est conseillé d’utiliser la constante hSansRafraichir (par exemple si un seul poste modifie les données).

Donc, pour résumer, si vous utilisez la fonction HLitPremier et que vous ne souhaitez pas que celle-ci ré-exécute la requête précédemment exécutée par la fonction HExécuteRequêteSQL, vous devez utiliser la fonction avec le paramètre suivant :

• HLitPremier(SD,  hSansRafraîchir)

Et effectivement, le log général ne mentionnait plus qu’une seule exécution après cette correction de code Windev !

J’espère que cette information vous sera utile pour vos devs Windev existants ou à venir. N’hésitez pas à nous faire un retour si vous aviez déjà rencontré ce problème ou pour tout retour d’expérience Windev / MySQL.

Un grand merci à toute l’équipe qui se reconnaitra et à bientôt sur le blog de capdata.
Cédric

Documentation sur le log des requêtes lentes : http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
Documentation sur le log général :  http://dev.mysql.com/doc/refman/5.1/en/query-log.html
Documentation Windev : http://doc.pcsoft.fr/fr-FR/Continuez votre lecture sur le blog :

• Règles d’installation de base (épisode 1) (David BAFFALEUF) [SQL Server]
• Un trigger fait-il parti d’une transaction ? (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]
• Mythe: SQL Server associe un thread à chaque connexion (David BAFFALEUF) [SQL Server]
• Abonnez-vous au blog de la CapData team ! (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]
• Intérêt de créer des indexes cluster uniques (David BAFFALEUF) [SQL Server]

Juste un petit post pour vous rappeler les différentes méthodes vous permettant de suivre l’activité débordante du blog de la capdata team :

• Accès standard via la page web : http://blog.capdata.fr
• Vous abonner au flux RSS du blog et être ainsi prévenu de l’arrivée des nouveaux articles : http://feeds.feedburner.com/CapDataTeamBlog
• Nous suivre sur Twitter pour retrouver l’actualité des bases de données : http://twitter.com/capdata_blog

Vous êtes de plus en plus nombreux à nous suivre sur ce blog technique autour des bases de données et nous vous en remercions.

J’en profite pour vous proposer quelques articles qui semblent avoir remportés un vif succès dernièrement :

• http://blog.capdata.fr/index.php/jeux-de-caracteres-unicode-et-base-de-donnees/
• http://blog.capdata.fr/index.php/installation-oracle-11gr2-64-bits-sur-red-hat-5-partie-1/
• http://blog.capdata.fr/index.php/modifier-la-collation-dune-base-sql-2005/
• http://blog.capdata.fr/index.php/mysql-et-les-tables-temporaires-internes/
• http://blog.capdata.fr/index.php/sybase-ase-direct-io-dsync-onoff-raw-device/

Encore une fois, n’hésitez pas à poster vos commentaires sur les articles, l’échange est la richesse du contenu.

A bientôt sur le blog de la capdata team !Continuez votre lecture sur le blog :

• Scruter les journaux d’évènements Windows avec LogParser (David BAFFALEUF) [SQL Server]
• Intérêt de créer des indexes cluster uniques (David BAFFALEUF) [SQL Server]
• Que faire des “[Warning] Aborted connection” avec MySQL ? (Cédric PEINTRE) [MySQL]
• Planification dans Adaptive Server (David BAFFALEUF) [Sybase]
• Modes de récupération et journal de transactions, épisode 1 (David BAFFALEUF) [SQL Server]

Bref un court post pour dire que je serai présent à la prochaine réunion du GUSS le 1er juillet. La réunion a lieu dans les nouveaux locaux de MS à Issy-les-Moulineaux, de 14h à 18h. Rudi Bruchez et Frédéric Brouard y feront une présentation chacun, ainsi qu’une personne de Quest.

Il n’est pas trop tard pour vous inscrire. On espère vous y voir aussi !

A+ David B.

Continuez votre lecture sur le blog :

• Scruter les journaux d’évènements Windows avec LogParser (David BAFFALEUF) [SQL Server]
• Retrouver une transaction en échec (David BAFFALEUF) [SQL Server]
• Attention aux requêtes en double avec Windev et MySQL ! (Cédric PEINTRE) [MySQL]
• Abonnez-vous au blog de la CapData team ! (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]
• Intérêt de créer des indexes cluster uniques (David BAFFALEUF) [SQL Server]

Contenu d’une trace SQLDIAG:

Une fois la trace terminée, Ctrl-C stoppe le collecteur et commence à écrire les fichiers résultats sous l’arborescence. On retrouve une arborescence telle que:

<NOM TRACE>
|___ log_*.trc
|___ MS2K8-WIN2008-1__sp_sqldiag_Shutdown.OUT
|___ MS2K8-WIN2008-1_MSINFO32.txt
|___ MS2K8-WIN2008-1_SQLDIAG__sp_trace_*.trc
|___ SQLDIAG.lbg
|___ internal
     |___ *.txt
     |___ *.OUT

• log*.trc: ces fichiers contiennent la trace par défaut de l’instance. Ils ont été copiés de leur répertoire origine (le plus souvent ~MSSQL\LOG). Cf note bas de page dans le premier article.

• MS2K8-WIN29008-1__sp_sqldiag_Shutdown.OUT: ce fichier est divisé en plusieurs parties. Il n’est généré qu’au moment du Crtrl-C pour ne pas rater d’évènements survenus pendant la trace. Il commence par contenir le contenu de chaque fichier ERRORLOG*, puis la configuration de l’instance, la liste des processus (sp_who), la liste des verrous (sp_lock), la liste des bases (sp_helpdb), des informations sur le binaire (xp_msver), donne la liste de toutes les procédures stockées étendues déclarées (sp_helpextendedproc) , et le contenu de sysprocesses, sys.dm_exec_sessions, ::fn_virtualservernodes() si sur cluster, sysdevices, sysdatabases, sys.master_files. Enfin, la liste des requêtes en cours d’exécution. Le reste , où chaque vue sys.dm_os* est récupérée, est plus à destination du support pour diagnostic. Celà leur donne une image précise de l’activité au niveau des schedulers (SQLOS), de la mémoire, des latches, des modules chargés dans l’espace d’adressage de sqlservr.exe, etc… C’est beaucoup plus pratique que de faire générer un dump ou un mini-dump au client qui rencontre un problème. D’ailleurs, il y aura certainement un article prochainement sur les vues sys.dm_os* principales, je ne m’étends donc pas dessus, car pour l’instant ce n’est pas ce qui nous intéresse.

• MS2K8-WIN29008-1_MSINFO32.txt: Contient le contenu au format texte de msinfo32. Toutes les informations systèmes sont contenues dans ce fichier, classées par section entre crochets ([]). Nombre d’entre elles sont sans intérêt (IRQ/DMA, périphériques, clavier, pilotes, etc…), il y cependant certaines sections qui valent le coup d’oeil: [System Summary] pour les informations systèmes courantes (OS, CPU, mémoire, etc…), [Drives] et [Disks] pour les disques, [Running Tasks] pour les processus en cours d’exécution, [Windows Error Reporting] pour les messages d’erreurs de l’eventvwr.

• MS2K8-WIN29008-1_SQLDIAG__sp_trace_*.trc: ces fichiers contiennent la trace SQL Trace que l’on a paramétrée dans le fichier XML.

• SQLDIAG.blg: ce fichier contient la trace système paramétrée dans le fichier XML.

• ~internal\: ce sous répertoire contient les logs de l’exécution de SQLDIAG. En fonction de la version utilisée, les fichiers OUT et txt peuvent se trouver au même niveau que les fichiers résultats.

Les deux axes qui vont nous intéresser sont principalement les fichiers de trace SQL et le fichier BLG. Les autres fichiers seront plus à utiliser à des fins de diagnostic.

Charger une trace SQL et un fichier BLG dans SQL Profiler:

Il est possible de charger un fichier de compteurs perfmon et une trace SQL dans SQL Profiler pour corrélation. Ce n’est possible toutefois que si les deux traces ont été générées ensembles, le plus souvent à l’aide d’un outil comme SQLDIAG ou PSSDIAG. Tout d’abord, ouvrir la trace SQL dans Profiler, puis File->Import Performance Data… Sélectionner le fichier BLG généré dans la même trace, puis les compteurs à charger (pour plus de lisibilité, on ne prendra que les compteurs disque dans notre cas), et importer la trace:

L’écran se divise en quatre parties. Les zones extrêmes représentent l’affichage classique de SQL Profiler: texte et statistiques des requêtes. La partie centrale présente les graphes associés aux compteurs du fichier BLG, et la partie inférieure la liste des compteurs avec les valeurs Min, Max, Average:

Il est possible de zoomer et de déplacer le curseur temporel dans la partie centrale, et Profiler va pointer sur les requêtes exécutées à l’instant sélectionné, et la valeur de ‘Selected‘ dans la partie inférieure est également mise à jour pour chaque compteur.

Maintenant il faut bien faire attention à ce que l’on lit ou ce que l’on interprète. Il faut bien se rendre compte que SQL Trace collecte les statistiques pour un évènement une fois qu’il s’est terminé, donc ce n’est pas parce qu’on va pointer sur un pic CPU ou un de temps de service disque  qu’on va tomber pile sur le problème. On va dire que ça facilite la tâche, ça permet de corréler des informations, mais ça ne fait pas le travail à votre place. Une requête peut commencer à faire des lectures physiques depuis le disque pendant une minute, puis faire d’autres opérations en mémoire (des tris, des agrégations) et se terminer un certain temps  après le pic. Un petit exemple d’une requête qui fait des accès physiques pendant 1 minute et qui se termine à la fin du pic. Si on ne regarde pas bien, on rate l’information:

RML Utilities:

RML Utilities est une petite application due, comme pas mal d’outils et d’avancées sur le moteur, à l’initiative de l’équipe Customer Support Services. La première version de RML s’appuyait sur deux binaires Ostress et ReadTrace principalement, elle a été refondue en 2007 pour incorporer une petite application d’affichage des traces, basée sur des reports RDL. Les versions x86 et x64 sont téléchargeables depuis la page d’accueil de RML. L’installation n’a rien de compliqué, donc on va passer directement à la phase d’intégration des traces.

Cette intégration se fait avec ReadTrace. Tout d’abord, on va créer une base pour stocker les données des traces:

create database RMLDB

Puis ReadTrace avec les options -I<chemin vers fichier trc initial> -S<instance> -d<Baserepository> (important de ne pas laisser d’espace entre le commutateur et le paramètre)

$ ./ReadTrace -IE:/SQLDIAG/TRACE3/SQL2008__sp_trace.trc -SMS2K8-Win2008-1 -E -dRMLDB

05/10/10 17:13:20.920 [0X000013B4] Output path was not specified; defaulting to C:\Users\capdata\AppData\Local\Temp\2\\output
05/10/10 17:13:21.000 [0X000013B4] Readtrace a SQL Server trace processing utility.
Version 9.01.0109 built for x86.
Copyright (c) Microsoft Corporation 1997-2008. All rights reserved.
05/10/10 17:13:21.002 [0X000013B4] Number of processors: 1
05/10/10 17:13:21.002 [0X000013B4]     Active proc mask: 0x00000001
05/10/10 17:13:21.008 [0X000013B4]         Architecture: 0
05/10/10 17:13:21.009 [0X000013B4]            Page size: 4096
05/10/10 17:13:21.009 [0X000013B4]         Highest node: 0
05/10/10 17:13:21.010 [0X000013B4]         Package mask: 0x00000001
05/10/10 17:13:21.011 [0X000013B4]         Processor(s): 0x00000001 Function units: Separated
05/10/10 17:13:21.011 [0X000013B4]           Processors: 0x00000001 assigned to Numa node: 0
05/10/10 17:13:21.012 [0X000013B4] -IE:/SQLDIAG/TRACE3/SQL2008__sp_trace.trc
05/10/10 17:13:21.012 [0X000013B4] -SMS2K8-Win2008-1
05/10/10 17:13:21.013 [0X000013B4] -E
05/10/10 17:13:21.013 [0X000013B4] -dRMLDB
05/10/10 17:13:21.014 [0X000013B4] Using language id (LCID): 1024 [French_France.1252] for character formatting with NLS: 0x00500100 and Defined: 0x00050100
05/10/10 17:13:21.015 [0X000013B4] Attempting to cleanup existing RML files from previous execution
05/10/10 17:13:21.016 [0X000013B4] Using extended RowsetFastload synchronization
05/10/10 17:13:21.017 [0X000013B4] Establishing initial database connection:
05/10/10 17:13:21.017 [0X000013B4] Server: MS2K8-Win2008-1
05/10/10 17:13:21.018 [0X000013B4] Database: RMLDB
05/10/10 17:13:21.018 [0X000013B4] Authentication: Windows
05/10/10 17:13:21.470 [0X000013B4] Using SQL Client version 10
05/10/10 17:13:21.471 [0X000013B4] Creating or clearing the performance database
05/10/10 17:13:22.916 [0X000013B4] Processing file: E:\SQLDIAG\TRACE3\SQL2008__sp_trace.trc (SQL 2005)
05/10/10 17:13:22.918 [0X000013B4] Validating core events exist
05/10/10 17:13:22.919 [0X000013B4] Validating necessary events exist for analysis
05/10/10 17:13:22.921 [0X000013B4] Validating necessary events exist for RML breakout
05/10/10 17:13:22.922 [0X000013B4] WARNING: Event [Server: Server Memory Change] missing from trace
05/10/10 17:13:22.923 [0X000013B4] WARNING REPLAY: The following trace events were not captured: [Sessions:ExistingConnection, Security Audit: Audit Login, Se
rity Audit: Audit Logout, Stored Procedures: RPC Output Parameter, Cursors:Unprepare, Cursors:CursorClose, Cursors:CursorPrepare, Cursors:CursorCreated, Curso
:CursorExecute, TSQL: Prepare SQL, TSQL: Unprepare SQL, Errors and Warnings:Attention, Transactions:SQLTransaction, Transactions:DTCTransaction, Transactions:
:Begin Tran starting and completed, Transactions:TM:Commit Tran starting and completed, Transactions:TM:Rollback Tran starting and completed, Transactions:TM:
ve Tran starting and completed, Transactions:TM:Promote Tran starting and completed, SQL:BatchStarting and SQL:BatchCompleted, RPC:Starting and RPC:Completed]
Review the help file to ensure that you have collected the appropriate set of events and columns for RML replay needs.  Lack of these events can lead to sever
replay problems.

Trace flag -T28 disables the check allowing RML output processing. *** Use with caution as the output and behavior could be unpredictable.
05/10/10 17:13:22.993 [0X000013B4] Events Read: 1000 Queued: 1000 Processed/sec: 0
05/10/10 17:13:23.026 [0X000013B4] Processing file: E:\SQLDIAG\TRACE3\SQL2008__sp_trace_1.trc (SQL 2005)
05/10/10 17:13:23.038 [0X000013B4] Events Read: 2000 Queued: 2000 Processed/sec: 0
05/10/10 17:13:23.070 [0X000013B4] Processing file: E:\SQLDIAG\TRACE3\SQL2008__sp_trace_2.trc (SQL 2005)
05/10/10 17:13:23.082 [0X000013B4] Events Read: 3000 Queued: 3000 Processed/sec: 0
05/10/10 17:13:23.261 [0X000013B4] Events Read: 4000 Queued: 4000 Processed/sec: 0
05/10/10 17:13:23.271 [0X000013B4] Processing file: E:\SQLDIAG\TRACE3\SQL2008__sp_trace_3.trc (SQL 2005)
05/10/10 17:13:23.284 [0X000013B4] Events Read: 5000 Queued: 5000 Processed/sec: 0
05/10/10 17:13:23.328 [0X000013B4] Processing file: E:\SQLDIAG\TRACE3\SQL2008__sp_trace_4.trc (SQL 2005)
05/10/10 17:13:23.342 [0X000013B4] Events Read: 6000 Queued: 6000 Processed/sec: 0
05/10/10 17:13:23.361 [0X000013B4] Processing file: E:\SQLDIAG\TRACE3\SQL2008__sp_trace_5.trc (SQL 2005)
05/10/10 17:13:23.366 [0X000013B4] Events Read: 7000 Queued: 7000 Processed/sec: 0
05/10/10 17:13:23.396 [0X000013B4] Processing file: E:\SQLDIAG\TRACE3\SQL2008__sp_trace_6.trc (SQL 2005)
05/10/10 17:13:23.406 [0X000013B4] Processing file: E:\SQLDIAG\TRACE3\SQL2008__sp_trace_7.trc (SQL 2005)
05/10/10 17:13:23.475 [0X000013B4] Events Read: 8000 Queued: 8000 Processed/sec: 0
05/10/10 17:13:23.574 [0X000013B4] Processing file: E:\SQLDIAG\TRACE3\SQL2008__sp_trace_8.trc (SQL 2005)
05/10/10 17:13:24.175 [0X000013B4] Processing file: E:\SQLDIAG\TRACE3\SQL2008__sp_trace_9.trc (SQL 2005)
05/10/10 17:13:24.313 [0X000013B4] Events Read: 9000 Queued: 8998 Processed/sec: 2
05/10/10 17:13:24.854 [0X000013B4] Processing file: E:\SQLDIAG\TRACE3\SQL2008__sp_trace_10.trc (SQL 2005)
05/10/10 17:13:24.905 [0X000013B4] Processing file: E:\SQLDIAG\TRACE3\SQL2008__sp_trace_11.trc (SQL 2005)
05/10/10 17:13:24.939 [0X000013B4] Processing file: E:\SQLDIAG\TRACE3\SQL2008__sp_trace_12.trc (SQL 2005)
05/10/10 17:13:24.959 [0X000013B4] Found TRACE_STOP with 0 bytes of data
05/10/10 17:13:24.961 [0X000013B4] Found [TRACE_STOP] event indicating the end of the trace files.
05/10/10 17:13:24.962 [0X000013B4] Reads completed
05/10/10 17:13:24.965 [0X000013B4] Shutting down the worker thread message queues.
05/10/10 17:13:24.996 [0X000013B4] Waiting for the worker threads to complete final actions.
05/10/10 17:13:26.074 [0X000013B4] Performing general cleanup actions.
05/10/10 17:13:26.076 [0X000013B4] Reducing cached memory.
05/10/10 17:13:26.096 [0X000013B4] Total Events Processed: 11117
05/10/10 17:13:26.101 [0X000013B4]  Total Events Filtered: 0
05/10/10 17:13:26.102 [0X000013B4] Parse errors: 0
05/10/10 17:13:26.224 [0X000013B4] Table ReadTrace.tblUniqueBatches: loaded ~445 rows
05/10/10 17:13:26.225 [0X000013B4] Table ReadTrace.tblUniqueStatements: loaded ~0 rows
05/10/10 17:13:26.225 [0X000013B4] Table ReadTrace.tblUniquePlans: loaded ~0 rows
05/10/10 17:13:26.226 [0X000013B4] Table ReadTrace.tblUniquePlanRows: loaded ~0 rows
05/10/10 17:13:26.241 [0X000013B4] Table ReadTrace.tblBatches: loaded ~4355 rows
05/10/10 17:13:26.242 [0X000013B4] Table ReadTrace.tblStatements: loaded ~0 rows
05/10/10 17:13:26.243 [0X000013B4] Table ReadTrace.tblPlans: loaded ~0 rows
05/10/10 17:13:26.244 [0X000013B4] Table ReadTrace.tblPlanRows: loaded ~0 rows
05/10/10 17:13:26.257 [0X000013B4] Table ReadTrace.tblInterestingEvents: loaded ~3 rows
05/10/10 17:13:26.258 [0X000013B4] Table ReadTrace.tblConnections: loaded ~0 rows
05/10/10 17:13:26.262 [0X000013B4] WARNING: One or more warning conditions exist that may affect the quality of the analysis data.  See RMLDB.ReadTrace.tblWar
ngs table and the ReadTrace log for complete details.
05/10/10 17:13:26.269 [0X000013B4] Indexing tables...
05/10/10 17:13:27.413 [0X000013B4] Doing post-load data cleanup...
05/10/10 17:13:27.863 [0X000013B4] Computing partial aggregates...
05/10/10 17:13:28.977 [0X000013B4] Data load completed.
05/10/10 17:13:28.979 [0X000013B4] Using execution path: c:\Program Files\Microsoft Corporation\RMLUtils
05/10/10 17:13:28.980 [0X000013B4] Launching [c:\Program Files\Microsoft Corporation\RMLUtils\Reporter.exe]
05/10/10 17:13:29.253 [0X000013B4] *******************************************************************************
* ReadTrace encountered one or more WARNINGS. A warning condition typically   *
* continues processing with reduced functionality, but the ReadTrace output   *
* may be adversely affected. Review the log file for details.                 *
*******************************************************************************
05/10/10 17:13:29.324 [0X000013B4]
 

Les deux warnings indiquent juste qu’il va manquer certains évènements, notamment les évènements pour pouvoir faire un replay dans Profiler.  A la fin du chargement, l’application ‘Reporter‘ est lancée et permet de visualiser les informations de la trace:

Ensuite, il est assez simple de naviguer dans l’interface et de visualiser l’information agrégée, par exemple en cliquant sur ‘Unique Batches’:

… et de repérer les requêtes consommatrices par critère (coût CPU, I/Os, durée), et visualiser le plan (si pris dans la trace):

Dernier épisode, on verra OSTRESS et ORCA, et les alternatives à SQLDIAG+RML Utilites: PSSDIAG, SQL Nexus, SQLH2, Data Collector, les reports SSMS.

A+ David B. (avec la collaboration de Martial LUCAS. )

Continuez votre lecture sur le blog :

• SQLDIAG (épisode 1) (David BAFFALEUF) [SQL Server]

Le standard ASCII

Afin de répertorier tous les caractères, un standard a été mis en place.

Le premier standard qui voit le jour est l’ASCII.

Pour représenter des caractères dans un fichier texte, on associe un nombre (code) à une lettre, à un chiffre ou à un symbole.

L’encodage le plus connu de l’ASCII est US-ASCII.

A => 65 => 01000001

a => 97 => 01100001

Le codage est réalisé sur 7bits/octet soit 128 symboles possibles. Une trentaine d’entre eux sont des caractères de commande, le reste est constitué de l’alphabet anglais standard, des chiffres et des ponctuations.

L’extension la plus connue est la famille ISO-8859, permettant de représenter les caractères accentués. Le codage est réalisé sur un octet complet, soit 256 caractères possibles. Les codes de 160 à 255 sont affectés au codage des caractères latins accentués.

Standards ISO-8859-1 à 15

ISO-8859-1 puis 15 (euro): Europe de l’Ouest

ISO-8859-2: Europe Centrale ou de l’Est

ISO-8859-5: Cyrillique

ISO-8859-7: Grec

Les principaux problèmes rencontrés avec ce standard sont les suivants :

- On ne peut utiliser plusieurs langues à l’intérieur d’une même instance.

- Deux grands absents de l’ISO-Latin-1 sont la ligature oe « œ », essentielle à la typographie française correcte, et le symbole de l’euro « € ».

- Chaque constructeur d’ordinateur a aussi développé ses propres codes non conformes aux standards internationaux (IBM EBCDIC, Windows-1252,..)

L’unicode

La globalisation consiste à intégrer dans une même table tous les caractères connus du monde.

Le standard Unicode est un mécanisme universel de codage de caractères. Il définit une manière cohérente de coder des textes multilingues et facilite l’échange de données textuelles.

Il est un passage obligatoire pour la plupart des nouveaux protocoles de l’Internet, mis en œuvre dans tous les systèmes d’exploitation et langages informatiques modernes.

Formes de codage

Il existe plusieurs formes de codage de caractères pour l’Universal Character Set:

- UCS-2

- UCS-4

Cependant, les codages UCS-2 et UCS-4 sont difficiles à utiliser dans de nombreuses applications et protocoles qui travaillent avec des caractères codés sur 8 ou même 7 bits. Même certains systèmes plus récents capables de travailler avec des caractères sur 16 bits ne peuvent pas traiter les données codées en UCS-4. Cette situation a conduit au développement de formats de transformation UTF (UCS Transformation Formats). Chacun possédant ses propres caractéristiques.

Encodage UTF 8

UTF-8 a été conçu par Kenneth Thompson en 1992 pour être compatible avec certains logiciels originellement prévus pour traiter des caractères d’un seul octet.

Les caractères de numéro 0 à 127 sont codés sur un octet dont le bit de poids fort est toujours nul.

Les caractères de numéro supérieur à 127 sont codés sur plusieurs octets.

Encodage UTF 16

UTF-16 est une méthode permettant de transformer un sous-ensemble de la table UCS-4 en une paire de valeurs UCS-2 d’une plage réservée.

UTF-16 est décomposé en 2 types.

UTF-16-BE (Big-endian)

UTF-16-LE (Little-endian)

Le codage UTF-16-BE est l’un des codages préconisés par la norme Unicode. Ce codage associe simplement un code de 16 bits (2 octets) à chaque caractère.

Encodage UTF 32

UTF-32 est un protocole pour encoder les caractères sur 32 bits. Du fait que l’UTF-32 utilise 4 bytes par caractère.

Il est aussi  décomposé en 2 types.

UTF-32-BE (Big-endian)

UTF-32-LE (Little-endian)

Il est relativement consommateur d’espace.

Support SGBD

Oracle

Deux paramètres permettent de prendre en charge l’Unicode.

• Database Character Set
• National Character Set

Ces paramètres sont définis lors de la création de la base.

L’instruction CREATE DATABASE dispose de la clause character set et de la clause national character set qui permettent de déclarer le jeu de caractères qui servira de jeu de caractères de la base et de jeu de caractères nationaux.

Sans spécification de la clause national character set, le jeu AL16UTF16 sera utilisé par défaut.

Le paramètre Database Character Set ne peut pas être configuré en UTF-16.

Oracle gère l’unicode au niveau des colonnes grâce National Character Set et au types de données suivantes :

• Nchar
• Nvarchar2
• nclob

Sybase

Au moment de l’installation, vous devez définir le paramètre default character set. Par défaut il est déterminé par celui utilisé sur votre server (Win = 1252, solaris =ISO 8859-1). Sybase recommande de définir UTF 8 pour ce paramètre si vous utiliser de l’unicode.

Sybase supporte la gestion de l’unicode au niveau du type de colonne à l’aide des 3 types suivants :

- Unichar

- Univarchar

- Unitext

Ces 3 types de données sont encodés en UTF 16.

Sql server

Nous  allons introduire la notion de collation.

Une collation est une table de correspondance spécifique à un jeu de caractères.

Un jeu de caractères peut avoir plusieurs collations, en général une par langue (par exemple, latin1 français, latin1 suédois).

Ce paramètre se définit à l’installation, sa valeur par défaut est en fonction de la langue de l’OS sur lequel se fait l’installation (pour les collations de type windows) :

Système en français : French_CI_AS

Système en anglais : Latin1_General_CI_AS

Il sert à déterminer la page de code utilisée dans les champs non unicode (varchar par exemple), l’ordre de tri des chaînes de caractère et aussi la sensibilité aux accents, aux majuscules, etc.

La collation paramétrée s’applique aux bases de données systèmes (master, msdb, tempdb et model) et par défaut aux nouvelles bases de données créées. Elle peut cependant être modifiée lors de la création d’une nouvelle base de données ou lors de l’ajout d’un champ texte grâce au mot clef COLLATE.

Sql server supporte la gestion de l’unicode à travers le type de colonne :

- Nchar

- Nvarchar

- Ntext

My sql

MySQL détermine le jeu de caractères et la collation en fonction de l’option de configuration active quand le serveur démarre ou en fonction des valeurs de configuration à l’exécution.

Vous pouvez utiliser –default-character-set=character_set_name comme jeu de caractères et vous pouvez en même temps ajouter –default-collation=collation_name pour la collation. Par défaut mysql utilise les options suivante –default-charset=latin1 –collation=latin1_swedish_ci car latin1_swedish_ci est la collation par défaut de latin1.

La collation peut aussi être définie au niveau de la base ou de la colonne à l’aide de la clause collate.

En MySQL version 4.x et plus ancien, NCHAR et CHAR étaient synonymes.

MySQL utilise UTF8 comme jeu de caractère prédéfini pour les champs de types Nchar et Nvarchar .Continuez votre lecture sur le blog :

• Modes de récupération et journal de transactions, épisode 2 (David BAFFALEUF) [SQL Server]
• Modes de récupération et journal de transactions, épisode 1 (David BAFFALEUF) [SQL Server]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Abonnez-vous au blog de la CapData team ! (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]

Par défaut en version 2000, PAGE_VERIFY n’a qu’une seule valeur possible pour vérifier la consistance des pages sur disque, TORN_PAGE_DETECTION.  TPD propose un mécanisme de vérification lorsque l’écriture d’une page ne peut pas se faire en entier, en raison d’une coupure d’alimentation par exemple ou tout autre problème entre SQL Server et le disque. Mais le scope de détection était limité car seuls les 2 premiers bits de chacun des 16 secteurs de 512 octets d’une page étaient scrutés pour calculer une séquence de contrôle (m_tornBits dans un dbcc page). Un écrasement de bits au milieu d’un secteur ne pouvait donc pas être détecté de cette manière.

A partir de la version 2005, une nouvelle valeur CHECKSUM est devenue option par défaut. Elle va calculer une somme de contrôle de l’intégralité de chaque page pour pouvoir détecter lorsque le contenu d’une page a été modifié même de manière infime. L’algorithme a été revu et implémenté de telle sorte qu’il n’impacte pas les performances (cf post Linchi Shea). Il est évidemment recommandé de laisser cette option PAGE_VERIFY sur CHECKSUM.

Le problème, c’est que PAGE_VERIFY, tout comme le mode de compatibilité de la base, n’est pas modifié lorsque la base migre de 2000 vers 2005 ou 2008:

restore database pagechecksumtest from disk='F:\SQLDATA$SQL2000\BACKUP\pagechecksumtest.bak'
with move 'pagechecksumtest' to 'E:\SQLDATA$SQL2005\DATA\pagechecksumtest.mdf',
move 'pagechecksumtest_log' to 'F:\SQLDATA$SQL2005\LOG\pagechecksumtest_log.ldf',
stats

16 percent processed.
24 percent processed.
33 percent processed.
41 percent processed.
58 percent processed.
66 percent processed.
74 percent processed.
83 percent processed.
91 percent processed.
100 percent processed.
Processed 96 pages for database 'pagechecksumtest', file 'pagechecksumtest' on file 1.
Processed 1 pages for database 'pagechecksumtest', file 'pagechecksumtest_log' on file 1.
Converting database 'pagechecksumtest' from version 539 to the current version 611.
Database 'pagechecksumtest' running the upgrade step from version 539 to version 551.
(...)
Database 'pagechecksumtest' running the upgrade step from version 610 to version 611.
RESTORE DATABASE successfully processed 97 pages in 0.047 seconds (16.754 MB/sec).

select name, page_verify_option_desc from sys.databases where name = 'pagechecksumtest'

name                        page_verify_option_desc
-------------------------   -----------------------------------
pagechecksumtest            TORN_PAGE_DETECTION

Donc il faut bien penser à l’activer à la suite de la migration pour assurer un contrôle optimal:

alter database pagechecksumtest set PAGE_VERIFY CHECKSUM

Attention, les pages ne sont pas protégées pour autant immédiatement après: il faudra qu’elles suivent un cycle complet : qu’elles soient luent depuis le disque vers le buffer pool, modifiées, puis réécrites par le checkpoint pour embarquer la somme de contrôle dans l’entête.

Si on veut creuser un peu plus, on peut se demander si la page sera bien protégée si elle se trouve dans un filegroup en readonly. En effet, la page ne pouvant pas être modifiée, le checkpoint ne pourra pas marquer le checksum dans son entête (on peut le voir avec un dbcc page, en regardant la valeur de m_flagBits qui normalement doit embarquer 0×200 dans son bitmap pour montrer que le checksum a été calculé).

Scénario: je pars sur une base fraîchement migrée, je choisis d’ajouter un filegroup  et de créer des données dedans, pour enfin le passer en read-only. Et ensuite, je me rends compte que PAGE_VERIFY est toujours sur TORN_PAGE_DETECTION, donc je le passe sur CHECKSUM. Est-ce que l’écrasement de données par un filter driver ou tout autre logiciel tiers s’exécutant sous NTFS sera vue par SQL Server ?

Voici la repro pour vous permettre de vous faire votre propre idée:

1) Sur une instance en SQL Server 2000, créer une base, une table et sauvegarder la base:

create database tpdtest
use tpdtest
create table T1(a numeric identity, b char(1))
insert into T1 values ('A')
backup database tpdtest to disk='F:\SQLDATA$SQL2000\BACKUP\tpdtest.bak' with init, stats

2) Remonter la base sur une instance 2005 ou 2008, ajouter un filegroup, déplacer la table dans ce filegroup et le passer en READONLY:

restore database tpdtest from disk='F:\SQLDATA$SQL2000\BACKUP\tpdtest.bak'
with move 'tpdtest' to 'E:\SQLDATA$SQL2005\DATA\tpdtest.mdf',
move 'tpdtest_log' to 'F:\SQLDATA$SQL2005\LOG\tpdtest_log.ldf',
stats, replace

alter database tpdtest add filegroup RO_tpdtest
alter database tpdtest add file(name='RO_tpdtest',
filename='E:\SQLDATA$SQL2005\DATA\RO_tpdtest.ndf') to filegroup RO_tpdtest
use tpdtest
create unique clustered index UCQ_T1_a on T1(a) on RO_tpdtest
alter database tpdtest modify filegroup RO_tpdtest readonly

Oulala mais j’ai oublié de passer PAGE_VERIFY sur CHECKSUM, vite:

alter database tpdtest set PAGE_VERIFY CHECKSUM

Et là je simule une corruption de données avec un éditeur héxa dans la page de données. Un coup de dbcc ind pour identifier la page en question:

dbcc ind('tpdtest','T1',1)

PageFID    PagePID    IAMFID      IAMPID      ObjectID      PageType
---------  ---------  ----------  ----------  ------------  -----------
3          9          NULL        NULL        1977058079    10
3          8          3           9           1977058079    1

La page en question est la page 8 dans le fichier  3, donc en convertissant 8 en offset, on obtient (8*8192) = 0×10000.  A partir de là, je passe la base offline et avec un éditeur hexa de mon choix, je m’en vais écraser la valeur ‘A’ avec une autre valeur, ‘C’ par exemple:

A ce niveau-là, il est important de ne pas toucher aux premiers octets de l’entête, car dans ce cas le mécanisme de TPD jouerait son rôle et on verrait une erreur 824 pointer son nez. On veut vraiment simuler une corruption au beau milieu de la page. On remet la base en ligne et on va lire la valeur:

alter database tpdtest set online
use tpdtest
select * from T1

a   b
--  --
1    C

Oooops. ..

A titre de comparaison, en créant le même cas dans un filegroup en read-write:

use tpdtest
create table T2(a numeric identity, b char(1))
create unique clustered index UCQ_T2_a on T2(a)
insert into T2 values ('A')
dbcc ind('tpdtest','T2',1)

 PageFID    PagePID    IAMFID      IAMPID      ObjectID      PageType
---------  ---------  ----------  ----------  ------------  -----------
1          25          NULL        NULL        2089058478     10
1          15          1           25          2089058478    1

La page est la 15, donc en ouvrant le fichier MDF, à l’offset (15*8192) = 0×1E000, on refait la même vilaine corruption au même endroit:

Mais cette fois lorsque l’on veut relire la page:

alter database tpdtest set online
use tpdtest
select * from T2

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xb3834c64; actual: 0xb2834c64). It occurred during a read of page (1:15) in database ID 21 at offset 0×0000000001e000 in file ‘E:\SQLDATA$SQL2005\DATA\tpdtest.mdf’.
Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB).
This error can be caused by many factors; for more information, see SQL Server Books Online.

Le CHECKSUM est un superset de TPD, donc ça marche.

Donc résumé:
- Quand on migre, il faut penser aussi à modifier PAGE_VERIFY et le passer sur CHECKSUM car ce n’est pas automatique.
- Le checksum en question ne sera implémenté qu’une fois la page lue depuis le disque, modifiée, puis réécrite par le CHECKPOINT.
- Les données dans les filegroups en read_only ne sont pas pour autant protégés. Ils conservent le TPD comme seul mode de protection.

A+ David B.

Continuez votre lecture sur le blog :

• Fragmentation sur des tables stockées en S-GAM (David BAFFALEUF) [SQL Server]
• Mythe: TORN PAGE DETECTION est moins coûteux que CHECKSUM (David BAFFALEUF) [SQL Server]
• Modifier la Collation d’une base SQL 2005 (Louis HOCHBERG) [SQL Server]

C’est d’ailleurs une question que l’on s’est posée, autour de la machine café, et qui du coup a fait l’objet d’un petit exercice avec Oracle, SQL Server, MySQL et Sybase… Bon, je vous rassure, nous ne faisons pas que parler bases de données pendant les pauses café mais ça arrive, parfois.

Place aux tests : Un trigger fait-il parti d’une transaction ?

Test Oracle :

SQL > create table t2 ( cnt int );
 SQL > insert into t2 values ( 0 );
 SQL > commit;
 SQL > create table t ( x int check ( x>0 ) );

SQL > create trigger t_trigger
 before insert or delete on t for each row
 begin
 if ( inserting ) then
 update t2 set cnt = cnt +1;
 else
 update t2 set cnt = cnt -1;
 end if;
 dbms_output.put_line( 'I fired and updated ' ||sql%rowcount || ' rows'  );
 end;
 /

Résultat : OUI

SQL > set serveroutput on
 SQL > insert into t values (1);
 I fired and updated 1 rows
 1 row created.
 SQL > insert into t values(-1);
 I fired and updated 1 rows
 insert into t values(-1)
 *
 ERROR at line 1:
 ORA-02290: check constraint (SYS_C009597) violated
 SQL > select * from t2;
 CNT
 ----------
 1

Test MS SQL Server :

use tempdb
 go

create table t2 ( cnt int )
 insert into  t2 values ( 0 )
 select * from t2

create table t ( x int check  ( x>0 ) )

drop trigger t_trigger
 create trigger t_trigger  on t for insert
 as
 begin
 update t2 set cnt = cnt +1
 print( 'I fired and updated ' + CAST(@@ROWCOUNT AS NVARCHAR(8))  + '  rows' )
 end

Résultat : OUI

begin tran
 insert into t values(2);
 insert into t values(-1);

(1 row(s) affected)
 I fired and updated  1 rows

(1 row(s) affected)
 Msg 547, Level 16, State  0, Line 4
 The INSERT statement conflicted with the CHECK constraint  "CK__t__x__7A5DD7C8". The conflict occurred in database "tempdb", table  "dbo.t", column 'x'.
 The statement has been terminated.

Test MySQL (InnoDB) :

Au passage, cela m’a permis de découvrir que les contraintes CHECK n’étaient pas prises en charge par MySQL : The CHECK clause is parsed but ignored by all storage engines

mysql> set autocommit=off;
 Query OK, 0 rows affected (0.02  sec)

mysql> use test
 Database changed
 mysql> create  table t2 ( cnt int ) engine=innodb;
 Query OK, 0 rows affected (0.11  sec)

mysql> insert into t2 values ( 0 );
 Query OK, 1 row  affected (0.02 sec)

mysql> commit;
 Query OK, 0 rows  affected (0.11 sec)

mysql> create table t ( x int primary key)  engine=innodb;
 Query OK, 0 rows affected (0.09 sec)

mysql>  delimiter //

mysql> create trigger t_trigger
 ->             before insert on t for each row
 ->              begin
 ->             update t2 set cnt = cnt +1;
 ->             end;
 -> //
 Query OK, 0 rows affected (0.11  sec)

mysql> delimiter ;

Résultat : OUI

mysql> insert into t values (1);
 Query OK, 1 row affected  (0.02 sec)

mysql> insert into t values (1);
 ERROR 1062  (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> select *  from t2;
 +------+
 | cnt  |
 +------+
 |    1 |
 +------+
 1 row in set (0.00 sec)

Test Sybase :

1> create trigger T1_ins_tr on T1 for insert

2> as

 3> begin

 4> select 1/0

 5> end

 6> go

Résultat : OUI

5> begin tran
 6> go
 1> insert into T1 values ('kjhkjhkjh')
 2> go
 Msg 3607, Level 16, State 0:
 Server 'ASE1502', Procedure 'T1_ins_tr', Line 4:
 Divide by zero occurred.

-----------
 1> select @@trancount
 2> go

-----------
 0

(1 row affected)

Résultat des tests : LE VERDICT !

Oui, un trigger fait bien parti d’une transaction, quel que soit le SGBD, enfin, au moins sur Oracle, SQL Serveur, MySQL et Sybase.

Je vous laisse faire vos tests sur DB2, Postgre et consorts… N’hésitez pas à nous faire un retour dans les commentaires.

A+
CédricContinuez votre lecture sur le blog :

• Mythe: SQL Server associe un thread à chaque connexion (David BAFFALEUF) [SQL Server]
• Fragmentation sur des tables stockées en S-GAM (David BAFFALEUF) [SQL Server]
• Saines lectures (David BAFFALEUF) [SQL Server]
• Scripting et SMO (suite): scripter les objets directement en T-SQL (David BAFFALEUF) [SQL Server]
• Règles d’installation de base (épisode 1) (David BAFFALEUF) [SQL Server]

Depuis la version 2008, Sql server nous propose de faire tout ça en une seule passe grâce à l’instruction MERGE.

CREATE TABLE clients
(client_id INT PRIMARY KEY,
client_nom VARCHAR(10),
client_prenom VARCHAR(10),
Date_naissance DATETIME)

CREATE TABLE prospects
( prospect_id INT PRIMARY KEY,
prospect_nom VARCHAR(10),
prospect_prenom VARCHAR(10),
Date_naissance DATETIME)

INSERT INTO clients VALUES
(1,'Dupont', 'Guy','1988-01-01'), (3,'Durand', 'Nicole','1990-03-10'),
(5,'Erie', 'Caroline','1984-11-22'), (7,'Toche', 'vincent','1977-07-07'),
(8,'Lagerec', 'Isabelle','1989-05-08')

INSERT INTO prospects VALUES 
(1,'Dupont', 'Guy','1977-02-02'),(2,'Kyle', 'Dorine','1981-11-11'),
(4,'Ravole', 'Nicolas','1982-02-06'),(5,'Erie', 'Caroline','1984-12-05'),
(6,'Kent', 'Cyril','1982-07-15')

MERGE clients USING prospects ON (clients.client_id=prospects.prospect_id)
WHEN NOT MATCHED THEN
 INSERT  values (prospects.prospect_id,prospects.prospect_nom,
 prospects.prospect_prenom,prospects.Date_naissance)
WHEN MATCHED THEN
 UPDATE  SET  clients.Date_naissance=prospects.Date_naissance;

Continuez votre lecture sur le blog :

• Règles d’installation de base (épisode 2) (David BAFFALEUF) [SQL Server]
• Que faire des “[Warning] Aborted connection” avec MySQL ? (Cédric PEINTRE) [MySQL]
• Fragmentation sur des tables stockées en S-GAM (David BAFFALEUF) [SQL Server]
• Retrouver une transaction en échec (David BAFFALEUF) [SQL Server]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]

Je veux parler ici d’erreurs récurrentes, qui se produisent de façon aléatoire plusieurs fois par jour, pas d’une erreur liée à un mauvais mot de passe ou à des privilèges insuffisants qui peuvent également générer ce type d’erreur.

Ce qu’en dit MySQL :

La page de documentation concernant ce problème est la suivante :

• http://dev.mysql.com/doc/refman/5.1/en/communication-errors.html

En fait, il y a deux raisons essentielles pour lesquelles MySQL est susceptible de renvoyer cette erreur : Des problèmes liés au réseau ou des problèmes liés au mode de sortie de session dans le code applicatif.
Pour ce dernier cas, attention à bien utiliser la fonction mysql_close() dans votre code applicatif.

Dans la pratique, que faire de cet avertissement ?

La première question à se poser est de savoir si ce problème affecte, d’une façon ou d’une autre, votre production.
Pour le savoir, plusieurs sources sont à votre disposition : Les utilisateurs qui peuvent vous remonter des erreurs, les logs applicatifs ou vos outils de monitoring qui clignotent trop régulièrement…

Si l’impact de cette erreur sur votre production est nulle, je vous recommande simplement de désactiver la remontée d’info la concernant via le paramètre log-warnings :

• set global log-warnings = 1;

Attention toutefois, la manipulation de ce paramètre peut entraîner la désactivation complète de tous les warnings. Rapprochez vous de la documentation de ce paramètre pour plus d’infos :

• http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_log-warnings

Sinon, je vous invite à suivre le mode opératoire suivant :

• Demander aux admins réseau de vérifier les différents composants réseau de votre architecture afin d’identifier d’éventuels problèmes
• Vérifier votre code applicatif et la bonne utilisation de la fonction mysql_close()
• Vérifier que vos clients MySQL ne dépassent pas le timeout de connexion défini par la variable connect_timeout
• Augmenter la valeur du paramètre max_allowed_packet (cette dernière méthode semble d’ailleurs porter ses fruits chez un grand nombre d’utilisateurs)

Si ces différentes opérations ne permettent pas d’identifier ou de régler votre problème, essayez d’appliquer chaque point préconisé dans la documentation mentionnée en début d’article et croisez les doigts !

N’hésitez pas à me faire un retour d’expérience sur ce point à travers les commentaires. Merci.

Bonne fin de semaine
Cédric.Continuez votre lecture sur le blog :

• Scruter les journaux d’évènements Windows avec LogParser (David BAFFALEUF) [SQL Server]
• Abonnez-vous au blog de la CapData team ! (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]
• Intérêt de créer des indexes cluster uniques (David BAFFALEUF) [SQL Server]
• Planification dans Adaptive Server (David BAFFALEUF) [Sybase]
• Insert et Update en une seule fois avec MERGE (Guillaume DEFENDINI) [SQL Server]

Un petit post rapide sur l’impact que peuvent avoir des valeurs de clés d’index clusterisé dupliquées sur les pages d’indexes non clusterisés.

En effet, par défaut la commande CREATE CLUSTERED INDEX va créer un index clusterisé non-unique, qui donc autorise l’insertion de valeurs dupliquées. Or cela pose un certain nombre de problèmes, notamment lorsque l’optimiseur choisit d’utiliser un index non clusterisé pour résoudre un prédicat et de faire des lectures additionnelles dans l’index clusterisé pour lire des colonnes non inclues dans l’index NC, ce qu’on appelle un bookmark lookup (bookmark = marque pages).

Exemple: une table MATABLE (col1,col2,col3) avec un index clusterisé sur col1 (bigint) et un index non clusterisé sur col2 (datetime)

set showplan_text on
select col1, col2, col3 from MATABLE where col2 = '2010/03/16'

 |--Nested Loops(Inner Join, OUTER REFERENCES:([CAPDATA].[dbo].[MATABLE].[col1]))
    |--Index Seek(OBJECT:([CAPDATA].[dbo].[MATABLE].[MATABLE_IDXNCLU_col2]),
                 SEEK:([CAPDATA].[dbo].[MATABLE].[col2]='2010-03-16 00:00:
                  00.000') ORDERED FORWARD)
    |--Clustered Index Seek(OBJECT:([CAPDATA].[dbo].[MATABLE].[MATABLE_IDXCLU_col1]),
                 SEEK:([CAPDATA].[dbo].[MATABLE].[col1]=[CAPDATA].[dbo].[MATABLE].[col1])
                 LOOKUP ORDERED FORWARD)

L’opérateur Index Seek est celui utilisé pour résoudre le prédicat sur la date et le Clustered Index Seek est l’opérateur choisi par l’optimiseur pour lire les colonnes additionnelles col1 et col3 (LOOKUP ORDERED FORWARD…).

Dans les pages  d’un index non clusterisé, on va stocker  à la fois la valeur de la clé de l’index NC (donc ici col2) ainsi que la valeur de la clé de l’index clusterisé (donc ici col1), qui va servir de pointeur entrant dans l’index clusterisé pour faire ce fameux Bookmark Lookup. La valeur de col1 représente donc le ‘marque-pages’ vers les pages de l’index clusterisé.

Maintenant comment savoir vers quelle page l’index NC va pointer lorsque col1 peut se trouver à plusieurs endroits de l’index clusterisé (lorsqu’il y a des valeurs dupliquées) ? Lorsqu’il y a des valeurs de clé dupliquées dans l’index clusterisé, SQL Server est obligé d’ajouter en plus de col2 et col1 un identifiant unique de type uniquifier (non visible par les outils courants SSMS et autres sp_helpindex) pour pointer vers la bonne ligne dans la bonne page. Et cet uniquifier ajoute 16 octets supplémentaires par ligne dans les pages de l’index NC.

Pour le démontrer, nous allons d’abord créer une table TABLE1 avec 3 colonnes, un index clusterisé non unique sur col1 et un index non clusterisé non unique sur col2.

CREATE TABLE TABLE1 (
 col1 bigint NOT NULL,
 col2 bigint NOT NULL DEFAULT 10000,
 col3 CHAR (400) NOT NULL DEFAULT 'abcdefghijklmnopqrstuvwxyz');

CREATE CLUSTERED INDEX TABLE1_IDXCLU_col1 ON TABLE1 (col1);
CREATE NONCLUSTERED INDEX TABLE1_IDXNCLU_col2 ON TABLE1 (col2);
GO

Nous allons ensuite alimenter la table  en forçant la génération de valeurs dupliquées:

DECLARE @a INT, @random BIGINT;
SELECT @a = 1;
SELECT @random = cast(rand(@a)*100 as bigint);
WHILE (@a < 10000)
BEGIN
 INSERT INTO TABLE1 VALUES (@random, DEFAULT, DEFAULT);
 SELECT @a = @a + 1;
 SELECT @random = cast(rand(@a)*100 as bigint);
END;
GO

En insérant 9999 valeurs comprises entre 1 et 100, il y a des chances pour que j’obtienne un certain nombre de valeurs en double:

select col1, COUNT(*) as compte
from TABLE1 group by col1 having COUNT(*) > 1

col1   compte
----    ---------
89    531
72    537
75    537
78    536
86    537
81    536
84    536
87    536
73    537
85    537
76    537
88    537
79    537
82    537
77    537
80    537
71    344
83    537
74    536

Maintenant je vais m’intéresser à la structure de mon index NC, en utilisant l’option DETAILED de la DMF sys.dm_db_index_physical_stats(), afin de voir l’espace utilisé à chaque niveau de l’index NC ( la valeur 2 = indid du NC):

select index_level, min_record_size_in_bytes,
max_record_size_in_bytes, avg_record_size_in_bytes,
record_count, page_count
from sys.dm_db_index_physical_stats (db_id('CAPDATA'),object_id('TABLE1'),2,NULL,'DETAILED')

index_level min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
----------- ------------------------ ------------------------ ------------------------
0           17                        25                       24,984    
1           23                        31                       30,764

record_count         page_count
-------------------- --------------------
9999                 34
34                   1

L’index NC compte 35 pages, 1 pages de niveau root et 34 pages de niveau feuille. Le niveau 1 représente le niveau root. Il compte une seule page donc et 34 lignes pointant vers les 34 pages du niveau inférieur. On voit que les tailles minimale et maximale varient entre 23 et 31 octets. Celà est dû au fait que presque chaque ligne  contient en plus des valeurs de col2 et col1 un uniquifier permettant de distinguer les lignes dupliquées qui sont référencées. On peut le voir en allant lire le contenu de cette root page. Le dbcc ind() va nous permettre d’identifier cette root page:

dbcc ind('CAPDATA','TABLE1',2)
PageFID  PagePID     IAMFID    IAMPID      ObjectID    IndexID     (...)            IndexLevel      (...)
-------  ----------- ------    ----------- ----------- ----------- --------------- --------------------       
1        25916       NULL      NULL        1138103095    2                             0   
1        25915       1         25916       1138103095    2                             0
1        23719       1         25916       1138103095    2                             1
(...)

index_level à 1, notre page est la 23719. un bref dbcc page:

dbcc traceon(3604)
dbcc page(5,1,23719,3)
FileId PageId      Row    Level  ChildFileId ChildPageId col2 (key)           col1 (key)           UNIQUIFIER (key) KeyHashValue
------ ----------- ------ ------ ----------- ----------- -------------------- -------------------- ---------------- ----------------
1    23719         0       1      1           25915       NULL                  NULL               NULL              NULL
1    23719         1       1      1           23728       10000                 71                 300               NULL
1    23719         2       1      1           23729       10000                 72                 256               NULL
1    23719         3       1      1           23730       10000                 73                 19                NULL
(...)

On voit qu’une colonne a donc été ajoutée. On pourra faire les mêmes observations avec les pages de niveau feuille de l’index NC.

Maintenant si on crée une seconde table TABLE 2 avec cette fois un index clusterisé UNIQUE:

CREATE TABLE TABLE2 (
 col1 bigint NOT NULL,
 col2 bigint NOT NULL DEFAULT 10000 ,
 col3 CHAR (400) NOT NULL DEFAULT 'abcdefghijklmnopqrstuvwxyz');

CREATE UNIQUE CLUSTERED INDEX TABLE2_IDXCLU_col1 ON TABLE2 (col1);
CREATE NONCLUSTERED INDEX TABLE2_IDXNCLU_col2 ON TABLE2 (col2);
GO

DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 10000)
BEGIN
 INSERT INTO TABLE2 VALUES (@a, DEFAULT, DEFAULT);
 SELECT @a = @a + 1;
END;
GO

Jetons un coup d’oeil à sa structure:

select index_level,min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, record_count, page_count
from sys.dm_db_index_physical_stats (db_id('CAPDATA'),object_id('TABLE2'),2,NULL,'DETAILED')

index_level min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes record_count         page_count
----------- ------------------------ ------------------------ ------------------------ -------------------- --------------------
0            17                      17                        17                       9999                 24
1            23                      23                        23                        24                  1

Il ne fait plus que 25 pages (24+1), tout simplement parce qu’il n’y a plus de uniquifier dans les pages. La taille moyenne de chaque ligne est redevenue normale. On retrouve la root page correspondante de la même façon que plus haut, et en lisant son contenu:

dbcc traceon(3604)
dbcc page(5,1,23745,3)
FileId PageId      Row    Level  ChildFileId ChildPageId col2 (key)           col1 (key)           KeyHashValue
------ ----------- ------ ------ ----------- ----------- -------------------- -------------------- ----------------
1      23745       0       1        1         25923         NULL                NULL                NULL
1      23745       1       1        1         23746         10000               427                 NULL
1      23745       2       1        1         23747         10000               853                 NULL
(...)

La colonne UNIQUIFIER a disparu. Donc morale de l’histoire, il faut penser à ajouter le mot-clé UNIQUE dans les créations d’indexes clusterisés.

J’ajoute aussi que le uniquifier n’est ajouté que lorsqu’il y a effectivement des doublons dans l’index clusterisé. Tant qu’il n’y a pas de doublon détecté, même si l’index a été créé sans préciser UNIQUE, il n’y aura pas de colonne additionnelle.

A+, David B.

Continuez votre lecture sur le blog :

• Scruter les journaux d’évènements Windows avec LogParser (David BAFFALEUF) [SQL Server]
• Abonnez-vous au blog de la CapData team ! (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]
• Que faire des “[Warning] Aborted connection” avec MySQL ? (Cédric PEINTRE) [MySQL]
• Planification dans Adaptive Server (David BAFFALEUF) [Sybase]
• Retrouver une transaction en échec (David BAFFALEUF) [SQL Server]

Logparser est un petit exécutable fourni gratuitement par Microsoft et qui permet de lire des fichiers structurés propriétaires tels que les eventlogs *.EVT, mais aussi les fichiers CVS, ETW, REG, etc… Tout d’abord il s’agit de le télécharger et de l’installer en trois ou quatre clics. Je passe rapidement sur l’install, il faudra juste bien penser à ajouter le chemin de logparser.exe dans votre PATH.

L’avantage principal est qu’il utilise un pseudo langage proche du SQL pour effectuer des recherches. Par exemple, si je veux remonter les 3 dernières erreurs dans le journal Application :

$ logparser "SELECT TOP 3 TimeGenerated, SourceName, Message
from Application
where eventtype=1
order by TimeGenerated desc" -i:EVT -stats:OFF

TimeGenerated           SourceName      Message
-------------------     -----------     ------------------------------------------------
2010-03-11 13:03:39     MSSQLSERVER     Database mirroring connection error 2 'Connection ...
2010-03-11 13:03:17     MSSQLSERVER     Database mirroring connection error 4 '64...
2010-03-11 13:02:57     MSSQLSERVER     The mirroring connection to "tcp://192...

On peut utiliser le mot-clé ‘into‘ après le SELECT pour indiquer que l’on souhaite placer le résultat dans un fichier. La répartition des codes erreurs dans les journaux d’évènements se fait comme suit:

$ logparser "select distinct EventTypeName, EventType
from Application" -i:EVT -stats:OFF

EventTypeName       EventType
------------------- ---------
Information event   4
Success event       0
Warning event       2
Error event         1
Failure Audit event 16

On recherchera donc en priorité les eventtype = 1.

Parmi les options intéressantes:

-i: Donne le type d’entrée du fichier. exemple, -i:EVT indique que l’on souhaite lire un fichier *.evt. S’il n’est pas indiqué, logparser le trouve tout seul en fonction de l’entrée.
-o: Donne le format de sortie. Par exemple -o:XML pour écrire au format XML. S’il n’est pas indiqué, logparser le trouve tout seul en fonction du fichier indiqué dans la clause ‘into‘.
-stats: ON/OFF: si les stats sont indiquées, alors logparser renvoie le nombre de lignes traitées et le temps passé.
-q: ON/OFF: mode quiet, par exemple pour éliminer les entêtes.

Il possède également des fonction d’agrégation de type GROUP BY / HAVING… Par exemple pour ramener le nombre d’erreurs par Source:

$ logparser "SELECT SourceName, count(*)
from Application where EventType = 1
group by SourceName order by count(*) desc"
-i:EVT -stats:OFF

SourceName                        COUNT(ALL *)
--------------------------------- ------------
MSSQLSERVER                       83
Winlogon                          30
Software Licensing Service        2
Perflib                           2
.NET Runtime Optimization Service 2
SideBySide                        2
SQLDIAG                           1

Dès lors il devient facile de créer un script qui va régulièrement exécuter une recherche des erreurs remontées dans les journaux Application et System. Nous pouvons utiliser un simple fichier par exemple pour stocker la dernière ligne lue à chaque passage, et nous permettre de repartir de cette ligne à l’exécution suivante:

$ logparser "SELECT MAX(RecordNumber) into E:\maxrecod.app.log FROM Application"
-i:EVT -stats:OFF -q:ON
$ cat E:/maxrecod.app.log
3597

… et dans un script, affecter le contenu du fichier dans une variable, et utiliser la variable dans une nouvelle recherche. Par exemple sous cygwin:

MAXRECORD=$(cat E:/maxrecod.app.log)
if [ ! -z ${MAXRECORD} ]
then
      SELECT TimeGenerated, SourceName, Message into E:/Application.err
      from Application where EventType=1 and RecordNumber >= ${MAXRECORD}" 
      -i:EVT -stats:OFF -q:ON
else
      echo "borne invalide"
fi

A+. David  B.

Continuez votre lecture sur le blog :

• Abonnez-vous au blog de la CapData team ! (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]
• Intérêt de créer des indexes cluster uniques (David BAFFALEUF) [SQL Server]
• Réunion GUSS le 1er juillet ! (David BAFFALEUF) [SQL Server]
• Que faire des “[Warning] Aborted connection” avec MySQL ? (Cédric PEINTRE) [MySQL]
• Planification dans Adaptive Server (David BAFFALEUF) [Sybase]

Sauvegarder le cache, oui, mais pour quoi faire ?

Vous devez effectivement comprendre l’intérêt de la chose si vous vous êtes déjà frotté à l’administration d’un MySQL mais je dois vous avouer qu’en abordant le sujet pendant la pose café, tout le monde n’était pas forcement convaincu.

En effet, la mise en œuvre d’une telle fonctionnalité soulève une autre question : Pourquoi redémarrer MySQL ?

Effectivement, le véritable intérêt de cette sauvegarde est de pouvoir couper son MySQL sans perdre les informations stockées dans le cache. Intérêt évidemment compris de tous dans le cas d’un crash du serveur MySQL.

Dans les faits, XtraDB permet de sauvegarder et restaurer le cache même si il n’y a pas eu d’arrêt de l’instance. Ce qui peut d’ailleurs s’avérer utile dans certaines conditions, après le passage d’un gros batch par exemple, afin de retrouver un cache d’activité transactionnelle standard.

Pour en revenir à la question posée par mon collègue, en dehors du contexte de crash, pourquoi redémarrer MySQL ?
Alors que d’autres éditeurs de bases de données concentrent leurs efforts pour justement limiter au maximum le redémarrage des instances, il semble que MySQL soit plus exposé que les autres à ce type de problématique.
Et effectivement, même si la modification dynamique de paramètres gagne du terrain avec les nouvelles versions, la gestion des fichiers physiques reste problématique dans un environnement de production critique.

Il s’agit donc ici de se poser la question à l’envers et c’est donc pour ces différentes raisons que la sauvegarde du cache de données est pertinente avec MySQL !

Comment ça marche ?

Le pré-requis est évidemment d’installer et d’utiliser le moteur de stockage XtraDB fourni par Percona (lien en fin d’article).

Pour comprendre le fonctionnement de cette sauvegarde, j’ai réalisé un petit test en utilisant le compteur Innodb_buffer_pool_pages_data qui indique combien de pages du cache sont utilisées :

1 – Arrêt et relance de l’instance MySQL (Tous les caches sont vidés)

2 – Je contrôle l’état de mon cache :

• mysql> show global status like ‘Innodb_buffer_pool_pages_data’;

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 14    |
+-------------------------------+-------+

3 – Je fais une requête simple sur ma table ETAT :

• mysql> select * from capdata_innodb.ETAT;

4 -Nouvelle vérification du cache, à ce stade, la table ETAT a bien été montée dans le cache (au moins une partie de ses données) :

• mysql> show global status like ‘Innodb_buffer_pool_pages_data’;

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 17    |
+-------------------------------+-------+

5 – Je lance une sauvegarde du cache :

• mysql> select * from information_schema.XTRADB_ADMIN_COMMAND /*!XTRA_LRU_DUMP*/;

+------------------------------+
| result_message               |
+------------------------------+
| XTRA_LRU_DUMP was succeeded. |
+------------------------------+

6 – Arrêt et relance de l’instance MySQL (Tous les caches sont vidés)

7 – Vérification du cache :

• mysql> show global status like ‘Innodb_buffer_pool_pages_data’;

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 14    |
+-------------------------------+-------+

8 -Restauration du cache :

• mysql> select * from information_schema.XTRADB_ADMIN_COMMAND /*!XTRA_LRU_RESTORE*/;

+---------------------------------+
| result_message                  |
+---------------------------------+
| XTRA_LRU_RESTORE was succeeded. |
+---------------------------------+

9 – Dernière vérification du cache :

• mysql> show global status like ‘Innodb_buffer_pool_pages_data’;

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 17    |
+-------------------------------+-------+

Le cache a bien été restauré, à vous le warm restart facile !

Percona a réalisé un petit benchmark comparatif dont voici le résultat :

Retrouvez toutes les informations sur le moteur XtraDB ici : http://www.percona.com/docs/wiki/percona-xtradb:startContinuez votre lecture sur le blog :

• Principes d’une sauvegarde à chaud (David BAFFALEUF) [SQL Server]
• MySQL et les tables temporaires internes (Cédric PEINTRE) [MySQL]
• Attention : publication transactionnelle et indexes non clusters (David BAFFALEUF) [SQL Server]
• Insert et Update en une seule fois avec MERGE (Guillaume DEFENDINI) [SQL Server]
• Scripting et SMO (suite): scripter les objets directement en T-SQL (David BAFFALEUF) [SQL Server]

Remarque :Toutes les opérations SQL se font connecté en tant que “as sysdba”

1. Configuration  des bases en archivelog et force logging

SQL> ALTER DATABASE FORCE LOGGING;
SQL> alter database archivelog ;

2. Créer une sauvegarde de la base primaire

A) Créer les standby controlfile
SQL> alter database create standby controlfile as ‘/u01/app/oracle/TEST/standby1.ctl’;

B) Créer les standby redo log .
SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/TEST/srl01.log’ SIZE 10M   REUSE;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/TEST/srl02.log’ SIZE 10M   REUSE;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/TEST/srl03.log’ SIZE 10M   REUSE;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/TEST/srl04.log’ SIZE 10M   REUSE;

Il est conseillé :

• de créer un fichier “standby redo log” de plus que les redo log
• d’avoir une taille des standby redo log >= taille des redo log

C) Effectuer une sauvegarde de la base primaire ((il est possible de le faire à chaud par rman)

Arrêter la base primaire

Faire une copie physique dans un répertoire de sauvegarde des types de fichier suivants :
- datafiles
- online redo logs
- standby controlfile
- standby redolog

Redémarrer la base primaire

3.  Modification ou création des  paramètres d’initialisation des bases primaire et standby

Base ayant le rôle primaire (PTEST) :
log_archive_format=%t_%s_%r.arc
log_archive_dest_2=’service=STEST LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=STEST’
log_archive_config=’DG_CONFIG=(PTEST,STEST)’
LOG_ARCHIVE_DEST_STATE_2=ENABLE
db_name=TEST
db_unique_name=PTEST
fal_server=STEST
fal_client=PTEST
DB_FILE_NAME_CONVERT=(‘/u01/app/oracle/TESTSTANDBY’,'/u01/app/oracle/TEST’)
LOG_FILE_NAME_CONVERT=(‘/u01/app/oracle/TESTSTANDBY’,'/u01/app/oracle/TEST’)
standby_archive_dest=/u01/app/oracle/flash_recovery_area/STEST/standby_archive
STANDBY_FILE_MANAGEMENT=AUTO

Base ayant le rôle standby (STEST)  :
fal_server=PTEST
fal_client=STEST
DB_FILE_NAME_CONVERT=(‘/u01/app/oracle/TEST’,'/u01/app/oracle/TESTSTANDBY’)
LOG_FILE_NAME_CONVERT=(‘/u01/app/oracle/TEST’,'/u01/app/oracle/TESTSTANDBY’)
STANDBY_ARCHIVE_DEST=/u01/app/oracle/flash_recovery_area/STEST/standby_archive
log_archive_format=%t_%s_%r.dbf
STANDBY_FILE_MANAGEMENT=AUTO
log_archive_config=’DG_CONFIG=(PTEST,STEST)’
LOG_ARCHIVE_DEST_2=’service=STEST LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=PTEST’
LOG_ARCHIVE_DEST_STATE_2=DEFER
CONTROL_FILES=(“/u01/app/oracle/TESTSTANDBY/standby1.ctl”, “/u01/app/oracle/TESTSTANDBY/standby2.ctl”, “/u01/app/oracle/TESTSTANDBY/standby3.ctl”)

4.  Configurer le listener, Oracle services names de la base primaire et de la standby

A) Configurer les fichiers $ORACLE_HOME/network/admin/listener.ora pour l’enregistrement statique

listener.ora :

SID_LIST_LISTENER_PRIMARY =
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=TEST)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME=PTEST)
)
)

SID_LIST_LISTENER_STANDBY =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TEST)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 )
(SID_NAME = STEST)
)
)

LISTENER_PRIMARY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = LINUX-ORA10-DATAGUARD-PRIMARY)(PORT = 1521))
)
)

LISTENER_STANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = LINUX-ORA10-DATAGUARD-PRIMARY)(PORT = 1522))
)
)

B) Configurer les $ORACLE_HOME/network/admin/tnsnames.ora

STEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = LINUX-ORA10-DATAGUARD-PRIMARY)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =STEST)
)
)

PTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = LINUX-ORA10-DATAGUARD-PRIMARY)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =PTEST)
)
)

C) Redémarrer les listeners des deux bases.

$ORACLE_HOME/bin/lsnrctl start LISTENER_PRIMARY
$ORACLE_HOME/bin/lsnrctl start LISTENER_STANDBY

5. Copie du fichier de mot de passe ou génération avec $ORACLE_HOME/bin/orapwd

6. Faire un startup mount de la base standby :

SQL> startup mount

7. Démarrer le recovery sur la base standby

A l’aide de la commande suivante :
SQL> alter database recover managed standby database disconnect from session;

• Dès qu’un archive log est transmis par la base primaire, la base standby commence à effectuer le recovery.
• L’option “disconnect from session” permet de garder la main sur la session SQL dans laquelle la commande a été exécutée, le process est alors lancé en background.

8.  Vérifier le transport des journaux

En forçant la création d’un archive log sur la base primaire :
SQL> alter system archive log current ;
L’archive log est alors transmis vers le répertoire d’archive de la base standby, et cette dernière fait automatiquement un recovery à partir du log transmis.

9. Vérifier que le data Guard fonctionne

en exécutant la requête suivante sur le primary et sur la standby:
SQL> select max(sequence#) from v$log_history;

Continuez votre lecture sur le blog :

• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Installation Oracle 64 bits sur Red Hat 5 (Thierry GASCARD) [Oracle]
• Direct i/o, dsync on/off, raw device (David BAFFALEUF) [Sybase]
• Fragmentation sur des tables stockées en S-GAM (David BAFFALEUF) [SQL Server]

1.  Configuration système

1.1 Pré-requis matériel :

-  5 Go pour les binaires Oracle en RAID 10
-  400 Mo sur /tmp
-  disques pour ASM

1.2 Noyau linux utilisé

RH 5 Entreprise 64 Bits Update 4

1.3 Pré-requis noyau

SELinux désactivé
X installé et configuré
Rpms suivants installés en plus des Rpms par défaut (version minimum) :
binutils-2.17.50.0.6-12.el5  (x86_64)
compat-libstdc++-33-3.2.3-61  (x86_64)
compat-libstdc++-33-3.2.3-61  (i386)
elfutils-libelf-0.137-3.el5  (x86_64)
elfutils-libelf-0.137-3.el5  (i386)
elfutils-libelf-devel-0.137-3.el5  (i386)
elfutils-libelf-devel-0.137-3.el5  (x86_64)
elfutils-libelf-devel-static-0.137-3.el5  (i386)
elfutils-libelf-devel-static-0.137-3.el5  (x86_64)
gcc-4.1.2-46.el5  (x86_64)
gcc-c++-4.1.2-46.el5  (x86_64)
glibc-2.5-42  (i686)
glibc-2.5-42  (x86_64)
glibc-common-2.5-42  (x86_64)
glibc-devel-2.5-42  (x86_64)
glibc-devel-2.5-42  (i386)
glibc-headers-2.5-42  (x86_64)
ksh-20080202-14.el5  (x86_64)
libaio-0.3.106-3.2  (x86_64)
libaio-0.3.106-3.2  (i386)
libaio-devel-0.3.106-3.2  (i386)
libaio-devel-0.3.106-3.2  (x86_64)
libgcc-4.1.2-46.el5  (x86_64)
libgcc-4.1.2-46.el5  (i386)
libstdc++-4.1.2-46.el5  (x86_64)
libstdc++-4.1.2-46.el5  (i386)
libstdc++-devel-4.1.2-46.el5  (x86_64)
libstdc++-devel-4.1.2-46.el5  (i386)
make-3.81-3.el5  (x86_64)
sysstat-7.0.2-3.el5  (x86_64)
unixODBC-2.2.11-7.1  (x86_64)
unixODBC-2.2.11-7.1  (i386)
unixODBC-devel-2.2.11-7.1  (x86_64)
unixODBC-devel-2.2.11-7.1  (i386)

La commande suivante nous donne des informations sur les Rpms nécessaires :
rpm -q –qf ‘%{NAME}-%{VERSION}-%{RELEASE}  (%{ARCH})\n’ \
binutils compat-libstdc++-33-3.2.* elfutils-libelf elfutils-libelf-devel \
elfutils-libelf-devel-static gcc gcc-c++ glibc glibc-common glibc-devel glibc-headers \
ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel

Rem : attention à la présence de rpms 32 bits : utilisation possible de l’option force : rpm -ivh –force ..

1.4 Configuration des limites systèmes

[root@]# cat >> /etc/security/limits.conf <<EOF
grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536
EOF

[root@]# cat >> /etc/pam.d/login <<EOF
session    required     pam_limits.so
EOF

[root@]# cat >> /etc/profile <<EOF
if [ \$USER = "oracle" ] || [ \$USER = "grid" ]; then
if [ \$SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
ulimit -n 25165824
else
ulimit -u 16384 -n 65536 -l 25165824
fi
umask 022
fi
EOF

1.5 Configuration du serveur pour oracle

[root@]# cat >> /etc/sysctl.conf <<EOF
kernel.shmmax=25769803776 — soit 24 Go
kernel.shmall=6291456 — avec taille de 4ko soit 24 Go
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048576
fs.aio-max-nr=1048576
EOF

2.   Préparation système

2.1 Création groupe oinstall,asmadmin,asmdba,asmoper

[root@]# groupadd -g 1000 oinstall
[root@]# groupadd -g 1200 asmadmin
[root@]# groupadd -g 1201 asmdba
[root@]# groupadd -g 1202 asmoper

2.2   Création utilisateur grid

[root@]# useradd -m -u 1100 -g oinstall -G asmadmin,asmdba,asmoper -d /home/grid -s /bin/bash -c “Grid Infrastructure Owner” grid
[root@]# id grid
[root@]# passwd grid

2.3 Vérification de l’existence de l’utilisateur nobody

[root@]# id nobody

2.4    Création des répertoires

[root@]# mkdir -p /capdata/app/grid
[root@]# mkdir -p /capdata/app/11.2.0/grid
[root@]# chown -R grid:oinstall /capdata
[root@]# chmod -R 775 /capdata

2.4 Mise à jour du profile de l’utilisateur grid

export ORACLE_SID=+ASM
export ORACLE_BASE=/capdata/app/grid
export ORACLE_HOME=/capadata/app/11.2.0/grid
export ORA_NLS11=$ORACLE_HOME/nls/data
export PATH=${PATH}:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/oracm/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
export TEMP=/tmp
export TMPDIR=/tmp
umask 022

3. Installation et configuration de la librairie ASM
3.1   Téléchargement

En fonction du noyau linux (uname -r), télécharger les rpms ASM sur le site Oracle d’asm
- ASMLib Kernel Driver
- Userspace Library
- Driver Support Files

3.2   Installation

[root@]#rpm -Uvh oracleasm*

3.2   Configuration

[root@]# /usr/sbin/oracleasm configure -i
..
Default user to own the driver interface []: grid
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done

puis chargement
[root@]# /usr/sbin/oracleasm init

3.2   Création des disques ASM
a – partitionnement des LUNs EMC

J’effectue un alignement des partitions en enlevant les 256 premiers secteurs de disques soit 128 Ko

[root@]# /sbin/parted /dev/emcpowera
mklabel msdos
unit s
mkpart primary 256 -1s
print
quit

[root@]# /sbin/parted /dev/emcpowerb
mklabel msdos
unit s
mkpart primary 256 -1s
print
quit

b- création des disques ASM

[root@]# /usr/sbin/oracleasm createdisk DATA_DISK1 /dev/emcpowera1
[root@]# /usr/sbin/oracleasm createdisk BACK_DISK1 /dev/emcpowerb1

4. Installation d’Oracle Grid Infrasture 11gR2

[grid@]# ./runInstaller &

Ordre d’installation
1. Installation Option : « Install and Configure for Standalone Server »
2. Product Languages : « english, french »
3. Create ASM Disk Group : « DATA,BACK »
4. ASM passwaord  : «same password»
5. Operating system Group : « asmdba,asmoper,asmadmin »
6. Installation location :
ORACLE_BASE : /capdata/app/grid
ORACLE_HOME : /capadata/app/11.2.0/grid
7. Prerequisite checks :
Génération d’un script fixit.sh pour fixer d’événtuel problème
8. Summary
9. Setup
Lancement de deux scripts sous root
[root@]# /capdata/app/oraInventory/orainstRoot.sh
[root@]# /capdata/app/11.2.0/grid/root.sh
10.Finish

Et voilà pour la première partie….Continuez votre lecture sur le blog :

• Installation Oracle 64 bits sur Red Hat 5 (Thierry GASCARD) [Oracle]
• Se connecter à SQL Server à travers Oracle, quelle drôle d’idée ? (Thierry GASCARD) [Oracle]
• Création et utilisation d’OCFS2 (Thierry GASCARD) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Utiliser ASMCMD (Thierry GASCARD) [Oracle]

SQLDIAG est un utilitaire en mode ligne de commandes qui est livré avec SQL Server depuis plus de 10 ans maintenant. Il est bien caché, peu de gens l’utilisent mais il peut être d’une puissance redoutable. Il est capable de lancer en même temps:

• Une collecte perfmon.
• Une trace type SQL Trace.
• Une collecte des journaux d’évènements.
• Un inventaire complet  de la machine basé sur msinfo32.
• Un inventaire complet de l’instance.

Il permettra même de corréler des traces de compteurs perfmon et le traçage de sessions SQL dans Profiler. Il se base sur un fichier de configuration au format XML. Il en existe un par défaut (SQLDiag.xml) qui se trouve sous ~Tools\Binn, mais il est très exhaustif  et trace tous les compteurs et évènements disponibles. L’inconvénient de l’utiliser est qu’il créé des traces pénalisantes et très encombrantes, plusieurs dizaines de Gb de fichiers textes, blg, trc, etc… Donc on a intérêt à se créer son petit fichier XML pour ne capturer que ce dont on aura besoin.

SQLDiag.xml

Dans ce premier épisode, nous allons donc parler de la structure de ce fichier XML. Elle ressemble vu d’en haut à ceci:

<Collection>
<Machines>
        <Machine name=".">
                <MachineCollectors>
                        <EventlogCollector />
                        <PerfmonCollector />
                </MachineCollectors>
                <Instances>
                       <Instance>
                              <Collectors>
                                    <SqldiagCollector />
                                    <BlockingCollector />
                                    <ProfilerCollector />
                                    <CustomDiagnostics />
                             </Collectors>
                     </Instance>
              </Instances>
      </Machine>
</Machines>
</Collection>

Chaque balise %Collector délimite la zone de paramétrage de chaque collecteur de trace. Pour activer / désactiver un collecteur, passer son attribut enabled à true ou false:

• EventlogCollector pour collecter les journaux d’évènements.
  
• PerfmonCollector pour lancer la trace perfmon.
• SQLdiagCollector pour récupérer toute la configuration de l’instance: paramètres, contenu de tous les errorlogs, connexions, verrous, liste des bases et options, xp_msver, etc…
  
• BlockingCollector pour l’évènement BlockedProcessReport de SQL Trace, rapporte les processus bloqués si le paramètre ‘blocked process threshold (s)‘ est configuré.
• ProfilerCollector pour la trace SQL Trace complète avec les évènements que l’on aura indiqué.
  
• CustomDiagnostics pour lancer des exécutables supplémentaires, en l’occurence msinfo32 dans notre cas, et récupérer la configuration complète de la machine.
  

PerfmonCollector

C’est le collecteur de compteurs perfmon. Ses éléments fils sont <PerfmonObject /> qui désigne la famille de compteurs comme “PhysicalDisk(*)” par exemple, et <PerfmonCounter /> qui désigne le compteur associé comme “\Avg. Disk sec/Read“. On aura besoin d’activer chaque compteur en passant son attribut enabled à true. Une zone PerfmonCollector peut ressembler à ceci, avec le nom des compteurs en vert:

<PerfmonCollector enabled="true" pollinginterval="30" maxfilesize="256">
    <PerfmonCounters>
        <PerfmonObject name="\PhysicalDisk(*)" enabled="true">
             <PerfmonCounter name="\Avg. Disk sec/Read" enabled="true" />
             <PerfmonCounter name="\Avg. Disk sec/Write" enabled="true" />
             <PerfmonCounter name="\Disk Read Bytes/sec" enabled="true" />
             <PerfmonCounter name="\Disk Write Bytes/sec" enabled="true" />
             <PerfmonCounter name="\Avg. Disk Bytes/Transfer" enabled="true" />
        </PerfmonObject>
        <PerfmonObject name="\Processor(*)" enabled="true">
            <PerfmonCounter name="\% Processor Time" enabled="true" />
            <PerfmonCounter name="\% User Time" enabled="true" />
            <PerfmonCounter name="\% Privileged Time" enabled="true" />
        </PerfmonObject>
    </PerfmonCounters>
</PerfmonCollector>

L’attribut pollinginterval indique la fréquence d’échantillonnage des compteurs. Le fichier de sortie de ce collecteur sera un fichier BLG intitulé tout simplement SQLDIAG.blg. Il pourra être corrélé plus tard avec la trace qui sera générée par le collecteur ProfilerCollector.

SQLdiagCollector

C’est le collecteur de données de l’instance. Il n’est pas paramétrable est composé d’une seule ligne:

<SqldiagCollector enabled="true" startup="false" shutdown="true" /> 

On l’activera donc en passant son attribut enabled à true là encore. Les paramètres startup et shutdown permettent d’indiquer si la collecte se fait au démarrage ou à l’arrêt de la trace SQLDIAG.

BlockingCollector:

C’est le collecteur des processus bloqués en attente de libération de ressources (verrous). Lui aussi n’est constitué  que d’un seul élément:

<BlockingCollector enabled="true" pollinginterval="5" maxfilesize="350" /> 

L’attribut pollinginterval désigne la fréquence d’échantillonnage  de l’évènement, et maxfilesize la taille maximale du fichier en sortie. Ce collecteur se base sur le paramètre d’instance ‘blocked process threshold‘ qui définit la durée en secondes au delà de laquelle on considère que le blocage est anormalement long. Il faudra définir ce paramètre car sa valeur par défaut est à zéro, ce qui signifie qu’il n’est pas activé. Par exemple pour mettre le seuil à 20 secondes:

sp_configure 'blocked process threshold', 20
go
reconfigure
go

Ensuite, le collecteur va créer une trace SQL Trace avec le seul évènement ‘Blocked Process Report‘, et capturer les sessions qui auront été bloquées pendant plus de 20 secondes. Le fichier de sortie de ce collecteur est un fichier trc classique nommé <INSTANCE>_SQLDIAG_sp_trace_blk.

ProfilerCollector:

Ensuite, ProfilerCollector est le collecteur de trace SQL Trace classique. Comme pour PerfmonCollector, il va disposer d’éléments fils comme <EventType /> et <Event /> dans lesquels on va bien entendu retrouver nos classes d’évènements:

<ProfilerCollector enabled="true" template="_GeneralPerformance100.xml" pollinginterval="5" maxfilesize="350">
     <Events>
         <EventType name="Stored Procedures">
             <Event id="10" name="RPC:Completed" enabled="true"/>
             <Event id="11" name="RPC:Starting" enabled="true" />
             <Event id="43" name="SP:Completed" enabled="true" />
             <Event id="42" name="SP:Starting" enabled="true" />
             <Event id="45" name="SP:StmtCompleted" enabled="true" />
             <Event id="44" name="SP:StmtStarting" enabled="true" />
        </EventType>
       <EventType name="TSQL">
             <Event id="40" name="SQL:StmtStarting" enabled="true"/>
             <Event id="41" name="SQL:StmtCompleted" enabled="true" />
       </EventType>
     </Events>
 </ProfilerCollector>

Le fichier de sortie de ce collecteur sera aussi un fichier trace classique nommé <INSTANCE>_SQLDIAG_sp_trace.

CustomDiagnostics

Enfin, CustomDiagnostics permet de pousser encore plus loin la personnalisation du rapport, en nous permettant d’utiliser nos propres outils de collecte. L’outil qui est utilisé par défaut est msinfo32 qui est utilisé pour afficher de manière graphique (Démarrer -> Exécuter -> msinfo32) ou en ligne de commande toute la configuration de la machine hôte:

<CustomDiagnostics>
 <CustomGroup name="msinfo" enabled="true" />
 <CustomTask enabled="true" groupname="MsInfo" taskname="Get MSINFO32" type="Utility" point="Startup" wait="OnlyOnShutdown" cmd="start /B /WAIT MSInfo32.exe /computer %server% /report
         &quot;%output_path%%server%_MSINFO32.TXT&quot; /categories +SystemSummary+ResourcesConflicts+ResourcesIRQS+ComponentsNetwork+ComponentsStorage+ComponentsProblemDevices+SWEnvEnvVars+
         SWEnvNetConn+SWEnvServices+SWEnvProgramGroup+SWEnvStartupPrograms" />
 <CustomTask enabled="true" groupname="MsInfo" taskname="Get default traces" type="Copy_File" point="Startup" wait="OnlyOnShutdown" cmd="&quot;%sspath%log\log*.trc&quot; &quot;%output_path%&quot;" />
 <CustomTask enabled="true" groupname="MsInfo" taskname="Get SQLDumper log" type="Copy_File" point="Startup" wait="OnlyOnShutdown" cmd="&quot;%sspath%log\SQLDUMPER_ERRORLOG.log&quot;
          &quot;%output_path%%server%_%instance%_SQLDUMPER_ERRORLOG.log&quot;" />
 </CustomDiagnostics>

Mais en plus de récupérer le contenu d’une trace msinfo32, il peut également récupérer le contenu des traces SQL Server par défaut *, et les stacktraces générées par SQL Server lorsqu’il rencontre une erreur fatale sur un module. Il va copier le contenu de ces traces dans le répertoire passé à l’exécution de SQLDIAG.

Exécution d’une trace

Une fois le fichier XML défini, lancer une trace devient un jeu d’enfant:

$ sqldiag /I MyXMLfile.xml /O e:/SQLDIAG/TRACE1
2010/02/04 18:58:10.28 SQLDIAG Collector version
2010/02/04 18:58:10.29 SQLDIAG

IMPORTANT:  Please wait until you see "Collection started" before attempting to reproduce your issue

2010/02/04 18:58:10.29 SQLDIAG Output path: e:\SQLDIAG\TRACE1\
2010/02/04 18:58:10.33 SQLDIAG Collecting from 1 logical machine(s)
2010/02/04 18:58:10.34 SQLDIAG Invalid node fetched. (null)
2010/02/04 18:58:10.34 SQLDIAG Invalid node fetched. (null)
2010/02/04 18:58:10.34 MS2K8-WIN2008-1\* SQL Server version: 10
2010/02/04 18:58:10.35 MS2K8-WIN2008-1\* Machine name: MS2K8-WIN2008-1 (this machine)
2010/02/04 18:58:10.35 MS2K8-WIN2008-1\* Target machine is not a cluster
2010/02/04 18:58:10.35 MS2K8-WIN2008-1\* Instance: (Default) (32-bit)
2010/02/04 18:58:12.40 SQLDIAG Initialization starting...
2010/02/04 18:58:13.98 MS2K8-WIN2008-1\* Starting Profiler trace
2010/02/04 18:58:14.94 MS2K8-WIN2008-1\* Starting Blocking script
2010/02/04 18:58:16.88 MS2K8-WIN2008-1\* MsInfo: Get MSINFO32
2010/02/04 18:58:17.26 MS2K8-WIN2008-1\* MsInfo: Get default traces
2010/02/04 18:58:17.47 MS2K8-WIN2008-1\* MsInfo: Get SQLDumper log
2010/02/04 18:58:17.71 MS2K8-WIN2008-1\* Adding Perfmon counters...
2010/02/04 18:58:20.73 MS2K8-WIN2008-1\* Starting Perfmon
2010/02/04 18:58:20.73 SQLDIAG Initialization complete
2010/02/04 18:58:21.53 MS2K8-WIN2008-1\* Perfmon started
2010/02/04 18:58:21.58 MS2K8-WIN2008-1\* Collecting diagnostic data

2010/02/04 18:58:22.28 SQLDIAG Collection started.  Press Ctrl+C to stop.

La trace est lancée, un simple Ctrl-C suffira à la stopper. Les commutateurs /I et /O permettent d’indiquer le fichier de configuration XML choisi et le répertoire où va se loger le contenu de la trace complète.

Il existe aussi des commutateurs pour indiquer à SQLDIAG de programmer son exécution à des heures déterminées. Il est même possible de l’enregistrer en tant que service dans windows.

Dans le prochain post sur SQLDIAG, on verra comment dépouiller les fichiers de résultat.

A+ David B.

(avec la collaboration de Martial LUCAS. )

* Depuis la version SQL Server 2005, un paramètre instance ‘default trace enabled’ avec une valeur de 1 (valeur par défaut) génère une trace systématiquement au démarrage de l’instance. Cette trace contient les classes évènements Database, Errors and Warnings, Full text, Objects, Performance, Server et Security Audit. Chaque trace vient se loger dans le répertoire des ERRORLOG par défaut.

Continuez votre lecture sur le blog :

• Utiliser ASMCMD (Thierry GASCARD) [Oracle]
• Modes de récupération et journal de transactions, épisode 1 (David BAFFALEUF) [SQL Server]
• SQLDIAG (épisode 2) (David BAFFALEUF) [SQL Server]

On va créer une base de test pour démontrer ce point:

create database test_statistics

On va vérifier si les options de création et de mise à jour auto sont bien activées

select databasepropertyex('test_statistics','IsAutoCreateStatistics')
select databasepropertyex('test_statistics','IsAutoUpdateStatistics')

1
1

On créé un peu de volumétrie et on ajoute les indexes clusterisés

use test_statistics
create table STATS1(a numeric identity, b varchar(400))
create table STATS2(a numeric identity, b varchar(400))
insert into STATS1 values (replicate('a',400))
go 1000
insert into STATS2 values (replicate('a',400))
go 1000
alter table STATS1 add constraint PK_STATS1 primary key (a)
alter table STATS2 add constraint PK_STATS2 primary key (a)

On fixe ensuite la date de dernière mise à jour des stats pour les deux tables avec STATS_DATE()

select object_name(object_id), stats_date(object_id,stats_id) FROM sys.stats S
inner join sys.tables T on T.object_id = S.object_id

STATS1    2010-01-28 17:41:45.617
STATS2    2010-01-28 17:41:45.963

A partir de là, on va forcer le calcul des stats en NORECOMPUTE sur STATS1 seulement et revérifier les dates de stats. NORECOMPUTE aura pour effet de dire à SQL Server de ne plus compiler en auto sur cette table.

update statistics dbo.STATS1 PK_STATS1 WITH NORECOMPUTE
 Command(s) completed successfully.

select object_name(S.object_id), stats_date(S.object_id,S.stats_id) FROM sys.stats S
inner join sys.tables T on T.object_id = S.object_id
STATS1    2010-01-28 17:42:14.350 
STATS2    2010-01-28 17:41:45.963

La date pour STATS1 a bien été modifiée. OK
On va générer quelques perturbations identiques aux niveau des deux tables comme ajouter une colonne, supprimer des lignes, etc de manière à déclencher statman, l’utilitaire de recompilation auto des stats… On le verra passer dans une session Profiler:

Si  on revérifie maintenant les dates de mise à jour:

select object_name(S.object_id), stats_date(S.object_id,S.stats_id) FROM sys.stats S
inner join sys.tables T on T.object_id = S.object_id
STATS1    2010-01-28 17:42:14.350
STATS2    2010-01-28 17:47:59.630

Cette fois seule STATS2 a été modifiée. Il faudra donc que je prévoie de mettre à jour manuellement STATS1 à intervalles réguliers, 1 fois par jour la nuit par exemple.

Conclusion pour retrouver sur une base les tables qui sont passées en norecompute au niveau des stats:

select T.name 'tablename', S.name 'statsname', S.no_recompute
from sys.tables T
INNER JOIN sys.stats S on S.object_id = T.object_id
WHERE S.no_recompute = 1

tablename             statsname                        no_recompute
-------------------  -------------------------------  ------------------------------
STATS1                PK_STATS1                       1

A+ [David B.]

Continuez votre lecture sur le blog :

• MySQL et les tables temporaires internes (Cédric PEINTRE) [MySQL]
• Scruter les journaux d’évènements Windows avec LogParser (David BAFFALEUF) [SQL Server]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Scripting et SMO (suite): scripter les objets directement en T-SQL (David BAFFALEUF) [SQL Server]
• Retrouver une transaction en échec (David BAFFALEUF) [SQL Server]

Le détail des cas de création de tables temporaires est disponible dans la documentation MySQL :

• http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

Ces tables temporaires sont créées en mémoire par défaut, dans la limite de la taille définie par le paramètre tmp_table_size [taille par défaut dépendante de l'OS]. Au delà de cette limite, la table temporaire est créée sur disque. Attention toutefois, si la valeur du paramètre max_heap_table_size [16M par défaut] est inférieure à celle de tmp_table_size, alors les tables temporaires en mémoire seront limitées à max_heap_table_size. Pour simplifier les choses, positionnez les deux paramètres à la même valeur.

Il est évidemment plus performant de provoquer le maximum de création en mémoire en positionnant la valeur du paramètre tmp_table_size à une taille relativement importante (16M à 32M).
Attention toutefois au débordement mémoire, en effet, cette taille peut potentiellement être consommée par chaque thread connecté !

La colonne Extra de la commande EXPLAIN pour une requête de type SELECT permet d’obtenir une information sur la création d’une éventuelle table temporaire.
Dans ce cas (utilisation d’un GROUP BY), une table temporaire sera créée :

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_evenement
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 14988
Extra: Using temporary; Using filesort

En revanche, cette commande ne permet pas de savoir si la table temporaire est créée sur disque ou en mémoire, pour cela, il faudra s’appuyer sur les compteurs MySQL évoqués dans la suite de l’article.

Le graphe suivant présente un état des tables temporaires créées en mémoire et sur disque pour notre instance de production  :

Tables temporaires MySQL

Les compteurs MySQL utilisés pour relever ces valeurs sont Created_tmp_table et Created_tmp_disk_tables. Il n’est malheureusement pas possible de connaitre la taille consommée en mémoire ou sur disque par ces tables temporaires.

Ici, la quasi totalité des tables temporaires est créée sur disque. La taille maximum d’une table temporaire en mémoire est pourtant positionnée à une valeur relativement importante (48M).

Le fait d’augmenter la valeur du tmp_table_size ne changera rien dans ce cas ! Explications :

MySQL utilise le moteur de stockage MEMORY pour la création des tables temporaires internes. Ce moteur se comporte quasiment comme MyISAM, cependant, une  contrainte  de ce moteur vient perturber le fonctionnement des tables temporaires internes : Il n’est pas possible d’utiliser des colonnes de type BLOB ou TEXT pour les tables MEMORY.

De ce fait, si MySQL doit créer une table temporaire pour un ordre SELECT contenant ces types de colonne, elle sera automatiquement créée sur disque.

Deux possibilités s’offrent à vous dans ce cas :

• Limiter l’utilisation des colonnes de type BLOB ou TEXT
• Monter un système de fichier mémoire pour le stockage des tables temporaires interne MySQL

[ Cédric P ]Continuez votre lecture sur le blog :

• Que faire des “[Warning] Aborted connection” avec MySQL ? (Cédric PEINTRE) [MySQL]
• Retrouver les tables dont les stats ne sont plus compilées en automatique (David BAFFALEUF) [SQL Server]
• Scripting et SMO (suite): scripter les objets directement en T-SQL (David BAFFALEUF) [SQL Server]
• Fragmentation sur des tables stockées en S-GAM (David BAFFALEUF) [SQL Server]
• XtraDB sauvegarde votre cache, et ça marche ! (Cédric PEINTRE) [MySQL]

Une fois le matériel commandé, livré, déballé, racké, etc…, il va falloir installer Windows et SQL Server. Là encore il va falloir regarder certains détails de près :

Paramétrage de Windows Server:

Préparation des disques:

On l’a dit dans l’épisode 1, le disque c’est le nerf de la guerre. Je vais donc apporter un soin particulier à la configuration de mes axes.

Pour placer sur disque une base de données transactionnelle, on essaie de considérer deux règles de base:

• Toujours séparer physiquement le fichier de données du journal de transactions: pour deux raisons. La première pour la sécurité des données: si je perds mon disque de données, j’ai besoin d’avoir accès au journal pour récupérer les dernières transactions validées mais pas encore prises par le backup log. La seconde pour des raisons de performances: le critère qui pèse le plus dans le temps de service d’un disque, c’est le déplacement de la tête au dessus de la bonne piste (seek time). Les accès aux données sont de nature aléatoire et donc augmentent cette latence de recherche. En revanche, les accès sur le journal sont séquentiels, donc limitent le seek time car une fois la tête positionnée sur la bonne piste / bon secteur, elle glisse tranquillement pour lire (recovery) ou écrire (commit / checkpoints) sur les secteurs adjacents. Mélanger les deux, ça plombe les accès au journal, et sur une instance OLTP, on sait que l’accès en écriture sur le journal est critique car il conditionne le temps de réponse du commit.

• Toujours séparer physiquement les données des sauvegardes: pour une raison évidente de sécurité des données, mais il faut y penser avant d’avoir un problème. Et par défaut jusqu’en SQL Server 2005, tout allait dans ~MSSQL\Data et ~MSSQL\Backup.

Séparer physiquement, ça veut dire PAS sur les mêmes disques. Ce n’est pas parce que j’ai trois lecteurs E:\, F:\ et G:\ que j’ai trois disques, il faudra le vérifier avec la console de gestion des volumes (diskmgmt.msc) et avec votre ami l’administrateur système en charge du stockage pour vérifier s’il ne vous a pas refilé 3 LUNs découpées dans le même diskgroup. Si je reprends mon Proliant DL 580 G5  avec ses 16 slots:

Je me paye même le luxe d’avoir un RAID10 pour placer mes filegroups d’indexes, comme sur Oracle. Cela dit, la bagarre va être rude parce que tout le monde pense que l’espace disque est gâché, surtout pour ce qui concerne les journaux de transactions, où on va utiliser peut être 20% de l’espace alloué. Mais il faut le voir comme un investissement. Vous aurez des temps de service garantis de moins de 4 ms en écriture sur le commit, et le nombre de transactions par seconde va en bénéficier. C’est une réalité, on voit des exemples de ce type régulièrement chez nos clients. 

Une fois les axes définis et montrés à la machine, la première chose va être de partitionner le disque en alignant sur le 128ième secteur (=64K), sous diskpart:

diskpart> create partition primary align=64

Globalement, il faut savoir que jusqu’en version 2003 incluse, Windows continue de préréserver les 63 premiers secteurs d’un disque pour y stocker son MBR (master boot record). Lorsque l’on va formater ensuite le file system en 64K, le premier offset va commencer réellement sur le 63ième secteur et décaler les clusters NTFS de 512 octets (1 secteur). il faut que les écritures de SQL Server (64K) soient alignées avec le début de la partition. Pour davantage d’infos, voir l’article de David BARBARIN sur le sujet.

En deux étapes:

• Partitionner le disque en alignant sur le 128ième secteur.
• Puis créer un FS en NTFS avec un cluster size de 64K pour les disques de données et de journaux.

Il faudra aussi aller vérifier que la politique d’écriture est bien en write-through au niveau du cache du contrôleur disque. Cet exemple sous OpenManage:

Paramètres noyau de Windows pour SQL Server:

Pas grand chose à faire dans 99% des cas. Il faut valider que le bail (quantum en anglais) par thread est bien de type ‘long’ dans les performances avancées de la machine (Poste de Travail -> Properties -> Advanced -> Performance -> Advanced):

Background services => chaque thread a le droit de s’exécuter pendant 12 ticks d’horloge avant d’être interrompu par le Dispatcher.  Par défaut sur la gamme Server, le radiobutton doit être sur background services (quantum = 12 ticks d’horloge) et sur la gamme workstation, sur Programs (quantum = 2 ticks d’horloge). En général pour tous les SGBD, le quantum doit être plus long pour limiter le nombre de changements de contexte opérés par le système d’exploitation. Pour plus de détails, voir cet article.

Il faudra aussi aller vérifier que les ressources allouées seront en priorité sur les programmes générant beaucoup d’IOs comme SQL Server (Connexions réseau, clic-droit sur l’interface réseau -> Properties-> File and Printer Sharing (et oui !) -> Properties):

Maximize throughput for network applications: sous entendu on n’est pas là pour faire du partage de fichiers.

Et éviter l’utilisation des antivirus autant que possible.

Compte de service pour SQL Server et l’agent:

Le compte de service est le compte déclaré au niveau de la machine ou du domaine pour supporter l’exécution de SQL Server et l’ensemble de ses interactions avec le système: poster des IOs, atteindre des machines sur le réseau, écrire dans un fichier de sauvegarde, etc… Toutes ces actions seront donc exécutées sous le contexte de droits du compte de service.  Dans la plupart des cas, les SQL  Server sont démarrés sous le contrôle des comptes systèmes locaux (LOCAL SYSTEM) avec lesquels sont lancés la plupart des modules de windows exécutés en mode user (smss.exe, crss.exe, lsass.exe, winlogon.exe, etc…). Et négligence du  DBA, on trouve aussi des SQL Server démarrés sous le compte Administrateur local voire de domaine, ce qui du point de vue sécurité  n’est pas vraiment l’idéal.

Et bien depuis la version SQL Server 2005, il n’y a plus d’excuse: on créé simplement des comptes utilisateurs locaux ou de domaine sans privilège *** pour SQL Server et SQL Agent (je ne parle pas de SQL Browser puisque du point de vue sécurité, on ne doit pas l’utiliser **), on les passe ensuite à l’assistant d’installation qui créé des groupes avec les droits appropriés, et y intègre les comptes qui héritent du coup des droits strictement nécessaires.

Éléments à définir avant l’installation de SQL Server :

Avant de lancer le setup,il faut réfléchir à quelques éléments caractéristiques de l’instance que l’on souhaite installer:

Instance locale / instance nommée:

L’instance locale est l’instance que l’on créé par défaut sur une machine. Elle prend par défaut le nom de la machine et le port 1433 attribué par l’IANA. On ne peut donc en créer qu’une seule par machine. Ma chaîne de connexion ressemblera à :

sqlcmd -Udba_exploit -w1000 -S PIXIES 

Où PIXIES est le nom de ma machine, et donc le nom de mon instance locale par la même occasion. Maintenant, lorsqu’on souhaite en installer plus d’une par machine, on pourra utiliser les instances nommées, dont le nom sera constitué d’un préfixe (nom de la machine hôte) et d’un suffixe (nom de l’instance à choisir) séparés par un backslash (‘\’). Dans ce cas, ma chaîne de connexion ressemblera à :

sqlcmd -Udba_exploit -w1000 -S PIXIES\SQL1 

Où SQL1 sera le nom de nom instance nommée. Autre différence majeure, le port de démarrage sera défini dynamiquement. Ce qui signifie qu’il peut changer entre deux redémarrages, donc problème de firewall à l’horizon. Pour l’éviter, il faudra fixer le port de démarrage de l’instance dans les propriétés TCP, et redémarrer SQL Server. **

Collation:

La collation va définir la manière de coder les caractères dans ma base de données. Là aussi le sujet est vaste et largement débattu sur le net. Il faut simplement réfléchir aux types de champs texte que mon application va devoir stocker et choisir le codepage approprié. Il faut essayer de prendre le codepage le plus large possible. Lors de l’installation en SQL Server 2005, deux types de collations sont proposées: les collations windows et les collations SQL Server. En fait la seconde est incluse pour compatibilité vers les versions précédentes, et ne doit donc être utilisée que pour assurer la compatibilité lors d’une migration. Il vaut mieux essayer de partir avec une collation Windows dès le départ, elles sont plus riches et plus précises, notamment lorsqu’on utilise à la fois des colonnes UNICODE et NON-UNICODE, car la collation windows sait convertir du NON-UNICODE à la volée.  Changer la collation à tous niveaux n’est pas une opération de routine: au niveau de l’instance, il faudra la reconstruire, au niveau de la base c’est plus épique encore.

Mode d’authentification:

Deux solutions:

• Le mode intégré: l’authentification se fait par une autorité telle qu’un contrôleur de domaine ou la machine locale. Avantage, SQL Server ne stocke pas de mot de passe, côté sécurité c’est la meilleure option. Inconvénient, lorsqu’on souhaite atteindre depuis son application cliente une instance qui n’est pas dans le même domaine, ou simplement une machine en workgroup, l’authentification intégrée ne fonctionnera pas.
• Le mode mixte: donc on sera obligé de permettre aussi de créer et d’utiliser  des comptes et des mots de passe dans SQL Server. En mode mixte, on se connecte soit avec un compte windows, soit avec un compte SQL Server.

Les bonnes pratiques de sécurité préconisent de rester en mode intégré, mais ce n’est n’est bien souvent pas très pratique, et donc pas appliqué. Il vaut mieux autoriser le mode mixte mais désactiver le compte sa, et faire appliquer la politique de mots de passe de Windows.

Une fois ces étapes validées, l’installation peut commencer. Le travail de préparation est terminé.

A bientôt !

* Dans cet exemple, on choisit de faire les backups sur un partage CIFS
** Je ne parle pas de sécurité dans cet article, le sujet est vaste et fera l’objet d’un prochain post.
*** Penser à cocher le compte n’expire jamais pour les comptes de service.

[ David B. ]

Continuez votre lecture sur le blog :

• Insert et Update en une seule fois avec MERGE (Guillaume DEFENDINI) [SQL Server]
• Modes de récupération et journal de transactions, épisode 2 (David BAFFALEUF) [SQL Server]
• Scripting et SMO (suite): scripter les objets directement en T-SQL (David BAFFALEUF) [SQL Server]
• Règles d’installation de base (épisode 1) (David BAFFALEUF) [SQL Server]

La solution http://public-yum.oracle.com et YUM.

[root@OEL5-ORA1 yum.repos.d]# cd /etc/yum.repos.d
[root@OEL5-ORA1 yum.repos.d]#wget http://public-yum.oracle.com/public-yum-el5.repo

car j’utilise OEL 5 Update 4

[root@OEL5-ORA1 ~]# cat /etc/enterprise-release
Enterprise Linux Enterprise Linux Server release 5.4 (Carthage)

Je vais modifier le fichier public-yum-el5.repo en mettant enabled=1 pour ma version el5_u4_base

vi public-yum-el5.repo

…

[el5_u4_base]
name=Enterprise Linux $releasever U4 – $basearch – base
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/4/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1

…

[root@OEL5-ORA1 yum.repos.d]# yum list oracleasm
Loaded plugins: security
Error: No matching Packages to list
ocfs2 packages are not available. Let’s install it now

[root@OEL5-ORA1 yum.repos.d]# yum install oracleasm

Loaded plugins: security
Setting up Install Process
Package oracleasm-2.6.18-164.el5-2.0.5-1.el5.i686 already installed and latest version
Nothing to do

Quel plaisir
Continuez votre lecture sur le blog :

• Création d’un Dataguard physique (Guillaume DEFENDINI) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Retrouver une transaction en échec (David BAFFALEUF) [SQL Server]
• Scripting et SMO (suite): scripter les objets directement en T-SQL (David BAFFALEUF) [SQL Server]
• Direct i/o, dsync on/off, raw device (David BAFFALEUF) [Sybase]

La collation (ou classement en français) d’une base de données indique le jeu de caractères et l’ordre de tri des données stockées dans la base de données. De nombreux articles expliquent le rôle et traitent des problèmes de collation. L’objectif de celui-ci est de présenter une méthode pour changer la collation d’une base de données.

La collation peut être fixée:

1. Au niveau de l’instance: à l’installation de SQL Server 2005.
2. Au niveau de la base:

    CREATE DATABASE ... COLLATE ou ALTER DATABASE ... COLLATE.

3. Au niveau de la colonne:

   CREATE TABLE maTable ... COL1 NVARCHAR(100) COLLATE French_CI_AS

4. Au niveau de l’expression SQL:

   SELECT A, B from T1, T2 where T1.A = T2.B collate French_CI_AS

On peut parfois se retrouver dans la situation suivante : la base de données a été créée ou restaurée dans une collation qui pose problème à l’application et il faut la modifier.

Attention, gare alors aux problèmes lorsque l’on utilise des bases de collations différentes sur une même instance : La collation de tempdb est héritée de model, qui elle-même hérite de celle de l’instance. Lors de jointures avec des tables temporaires sur des colonnes contenant du texte, des erreurs de comparaison peuvent survenir:

Msg 468, Level 16, State 9, Line 5 Cannot resolve the collation conflict between
"French_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation

Pour changer la collation d’une base de données, il existe plusieurs techniques :

Technique 1 :

1. Modifier la collation de la base : ALTER DATABASE … COLLATE
   Toutes les nouvelles colonnes de type caractère créées dans la base hériteront de la nouvelle collation mais pas les colonnes caractère déjà créées .
2. Modifier la collation de chaque colonne à condition quelle ne soit pas référencée dans un index, une contrainte Foreign key ou CHECK, une statistique de distribution ou une colonne calculée
   … pas évident

Technique 2 : celle que je vous propose

1. Générer le script de création de la base et de tous ses objets
2. Modifier ce script, pour créer une nouvelle base vide avec la bonne collation
3. Importer dans la nouvelle base les données de l’ancienne base à l’aide de l’assistant de Management Studio
4. Vérifier si l’application fonctionne correctement sur la nouvelle base
5. Intervertir l’ancienne base et la nouvelle
   On peut renommer une base ( commande ALTER DATABASE … MODIFY NAME). Attention de bien récupérer les comptes utilisateurs de l’ancienne base et le mapping sur les logins.

Exemple : Changer la collation de la base AdventureWorks Latin1_General_CS_AS en En French_CI_AS

Avant de commencer:

– Vérifier la collation du serveur

sp_helpsort

Server default collation
---------------------------------------------------------------------------
French, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

– Vérifier la collation de la base

select DATABASEPROPERTYEX('AdventureWorks', 'Collation')
-----------------------------------------------------------------------
Latin1_General_CS_AS

– Vérifier la collation des colonnes

SELECT B.table_name, column_name, collation_name, data_type FROM INFORMATION_SCHEMA.COLUMNS A,
INFORMATION_SCHEMA.TABLES B
WHERE A.table_name = B.table_name and collation_name is not null
ORDER BY B.table_name

table_name    column_name   collation_name             data_type
-----------------------------------------------------------------------------------
Address       AddressLine1  Latin1_General_CS_AS        nvarchar
Address       AddressLine2  Latin1_General_CS_AS        nvarchar
...

Etape 1 : Générer le script de création de la base et de tous ses objets

A Partir de Management Studio, cliquer sur “Generate Scripts”

—
Choisir la base AdventureWorks,
Cocher l’option “Script All Objects”
Next
—
Choose script Options
Modifier les options suivantes :
Include Descriptive Headers: True
Script Database Create: True
Script Object-Level Permissions : True
Script USE DATABASE : False
Script Full-Text Indexes : True
—
Next
Script to New Query Window
—
Next
—
Finish
—
Close

Etape 2 : Modifier ce script, pour créer une nouvelle base vide avec la bonne collatio

Avec l’outil Find/Replace Remplacer le mot AdventureWorks par AdventureWorks_2
(nom de la nouvelle base)

Modifier la Collation de la base de données avec la valeur voulue en ajoutant la clause collate

CREATE DATABASE
…
( NAME =
N'AdventureWorks_2_Log', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\AdventureWorks_2_Log.ldf' , SIZE = 916352KB , MAXSIZE
= UNLIMITED, FILEGROWTH = 10%)
COLLATE FRENCH_CI_AS
END

Modifier les chemins des fichiers de données en cas de besoin (pour ne pas utiliser les mêmes que la base d’origine).

=> Exécuter le script

Vérifier qu’il n’y a pas d’erreur problématique lors de la création de la base et de ses objetts.
Vérifier la collation de la nouvelle base et de ses colonnes.

Etape 3 : Importer dans la nouvelle base les données les données de l’ancienne

A l’aide SSMS depuis la base AdventureWorks_2, lancer l’assistant d’import de données :

—
Choose a Datasource: AdventureWorks
Next
—
Choose a destination : AdventureWorks_2
Next
—
Copy data from one or more tables or views
Next
—
Ne sélectionner que les tables , pas les vues
Next
—
Execute immediately
Next
Finish
—-

Les lignes de chaque table ont été ajoutées aux tables vides avec la nouvelle collation pour les données type Caractère

L.Hochberg

Continuez votre lecture sur le blog :

• Modifier PAGE_VERIFY après une migration depuis SQL 2000 (David BAFFALEUF) [SQL Server]
• Principes d’une sauvegarde à chaud (David BAFFALEUF) [SQL Server]
• Un trigger fait-il parti d’une transaction ? (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]
• Mythe: SQL Server associe un thread à chaque connexion (David BAFFALEUF) [SQL Server]
• Abonnez-vous au blog de la CapData team ! (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x0000CD53000011DE001000000000, Command ID: 1)

Error messages:
Syntax error, permission violation, or other nonspecific error (Source: MSSQLServer,
Error number: 20025)
Get help: http://help/20025
Syntax error, permission violation, or other nonspecific error (Source: MSSQLServer,
Error number: 42000)
Get help: http://help/42000

Ce n’est même pas la peine d’essayer de voir ce qu’il y a au bout des deux liens http://help/… proposés. En gros l’agent de distribution a trouvé une erreur en essayant d’appliquer une transaction. Le but est de retrouver le code SQL qui correspond à la transaction en échec. Lancer une trace Profiler sur le distributeur ne servira pas à grand chose car la transaction en question sera noyée dans le flot de mises à jour sur les tables systèmes de la réplication. En fait la chose la plus importante dans le log d’erreur est le numéro de transaction: 0×0000CD53000011DE001000000000

Pour retrouver l’ordre correspondant sur le distributeur, utiliser sp_browsereplcmds, regarder la première ligne:

use distribution
sp_browsereplcmds @xact_seqno_start = '0x0000CD53000011DE001000000000'

0x0000CD53000012EE000E	NULL	NULL	1	30	0	0	NULL	NULL
0x00000000000000000000
{CALL sp_MSins_EMAIL MOBILE DATA ('MICHAEL.HEARSLEY#TATOINE.COM', 'J74K6L')}	1

0x0000CD5300001304000F	NULL	NULL	1	30	0	0	NULL	NULL
0x00000000000000000000
{CALL sp_MSins_EMAIL MOBILE DATA ('EDUARDO.ALFONSO#JPMM.COM', 'J91RCX')}	1

0x0000CD530000131A0018	NULL	NULL	1	30	0	0	NULL	NULL
0x00000000000000000000
{CALL sp_MSins_EMAIL MOBILE DATA ('MARK.D.SPELLMAN#ATTACH.CO.UK', 'J731FZ')} 1

0x0000CD530000133B000E	NULL	NULL	1	30	0	0	NULL	NULL
0x00000000000000000000
{CALL sp_MSins_EMAIL MOBILE DATA ('XAVIER.BARTOLDI#NRJ.PICATRIX.COM', 'J92HBF')} 1

...

Effectivement si on lance l’exécution de la proc à la main sur le subscriber:

CALL sp_MSins_EMAIL MOBILE DATA ('MICHAEL.HEARSLEY#TATOINE.COM', 'J74K6L')

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'MOBILE'.

Il y a des espaces dans le nom de la proc. L’assistant graphique de mise en place de la réplication créera la procédure avec des underscores, donc pas de souci, c’est juste pour l’exemple.

[ David B. ]

Continuez votre lecture sur le blog :

• Attention : publication transactionnelle et indexes non clusters (David BAFFALEUF) [SQL Server]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Intérêt de créer des indexes cluster uniques (David BAFFALEUF) [SQL Server]
• Réunion GUSS le 1er juillet ! (David BAFFALEUF) [SQL Server]
• Insert et Update en une seule fois avec MERGE (Guillaume DEFENDINI) [SQL Server]

Je vous répondrai que le problème n’est pas trivial. Tout le monde utilise les vues DMV pour récupérer les infos de la session qui occupe l’espace dans tempdb, notamment le texte SQL de la requête:
- sys.dm_db_task_space_usage: pour voir l’espace utilisé par session.
- sys.dm_exec_requests ou sys.dm_exec_query_stats + cross apply sys.dm_exec_sql_text(sql_handle): pour plus d’infos sur les sessions (stats + texte des requêtes).

Seulement voilà: les sessions dont le plan a été purgé du cache ne sont plus visibles dans les DMV sys.dm_exec… donc la jointure entre sys.dm_db_task_space_usage et sys.dm_exec… ne renverra pas d’infos pour des sessions qui utilisent pourtant bel et bien de l’espace dans tempdb.

Donc il faut avoir recours au bon vieux dbcc inputbuffer pour récupérer tout ça.

1) D’abord, on va créer les tables de recueil.

1.1 DBCCINPUTBUFFER va nous permettre de récupérer le contenu du dbcc inputbuffer par session:

CREATE TABLE tempdb.guest.DBCCINPUTBUFFER
(
EventType varchar(50),
Parameters int,
SQLText varchar(2000)
)

1.2 DBCCSHOWFILESTATS va nous permettre de récupérer l’espace total et utilisé dans tempdb:

CREATE TABLE tempdb.guest.DBCCSHOWFILESTATS
(
[fileid] [int], [filegroup] [int],
[totalextents] [int], [usedextents] [int],
[name] [varchar] (255), [filename] [varchar] (255)
)

1.3 monitorTempdb sera notre table finale d’historique.

CREATE TABLE tempdb.guest.monitorTempdb
(
curdate datetime,
spaceintempdb bigint,
spaceusedintempdb bigint,
spid int,
sqltext varchar(1000),
internal_objects_alloc_MB bigint,
internal_objects_dealloc_MB bigint,
user_objects_alloc_MB bigint,
user_objects_dealloc_MB bigint,
program_name varchar(100),
login_time datetime,
hostname varchar(20),
login_name varchar(100)
)

2) Par session récupérer les infos et le texte de la requête: on va placer les sessions consommatrices ainsi que les espaces consommés dans un curseur, puis boucler pour chaque session_id et récupérer plus d’infos (program_name, login, etc… depuis sys.dm_exec_sessions, la seule DMV qui affiche toutes les sessions actives ou non) et le dernier texte SQL généré:

use tempdb
go
declare
	@spid int,
	@sqltext varchar(1000),
	@internal_objects_alloc_page_count bigint,
	@internal_objects_dealloc_page_count bigint,
	@user_objects_alloc_page_count bigint,
	@user_objects_dealloc_page_count bigint,
	@program_name varchar(100),
	@login_time datetime,
	@hostname varchar(20),
	@login_name varchar(100),
	@SQLQUERY varchar(100),
	@spaceintempdb bigint,
	@spaceusedintempdb  bigint

declare cr_sqltext CURSOR READ_ONLY for
select session_id,internal_objects_alloc_page_count,internal_objects_dealloc_page_count,
user_objects_alloc_page_count, user_objects_dealloc_page_count
from sys.dm_db_task_space_usage where internal_objects_alloc_page_count > 12000 

open cr_sqltext
fetch next from cr_sqltext into
	@spid,
	@internal_objects_alloc_page_count,
	@internal_objects_dealloc_page_count,
	@user_objects_alloc_page_count,
	@user_objects_dealloc_page_count

while @@fetch_status = 0
begin
	select
		@program_name = program_name,
		@login_time = login_time,
		@hostname = host_name,
		@login_name = login_name
	from sys.dm_exec_sessions where session_id = @spid

	select @SQLQUERY = 'dbcc inputbuffer('+convert(char(3),@spid)+')'

	insert into tempdb.guest.DBCCINPUTBUFFER exec (@SQLQUERY)
	select @sqltext = SQLText from tempdb.guest.DBCCINPUTBUFFER 

	insert into tempdb.guest.DBCCSHOWFILESTATS exec ('DBCC showfilestats with NO_INFOMSGS')
	select
		@spaceintempdb = sum(totalextents)/16,
		@spaceusedintempdb = sum(usedextents)/16
	from tempdb.guest.DBCCSHOWFILESTATS

	delete from tempdb.guest.DBCCINPUTBUFFER
	delete from tempdb.guest.DBCCSHOWFILESTATS

	insert tempdb.guest.monitorTempdb values
	(getdate(),
	@spaceintempdb,
	@spaceusedintempdb ,
	@spid, 	@sqltext,
	@internal_objects_alloc_page_count*8192/1048576 ,
	@internal_objects_dealloc_page_count*8192/1048576 ,
	@user_objects_alloc_page_count*8192/1048576 ,
	@user_objects_dealloc_page_count*8192/1048576 ,
	@program_name ,
	@login_time ,
	@hostname ,
	@login_name)

fetch next from cr_sqltext into
	@spid,
	@internal_objects_alloc_page_count,
	@internal_objects_dealloc_page_count,
	@user_objects_alloc_page_count,
	@user_objects_dealloc_page_count

end
close cr_sqltext
deallocate cr_sqltext
go

Cette dernière partie peut être mise dans un job planifié toutes les minutes par exemple. C’est ainsi que j’ai découvert que sp_MSget_repl_commands et sp_MSdistribution_cleanup (procs de répli) peuvent être extrêmement consommatrices lorsque la file des requêtes à répliquer est très importante.

A+ [ David B. ]

Continuez votre lecture sur le blog :

• Planification dans Adaptive Server (David BAFFALEUF) [Sybase]

Globalement, ASE utilise des threads pour supporter toutes les tâches qu’il exécute: requêtes, IO réseau ou disque, connexions utilisateur, tâches internes… en conjonction avec son propre planificateur de tâche.

Le rôle du planificateur de tâche sur tout système est de partager le temps d’un CPU entre plusieurs unités d’exécution (tâches). Il existe deux grandes familles de planificateurs, les préemptifs, et les coopératifs.

Le planificateur préemptif a le pouvoir de planifier et d’interrompre les tâches selon des règles qui lui sont propres et sans tenir compte du type de tâche en cours d’exécution. One size fits all, comme on dit. Ce mécanisme, qui garantit qu’aucune tâche ne va monopoliser les ressources CPU, permet à chaque programme de pouvoir avoir la main sur la CPU à un moment où un autre. Sur la plupart des OS (linux, UNIX System V et Windows NT à partir de la 4.0), le planificateur alloue au thread un bail d’exécution ou un quantième. Si la tâche n’a pas terminé son travail dans le temps imparti, elle est interrompue. Lorsqu’il interrompt la tâche en cours, le planificateur sauvegarde son contexte dans un coin (c’est à dire les structures process + thread qui la représentent en mémoire), et planifie une nouvelle tâche sur la CPU. Ce qu’on appelle un changement de contexte.

Le planificateur coopératif, lui, va au contraire laisser à la tâche le soin de se terminer toute seule. C’est là la plus grosse différence, il n’interrompt pas une tâche qui n’a pas terminé son travail. C’est à la charge de la tâche de rendre la main pour laisser de la place aux autres, c’est donc à elle de prévoir un mécanisme de relâche (to yield = “céder”). Windows 3.1 employait un scheduler coopératif, les Windows 98 et 95 aussi pour assurer la compatibilité avec les programmes 16 bits.

Maintenant, on imagine facilement le risque d’employer un tel système de planification: une tâche qui part en vrille ne libèrera jamais la CPU, ça n’a donc plus été employé pour faire des operating systems.

Micheal Stonebraker avait publié un article de quelques pages au début des années 80* expliquant en gros que les ’services’ apportés par UNIX system V n’étaient pas adaptés au monde de la base de données, et notamment en matière de planification. Les tâches typiques issues des SGBD prennent plus de temps à s’effectuer car il ne s’agit pas de calcul mais d’IOs, et le mode préemptif n’est pas adapté car il ‘coupe’ la dynamique et l’efficacité du SGBD en produisant de nombreux changements de contexte.

Donc les développeurs chez Sybase ont répondu en créant un scheduler coopératif ‘on top‘ **, propre à Adaptive Server et par dessus le planificateur de l’OS, de telle manière à ce qu’il masque les choses vis à vis de celui-ci et limite au maximum les changements de contexte.

- Les tranches de temps:
ASE va donc fonctionner comme un OS, mais avec ses règles à lui: il gère sa propre planification, son propre partage du temps CPU, ses propres valeurs de quantième. Car bien que coopératif, il doit pouvoir stopper une tâche qui ne répond plus et éviter la saturation d’un engine. Lorsqu’il planifie une tâche, il va lui allouer un bail. Ce bail n’est pas une valeur temporelle, c’est un nombre de ticks d’horloge qui est décrémenté. Il est calculé en divisant le paramètre ‘timeslice’ par ’sql server clock tick length’. Comme les deux valeurs sont à 100 ms par défaut, le nombre de ticks d’horloge autorisé est de 1. Dans le déroulement de son exécution, une tâche va passer par des parties de code qu’on appelle des yield points, au cours desquels elle va prendre une seconde pour vérifier qu’elle n’a pas dépassé son bail. Si elle découvre que le bail est excédé (bail < 0), elle va demander à ASE de lui accorder une grace supplémentaire (cpu grace time), qui peut aller jusqu’à 500 ticks (soit 50 secondes). Si elle n’a pas terminé au delà de cette valeur, alors ASE sort les gros moyens, termine la tâche, annule ses transactions et affiche une stacktrace dans l’errorlog:

00:00000:00005:2007/04/02 22:09:24.07 kernel  timeslice -501, current process infected
00:00000:00005:2007/04/02 22:09:24.75 kernel  ************************************
00:00000:00005:2007/04/02 22:09:24.77 kernel  curdb = 1 tempdb = 2 pstat = 0x200
00:00000:00005:2007/04/02 22:09:24.77 kernel  lasterror = 0 preverror = 0 transtate = 1
00:00000:00005:2007/04/02 22:09:24.77 kernel  curcmd = 0 program =
...

Tout ceci se passe toujours sans que l’OS ne sache rien, car de son côté, le processus dataserver est toujours en exécution. C’est le but: l’OS ne doit pas interrompre le processus dataserver puisque celui-ci semble toujours travailler.

- Runnable Process search count:
Toujours dans le souci de préserver son exécution vis à vis de l’OS, lorsqu’il a terminé d’exécuter des tâches, et plutôt que de rendre la main tout de suite, Adaptive Server va entrer dans une boucle de vérification des IOs en attente côté réseau puis côté disque. S’il ne trouve rien, il va tourner (spinning) pour rechercher de nouvelles tâches en attente d’exécution, puis de nouvelles IOs réseau, puis de nouvelles IOs disques, etc… tout ça 2000 fois par défaut (runnable process search count), même s’il n’y a rien à traiter dans aucune file d’attente. Ce qui donne une apparente sensation d’hyperactivité côté OS, alors que côté Adaptive Sever, l’électro-encéphalo est plutôt plat.

Pour illustrer tout ça, une petite explication de la section ‘kernel’ de sp_sysmon:

Kernel Utilization
------------------

Your Runnable Process Search Count is set to 5000
and I/O Polling Process Count is set to 10

Engine Busy Utilization        CPU Busy   I/O Busy       Idle
------------------------       --------   --------   --------
Engine 0                       80.0 %      7.5 %     12.5 %
Engine 1                       80.8 %      6.0 %     13.2 %
Engine 2                       82.5 %      5.7 %     11.9 %
Engine 3                       84.0 %      6.7 %      9.3 %
Engine 4                       79.0 %      6.8 %     14.2 %
Engine 5                       80.0 %      7.3 %     12.7 %
Engine 6                       79.6 %      8.3 %     12.0 %
------------------------       --------   --------   --------
Summary           Total         565.9 %     48.4 %     85.7 %
Average                         80.8 %      6.9 %     12.2 %

Avant de commencer, il faut bien rappeler le cheminement de la boucle:

Chaque engine tient le compte de ticks reçus de l’OS (par le biais de signaux SIGALRM) tous les 100ms, et les répertorie selon trois catégories:

CPU Busy: représente le temps (en fait le nombre de ticks ramené en pourcentage) passé par l’engine à exécuter une tâche sur l’intervalle.
Idle: représente le temps passé par l’engine à n’exécuter aucune tâche sur l’intervalle.
I/O Busy: est une soustraction de Idle. Il s’agit du temps passé par l’engine à n’exécuter aucune tâche (donc Idle) mais où il y avait au moins une IO disque en attente dans la file. Elle représente donc la quantité de temps où l’engine n’a pas pu exécuter une tâche parce qu’il y avait au moins une IO disque en attente.

On peut retrouver ces valeurs brutes en interrogeant les variables globales @@cpu_busy, @@idle, et @@io_busy. La durée d’un tick peut être retrouvée avec @@timeticks.

A+. [ David B. ]

*: Michael Stonebraker, Operating System Support for Database Management, CACM 24(7), p412-418 (1981).
**: et Microsoft fit de même en dotant SQL Server 7.0 de son propre scheduler coopératif (UMS), toujours bien présent en version 2008 sous une autre dénomination (SQLOS).

Continuez votre lecture sur le blog :

• Scruter les journaux d’évènements Windows avec LogParser (David BAFFALEUF) [SQL Server]
• Abonnez-vous au blog de la CapData team ! (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]
• Intérêt de créer des indexes cluster uniques (David BAFFALEUF) [SQL Server]
• Que faire des “[Warning] Aborted connection” avec MySQL ? (Cédric PEINTRE) [MySQL]
• Création d’un Dataguard physique (Guillaume DEFENDINI) [Oracle]

WARNING
Although Linux includes support for rawio, it is now a deprecated interface. If your application performs device access using this interface, Red Hat encourages you to modify your application to open the block device with the O_DIRECT flag. The rawio interface will exist for the life of Red Hat Enterprise Linux 5, but is a candi-
date for removal from future releases.

Le bénéfice du raw device n’est pas nouveau, que ce soit pour Adaptive Server ou Oracle, le fait d’écrire directement sur la partition brute a deux avantages majeurs pour les SGBD:
1 – Ca va plus vite.
2 – Ca garantit que ce que l’on écrit va bien sur le disque et non dans un buffer en mémoire. En effet, le système de base de données gère lui-même la bufferisation de ses pages de données et d’indexes, et ne doit pas se reposer sur le système pour ça.

A partir du moment où le raw device cesse d’être supporté, on va devoir écrire dans un fichier, et qui dit écriture dans un fichier sous UNIX/Linux dit écriture bufferisée. C’est là où le critère 2 n’est plus garanti.

Il reste alors deux solutions qui vont nous permettre de garantir celà: DIRECTIO ou DSYNC. Ce sont en fait des arguments qui vont être passés à la primitive d’ouverture du fichier, le plus souvent open(), sous la forme de flags: O_DIRECT, O_DSYNC ou O_ASYNC.

A travers Adaptive Server, ces arguments seront manipulés lors de la création de devices avec la commande disk init:

disk init name='PERFSTATS_data01', physname='/sybase_data/ASE1502/userdb/PERFSTATS_data01.dat',
size=512000,
directio=true

ou

disk init name='PERFSTATS_data01', physname='/sybase_data/ASE1502/userdb/PERFSTATS_data01.dat',
size=512000,
dsync=true

On ne peut pas utiliser à la fois directio=true et dsync=true pour le même device, les options sont mutuellement exclusives.

DIRECTIO:
Lorsque directio est à true, le fichier PERFSTATS_data01.dat sera ouvert avec le flag O_DIRECT:

open("/sybase_log/ASE1502/userdb/PERFSTATS_data01.dat", O_RDWR|O_SYNC|O_DIRECT|O_LARGEFILE) = 17

Lorsque la primitive d’écriture est invoquée (write(), aiowrite()), l’écriture va directement sur le disque. Donc sémantiquement équivalent à ce que donne une écriture sur un raw device, à la fois en termes de performances et de garantie.

DSYNC:
Lorsque dsync est à true, le fichier PERFSTATS_data01.dat sera ouvert avec le flag O_DSYNC:

open("/sybase_log/ASE1502/userdb/PERFSTATS_data01.dat", O_RDWR|O_DSYNC|O_LARGEFILE) = 17

Lorsque dsync est à false, le fichier PERFSTATS_data01.dat sera ouvert avec le flag O_ASYNC:

open("/sybase_log/ASE1502/userdb/PERFSTATS_data01.dat", O_RDWR|O_ASYNC|O_LARGEFILE) = 17

Lorsque la primitive d’écriture est invoquée, l’écriture sera bufferisée mais en fonction de la valeur passée à disk init (true ou false), certains éléments vont être synchronisés sur disque avant que aiowrite() ne revienne au programme appelant.
- dsync=true: alors le flag O_DSYNC est passé à open(). Ce flag garantit que write() reste en attente tant que les données n’ont pas été synchronisées sur disque.
- dsync=false: alors le flag O_ASYNC est passé à open(). Ce flag indique que write() rend la main une fois l’écriture bufferisée mais AVANT la synchronisation sur disque, ce qui ne garantit pas notre critère 2.

On recommandera toujours de mettre dsync=true pour les devices de données et de journaux pour avoir la garantie d’écriture stabilisée, et dsync=false pour les devices tempdb pour favoriser la perf.

- Avantages / Inconvénients pour le DSYNC:
Avantages: c’est complètement supporté par les file system.
Inconvénients: C’est moins performant en écriture.

- Avantages / Inconvénients pour le DIRECTIO:
Avantages: c’est comme du raw device, sauf que c’est dans un fichier. C’est même plus efficace en lecture.
Inconvénients: il faut que le filesystem le supporte (c’est le cas pour ext3), et d’après Linus l’implémentation de directio au niveau du kernel est épouvantable. Le problème c’est que ce qu’il indique comme contournement à destination des développeurs ASE (Wim Ten Have et Dave Wein en tête) n’est pas vraiment applicable.

Pas de doute, le DIRECTIO reste la meilleure option. Ce sera sûrement réécrit un jour, mais pour l’instant on n’a pas trouvé mieux.

Un dernier mot sur le dsync=true sur les noyaux 2.6+, l’écriture asynchrone se transforme en écriture synchrone et les perfs chutent (( . Il faut vraiment utiliser directio sur les noyaux récents. Ce sera peut être le sujet d’un prochain post sur ASE.

A+ [ David B. ]

Continuez votre lecture sur le blog :

• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Création d’un Dataguard physique (Guillaume DEFENDINI) [Oracle]
• Création et utilisation d’OCFS2 (Thierry GASCARD) [Oracle]

Hélas, ce n’est pas plus facile qu’un autre SGBD. C’est juste plus facile de se tromper. Voici quelques règles de bases lorsqu’on s’apprête à installer un SQL Server en production. Episode 1: le matériel.

1) La machine: physique ou virtuelle ?
Le gros débat des mois à venir. Même si la plupart des clients vont conserver des machines physiques pour leurs bases critiques, il y aura des bases de prod, uat, dev, qualif moins critiques qui seront virtualisées.
Les constructeurs (Dell, HP,…) sortent de plus en plus des chassis dédiés à la virtualisation, avec ESXi embarqué. La virtualisation est (heureusement ? maheureusement ?) un train en marche, et plutôt que de freiner des quatre fers, posons-nous la question “comment virtualiser”, parce qu’il ne faut pas se voiler la face, on va y venir de toutes façons, ce n’est qu’une question de temps. HYPER-V et ESX sont tous les deux de bons concurrents, et ESX est annoncé compatible avec les produits MS. La combinaison des technos aujourd’hui (full virtualisation + assistance matérielle) fait que l’overhead de performances se réduit de plus en plus (on est quasiment en CPU-direct sur les Intel-VT et les AMD-V). Je penche personnellement pour le raw device mapping, une assistance matérielle CPU et une configuration mémoire appropriée. Dans quelques temps nous aurons un ESX ou deux et nous pourrons tester tout ça.

2) Admettons, machine physique:
LE PRIX ! Franchement les configurations à moins de 8000€ sont assez répandues et fiables. Je pense au HP Proliant DL 580 G5 (2×4 Xeon 3Ghz, 4Gb RAM, 2 cartes 1ge et 16 slots disque pour $7600) ou au très populaire Dell PowerEdge 2950 III (même config mais avec seulement 6 slots disques pour $3600).

DEDIEE/MUTUALISEE: en fonction des moyens. Mais idéalement il faut une machine dédiée. Les petits budgets vont faire tourner IIS et SQL Server sur la même machine, et d’expérience, c’est un mauvais calcul. Il vaut mieux investir un peu plus dans deux machines, et on aura moins de soucis ensuite.

CPU: le plus de cores il a sous le coude, et le mieux il se porte, SQL Server. Il est multithreadé, il sait parralléliser ses traitements. Evidemment il faut partir directement en 64 bits (EM64T, IA64, AMD64). Attention les éditeurs commencent à revoir leur politique de licence par socket, ils perdaient trop d’argent avec les hexacores et certains commencent à limiter à 6 cores par socket. Et attention à ne pas activer l’hyperthreading sur les machines SQL Server.

MEMOIRE: Oublier le 32 bits, la version 2008 de Windows Server sera la dernière à être proposée dans ce mode. En 64 bits, et avec le prix de la DDR2, il ne faut pas lésiner. C’est encore plus important que la CPU. Plus le cache de données est gros, moins SQL Server fera de lectures physiques.

RESEAU: Ca dépend. Je suis intervenu chez un client DRM il y a quelques temps et là le réseau c’était VRAIMENT crucial. Ca va le devenir de plus en plus avec FILESTREAM, la spatialisation, la vidéo HD, le plus de données on stocke, et le plus de données on va faire transiter. Les cartes et les core switches en 10Ge sont là, les Jumbo Frames, donc tout ça suit derrière. Le réseau entre systèmes Production / Secours est aussi crucial (Database mirroring, réplication). Nous avons un client en database mirroring 2005 qui avait installé un lien 100Mb entre ses deux sites, il va passer en fibre car il a de plus en plus de choses à faire passer sur le lien en plus du streaming lié au DB mirroring (sauvegardes, recopies de fichiers, etc…). Autre exemple, un client qui a une réplication transactionnelle entre Frankfort et Londres, le lien est vraiment très lent, et la resynchro de répli (34Gb) se fait en moyenne en 10-12 heures.
Mais si l’application est codée en procédures stockées, que la quantité de données qui transite n’est pas importante et que SQL server est standalone, alors une carte 100Mb/s devrait faire l’affaire.

DISQUES: tout le monde ne peut pas se payer un SAN, je pense aux petites PME du web avec 1 machine serveur, un IIS et une base de données de production, avec des moyens modestes. Donc on va considérer deux configurations: les disques internes pour les petits, et le stockage en réseau pour les riches et la classe moyenne.
– DISQUES INTERNES: choisir du SAS en 15KRPM. Le disque pour la base de données, c’est le nerf de la guerre. Le HP DL580 G5 avec ses 16 slots est parfait, il permet de faire trois RAID10 (data, log, backup) et un RAID1 (système) et de garder deux slots de spare. Il faudra vérifier au niveau de l’interface du contrôleur SAS que celui-ci est bien sur batterie et que celle-ci est valide, et que la politique est en write-through, c’est à dire que le cache du contrôleur n’est pas utilisé en écriture. Les disques SAS 73 Gb 15KRPM sont à 330€ chez Dell en 2′5, ça ne vaut pas le coup de se priver.
- STOCKAGE RESEAU: ne pas utiliser de NAS au sens strict (attachement réseau), pas de CIFS, ce n’est pas supporté de base par SQL Server. Il faut que les disques soient vus comme des disques locaux, donc soit une baie avec un attachement direct, soit un SAN. Pour les riches, le SAN en fibre channel est à privilégier. Pour la classe moyenne, le iSCSI est un bon moyen de faire du SAN à moindre coût, à la condition d’utiliser sur la machine hôte un initiateur matériel et non logiciel, c’est à dire une carte 1Ge qui soit compatible TSO (TCP Segmentation Offload, calcul de la pile TCP déchargée du CPU hôte), et que le réseau soit dédié au stockage. Pour les disques SAN, même politique que les disques internes. Au niveau du choix de la baie, il faut regarder plusieurs choses: Le nombre de storage processors (va déterminer le multiplexage des entrées, il en faut au moins 2), la taille du cache par storage processor et non en globalité, le nombre de slots pour les disques, la capacité globale. Le plus important c’est la taille du cache par SP. Si les caches entre SP sont mirrorés l’un sur l’autre, on perd la moitié de la capacité mémoire, si la baie est mirorrée sur une autre, on perd encore car le soft de miroir utilise des buffers dans le cache de chaque SP. Ensuite, il faut voir comment le cache est partitionné entre les lectures et les écritures. Le plus souvent, c’est fait à la louche pour des systèmes majoritairement en lecture, mais attention à ne pas négliger les écritures en transactionnel. Un cache en écriture plein, et les queues se remplissent au niveau des SP, on observe des outstanding IOs côté SQL Server et c’est rapidement la cata.

Il paraît que je fais des articles trop longs, donc ce sera tout pour aujourd’hui
Prochain épisode, l’installation Windows et les prérequis SQL Server.

A+ [ David B. ]

Continuez votre lecture sur le blog :

• Mythe: TORN PAGE DETECTION est moins coûteux que CHECKSUM (David BAFFALEUF) [SQL Server]
• Un trigger fait-il parti d’une transaction ? (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]
• Attention aux requêtes en double avec Windev et MySQL ! (Cédric PEINTRE) [MySQL]
• Mythe: SQL Server associe un thread à chaque connexion (David BAFFALEUF) [SQL Server]
• Fragmentation sur des tables stockées en S-GAM (David BAFFALEUF) [SQL Server]

Explication: lorsque l’agent de snapshot doit extraire le DDL des articles et sortir les données par bcp en parrallèle, il se base sur deux options passées lors du sp_addarticle:
- @type: qui va indiquer à quel type de publication on a affaire. Dans notre cas, ce sera logbased (=transac)
- @schema_option : bitmask qui va indiquer ce qui doit être généré au niveau DDL.

D’après les BOLs 2005, si @type = ‘logbased’, alors @schema_option doit être à 0×30F3, qui décomposé contient bien à la fois le bitmask pour les index clusterisés (0×10) et celui pour les indexes NC (0×40).

Je vous engage à tester ça depuis SSMS, en créant une publication transac bidon et en jetant un coup d’oeil aux propriétés de l’article:

Dans le script généré, effectivement on a bien un problème:

exec sp_addarticle
@publication = N'pubtest1',
@article = N'Address',
@source_owner = N'dbo',
@source_object = N'Address',
@type = N'logbased',
@description = null,
@creation_script = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'manual',
@destination_table = N'Address',
@destination_owner = N'dbo',
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_dboAddress',
@del_cmd = N'CALL sp_MSdel_dboAddress',
@upd_cmd = N'SCALL sp_MSupd_dboAddress'
GO

Le bitmask 0×803509F contient bien le 0×10, mais pas le 0×40. Pour en avoir le coeur net, utilisez le ET logique en transac (134434975=0x803509, 64=0x40, 16=0x10):

select 134434975 & 64
select 134434975 & 16

-----------
0

-----------
16

Suite à une petite discussion avec Paul Randal, il se trouve que nombre de ses clients ont eu le même problème en migrant. Donc si vous migrez une réplication de 2000 vers 2005, et que vous constatez des problèmes de perfs sur les abonnés, vérifiez si vous avez tous vos indexes NC, juste comme ça…

A+ [ David B. ]


Continuez votre lecture sur le blog :

• Retrouver une transaction en échec (David BAFFALEUF) [SQL Server]
• Attention aux requêtes en double avec Windev et MySQL ! (Cédric PEINTRE) [MySQL]
• XtraDB sauvegarde votre cache, et ça marche ! (Cédric PEINTRE) [MySQL]
• MySQL et les tables temporaires internes (Cédric PEINTRE) [MySQL]
• Insert et Update en une seule fois avec MERGE (Guillaume DEFENDINI) [SQL Server]

Nous allons créer un système de fichiers supportant le cluster (Cluster FileSystem).
L’OS utilisé est une SUSE 10 en 64 bits.

1. Installation du driver OCFS2

A effectuer via l’utilisateur système root.
OCFS2 1.4 est compris dans la distribution SLES10 SP2.
sinon sous les autres linux :
Déterminer la version du noyau : uname –rm
Rechercher les drivers sur le site d’Oracle
Télécharger les fichiers correspondants à la plate forme et la version du noyau (ici RH5 64 bits)

• ocfs2-tools-1.4.1-1.el5.x86_64.rpm
• ocfs2console-1.4.1-1.el5.x86_64.rpm
• ocfs2-2.6.18-128.1.1.el5-1.4.1-1.el5.x86_64.rpm

Installer les RPMs sur chaque noeud :
rpm -Uvh ocfs2*.rpm
vérifier l’installation
chkconfig –list o2cb

2. Configuration OCFS2

1) Sur un des nœuds en x window lancer ocfs2console puis cluster/configure node
ajouter hostname1   x.x.x
ajouter hostname2   x.x.x
puis propager la configuration
Rem : attention au nom de domaine
2) vérifier sur chaque nœud le fichier /etc/ocfs2/cluster.conf
3) configurer le service o2cb sur chaque nœud en root
/etc/init.d/o2cb configure

puis répondre y et accepter les valeurs par défaut sauf Use user-space driven heartbeat? (y/n) [y] n

3. Formater la partition en OCFS2 sur un nœud

en x window lancer ocfs2console  puis task/format

• /dev/emcpowerd
• label : oracle
• cluster size : 128 K
• block size 4k

4. Monter le système de fichiers OCFS2

1) créer un point de montage sur chaque nœud
mkdir /ocfs
2) ajouter dans /etc/fstab, voire la note metalink 835839.1
/dev/emcpowerd /ocfs    ocfs2    _netdev,datavolume,nointr,noatime 0 0

les options correspondent avec ocfs2 1.4.1 :

• datavolume : utilisation des “direct IO”
• _netdev : montage après le réseau
• nointr: désactive les siganux durant les opération de cluster
• noatime : désactive la mise à jour de l’heure

Continuez votre lecture sur le blog :

• Installation Oracle 64 bits sur Red Hat 5 (Thierry GASCARD) [Oracle]
• Se connecter à SQL Server à travers Oracle, quelle drôle d’idée ? (Thierry GASCARD) [Oracle]
• Installation Oracle 11gR2 64 bits sur Red Hat 5 Partie 1 (Thierry GASCARD) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Utiliser ASMCMD (Thierry GASCARD) [Oracle]

Un utilitaire asmcmd permet de naviguer dans les disques ASM.
Voici quelques commandes utiles.
Avant de se connecter, vérifier que les processus de l’instance ASM sont lancés
ps –ef |grep ASM
export ORACLE_SID=+ASM
export ORACLE_HOME=/u01/app/asm/asm_1

Afficher la version : asmcmd –v
Lancer asmcmd sous l’utilisateur asm ou oracle
Asmcmd>
Lister les disques groupes ( affiche également la taille des disques et l’espace libre dans les disques ) : lsdg
Lister les disques : lsdsk
Avoir les io sur les disques : lsdsk -s
Vérifier le status des disques : lsdsk –p
Aller dans un groupe :  cd +diskgroup1/
Afficher l’espace total utilisé : du
Trouver tous les fichiers d’un type dans un diskgroup :
find –t archivelog  + DISKGROUP1/ *
find –t datafile +DISKGROUP1/  *
find –t onlinelog +DISKGROUP1/ *
find –t tempfile +DISKGROUP1/ *
find –t backupset +DISKGROUP1/  *
find –t parameterfile +DISKGROUP1/  *
find –t dataguardconfig + DISKGROUP1/  *
find –t flashback +DISKGROUP1/ *
find –t dumpset + DISKGROUP1/  *
Trouver les fichiers sans le type mais avec une wildcard : find +DISKGROUP1/  undo*
Créer une sauvegarde contenant les métadonnées pour un ou plusieurs groupes  :
md_backup –b /u01/bdd/backupall
md_backup –b /u01/bdd/backupdiskgroup1 –g diskgroup1
Restaurer des métadonnées d’un diskgroup :
md_restore –b backup_file ( lit les métadata de backup_file )Continuez votre lecture sur le blog :

• SQLDIAG (épisode 1) (David BAFFALEUF) [SQL Server]
• Installation Oracle 11gR2 64 bits sur Red Hat 5 Partie 1 (Thierry GASCARD) [Oracle]
• Création et utilisation d’OCFS2 (Thierry GASCARD) [Oracle]
• Se connecter à SQL Server à travers Oracle, quelle drôle d’idée ? (Thierry GASCARD) [Oracle]
• Installation Oracle 64 bits sur Red Hat 5 (Thierry GASCARD) [Oracle]

1. Installation et configuration du driver ASMLIB

Sous l’utilisateur root

1.1    Installation ASMLIB

- Déterminer la version du noyau : uname –rm
- Rechercher les drivers ASM sur le site d’Oracle
- Télécharger les fichiers correspondants à la plate forme et la version du noyau
oracleasm-support-2.1.3-1.SLE10.x86_64.rpm
oracleasmlib-2.0.4-1.SLE10.x86_64.rpm
oracleasm-2.6.16.60-0.39.3-smp-2.0.5-1.SLE10.x86_64.rpm
- Installer les RPM correspondants :
rpm -Uvh \
> oracleasm-2.6.16.60-0.39.3-smp-2.0.5-1.SLE10.x86_64.rpm \
> oracleasmlib-2.0.4-1.SLE10.x86_64.rpm \
> oracleasm-support-2.1.3-1.SLE10.x86_64.rpm

1.2 Configuration du driver ASM

- Lancer la commande de configuration
# /etc/init.d/oracleasm configure
- Renseigner les informations suivantes :
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration:           [  OK  ]
Creating /dev/oracleasm mount point:                       [  OK  ]
Loading module “oracleasm”:                                [  OK  ]
Mounting ASMlib driver filesystem:                         [  OK  ]
Scanning system for ASM disks :                            [  OK  ]

1.3 Créer les volumes ASM

/usr/sbin/asmtool -C -l /dev/oracleasm -n VOLDB1-s /dev/emcpowerb -a force=yes
/usr/sbin/asmtool -C -l /dev/oracleasm -n VOLBK1 -s /dev/emcpowerc -a force=yes
puis redémarrer le serveur

Rem : oracleasm createdisk ne fonctionne pas note Metalink : 469163.1
erreur de reconnaissance des disques fournis avec powerpath
# /etc/init.d/oracleasm createdisk VOLDB1 /dev/emcpowerb
Marking disk “/dev/emcpowerb” as an ASM disk: asmtool: Device “/dev/emcpowerb” is not a partition
[FAILED]

1.3 Vérifier

lancer la découverte
/etc/init.d/oracleasm scandisks
Vérifier l’existence des volumes sur les deux noeuds
# /etc/init.d/oracleasm listdisks

1.4 configurer les chemins de découverte des disques

Modifier le fichier /etc/sysconfig/oracleasm pour paramétrer la gestion du  “multipathing” en gras :

..
# ORACLEASM_SCANORDER: Matching patterns to order disk scanning
ORACLEASM_SCANORDER=”emcpower”

# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan
ORACLEASM_SCANEXCLUDE=”sd”

2. Installation ASM 11g

Dans son propre ORACLE_HOME (ORACLE_ASM_HOME)

3. Passage patch 11.1.0.7

4. Création de l’instance ASM

4.1. Configuration du listener pour ASM

Connexion avec l’utilisateur asm ou oracle sinon
export ORACLE_HOME=/u01/app/asm/11.1.0/asm_1
$ORACLE_HOME/admin/netca &
Creation du listener LISTENER_ASM sur le port 1521 en TCP

4.2 Création de l’instance ASM

Connexion avec l’utilisateur système oracle
export ORACLE_SID=+ASM1
/u01/oracle/11g/asm/bin/dbca &

puis configurer les “disques groupe”Continuez votre lecture sur le blog :

• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Installation Oracle 64 bits sur Red Hat 5 (Thierry GASCARD) [Oracle]
• Création d’un Dataguard physique (Guillaume DEFENDINI) [Oracle]
• Installation Oracle 11gR2 64 bits sur Red Hat 5 Partie 1 (Thierry GASCARD) [Oracle]
• Création et utilisation d’OCFS2 (Thierry GASCARD) [Oracle]

1 Configuration système

1.1 Pré-requis matériel :

- 12 Go pour les binaires Oracle en RAID 10
- 400 Mo sur /tmp

1.2 Pré-requis logiciel :

RH 5 Entreprise 64 Bits : Oracle 10.2.0.1 Entreprise Edition Linux x86-64

Patch 10.2.0.4 pour Linux x86-64 sur le site du support Oracle

Patch 6620371 pour Linux 64 Bits sur le site du support Oracle

1.3 Pré-requis noyau

version du noyau >=2.6.18-8
SELinux désactivé
X installé et configuré
Rpms suivants installés en plus des Rpms par défaut (version minimum) :
binutils-2.17.50 (x86_64)
compat-db-4.2.52 (x86_64)
compat-libstdc++-296(i386)
compat-libstdc++-33-3.2.3 (x86_64)
compat-libstdc++-33-3.2.3(i386)
control-center-2.16.0 (x86_64)
gcc-4.1.1 (x86_64)
gcc-c++-4.1.1 (x86_64)
glibc-2.5-12 (x86_64)
glibc-2.5-12 (i686)
glibc-common-2.5-12 (x86_64)
glibc-devel-2.5-12 (x86_64)
glibc-devel-2.5-12(i386)
glibc-headers-2.5-12 (x86_64)
ksh-20060214-1.4 (x86_64)
libaio-0.3.96 (x86_64)
libgcc-4.1.1(i386)
libgcc-4.1.1(x86_64)
libgnome-2.16.0 (x86_64)
libgnomeui-2.16.0 (x86_64)
libgomp-4.1.1 (x86_64)
libstdc++-4.1.1 (x86_64)
libstdc++-devel-4.1.1 (x86_64)
libXp-1.0.0-8 (i386)
make-3.81 (x86_64)
sysstat-7.0.0 (x86_64)
unixODBC-2.2.11-7.1 x86_64.
unixODBC-devel-2.2.11-7.1 x86_64
util-linux-2.13-0.50.el5 x86_64
xorg-x11-xinit-1.0.2-15.el5 x86_64

Rem : attention à la présence de rpms 32 bits : utilisation possible de l’option force : rpm -ivh –force ..

1.4 Paramètres noyau

Modification du fichier /etc/sysctl.conf :
kernel.shmmni = 4096
kernel.sem= 250 32000 100 128
kernel.sysrq=1
kernel.msgmnb=65536
kernel.msgmni=2878
kernel.msgmax=8192
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max=2097152
net.core.wmem_default = 262144
net.core.wmem_max = 262144
fs.aio-max-nr=3145728
fs.file-max=327679
vm.lower_zone_protection=100
kernel.shmall = 3145728
kernel.shmmax = 12884901888
vm.nr_hugepages = 6144

Modification du fichier /etc/security/limits.conf :
oracle hard memlock 12582912
oracle soft memlock 12582912
oracle soft core unlimited
oracle hard nofile 131072
oracle hard nproc 131072
oracle soft nproc 131072
oracle soft nofile 131072
oracle hard core unlimited

2    Préparation système

2.1 Création groupe oinstall,dba

root> groupadd -g 55 oinstall
root>groupadd -g 56 dba

2.2    Ajout utilisateur oracle

root> useradd -u 500 -g 55 -G dba -c “Oracle Owner” -d /home/oracle -s /bin/bash oracle
root> passwd oracle

2.2 Modification du fichier /etc/profile
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
ulimit -l 12582912
else
ulimit -u 16384 -n 65536 -l 12582912
fi
fi

2.2    Création des répertoires

root> mkdir /opt/oracle
root> mkdir /u01
root> chown –R oracle:oinstall /opt/oracle
root> chown –R oracle:oinstall /u01
root> su – oracle
mkdir -p /opt/oracle/product/10.2.0/db_1
mkdir -p /opt/oracle/admin

3 Installation Oracle

3.1    Décompression des zips et cpio

$ cat 10201_database_linux_x86_64.cpio.gz | gunzip | cpio -idvm
$ cat 10201_companion_linux_x86_64.cpio.gz | gunzip | cpio –idvm
$unzip p6810189_10204_Linux-x86-64

3.2   installation

export ORACLE_BASE=/opt/oracle/product ;
export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
./runInstaller &

Ordre d’installation
1. Installation “Base Product (10.2.0.1)”
2. Installation “Companion Disk” pour éviter l’erreur NCOMP lors du patch 10.2.0.4
- Choisir l’option «Oracle Database 10g Products 10.2.0.1.0»
3. Installation Patch (10.2.0.4)
4. Installation patch 6620371 pour “huge page”
[oracle]$ cd 6620371/
[oracle]$ /opt/oracle/product/10.2.0/db_1/OPatch/opatch apply

4    Mise à jour de l’environnement Oracle

4.1 mise à jour du .profile_bash d’oracle

4.2 mise à jour du glogin.sql

vi $ORACLE_HOME/slplus/admin/glogin.sql
set sqlprompt “_user ‘on’ _date ‘at’ _connect_identifier >”

5    configuration de huge page sous linux

- vm.nr_hugepages  : nombre de pages (taille de 2Mo sur linux avec grep Hugepagesize /proc/meminfo)
- vm.hugetlb_shm_group : ID du groupe utilisé par oracle (dba)
- kernel.shmmax supérieur à la taille de l’espace mémoire accordé aux “huge page”
- memlock  : avec /etc/security/limit.conf et ulimit -l
Tout est bien expliqué dans le blog d’ arkzoyd: à lire absolument !!!!!Continuez votre lecture sur le blog :

• Création et utilisation d’OCFS2 (Thierry GASCARD) [Oracle]
• Installation Oracle 11gR2 64 bits sur Red Hat 5 Partie 1 (Thierry GASCARD) [Oracle]
• Se connecter à SQL Server à travers Oracle, quelle drôle d’idée ? (Thierry GASCARD) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Utiliser ASMCMD (Thierry GASCARD) [Oracle]

BACKUP DATABASE maBase to disk='D:\MSSQL.1\MSSQL\Backup\maBase.bak' with init, stats

10 percent processed.

21 percent processed.

32 percent processed.

43 percent processed.

54 percent processed.

60 percent processed.

71 percent processed.

82 percent processed.

93 percent processed.

Processed 144 pages for database 'maBase', file 'maBase' on file 1.

100 percent processed.

Processed 2 pages for database 'maBase', file 'maBase_log' on file 1.

BACKUP DATABASE successfully processed 146 pages in 0.816 seconds (1.463 MB/sec).

Et voilà. L’avantage d’une telle sauvegarde est que je peux la lancer sans stopper mon service, sous entendu avec de l’activité: connexions utilisateurs, transactions en cours, etc… Et tout ça en me garantissant de restaurer une base transactionnellement cohérente. Pour y parvenir, elle commence par sauvegarder les données puis les transactions présentes dans le journal.

Avant de parler de ce que fait le BACKUP DATABASE, il faut parler un peu des rôles respectifs du CHECKPOINT et du COMMIT dans le processus de RECOVERY.

i) Lorsqu’un utilisateur initie une transaction, ses modifications sont d’abord journalisées en mémoire dans une zone qui est réservée pour sa session. Puis les pages de données et d’index concernées sont modifiées dans le cache de données.

ii) Lorsqu’il valide sa transaction de manière implicite ou explicite (avec COMMIT TRANSACTION), le contenu de son journal de session est enregistré physiquement dans le fichier LDF. Il est impératif de s’assurer que le COMMIT ne peut rendre la main à la commande appelante que lorsque la transaction a été véritablement écrite sur disque (le ‘write-ahead logging’ en d’autres termes). Ce mécanisme permet de pouvoir récupérer la transaction validée lorsqu’un problème survient avant que les pages de données n’aient pu être écrites.

iii) De manière complètement indépendante et asynchrone, le processus d’arrière plan CHECKPOINT écrit les pages de données et d’index modifiées (dirty pages) sur le disque, sans se préoccuper de savoir si elles appartiennent à une transaction validée ou non.

A partir de là, deux situations peuvent se produire au point de crash:

1) Une transaction peut être validée (committée) par l’utilisateur mais le CHECKPOINT n’a pas eu le temps d’écrire les pages de données / d’index sur disque. Il va falloir réécrire ces pages dans le fichier de données au redémarrage de l’instance (REDO ou ROLL-FORWARD).

2) Le CHECKPOINT qui n’a pas de vision sur l’état de la transaction peut avoir écrit ses pages sur disque avant qu’elle n’ait été validée par l’utilisateur. Auquel cas il faudra défaire les pages écrites car au point de crash car la transaction ne peut être considérée comme valide. (UNDO ou ROLLBACK).

C’est la combinaison de ces deux actions que l’on appelle le RECOVERY. Ce processus intervient dans de nombreux changements d’état de la base, et notamment pour ce qui nous intéresse, lors de la phase de restauration d’une sauvegarde.

Lorsque le BACKUP DATABASE est exécuté, il commence par forcer l’exécution d’un CHECKPOINT dans la base concernée. Ce CHECKPOINT aura pour effet d’entériner des transactions validées depuis ma dernière sauvegarde de journal : Les pages de données / d’index sont écrites sur disque, la sauvegarde n’a donc plus besoin de s’en préoccuper. Bien sûr il peut aussi écrire les pages de données / d’index d’une transaction qui n’est pas encore validée. Immédiatement ensuite, il va relever un numéro de transaction (LSN pour Log Sequence Number) dans le journal pour s’en servir comme borne de départ pour le RECOVERY. Appelons-le LSN1.

Puis il va lire et écrire les pages de données depuis le fichier MDF ou les différents filegroups, et les écrire dans le fichier de sauvegarde. Une fois terminées ces écritures, il renote le LSN courant (qui entre temps peut avoir évolué), et s’en sert comme borne de fin (LSN2). Puis il va sauvegarder tout ce qui se trouve entre (LSN2 – LSN1). Certaines transactions seront validées, certaines ne seront peut être pas terminées, mais peut importe, car au moment de la restauration, le processus de RECOVERY fera son œuvre pour ramener la base à un état transactionnel consistant.

Si le LSN2 lui ne change pas car c’est toujours le dernier numéro généré dans le journal qui sera utilisé, le problème vient plus du calcul du LSN de départ car il va dépendre de la situation dans laquelle se trouve le BACKUP DATABASE au départ. Globalement, je peux me trouver dans deux cas :

- Soit il n’y a aucune transaction en cours au moment où commence la sauvegarde : auquel cas LSN1 sera égale à la valeur du LSN généré par le CHECKPOINT (lancé par le BACKUP):

L’effet du CHECKPOINT ici est de flusher les pages de données modifiées par T1, T2 et T3, pour ne pas à avoir à les prendre dans la sauvegarde du journal. Ces pages se retrouvent sur disque, et seront capturées lors de la copie de données.

LSN2 est toujours le LSN courant capté dès la fin de la copie de données. Dans ce cas, T4 est terminée mais on est en plein milieu de T5, donc on prend quand même ce ‘fragment’ de T5. Lors de la restauration, T4 sera réappliquée et T5 sera rollbackée:

Soit il y aura au moins une transaction en cours : auquel cas, SQL Server va aller rechercher le LSN de départ de la transaction active la plus ancienne. Dans l’exemple ci-dessous, il s’agit de T2:

Dans ce cas, T2 et T4 qui sont terminées avant la fin de la copie de données seront réappliquées et T5 et T3 rollbackées:

La table msdb.dbo.backupset qui stocke des données pour chaque sauvegarde effectuée permet de vérifier dans quel état se trouvait la base au moment de la sauvegarde. Pour le déterminer, il faut comparer les valeurs de checkpoint_lsn et first_lsn:

- checkpoint_lsn = first_lsn: alors il n’y avait pas de transaction en cours au moment du BACKUP DATABASE.

select first_lsn, checkpoint_lsn, last_lsn from msdb.dbo.backupset where database_name  = ‘CAPDATA’

first_lsn                  checkpoint_lsn             last_lsn

--------------------       --------------------       --------------------

1341 0000000302 00051     1341 0000000302 00051       1341 0000000323 00001

- first_lsn < checkpoint_lsn: alors il y avait au moins une transaction en cours.

select first_lsn, checkpoint_lsn, last_lsn from msdb.dbo.backupset where database_name  = ‘CAPDATA’

first_lsn                checkpoint_lsn          last_lsn

--------------------     ---------------         --------------------

1341 0000000510 0002     1341 0000000523 00098    1341 0000000563 00001

Continuez votre lecture sur le blog :

• Une alternative à xp_fixeddrives (David BAFFALEUF) [SQL Server]
• Planification dans Adaptive Server (David BAFFALEUF) [Sybase]
• Création d’un Dataguard physique (Guillaume DEFENDINI) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]

Ici nous allons aborder le troisième mode BULK-LOGGED, ses avantages, inconvénients et contextes d’utilisation. Quelques rappels du post précédent:

BULK-LOGGED: c’est un mode à combiner avec le mode FULL. Dans ce mode, les opérations de modification de masse ne sont pas complètement journalisées, et l’impact de telles opérations sur le journal est limité. Le fait de changer de FULL à BULK-LOGGED ne modifie en rien ma façon de sauvegarder mes bases. En mode BULK-LOGGED, mes sauvegardes transactionnelles horaires continuent de s’exécuter sans problème. Très utile lorsqu’on lance notre réorganisation des indexes hebdomadaire car on gagne un temps fou.

Quelles sont ces opérations de modification de masse:

1. SELECT INTO
2. BCP IN / BULK INSERT
3. ALTER INDEX … REBUILD
4. Manipulation de champs de type TEXT:

On les appelle parfois par abus de langage opérations non loggées, le terme plus juste est minimallement loggées.

Lorsqu’une de ces opérations minimalement loggée est exécutée, seules les allocations de pages sont journalisées, mais elles seules ne suffisent pas à restaurer l’intégralité de mon reindex ou de mon select into en cas de crash. C’est pourquoi pour permettre de restaurer ces données modifiées, la sauvegarde embarque avec elle les pages de données allouées lors de l’opération.

Pour le vérifier, on se propose de comparer sous les deux modes de récupération FULL et BULK_LOGGED, deux opérations minimalement loggées :

1. Un SELECT INTO d’une table de 2,6 millions de lignes dans une table d’archive.
2. Le rebuild d’un index clusterisé sur une table de 2,6 millions de lignes.

Dans chaque cas, nous allons mesurer plusieurs choses :

1. Le temps d’exécution de l’opération.
2. L’impact sur le journal de transactions (en Mb).
3. L’impact sur la taille de ma sauvegarde de transaction (en Mb).

1) En mode de récupération FULL :

Pour pouvoir exécuter la sauvegarde du journal à l’issue de chaque opération, il faut commencer par exécuter une sauvegarde complète (voir article précédent) :

ALTER DATABASE CAPDATA SET RECOVERY FULL

BACKUP DATABASE CAPDATA TO DISK='C:\YUKON\BACKUP\CAPDATA.bak'
WITH init, stats

Ensuite on va vérifier que l’on part sur un journal vide :

dbcc sqlperf(logspace)

Database Name        Log Size (MB) Log Space Used (%) Status
----------------------------------------------------------------------------------------------
CAPDATA            49,99219      2,951633           0

Le journal fait 50Mb, il est vide (2%). On peut donc lancer le SELECT INTO :

set statistics time on
select * into PIECEAPPARTEMENT2 from PIECEAPPARTEMENT

 SQL Server parse and compile time: 

 CPU time = 0 ms, elapsed time = 48 ms.

  SQL Server Execution Times:

 CPU time = 7641 ms, elapsed time = 57673 ms.

 (2644317 row(s) affected)

Quelle est la taille du journal ?

dbcc sqlperf(logspace)

Database Name        Log Size (MB) Log Space Used (%) Status
----------------------------------------------------------------------------------------------

CAPDATA             97,55469       93,43568            0

Il est passé de 50 à 97 Mb, et il est plein. Sauvegardons la transaction pour le vider :

BACKUP LOG CAPDATA TO DISK='C:\YUKON\BACKUP\CAPDATA.trn' with init, stats

(…)

BACKUP LOG successfully processed 11592 pages in 25.525 seconds (3.720 MB/sec).

11592 * 8192 ~= 93 Mb, soit la taille de ma transaction dans mon journal. Normal. Une fois la sauvegarde effectuée, on repart sur un journal vide. On peut donc lancer immédiatement le REBUILD de l’index sur PIECEAPPARTEMENT :

ALTER INDEX PK_PIECEAPPARTEMENT ON PIECEAPPARTEMENT REBUILD WITH (SORT_IN_TEMPDB = ON)

 SQL Server Execution Times:

 CPU time = 11828 ms, elapsed time = 66788 ms.

A nouveau le journal est plein et si je le sauvegarde encore, la taille du fichier sera égale à la taille de ma transaction.

dbcc sqlperf(logspace)

 Database Name      Log Size (MB)     Log Space Used (%)    Status

----------------------------------------------------------------------------------------------

CAPDATA             97,55469          92,42763               0

2) En mode de récupération BULK_LOGGED :

Même scénario mais cette fois la base est passée en mode de récupération BULK_LOGGED, et mon journal est vide. Pour partir sur la même enveloppe initiale, et ne pas fausser les résultats pour le SELECT INTO, je vais réduire la taille de mon journal à sa taille de départ (50Mb) :

dbcc shrinkfile('CAPDATA_log01',50)

DbId   FileId       CurrentSize MinimumSize UsedPages    EstimatedPages

------ ----------- ----------- ----------- ----------- --------------

6      2        6400           6400        6400          6400

Et je lance le SELECT INTO :

SQL Server Execution Times:

 CPU time = 8235 ms, elapsed time = 24353 ms.

 (2644317 row(s) affected)

Déjà, il a mis deux fois moins de temps. Voyons voir la situation du journal :

dbcc sqlperf(logspace)

 Database Name Log Size (MB) Log Space Used (%) Status

----------------------------------------------------------------------------------------------

CAPDATA       49,99219      3,073722             0

Quasiment rien n’a été utilisé !! Non seulement l’enveloppe n’a pas bougé mais en plus mon journal est toujours quasiment vide. J’en profite pour regarder ce qui se trouve dans ces 3% de journal utilisé:

select Operation, COUNT(*) nr_records, SUM([Log Record Length]) totalsize

from ::fn_dblog(null,null) 

group by Operation order by COUNT(*) desc

Operation                       nr_records  totalsize

------------------------------- ----------- -----------

LOP_SET_FREE_SPACE              11111       577772

LOP_SET_BITS                    5650        328272

LOP_MODIFY_ROW                  1570        138852

LOP_COUNT_DELTA                 33          6864

LOP_BUF_WRITE                   31          59512

LOP_INSERT_ROWS                 27          4068

LOP_ROOT_CHANGE                 5           480

LOP_BEGIN_XACT                  4           432

LOP_HOBT_DELTA                  4           256

LOP_COMMIT_XACT                 4           208

LOP_LOCK_XACT                   3           120

LOP_HOBT_DDL                    2           72

LOP_PREP_XACT                   1           68

LOP_END_CKPT                    1           136

LOP_FORMAT_PAGE                 1           84

LOP_BEGIN_CKPT                  1           96

LOP_CREATE_ALLOCCHAIN           1           40

L’opérateur logique LOP_SET_FREE_SPACE est typiquement un opérateur d’allocation, qui met à jour la page PFS du fichier de donnée et indique quels sont les pages qui ne sont plus libres pour de futures allocations car elles ont été utilisées par le SELECT INTO. Si je lance la sauvegarde de mon journal :

Processed 149 pages for database 'CAPDATA', file 'CAPDATA_log01' on file 1.

BACKUP LOG successfully processed 11277 pages in 28.242 seconds (3.270 MB/sec).

La sauvegarde fait 90Mb alors qu’il n’y a que 1,5Mb de transactions dans mon journal, parce qu’elle embarque les pages de données allouées par le SELECT INTO.

Exécutons maintenant le rebuild de l’index :

ALTER INDEX PK_PIECEAPPARTEMENT ON PIECEAPPARTEMENT REBUILD WITH (SORT_IN_TEMPDB = ON)

 SQL Server Execution Times:

 CPU time = 11594 ms, elapsed time = 48860 ms.

48 secondes au lieu de 66, et dans le journal:

dbcc sqlperf(logspace)

 Database Name   Log Size (MB)     Log Space Used (%) Status

----------------------------------------------------------------------------------------------

CAPDATA         49,99219           5,8515             0

Et la sauvegarde de journal fait 91 Mb, loin des 5% utilisés. Là encore, les pages de données créées lors du rebuild sont embarquées dans la sauvegarde.

Conclusion: en mode BULK_LOGGED, pour pouvoir restaurer une opération minimalement loggée, le BACKUP LOG est obligé de prendre avec lui les pages de données allouées dans l’opération. Ici, on ne voit pas de différence de taille des sauvegardes de journal entre les modes FULL et BULK_LOGGED, mais si en plus des opérations minimalement loggées, j’ai des ordres de modifications standards type INSERT/DELETE,UPDATE, la taille de ma sauvegarde va croître (et le temps de sauvegarde avec) en BULK_LOGGED et dépasser la taille d’une sauvegarde en mode FULL. Donc attention à l’espace disponible sur les disques de sauvegardes le week end pendant la reorg !

David B.

Continuez votre lecture sur le blog :

• Scripting et SMO (suite): scripter les objets directement en T-SQL (David BAFFALEUF) [SQL Server]
• Règles d’installation de base (épisode 2) (David BAFFALEUF) [SQL Server]
• Modes de récupération et journal de transactions, épisode 1 (David BAFFALEUF) [SQL Server]
• Jeux de caractères, Unicode et Base de données (Guillaume DEFENDINI) [GénéralMySQLOracleSQL ServerSybase]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]

Dans ce premier épisode:

1. Qu’implique le passage de SIMPLE à COMPLET.
2. Que devient mon journal en mode COMPLET si je ne le sauvegarde pas régulièrement.

Le mode de récupération (recovery model en anglais) est une propriété de base de données qui va préciser pour celle-ci deux choses essentielles:

1. Le recyclage de l’espace consommé par les transactions validées dans son journal de transactions.
2. La possibilité ou non de pouvoir sauvegarder ces transactions validées.

Il existe trois modes de récupération: FULL, SIMPLE et BULK-LOGGED.

FULL: c’est le mode par défaut. Dans ce mode, toutes les transactions sont journalisées, même les opérations de modifications de masse (CREATE INDEX, BULK INSERT ou bcp.exe, SELECT INTO, et les opérations sur les champs TEXT). Dès l’instant où il y a eu au moins une sauvegarde complète sur la base, les transactions validées restent dans le journal. La seule façon de recycler l’espace occupé par ces transactions est de lancer régulièrement une sauvegarde du journal. Les transactions validées sont sauvegardées dans un fichier et sont alors supprimées du journal * .

SIMPLE: dans ce mode, le recyclage des transactions validées est la responsabilité du checkpoint. En plus de flusher sur disque les pages de données modifiées dans le cache de données, le checkpoint supprime les transactions validées du journal sans les sauvegarder. Conséquence de celà, je n’ai pas la possibilité de sauvegarder mes transactions en mode SIMPLE. Je ne pourrai exécuter que des sauvegardes complètes ou différentielles (ou de fichier en lecture seule).

BULK-LOGGED: c’est un mode à combiner avec le mode FULL. Dans ce mode, les opérations de modification de masse (citées plus haut) ne sont pas complètement journalisées, et l’impact de telles opérations sur le journal est limité. Le fait de changer de FULL à BULK-LOGGED ne modifie en rien ma façon de sauvegarder mes bases. En mode BULK-LOGGED, mes sauvegardes transactionnelles horaires continuent de s’exécuter sans problème. Très utile lorsqu’on lance notre réorganisation des indexes hebdomadaire car on gagne un temps fou.

Note: Attention à ne pas confondre mode de récupération FULL et sauvegarde FULL.

Pour retrouver le mode de récupération d’une base:

SELECT DATABASEPROPERTYEX('maBase','RECOVERY') as RECOVERYMODE

Pour changer le mode d’une base:

ALTER DATABASE maBase SET RECOVERY [ FULL | SIMPLE | BULK_LOGGED ]

1) Première remarque: il faudra obligatoirement (re)lancer une sauvegarde complète avant de sauvegarder le journal:
- Quand je créé ma base.
- Quand je passe de SIMPLE à FULL.

La sauvegarde de transaction a besoin d’un numéro de transaction (LSN pour Log Sequence Number) de référence pour savoir à partir de quelle transaction commencer sa sauvegarde. Lorsqu’il n’y a pas eu d’autre sauvegarde de transactions avant elle, comme c’est le cas quand je passe de SIMPLE à FULL ou quand je viens de créer ma base, elle se base sur le LSN de fin de la dernière sauvegarde complète dans le même mode. Le fonctionnement précis de la sauvegarde à chaud fera l’objet d’un autre article prochainement.

Pour le mettre en évidence, créons-nous une petite base CRASHTEST:

CREATE DATABASE CRASHTEST

on PRIMARY

(NAME='CRASHTEST_data',

FILENAME='F:\KATMAI\MSSQL10.KATMAI\MSSQL\DATA\CRASHTEST_data.mdf',

SIZE=100MB, MAXSIZE=500MB)

LOG ON

(NAME='CRASHTEST_log',

FILENAME='F:\KATMAI\MSSQL10.KATMAI\MSSQL\DATA\CRASHTEST_log.ldf',

SIZE=50MB,
MAXSIZE=500MB)

Vérifions son mode de récupération:

SELECT DATABASEPROPERTYEX('CRASHTEST','RECOVERY') as RECOVERYMODE

RECOVERYMODE
---------------------------------------------------------
FULL

Créons une petite transaction et essayons de la sauvegarder:

use CRASHTEST

create table TABLE1 (A numeric identity, B varchar(5000))

insert into TABLE1 values (replicate('b',5000))

backup log CRASHTEST to disk='C:\CRASHTEST.log1.trn' with init, stats

Msg 4214, Level 16, State 1, Line 1

BACKUP LOG cannot be performed because there is no current database backup.

J’ai donc effectivement besoin d’avoir une sauvegarde complète pour initialiser ma chaîne:

backup database CRASHTEST to disk='C:\CRASHTEST.1.bak' with init, stats

Processed 152 pages for database 'CRASHTEST', file 'CRASHTEST_data' on file 1.
100 percent processed.
Processed 1 pages for database 'CRASHTEST', file 'CRASHTEST_log' on file 1.
BACKUP DATABASE successfully processed 153 pages in 0.266 seconds (4.711 MB/sec).

OK, je retente de sauvegarder mon log:

backup log CRASHTEST to disk='C:\CRASHTEST.log1.trn' with init, stats

100 percent processed.

Processed 6 pages for database 'CRASHTEST', file 'CRASHTEST_log' on file 1.

BACKUP LOG successfully processed 6 pages in 0.161 seconds (0.283 MB/sec).

Même problème lorsque je passe de SIMPLE à FULL:

ALTER DATABASE CRASHTEST set RECOVERY SIMPLE

ALTER DATABASE CRASHTEST set RECOVERY FULL

backup log CRASHTEST to disk='C:\CRASHTEST.log2.trn' with init, stats

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.

2) SAUVEGARDEZ VOS TRANSACTIONS !

C’est un problème que l’on retrouve chez beaucoup de clients: grands comptes, PME… Il y a toujours chez eux au moins une base de données en mode de récupération FULL dont le journal n’a JAMAIS été sauvegardé. Ce n’est pas étonnant en un sens puisque FULL est le mode par défaut pour toutes les bases créées par l’utilisateur. Le résultat est un fichier LDF qui fait 80 Gb parce qu’il contient une année ou deux de transactions validées, sauvegardées dans des backup FULL depuis belle lurette, mais on le répète, en mode FULL, il n’y a qu’une sauvegarde de journal qui permet de recycler l’espace occupé par des transactions validées.

Reprenons notre base CRASHTEST, et créons un peu de volumétrie…

USE CRASHTEST

declare @cpt int

set @cpt=1

while(@cpt<=50000)

begin

insert into TABLE1 values (replicate('b',5000))

set @cpt=@cpt+1

end

go

…et vérifions quelle est la situation du journal à l’issue:

DBCC SQLPERF(LOGSPACE)

Database Name       Log Size (Mb)    Log Space Used (%)      Status
------------------  ---------------  ----------------------  --------
(...)               (...)            (...)                   (...)
CRASHTEST           253,3047         97,07731                0

Il a plus que quadruplé de volume et il est plein.

Pour récupérer l’espace sur le disque, il faudra faire un DBCC SHRINKFILE en mode TRUNCATEONLY.
Si je lance le DBCC SHRINKFILE tout de suite:

DBCC SHRINKFILE(’CRASHTEST_log’,'TRUNCATEONLY’)

Dbid        Fileid        CurrentSize        MinimumSize    UsedPages         EstimatedPages
---------   ----------- ----------------  ---------------- ---------------   ------------------
8           2            31680            6400             31680             6400

31680*8192Kb = 247,5 Mb, autant dire que presque rien n’a été fait. Celà vient du fait que le TRUNCATEONLY tronque la taille du fichier jusqu’au dernier extent alloué.
Comme mon journal est plein, le dernier extent alloué est précisément à la fin du fichier.

Donc il faut commencer par vider ce journal: je sauvegarde ma transaction :

backup log CRASHTEST to disk='C:\CRASHTEST.log3.trn' with init, stats, compression

go

100 percent processed.

Processed 31461 pages for database 'CRASHTEST', file 'CRASHTEST_log' on file 3.

BACKUP LOG successfully processed 31461 pages in 16.391 seconds (15.723 MB/sec).

Dans mon journal…

DBCC SQLPERF(LOGSPACE)

Database Name        Log Size (Mb)    Log Space Used (%)      Status
------------------   ---------------  ----------------------  --------
(...)                (...)            (...)                   (...)
CRASHTEST            247,4922         4,201521                 0

Je retrouve mon enveloppe à 247,5Mb, et cette fois elle est bien vide. Je vais pouvoir ramener mon log à sa taille d’origine:

DBCC SHRINKFILE(’CRASHTEST_log’,'TRUNCATEONLY’)

Dbid        Fileid        CurrentSize        MinimumSize    UsedPages         EstimatedPages
---------   ----------- ----------------  ---------------- ---------------   ------------------
8           2            6400               6400            6400              6400

Attention, si je choisis de rester en mode FULL, je devrai programmer des sauvegardes de transactions (à travers le SQL Agent par exemple).

Dans le cas où ce journal fait plusieurs dizaines de Gb, je choisis de le vider en passant la base en mode SIMPLE:

ALTER DATABASE CRASHTEST SET RECOVERY SIMPLE

Un checkpoint initié dès la fin de l’ALTER se chargera de vider le contenu du journal, et il ne restera plus qu’à faire le SHRINKFILE.

A suivre l’effet du BULK-LOGGED sur la taille du journal…

[ David B. ]

Continuez votre lecture sur le blog :

• Abonnez-vous au blog de la CapData team ! (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]
• SQLDIAG (épisode 1) (David BAFFALEUF) [SQL Server]
• Modes de récupération et journal de transactions, épisode 2 (David BAFFALEUF) [SQL Server]
• Jeux de caractères, Unicode et Base de données (Guillaume DEFENDINI) [GénéralMySQLOracleSQL ServerSybase]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]

La première solution est d’utiliser la “gateway Oracle pour SQL SERVER ” (15000$ par serveur).

La deuxième gratuite est d’utiliser OLE DB.

Je vais m’intéresser à la deuxième solution, vous en devinez la raison

1) créer un fichier UDL (vide puis ajouter l’extension .udl) sous C:\\Documents and Settings\\thierry\\Bureau\\client1.udl, puis double cliquer

-onglet Fournisseur : choisir le fournisseur “Microsoft OLE DB Provider for SQL Server”

s’il n’est pas présent téléchargez MDAC (merci david)
-onglet connexion : nom du serveur SQL (minsk\aquarium), utilisateur/mot de passe, base de données client1
2) sous $ORACLE_HOME\hs\admin
faire une copie du fichier inithsoledb.ora en initclient1.ora
3) configuer initclient1.ora
HS_FDS_CONNECT_INFO =”UDLFILE=C:\\Documents and Settings\\thierry\\Bureau\\client1.udl”
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = client1.trc

attention doubler \ (merci manu..)
4) configuer le fichier tnsnames.ora
CLIENT1 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=pc-thierry)(PORT=1524))
(CONNECT_DATA=(SID=CLIENT1))
(HS=OK)
)
5) configurer le fichier listener.ora
ajouter aux services de votre listener
(SID_DESC=
(SID_NAME=CLIENT1)
(ORACLE_HOME = D:\app\oracle\10.2.0\db_1)
(PROGRAM=hsolesql)
)
6) créer le database link
CREATE PUBLIC DATABASE LINK “CLIENT1″ USING ‘CLIENT1′;

7) tester la connexion
select * from sysobjects@client1;

remarque : en cas de souci, vous pouvez poser une trace.
mettre le paramètre HS_FDS_TRACE_LEVEL = 4
la trace se trouvera sous D:\app\oracle\10.2.0\db_1\hs\traceContinuez votre lecture sur le blog :

• Installation Oracle 11gR2 64 bits sur Red Hat 5 Partie 1 (Thierry GASCARD) [Oracle]
• Installation Oracle 64 bits sur Red Hat 5 (Thierry GASCARD) [Oracle]
• Création et utilisation d’OCFS2 (Thierry GASCARD) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Utiliser ASMCMD (Thierry GASCARD) [Oracle]