Fragmentation des index et des données

DBA

Accueil > DBA > Articles

24/01/2010

Qu'est-ce que la fragmentation des index/données ?

C'est le fait que des données qui devraient être lues à la suite les unes des autres de manière logique ne sont pas rangées physiquement selon cet ordre.

Ceci oblige SQLServer à faire des déplacements dans son(ses) fichiers de base de données pour trouver le chainage logique.
C'est tout à fait comparable à une fragmentation de disque où un fichier est éclaté en plusieurs morceaux obligeant des déplacements de tête de lecture pour reconstituer l'ensemble.
D'ailleurs on peut hélas combiner la fragmentation logique (à l'intérieur du fichier de la base) et la fragmentation physique (celle du fichier) : il suffit pour cela de laisser la base s'agrandir par tout petit pas (extension automatique) avec, en plus des mises à jour très nombreuses et aléatoires de clés clustered pour avoir ce 'jackpot' : énormément d'entrées/sorties disques pour peu de lectures effectives.
Ps : pour ceux qui n'apprécieraient pas mon humour, c'est bien sûr ce QU'IL NE FAUT PAS FAIRE ....

En pratique la fragmentation s'exprime ainsi :
SQLServer est obligé de changer d'extent (les paquets de 8 pages) pour aller lire des données alors que ces données auraient pu se trouver dans le même extent.

Comment constater la fragmentation ?

Contrairement à SQL2000; l'interface graphique dispose maintenant de nombreux outils pour savoir ce qui se passe au niveau physique; c'est le cas pour les index.
La table COUREUR que je prendrai comme exemple possède 246000 lignes, on aborde donc là des volumes qui commencent à mériter notre attention.
Ces coureurs peuvent être licenciés (à la FFA) et la table possède donc un index sur ce NO_LICENCE ; voyons ses propriétés :

La fragmentation totale (69%) nous indique que cet index est très fragmenté : cela résulte des mises à jour qui ont été faites comme on va le voir.

Utilisation des fonctions de gestion dynamique

SQLServer2005 met à disposition du DBA des objets de gestion dynamique de la base de données.
Il est donc possible de tout savoir (ou presque !) par programmation, ce qui aura l'avantage, par rapport à l'interface graphique, de pouvoir traiter les choses de manière globale.
Il sera également possible de faire des traitements conditionnels du style :
si l'index est fragmenté de plus de x% et que ....

Par exemple nous allons utiliser la fonction :

sys.dm_db_index_operational_stats (
{ database_id | null }
, { object_id | null }
, { index_id | null | 0 }
, { partition_number | null }
)

qui va nous donner des informations de stockage et fragmentation sur une table et ses index.
Les arguments à NULL permettront d'obtenir tous les index par exemple (index_id à NULL) voir toutes les tables (object_id à NULL)
Ainsi il est très facile sur cette base de connaitre tous les index fragmentés :

-- Connaitre les index fragmentés

select

object_name(Stat.object_id) AS NomObjet,

Ix.Name as NomIndex,

Stat.partition_number AS NoPartition,

Stat.avg_fragmentation_in_percent AS CoeffFragmentation

from

sys.dm_db_index_physical_stats (DB_ID(), null, null , null, 'LIMITED') Stat

inner join sys.indexes Ix

on Stat.object_id = Ix.object_id AND

Stat.index_id = Ix.index_id

where

Stat.avg_fragmentation_in_percent > 10.0 AND

Stat.index_id > 0;

L'index PK_RECOMPENSE est fragmenté à 66% : mais que fait le DBA ??....

Partant de cette fonction, je me suis écrit une petite procédure stockée qui présente les données à la manière de l'ancien DBCC SHOWCONTIG (ah, les habitudes ...)

-- Analyse de tous les index de la table

exec SHOWCONTIG 'COUREUR'

Analyse de la contiguité
-------------------------
Base de données--------------------: DigitimeNet

Objet analysé----------------------: COUREUR
Nom index--------------------------: PK_COUREUR
database_id------------------------: 7
object_id--------------------------: 1962490070
index_id---------------------------: 1
partition_number-------------------: 1
index_type_desc--------------------: CLUSTERED INDEX
alloc_unit_type_desc---------------: IN_ROW_DATA
index_depth------------------------: 3
index_level------------------------: 0
avg_fragmentation_in_percent-------: 0.0559701
fragment_count---------------------: 147
avg_fragment_size_in_pages---------: 36.4626
page_count-------------------------: 5360

Objet analysé----------------------: COUREUR
Nom index--------------------------: X2_COUREUR
database_id------------------------: 7
object_id--------------------------: 1962490070
index_id---------------------------: 2
partition_number-------------------: 1
index_type_desc--------------------: NONCLUSTERED INDEX
alloc_unit_type_desc---------------: IN_ROW_DATA
index_depth------------------------: 3
index_level------------------------: 0
avg_fragmentation_in_percent-------: 0.173762
fragment_count---------------------: 43
avg_fragment_size_in_pages---------: 26.7674
page_count-------------------------: 1151

Objet analysé----------------------: COUREUR
Nom index--------------------------: X3_COUREUR
database_id------------------------: 7
object_id--------------------------: 1962490070
index_id---------------------------: 3
partition_number-------------------: 1
index_type_desc--------------------: NONCLUSTERED INDEX
alloc_unit_type_desc---------------: IN_ROW_DATA
index_depth------------------------: 3
index_level------------------------: 0
avg_fragmentation_in_percent-------: 69.7337
fragment_count---------------------: 590
avg_fragment_size_in_pages---------: 1.4
page_count-------------------------: 826

Objet analysé----------------------: COUREUR
Nom index--------------------------: X4_COUREUR
database_id------------------------: 7
object_id--------------------------: 1962490070
index_id---------------------------: 4
partition_number-------------------: 1
index_type_desc--------------------: NONCLUSTERED INDEX
alloc_unit_type_desc---------------: IN_ROW_DATA
index_depth------------------------: 3
index_level------------------------: 0
avg_fragmentation_in_percent-------: 82.7815
fragment_count---------------------: 762
avg_fragment_size_in_pages---------: 1.18898
page_count-------------------------: 906

Objet analysé----------------------: COUREUR
Nom index--------------------------: FK_COUREUR_CLUB
database_id------------------------: 7
object_id--------------------------: 1962490070
index_id---------------------------: 5
partition_number-------------------: 1
index_type_desc--------------------: NONCLUSTERED INDEX
alloc_unit_type_desc---------------: IN_ROW_DATA
index_depth------------------------: 3
index_level------------------------: 0
avg_fragmentation_in_percent-------: 82.133
fragment_count---------------------: 601
avg_fragment_size_in_pages---------: 1.20133
page_count-------------------------: 722

Objet analysé----------------------: COUREUR
Nom index--------------------------: FK_COUREUR_NATIONALITE
database_id------------------------: 7
object_id--------------------------: 1962490070
index_id---------------------------: 6
partition_number-------------------: 1
index_type_desc--------------------: NONCLUSTERED INDEX
alloc_unit_type_desc---------------: IN_ROW_DATA
index_depth------------------------: 3
index_level------------------------: 0
avg_fragmentation_in_percent-------: 7.44879
fragment_count---------------------: 43
avg_fragment_size_in_pages---------: 12.4884
page_count-------------------------: 537
Fin de l'analyse

et l'on voit, comme dans l'interface graphique que l'index X3_COUREUR est fragmenté à 69%.
D'autres informations intéressantes sont disponibles, telle la profondeur de l'index (index depht) qui est la hauteur maximum de l'arbre d'index.
Ici le nombre 3 nous indique que, au maximum, 3 pages seront traversées pour atteindre une des feuilles à partir de la racine.
Plus ce nombre est élevé, plus on aura d'IO et donc de temps de réponse pour atteindre une ou des clés de l'index.
Le fragment count nous donne le nombre de 'paquets' contigus de données ; plus ce nombre est faible et plus les données sont localisées dans le fichier et donc accessibles d'un seul tenant : ceci facilite bien sûr les accès disques.
Le (mauvais) cas extrême est celui où le nombre de fragments est égal au nombre de pages : rien n'est contigu dans ce cas.
avg_fragment_size_in pages est bien évidemment inversement proportionnel au nombre de fragments : moins il y a de fragments , plus ils sont gros.
Je peux aussi connaitre un index particulier par :

-- Analyse d'un index de la table

exec SHOWCONTIG 'COUREUR','X3_COUREUR'

Analyse de la contigüité
-------------------------
Base de données--------------------: DigitimeNet

Objet analysé----------------------: COUREUR
Nom index--------------------------: X3_COUREUR
database_id------------------------: 7
object_id--------------------------: 1962490070
index_id---------------------------: 3
partition_number-------------------: 1
index_type_desc--------------------: NONCLUSTERED INDEX
alloc_unit_type_desc---------------: IN_ROW_DATA
index_depth------------------------: 3
index_level------------------------: 0
avg_fragmentation_in_percent-------: 69.7337
fragment_count---------------------: 590
avg_fragment_size_in_pages---------: 1.4
page_count-------------------------: 826
Fin de l'analyse

Comment réparer la fragmentation :

On peut d'abord le faire par l'interface graphique, ce qui sera utile pour du cas par cas, mais peu pratique si on doit reconstruire beaucoup d'index.
Par ailleurs, cette interface graphique ne permet pas de positionner finement certains paramètres de la procédure de reconstruction.

Après une réorganisation :

-- Analyse d'un index de la table

exec SHOWCONTIG 'COUREUR','X4_COUREUR'

Analyse de la contigüité
-------------------------
Base de données--------------------: DigitimeNet

Objet analysé----------------------: COUREUR
Nom index--------------------------: X4_COUREUR
database_id------------------------: 7
object_id--------------------------: 1962490070
index_id---------------------------: 4
partition_number-------------------: 1
index_type_desc--------------------: NONCLUSTERED INDEX
alloc_unit_type_desc---------------: IN_ROW_DATA
index_depth------------------------: 3
index_level------------------------: 0
avg_fragmentation_in_percent-------: 2.22222
fragment_count---------------------: 66
avg_fragment_size_in_pages---------: 8.18182
page_count-------------------------: 540
Fin de l'analyse

l'index n'est plus fragmenté qu'à 2% : la réorganisation va moins en profondeur que la reconstruction qui elle, gère de manière globale l'ensemble des lignes concernées par l'index.

Défragmentation par le code :

-- Réorganiser un index non clustered

alter INDEX X3_COUREUR

ON COUREUR

REORGANIZE WITH ( LOB_COMPACTION = ON ) -- les pages contenant des image,varchar(max) ,... sont compactées

Analysons ce que cela donne en termes de stockage :

-- Analyse d'un index de la table

exec SHOWCONTIG 'COUREUR','X3_COUREUR'

Analyse de la contigüité
-------------------------
Base de données--------------------: DigitimeNet

Objet analysé----------------------: COUREUR
Nom index--------------------------: X3_COUREUR
database_id------------------------: 7
object_id--------------------------: 1962490070
index_id---------------------------: 3
partition_number-------------------: 1
index_type_desc--------------------: NONCLUSTERED INDEX
alloc_unit_type_desc---------------: IN_ROW_DATA
index_depth------------------------: 3
index_level------------------------: 0
avg_fragmentation_in_percent-------: 3.15399
fragment_count---------------------: 52
avg_fragment_size_in_pages---------: 10.3654
page_count-------------------------: 539
Fin de l'analyse

Même remarque que précédemment, nous arrivons à 3% car l'option REORGANIZE fait le travail moins en profondeur que REBUILD.

-- Reconstruire un index non clustered

alter INDEX X3_COUREUR

ON COUREUR

REBUILD WITH (

PAD_INDEX = OFF, -- ne pas remplir les noeuds intermédiaires comme les feuilles

STATISTICS_NORECOMPUTE = OFF, -- les statistiques seront recalculées en automatique

ALLOW_ROW_LOCKS = ON, -- la réindexation peut poser des verrous ligne

ALLOW_PAGE_LOCKS = ON, -- la réindexation eut poser des verrous page (obligatoire)

SORT_IN_TEMPDB = OFF, -- à défaut en mémoire

ONLINE = OFF ) -- sur SQL Server 2005 Enterprise seulement permet de garder la table opérationnelle durant la réindexation

-- Analyse d'un index de la table

exec SHOWCONTIG 'COUREUR','X3_COUREUR'

Objet analysé----------------------: COUREUR
Nom index--------------------------: X3_COUREUR
database_id------------------------: 7
object_id--------------------------: 1962490070
index_id---------------------------: 3
partition_number-------------------: 1
index_type_desc--------------------: NONCLUSTERED INDEX
alloc_unit_type_desc---------------: IN_ROW_DATA
index_depth------------------------: 3
index_level------------------------: 0
avg_fragmentation_in_percent-------: 0.188324
fragment_count---------------------: 19
avg_fragment_size_in_pages---------: 27.9474
page_count-------------------------: 531
Fin de l'analyse

On arrive de nouveau à quasiment 0% de fragmentation : un index parfaitement utilisable donc....

Conclusion

Les fragmentations trop importantes génèrent beaucoup d'entrées-sorties inutiles et donc augmentent le temps de réponse.
On sera face à deux cas de figure :
. base de petit volume : défragmentation systématique (par les jobs) tous les...
. base de fort volume : défragmentation au coup par coup, l'idéal étant un automate basé sur les vues de gestion étudiées plus haut

La fréquence des lancements dépend bien évidemment de l'activité de la base en mise à jour.

Publié à 16:34 par Compte système | Catégorie: Fragmentation des index et des données | Lien permanent | Envoi de cette publication par courrier électronique | Commentaires (0)

‭(Masqué)‬ WebPart1 Web Part

24/01/2010