Fragmentation sur des tables stockées en S-GAM

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 :

• Alter table rebuild (Benjamin VESAN) [SQL Server]
• Intérêt de créer des indexes cluster uniques (David Baffaleuf) [SQL Server]
• Oracle et SQL Server: La Fragmentation (Benjamin VESAN) [OracleSQL Server]
• Récupérer l’espace consommé par le versionning de lignes (David Baffaleuf) [SQL Server]
• Production SQL Server: Réorganisation des objets (Benjamin VESAN) [SQL Server]