Pour ceux qui utilisent la DMV sys.dm_db_index_physical_stats depuis la version 2005 de SQL Server ont certainement vu une colonne nommée fragment_count. La documentation Microsoft nous donne la description suivante : Nombre de fragments dans le niveau feuille d’une unité d’allocation IN_ROW_DATA. J’ai déjà eu pas mal de questions à ce sujet car même avec la description fournie nous pouvons avoir du mal à visualiser ce que cette colonne représente exactement et quelle peut être la relation avec la fragmentation des indexes.

Tout d’abord une définition :  un fragment est en réalité une séquence de pages physique contigüe. Un index possède au minimum un fragment et il peut y avoir autant de fragments que de pages dans le pire des scénarios. Pourquoi le pire des scénarios ? Nous le découvrirons un peu plus tard mais pour le moment nous prendrons l’exemple suivant :

CREATE TABLE T
(
id INT IDENTITY(1,1),
texte VARCHAR(100)
);

– Index cluster
CREATE UNIQUE CLUSTERED INDEX PK_id
ON T
(
id
);

INSERT T (texte) VALUES (REPLICATE(‘T’, 100))
GO 100

Nous avons donc une table relativement simple avec un index cluster. On remplit la table avec 100 lignes de données ce qui représente environ 10Ko à la louche … autant dire que notre index ne sera composé que de quelques pages. Vérifions le :

SELECT
OBJECT_NAME(object_id) AS [object_name],
index_id,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent,
avg_fragment_size_in_pages AS page_per_fragment,
fragment_count,
page_count,
record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(‘T’), NULL, NULL, ‘DETAILED’);

… qui donne

Effectivement nous avons trois pages dont une page racine et deux pages de niveau feuille qui composent l’index cluster (index_id = 1). J’ai également ajouté les colonnes fragment_count et avg_fragment_size_in_pages. On constate que pour le niveau feuille il existe deux fragments pour deux pages et que la fragmentation de l’index est de 50%. Si deux fragments existent cela signifie que les pages composants le niveau feuille ne se suivent pas physiquement. On peut le vérifier avec la commande DBCC IND. Ici nous nous intéresserons uniquement qu’au niveau feuille de l’index.

CREATE TABLE T_DBCC_IND
(
ID INT IDENTITY(1,1) PRIMARY KEY,
PageFID TINYINT,
PagePID INT,
IAMFID TINYINT,
IAMPID INT,
ObjectID INT,
IndexID TINYINT,
PartitionNumber TINYINT,
PartitionID BIGINT,
iam_chain_type VARCHAR(30),
PageType TINYINT,
IndexLevel TINYINT,
NextPageFID TINYINT,
NextPagePID INT,
PrevPageFID TINYINT,
PrevPagePID INT
);
INSERT INTO T_DBCC_IND
EXEC (‘DBCC IND (test, T, 1)’); 

SELECT *
FROM T_DBCC_IND
WHERE IndexLevel IS NOT NULL;

… qui donne le plan d’allocation de pages suivant :

On constate effectivement que les numéros de pages ne se suivent pas. On peut le représenter schématiquement de la manière suivante :

 

Quelle est le lien avec la fragmentation ? Définissons d’abord ce qu’est la fragmentation. La documentation en ligne donne l’explication suivante concernant la fragmentation logique :

Pourcentage de pages hors service dans les pages de feuilles d’un index. Une page non ordonnée est une page pour laquelle la page physique suivante allouée à l’index n’est pas la page désignée par le pointeur de page suivante dans la page feuille actuelle.

Autant dire que cette explication reste plutôt abscons … Je préfère la définition suivante :

La fragmentation logique représente le nombre de pages dont la page suivante annoncée dans la page IAM (donc le plan d’allocation) ne se trouve pas dans la même portion de fragment.

Nous sommes bien dans ce cas car la page 201 ne se trouve pas dans le même fragment que la page 206. Pour aller de la page 201 à 206 nous devons passer sur un fragment différent pour un lire un total de deux pages. Nous pouvons en déduire la formule suivante :

Fragmentation = (Nb de fragments – 1) / Nb de pages

Dans notre cas nous aurons :

Fragmentation = (2 – 1) / 2 = 50%

On peut vérifier notre raisonnement en ajoutant un jeu de données supplémentaire :

INSERT T (texte) VALUES (REPLICATE(‘T’, 100))
GO 1000

En utilisant la DMV sys.dm_db_index_physical_stats nous pouvons constater que le nombre de pages à bien évidement augmenté.

Le nombre de fragments est maintenant égale à trois avec 16 pages pour le niveau feuille. En utilisant la formule ci-dessus la fragmentation est de (3 – 1) / 16 soit 12.5% .. On peut vérifier par la commande DBCC IND le nombre de fragments :

On retrouve trois fragments composés de la manière suivante :

• Fragment 1 : page 201
• Fragment 2 : {page 206 à page 211}
• Fragment 3 : {page 336 à page 344}

Donc pour aller de la page 201 (niveau feuille) à la page 344 il faudra passer par 2 fragments :

Ajoutons encore quelques données à notre table T.

INSERT T (texte) VALUES (REPLICATE(‘T’, 100))
GO 1000

L’état de notre index cluster est le suivant :

Le nombre de fragments n’a pas augmenté. Le nombre de pages est passé de 16 à 31 soit 15 pages supplémentaires. La fragmentation a diminué puisque le ratio fragments / pages a également diminué. Le plan d’allocation de pages est devenu le suivant :

Aller un dernier petit calcul de fragmentation :

Fragmentation = (3 – 1) / 32 soit 6.25%

Vous aurez donc compris que plus le ratio fragment / pages est petit mieux c’est. Ce ratio évolue lorsque la table (et donc l’index) est mis à jour. Mais ceci fera certainement l’objet d’un autre billet

David BARBARIN (Mikedavem)
MVP SQL Server