Oracle et SQL Server: La Fragmentation

Si les mécanismes vus précédemment dans la série sont relativement similaires, la fragmentation est perçue et gérée de manière radicalement différente entre SQL Server et Oracle.

Qu’est-ce que la fragmentation ?

On parle d’une fragmentation, mais il existe deux  fragmentations sur les objets de bases de données relationnelles.

La fragmentation “externe”

Lorsqu’un ensemble de données est lu sur un disque dur “à plateaux”, cette lecture est infiniment plus rapide lorsque les données sont positionnées physiquement les unes à la suite des autres (on parle de données contiguës). Ceci tient au fait que la tête de lecture du disque ne doit pas se déplacer après chaque donnée lue. Lorsque les données ne sont pas contiguës, on parle de fragmentation externe.

Lorsqu’une table (ou un indexe) est fragmentée, sa lecture depuis le disque est donc plus lente que s’il ne l’était pas. Potentiellement beaucoup plus lente…

  La fragmentation “interne”

Ce phénomène se produit lorsque les blocs (ou pages) de données ne sont pas complètement remplis. L’objet occupe plus de place que nécessaire,  parcourir l’objet est donc plus coûteux en terme d’opérations (sur disque ou en mémoire).

Réduire la fragmentation

Cette opération permettra de réduire le volume de données brassées pour un objet (table ou indexe), et de réduire le temps de ces opérations sur disque.

Elle consiste à

• compacter les données pour que les pages soient remplies
• déplacer certaines pages utilisées pour rétablir la contiguité
• Supprimer les pages vides

On parle alors de réorganisation

Pourquoi la réorganisation est elle systématisée sur SQL Server, alors qu’elle n’est effectuée que de manière exceptionnelle sur Oracle ?

Ceci tient à l’existence d’un objet sur la quasi totalité des tables SQL Server: L’indexe Clusterisé

Pour bien comprendre en quoi l’indexe clusterisé influence la fragmentation, voici quelques rappels sur l’organisation des tables et indexes, et sur la réallocation des pages.

L’organisation d’une table

Une table “classique” suit une organisation dite “Heap”. Les données insérées sont ajoutées “en tas” à la table, sans aucune notion de classement. S’il n’existe pas d’espace disponible dans les pages allouées, le moteur en alloue de nouvelles. S’il existe de l’espace disponible (parce que des données ont été supprimées par exemple), le moteur sait réutiliser les pages libres.

L’exemple suivant, sur SQL Server, illustre ce concept de réutilisation d’espace libre.

--Création d'une table de 4000 octets par ligne (4o pour la colonne INT, 3989 pour la colonne char, et 7 pour le descripteur de ligne)
CREATE TABLE TEST_FRAG(ID int identity PRIMARY KEY NONCLUSTERED, COL char(3989) DEFAULT ' ')
GO
--Ajout de 1000 lignes dans cette table
INSERT INTO TEST_FRAG DEFAULT VALUES
GO 1000
--On regarde le nombre de pages de la table
select I.type_desc,total_pages
from sys.allocation_units AU
inner join sys.partitions P ON P.hobt_id = AU.container_id
inner join sys.indexes I ON (I.object_id =P.object_id AND I.index_id = P.index_id)
WHERE P.object_id = OBJECT_ID('TEST_FRAG')
/*
type_desc total_pages
------------------------------------------------------------ --------------------
HEAP 505
NONCLUSTERED 5
*/
GO
--Les 10 premières lignes de la table (donc les 5 premières pages) sont bien les 10 premières insérées.
select top 10 * FROM TEST_FRAG
/*
ID
1
2
3
4
5
6
7
8
9
10
*/
GO
--Suppression de la moitié des lignes (celles correspondant à un ID pair)
delete FROM TEST_FRAG WHERE ID%2=0
GO
--Le nombre de pages n'a pas baissé.
select I.type_desc,total_pages
from sys.allocation_units AU
inner join sys.partitions P ON P.hobt_id = AU.container_id
inner join sys.indexes I ON (I.object_id =P.object_id AND I.index_id = P.index_id)
WHERE P.object_id = OBJECT_ID('TEST_FRAG')
GO
/*
type_desc total_pages
------------------------------------------------------------ --------------------
HEAP 505
NONCLUSTERED 5
*/

--On insère de nouveau 500 lignes dans la table
INSERT INTO TEST_FRAG DEFAULT VALUES
GO 500

--Nouveau coup d'oeil à l'occupation de l'objet, pas de nouvelle allocation de page sur la table, et une nouvelle page sur l'indexe
select I.type_desc,total_pages
from sys.allocation_units AU
inner join sys.partitions P ON P.hobt_id = AU.container_id
inner join sys.indexes I ON (I.object_id =P.object_id AND I.index_id = P.index_id)
WHERE P.object_id = OBJECT_ID('TEST_FRAG')
/*
type_desc total_pages
------------------------------------------------------------ --------------------
HEAP 505
NONCLUSTERED 6
*/
GO
--On constate que les nouvelles données se sont intercalées avec les 10 premières lignes de la table (donc les 5 premières pages).
select top 10 * FROM TEST_FRAG
/*
ID
1
1002
3
1004
5
1006
7
1008
9
1010
*/
GO
DROP TABLE TEST_FRAG
GO

Lorsqu’il existe un indexe Clusterisé sur SQL Server, ou que la table suit l’organisation Index Organized sur Oracle, cette réutilisation d’espace ne peut s’effectuer puisque (par définition) les données doivent être physiquement triées selon la clé de l’indexe.L’espace libre d’une page ne pourra donc pas(sauf cas exceptionnels) être alloué à une nouvelle ligne.

Concrètement, dans ce type d’organisation de table, si des données sont régulièrement supprimées de manière “aléatoire”, la fragmentation interne peut être très grande.

L’exemple suivant correspond à l’exemple précédent, mais la clé primaire est maintenant un indexe clusterisé…

--Création d'une table de 4000 octets par ligne (4o pour la colonne INT, 3989 pour la colonne char, et 7 pour le descripteur de ligne)
CREATE TABLE TEST_FRAG_CLUSTERED(ID int identity PRIMARY KEY CLUSTERED, COL char(3989) DEFAULT ' ')
GO
--Ajout de 1000 lignes dans cette table
INSERT INTO TEST_FRAG_CLUSTERED DEFAULT VALUES
GO 1000
--On regarde le nombre de pages de la table
select I.type_desc,total_pages
from sys.allocation_units AU
inner join sys.partitions P ON P.hobt_id = AU.container_id
inner join sys.indexes I ON (I.object_id =P.object_id AND I.index_id = P.index_id)
WHERE P.object_id = OBJECT_ID('TEST_FRAG_CLUSTERED')
/*
type_desc total_pages
------------------------------------------------------------ --------------------
CLUSTERED 505
*/
GO
--On regarde l'indexe:
select index_depth,index_level,page_count from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('TEST_FRAG_CLUSTERED'),1,0,'DETAILED')
/*
index_depth index_level page_count
----------- ----------- --------------------
2 0 500
2 1 1
*/
--Les 10 premières lignes de la table (donc les 5 premières pages) sont bien les 10 premières insérées.
select top 10 * FROM TEST_FRAG_CLUSTERED
/*
ID
1
2
3
4
5
6
7
8
9
10
*/
GO
--Suppression de la moitié des lignes (celles correspondant à un ID pair)
delete FROM TEST_FRAG_CLUSTERED WHERE ID%2=0
GO
--Le nombre de pages n'a pas baissé.
select I.type_desc,total_pages
from sys.allocation_units AU
inner join sys.partitions P ON P.hobt_id = AU.container_id
inner join sys.indexes I ON (I.object_id =P.object_id AND I.index_id = P.index_id)
WHERE P.object_id = OBJECT_ID('TEST_FRAG_CLUSTERED')
GO
/*
type_desc total_pages
------------------------------------------------------------ --------------------
CLUSTERED 505
*/

--On insère de nouveau 500 lignes dans la table
INSERT INTO TEST_FRAG_CLUSTERED DEFAULT VALUES
GO 500

--Nouveau coup d'oeil à l'occupation de l'objet, 256 nouvelles pages ont été allouées (254 pour les données de la table, et 2 pour les branches de l'indexe)
select I.type_desc,total_pages
from sys.allocation_units AU
inner join sys.partitions P ON P.hobt_id = AU.container_id
inner join sys.indexes I ON (I.object_id =P.object_id AND I.index_id = P.index_id)
WHERE P.object_id = OBJECT_ID('TEST_FRAG_CLUSTERED')
/*
type_desc total_pages
------------------------------------------------------------ --------------------
CLUSTERED 761
*/
-- L'indexe a grossi, puisque la page de racine ne peut adresser seule les 750 pages de données
select index_depth,index_level,page_count from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('TEST_FRAG_CLUSTERED'),1,0,'DETAILED')
/*
index_depth index_level page_count
----------- ----------- --------------------
3 0 750
3 1 2
3 2 1
*/
GO
--bien entendu, les données sont restées triées...
select top 10 * FROM TEST_FRAG_CLUSTERED
/*
ID
1
3
5
7
9
11
13
15
17
19
*/
GO
--La reconstruction de l'indexe permet de réduire le nombre de pages allouées
ALTER INDEX ALL ON TEST_FRAG_CLUSTERED REBUILD
GO
select index_depth,index_level,page_count from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('TEST_FRAG_CLUSTERED'),1,0,'DETAILED')
/*
index_depth index_level page_count
----------- ----------- --------------------
2 0 501
2 1 1
*/
GO
DROP TABLE TEST_FRAG_CLUSTERED
GO

Sur SQL Server, lorsqu’une clé primaire est crée, celle-ci correspond par défaut à un indexe clusterisé. Et comme la normalisation d’un modèle de données impose l’utilisation systématique d’une clé primaire, la quasi totalité des tables d’une base SQL Server aura un indexe clusterisé.

Donc, par défaut, les tables qui voient leur données modifiées sur SQL Server se fragmentent avec le temps, et les tables sur Oracle restent en général non fragmentées.

L’organisation d’un indexe

L’indexe classique sur Oracle et SQL Server suit une organisation dite “Balanced Tree”. Un indexe est toujours équilibré, c’est à dire que sa profondeur (le nombre de pages à brasser depuis la racine jusqu’aux feuilles) est la même quelque soit la valeur de la clé.

Afin de maintenir cet équilibre tout en préservant l’ordre des valeurs de clé, les moteurs ont recours au mécanisme de “Page Split” lors d’insertions ou modifications de données. En deux mots, lorsqu’une page d’indexe déjà pleine doit contenir une nouvelle entrée:

• la page est séparée en deux moitiés également remplies lorsque la valeur insérée n’est pas la plus grande valeur (on parle de split 50/50, et les deux pages sont à moitié vides)
• ou une nouvelle page est créée contenant uniquement la nouvelle valeur et la plus haute ancienne valeur lorsque la nouvelle valeur est   la plus grande (on parle alors de split 90/10, l’ancienne page est presque pleine alors que la nouvelle est presque vide).

Les références aux données dans l’indexe sont toujours triées selon la clé de l’indexe. L’espace libre dans une page ne peut donc pas être réutilisé par défaut. A noter que lorsqu’une page est complètement vidée, elle n’est pas désallouée et pourra être réutilisée par le moteur (il s’agit des fameuses Deleted Leafs d’ indexe sur Oracle).

Lorsqu’une table est très fortement modifiée, ses indexes peuvent avoir subit beaucoup de Page Splits et posséder un grand nombre de pages vides non désallouées. La profondeur de chaque indexe peut donc être plus grande que sa valeur optimale et l’indexe peut occuper plus de place que nécessaire.

Pour un indexe classique, la perte de performance liée à la fragmentation d’un indexe est a priori très faible.

• Un indexe n’occupe qu’exceptionnellement plus de quelques centaines de méga octets, il résidera donc très souvent dans le Buffer Pool
• Lorsque la profondeur de l’indexe est plus grande que sa valeur optimale, elle ne dépassera qu’exceptionnellement  de 2 cette valeur. Une recherche dans un indexe fragmenté consistera donc à deux accès en mémoire de plus que pour un indexe non fragmenté.

La perte de performance liée à une forte fragmentation est donc généralement plus grande sur SQL Server que sur Oracle.

 Réorganisation

Les mécanismes de réduction de la fragmentation sont différents selon les moteurs.

Oracle

Un indexe peut être reconstruit intégralement grâce à la commande ALTER INDEX REBUILD.

Ses blocs vides peuvent être désalloués grâce à la commande ALTER INDEX SHRINK SPACE (qui déplace les clés de l’indexe pour remplir au maximum les blocs, puis déplace les blocs alloués vers le “début” de l’indexe, puis désalloue les extensions vidées).

Les données d’une table classique n’étant pas triées, la défragmentation d’une table peut s’effectuer en déplaçant des lignes dans certains blocs afin de les remplir, puis éventuellement de désallouer les blocs vides.

La commande ALTER TABLE SHRINK SPACE permet d’effectuer ces opérations.

Attention, cette opération modifie l’identifiant physique de ligne d’une table ( ROWID); Les indexes utilisant cet identifiant, ils sont rendus invalides par cette opération et nécessitent d’être reconstruits.

La vue V$INDEX_STATS donne un grand nombre d’informations sur la fragmentation des indexes (ALTER INDEX VALIDATE STRUCTURE est nécessaire pour que les informations concernant un indexe apparaissent dans cette vue).

SQL Server

Un indexe (ou une table ayant un indexe clusterisé) peut être reconstruit avec la commande ALTER INDEX REBUILD.

Lorsque la fragmentation n’est pas trop grande, il est possible d’effectuer une réorganisation (commande ALTER INDEX REORGANIZE). Cette opération ne va travailler que sur les feuilles de l’indexe. Elle est moins coûteuse en ressources mais moins performante que la reconstruction.

La fragmentation d’un indexe peut être mesurée via la fonction dynamique de gestion sys.dm_db_index_physical_stats. Attention, l’utilisation de cette fonction peut être coûteuse en ressources puisqu’elle nécessite la lecture d’un très grand nombre de pages d’indexes dans la base de données.

Continuez votre lecture sur le blog :

• Fragmentation sur des tables stockées en S-GAM (David Baffaleuf) [SQL Server]
• Msg 2508, Level 16, State 1: the In-Row data %% for object %% is incorrect (David Baffaleuf) [SQL Server]
• Production SQL Server: Réorganisation des objets (Benjamin VESAN) [SQL Server]
• Alter table rebuild (Benjamin VESAN) [SQL Server]
• Question bête: Faut-il créer des plans de maintenance avec Reconstruction ET Réorganisation ? (Benjamin VESAN) [SQL Server]