Fragmentation sur des tables stockées en S-GAM

Vendredi, août 20, 2010
By David Baffaleuf in SQL Server (dbaffaleuf@capdata-osmozium.com) [71 article(s)]

C’est une question que j’ai fréquemment eu en formation à la suite d’un TP sur la fragmentation des indexes.

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 nombre de pages dont la page suivante dans le plan d’allocation (IAM) n’est pas la page suivante dans l’extent:

Si je reprends le cas de la table FILLIALE,le chaînage des pages de données se fait comme suit:  (1:127) -> (1:94) -> (1:257):

Alors que dans le cas idéal, le chaînage aurait été par exemple (1:88 -> 1:89 -> 1:90). Dans ce cas la page suivante dans le plan d’allocation est bien la page suivante dans l’extent. Le problème avec les tables S-GAM est que rien ne garantit que les pages seront adjacentes, parce qu’elles sont allouées dans des extents mixtes.

On peut retrouver cette information avec 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.

Sur 3 pages de données, 2 pages (1:127 et 1:94) ont des next pages dans le plan d’allocation qui ne sont pas les pages suivantes dans l’extent. D’où le 2/3 => 66,666%

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 :




Cliquer pour partager cet article sur Viadeo
Cliquer sur "CAPTURER" pour sauvegarder cet article dans Evernote Clip to Evernote

Tags: , , ,

3 Responses to “Fragmentation sur des tables stockées en S-GAM”

  1. Zur

    Hello David,

    Du coup, à cause des IAM, du Level 1 qui génèrent des « trous » dans la numérotation des pages, on aura jamais un taux = à 0% ?

    Mis à part une analyse plus fine comme tu l’as réalisée dans ce post qui démontre tout de même que la dégramentation est bonne, on a aucun autre moyen pour le DBA ?

    Fabrice.

    #37905
  2. Hello Fabrice,

    Il faudrait ‘gonfler’ la table pour faire passer son allocation en dedicated puis supprimer les lignes ensuite. L’allocation va rester en dedicated même si la table occupe moins de 8 pages. Et là on a des chances de faire tomber la valeur à 1/3.

    Mais en général, ça ne fait que deux ou trois lectures aléatoires pour une table S-GAM, donc le critère perf ne pèse pas lourd dans l’addition.

    Merci pour ton commentaire,

    #37951
  3. Zur

    Hello David,

    Je comprends bien, seulement, lorsqu’un client s’attend à voir un taux de fragmentation proche de 0%, pour lui expliquer pourquoi on obtient un taux de 33% et que l’on ne pourra pas avoir mieux, c’est une autre paire de manches …

    Fabrice.

    #38260

Leave a Reply