Qu’est-ce que la fragmentation?

Qu’est-ce que la fragmentation? Comment détecter la fragmentation et comment l’éliminer?

Une table fragmentée est une table où certaines de ses pages de données pointent vers des pages qui ne sont pas les pages immédiatement suivantes dans l’extent, et si toutes ses pages sont contiguës à la fois dans le plan d’allocation et dans les extents, alors la table n’est pas fragmentée.

Un exemple pour mieux comprendre :

Imaginez qu’il existe 3 pages de données pour une table avec un index cluster.Une nouvelle ligne avec une clé primaire de “5” doit être insérée, et puisqu’il s’agit d’un index clustérisé, la nouvelle ligne est insérée dans l’ordre. Étant donné que la page cible est suffisamment complète pour que la nouvelle ligne ne puisse être insérée, SQL Server divise la page approximativement en deux et insère les nouvelles données sur la nouvelle page, comme le montre la figure au-dessus. Maintenant, l’ordre logique de l’index ne correspond pas à l’ordre physique, et l’index est devenu fragmenté.

Pour mesurer la fragmentation des indexes, vous pouvez exécuter la requête suivante sur votre base de données :

SELECT
  I.index_id, I.name,
  DM.avg_fragmentation_in_percent
FROM
  Sys.dm_db_index_physical_stats(db_id(),
  NULL, NULL, NULL, DEFAULT) DM
JOIN sys.Indexes I
ON I.object_id=DM.object_id
AND I.Index_id=DM.index_id

Résultat :

Pour réduire la fragmentation (Défragmentation), vous pouvez utiliser la commande ALTER INDEX [REBUILD | REORGANIZE] :

• Alter Index Reorganize :

  • Réorganise les pages de niveau feuille seulement, et compresse les pages d’indexes supprimant les pages vides.

  • Moins efficace qu’une reconstruction d’index

  • A faire lorsque 5%<=avg_fragmentation_in_percent<=30%

  • Attention : ne met pas les statistiques à jour

  • Requête T-SQL :

USE AdventureWorks2012;   
GO  
-- Reorganize all indexes on the HumanResources.Employee table.  
ALTER INDEX ALL ON HumanResources.Employee  
REORGANIZE ;   
GO

 

• Alter Index Rebuild :

  • Recréation de l’index. Lorsque l’index est clusterisé, la table est aussi réorganisée.

  • Attention : une reconstruction d’index clusterisé avec ALTER INDEX REBUILD ne reconstruit pas les indexes non clusterisés sur la table (v>=7.0), sauf si l’option ALL est précisée

  • A faire lorsque avg_fragmentation_in_percent>30%

  • L’option ONLINE=ON permet de reconstruire un index sans bloquer l’activité.

  • Requête T-SQL :

USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO