SQL Server Training (Jour 2)

Que retenir de cette deuxième journée:

XML
Maintenant que SQL Serveur dispose d'un datatype natif XML, il est possible:

1. De stocker du contenu XML dans un champs et d'y appliquer des méthodes de traitement spécifiques (XQuery).
2. De générer directement du contenu XML depuis une requête SQL (FOR XML).
3. D'accepter un input XML, de le parser et le transformer en données relationnelles (OPEN XML).
   

Ce chapitre fut relativement intéressant. Surtout concernant la génération de document XML directement depuis des requêtes sql.
Dans ce dernier cas, si l'output n'est pas trop conséquent, cela peut vraiment présenter un avantage. Dans le cas contraire, la mémoire cache et Execution Plan Cache seront pénalisés afins de pouvoir générer le document.
L'intégration de XQuery permet de faire des requêtes vraiment puissantes mixant traitement XML (sur du contenu XML d'un champ) et datatype SQL. Cependant, cela nécessite un parsing des documents XML row par row... ce qui est vraiment très pénalisant pour un moteur de DB.
A noter que la mise en place d'index XML primaire et secondaire (FOR PATH) permettent de réduire le temps de processing XML de façon significatif (D'un coût de 266 à 0.1).
Finallement, d'un avis tout personnel, je ne suis pas certain qu'il soit intelligent de stocker du contenu XML dans une DB en vue d'un traitement quelconque (surtout s'il est récurrent).
L'intégration de contenu XML n'est pas en concordance avec l'aspect d'exploitation relationnel de l'information. Par ailleurs, durant le training, nous n'avons pas fait la référence à un cas existant (même sur demande).

Définition des indexes
Nous avons également eu l'occasion de nous consacrer sur la gestion, la création et le l'optimisation des indexes.

Clustered Index:

Index dont les data pages sont stockées dans l'ordre physique de l'index.
L'index cluster est basé sur un arbre B-Tree BALANCE (équilibré autour du noeud root), chaque noeud disposant de relations "sibling" en plus des relations ascendantes et descendantes.
L'introduction d'un nouvel enregistrement dans de tel index est couteux car il faut eventuellement insérer des pages dans l'arbre, modifier les relations entre les différents noeuds... mais surtout garder l'arbre balancé.
L'avantage de cet index, est que SQL server maintien des statistique permettant d'évaluer la pertinance d'une recherche dans l'arbre (au lieu de simplement envisager un table-scan).

Non Clustered Index:
Fonctionne de façon identique au clustered index (B-Tree) A LA DIFFERENCE que les pages sont soit:

1. Stockées dans la heap
2. Soit déjà stockée dans un clustered index.
   

Chaque noeud du "Non Clustered Index" fait une référence à la page de donnée à l'aide d'une clé.

Note 1: Dans la cas d'une table ne disposant pas d'un Clustered Index, les pages sont stockées dans la heap. Il n'y a donc pas d'emplacement prédefinit pour créer de nouvelles les pages de données. Dans ce cas, les insertions sont plus rapides car la nouvelle page de donnée peut être placée arbitrairement.

Note 2: Dans le cas d'une table disposant d'un Clustered Index, pour accéder à l'information depuis un Non Cluster Index, SQL serveur doit faire beaucoup d'opérations en lecture.
A savoir:

1. La lecture du Non Clustered Index pour récupérer 'identification des pages data (DataPageID)
2. Parcours de l'arbre B-Tree pour localiser les pages (DataPageIDs) de données dans le Clustered Index. (il faut garder à l'esprit que dans ce cas, le clustered index n'est trié dans le même ordre).
   

Conditions de selection d'un index

1. Pertinence de l'index.
   Un index est utilisé par SQL serveur s'il permet d'exclure 95% (au moins) des records de la table (lors d'une selection).
   Pour ce faire, SQL serveur consulte les statistiques d'index qui permettent d'évaluer la pertinance de l'index pour certaine valeur (Si la table contient 10.000 records et que la statistique mentionne un range de 2.500 records pour une selection particulière alors l'index sera rejeter... un table scan sera plus performant ).
   Il est également important de savoir que lors d'index sur plusieurs colonnes, seul les statistiques de la première colonne de l'indexe sont utilisées.
   Par conséquent, la sélection d'un index couvrant plusieurs colonnes ne se fait que sur base de la pertinance de la première colonne de celui-ci.
   
2. Couverture de l'index.
3. Ordre de tri.
4. Les hints inclus dans les requêtes SQL pour modifier le comportement du moteur DB.
   

Gestion des indexes

En SQL2005 Enterprise, il est possible de faire des mise-à-jours d'indexes à la'ide de l'option WITH (ONLINE=ON). Cela évite de locker la table durant toute l'opération permattant ainsi aux applications de poursuivre leurs traitement sans interruption. Cette opération consomme néanmoins beaucoup de temps et de ressources.
Il est a noter qu'en SQL2000, le création d'un index place un Lock exclusif sur la table. Il ne faut donc jamais créer d'index sur les DB de production durant les heures de bureau.


En SQL2005, il est possible:

1. De modifier (ALTER) ou de reconstuire (REBUILD) un index. Sous SQL2000, la seule option est de détruire et recréer l'index.
2. d'indiquer le nbre maximum de processeur affecté à la modification d'un index. WITH( MAXDOP=3)
3. Il est possible de definir une granularité de locking plus fine sur les indexes (ALLOW_ROW_LOCKS). Par default le moteur SQL utilise des locks au niveau de la table!!!. 
4. D'inclure des colonnes dans l'index sans que ces dernières n'interviennent dans l'index lui-même. Cette dernière optimisation permet de tirer parti des index sur colonnes multiples sans en avoir les inconvénients. En général, les colonnes additionnelles sont ajoutées pour retrouver rapidement des information pertinantes depuis l'index sans lecture de data pages complémentaire. Avant SQL2005, ces colonnes faisaient partie intégrante de l'index... par conséquent, toute modification des colonnes complémentaires réclamaient la mise-à-jour des liens internes et une opération de re-balancing... alors même que ces informations ne participent pas activement à l'index... c'était donc des indexes couteux.
   
   Avec les "Included Columns", la modification des données complémentaires  n'implique pas les opérations de mise à jours de liens internes et de re-balancing.
   
   Les indexes en SQL2005 sont à ce point plus efficaces qu'il est possible d'éliminer jusqu'à 60% des indexes nécessaires en SQL2000 tout en gardant les mêmes performances
   
   
5. De créer des "Partitionned index" fonctionnant de façon similaire aux tables partitionnées.
6. D'indexer le contenu de champs XML afin d'améliorer les performances et cout de recherche de façon spectaculaire.
7. De désactiver/réactiver des indexes lors de l'exécution de gros batch. Cela permet de gagner du temps machine considérable en évitant à SQL server de constamment mettre à jour l'index. Lors de la réactivation de l'index, ce dernier est reconstruit.
   

Optimisation des indexes

Database Engine Tuning Advisor:
Cet outil de Microsoft est maintenant bien peaufiner et est un incontournable pour l'optimisation des indexes.
Afin d'effectuer une évaluation des indexes, le Database Engine Tuning Advisor utilise une série de requêtes SQL sensées représenter les cas d'utilisations pratique de la base de données. Ces informations peuvent être fournie depuis un fichier SQL, XML ou une trace collectée à l'aide du profiler SQL.
Malheureusement, ce cas de figure peut difficilement s'appliquer aux DB de production.

Exploiter les statistiques des DB:
Pour les DB de production, il faut savoir que SQL Serveur maintien des vues dynamiques reprenant les statistiques d'usage des différents indexes (dm_db_index_usage_stats).
Par ailleurs, le processus de plannification d'execution tiens des statistiques utiles à propos des indexes idéals répondant à certaines requêtes (dm_db_missing_index_details). Cette vue est utilisée par SQL Serveur lui-même pour éviter certaines conception de plan d'exécution inutiles.
Ces informations peuvent être exploitées avec IndexTuning.sql faisant des jointures sur sys.Indexes pour obtenir de précieux conseils... même en production.
From the result of indexTuning.sql, high cumulated_cost_reduction must be addressed!

Fragmentation des indexes:
Il existe deux types de fragmentations.
La fragmentation interne est due à SQL server lui même et nous ne pouvons rien y faire. Elle a d'ailleurs peu d'influence.
Par contre, la Fragmentation externe correspond à la distribution des pages de données sur le disque (espaces disques alloués par le système d'exploitation).
L'identification de la fragmentation se fait à l'aide de Fragmentation.sql (fichier de démonstration du module 4).
Pour une fragmentation inférieure à 30%, un simple ALTER INDEX ... REORGANIZE est suffisant.
Par contre, pour une fragmentation supérieur à 30% un ALTER INDEX.... REBUILD est absolument nécessaire. Cette dernière opération (couteuse) demande au système d'exploitation d'allouer un espace continu.