![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>Si les m\u00e9canismes vus pr\u00e9c\u00e9demment dans la s\u00e9rie sont\u00a0relativement similaires, la fragmentation est per\u00e7ue et g\u00e9r\u00e9e de mani\u00e8re radicalement diff\u00e9rente entre SQL Server et Oracle.<\/p>\n
<\/p>\n
On parle d’une fragmentation, mais il existe deux \u00a0fragmentations sur les objets de bases de donn\u00e9es relationnelles.<\/p>\n
Lorsqu’un ensemble de donn\u00e9es est lu sur un disque dur “\u00e0 plateaux”, cette lecture est infiniment plus rapide lorsque les donn\u00e9es sont positionn\u00e9es physiquement les unes \u00e0 la suite des autres (on parle de donn\u00e9es contigu\u00ebs). Ceci tient au fait que la t\u00eate de lecture du disque ne doit pas se d\u00e9placer apr\u00e8s chaque donn\u00e9e lue. Lorsque les donn\u00e9es ne sont pas contigu\u00ebs, on parle de fragmentation externe.<\/p>\n
Lorsqu’une table (ou un indexe) est fragment\u00e9e, sa lecture depuis le disque est donc plus lente que s’il ne l’\u00e9tait pas. Potentiellement beaucoup plus lente…<\/p>\n
Ce ph\u00e9nom\u00e8ne se produit lorsque les blocs (ou pages) de donn\u00e9es ne sont pas compl\u00e8tement remplis. L’objet occupe plus de place que n\u00e9cessaire, \u00a0parcourir l’objet est donc plus co\u00fbteux en terme d’op\u00e9rations (sur disque ou en m\u00e9moire).<\/p>\n
<\/p>\n
Cette op\u00e9ration permettra de r\u00e9duire le volume de donn\u00e9es brass\u00e9es pour un objet (table ou indexe), et de r\u00e9duire le temps de ces op\u00e9rations sur disque.<\/p>\n
Elle consiste \u00e0<\/p>\n
On parle alors de r\u00e9organisation<\/p>\n
<\/p>\n
Ceci tient \u00e0 l’existence d’un objet sur la quasi totalit\u00e9 des tables SQL Server: L’indexe Clusteris\u00e9<\/p>\n
<\/p>\n
Pour bien comprendre en quoi l’indexe clusteris\u00e9 influence la fragmentation, voici quelques rappels sur l’organisation des tables et indexes, et sur la r\u00e9allocation des pages.<\/p>\n
Une table “classique” suit une organisation dite “Heap”. Les donn\u00e9es ins\u00e9r\u00e9es sont ajout\u00e9es “en tas” \u00e0 la table, sans aucune notion de classement. S’il n’existe pas d’espace disponible dans les pages allou\u00e9es, le moteur en alloue de nouvelles. S’il existe de l’espace disponible (parce que des donn\u00e9es ont \u00e9t\u00e9 supprim\u00e9es par exemple), le moteur sait r\u00e9utiliser les pages libres.<\/p>\n
L’exemple suivant, sur SQL Server, illustre ce concept de r\u00e9utilisation d’espace libre.<\/p>\n
--Cr\u00e9ation 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)\r\nCREATE TABLE TEST_FRAG(ID int identity PRIMARY KEY NONCLUSTERED, COL char(3989) DEFAULT ' ')\r\nGO\r\n--Ajout de 1000 lignes dans cette table\r\nINSERT INTO TEST_FRAG DEFAULT VALUES\r\nGO 1000\r\n--On regarde le nombre de pages de la table\r\nselect I.type_desc,total_pages\r\nfrom sys.allocation_units AU\r\ninner join sys.partitions P ON P.hobt_id = AU.container_id\r\ninner join sys.indexes I ON (I.object_id =P.object_id AND I.index_id = P.index_id)\r\nWHERE P.object_id = OBJECT_ID('TEST_FRAG')\r\n\/*\r\ntype_desc total_pages\r\n------------------------------------------------------------ --------------------\r\nHEAP 505\r\nNONCLUSTERED 5\r\n*\/\r\nGO\r\n--Les 10 premi\u00e8res lignes de la table (donc les 5 premi\u00e8res pages) sont bien les 10 premi\u00e8res ins\u00e9r\u00e9es.\r\nselect top 10 * FROM TEST_FRAG\r\n\/*\r\nID\r\n1\r\n2\r\n3\r\n4\r\n5\r\n6\r\n7\r\n8\r\n9\r\n10\r\n*\/\r\nGO\r\n--Suppression de la moiti\u00e9 des lignes (celles correspondant \u00e0 un ID pair)\r\ndelete FROM TEST_FRAG WHERE ID%2=0\r\nGO\r\n--Le nombre de pages n'a pas baiss\u00e9.\r\nselect I.type_desc,total_pages\r\nfrom sys.allocation_units AU\r\ninner join sys.partitions P ON P.hobt_id = AU.container_id\r\ninner join sys.indexes I ON (I.object_id =P.object_id AND I.index_id = P.index_id)\r\nWHERE P.object_id = OBJECT_ID('TEST_FRAG')\r\nGO\r\n\/*\r\ntype_desc total_pages\r\n------------------------------------------------------------ --------------------\r\nHEAP 505\r\nNONCLUSTERED 5\r\n*\/\r\n\r\n--On ins\u00e8re de nouveau 500 lignes dans la table\r\nINSERT INTO TEST_FRAG DEFAULT VALUES\r\nGO 500\r\n\r\n--Nouveau coup d'oeil \u00e0 l'occupation de l'objet, pas de nouvelle allocation de page sur la table, et une nouvelle page sur l'indexe\r\nselect I.type_desc,total_pages\r\nfrom sys.allocation_units AU\r\ninner join sys.partitions P ON P.hobt_id = AU.container_id\r\ninner join sys.indexes I ON (I.object_id =P.object_id AND I.index_id = P.index_id)\r\nWHERE P.object_id = OBJECT_ID('TEST_FRAG')\r\n\/*\r\ntype_desc total_pages\r\n------------------------------------------------------------ --------------------\r\nHEAP 505\r\nNONCLUSTERED 6\r\n*\/\r\nGO\r\n--On constate que les nouvelles donn\u00e9es se sont intercal\u00e9es avec les 10 premi\u00e8res lignes de la table (donc les 5 premi\u00e8res pages).\r\nselect top 10 * FROM TEST_FRAG\r\n\/*\r\nID\r\n1\r\n1002\r\n3\r\n1004\r\n5\r\n1006\r\n7\r\n1008\r\n9\r\n1010\r\n*\/\r\nGO\r\nDROP TABLE TEST_FRAG\r\nGO<\/pre>\nLorsqu’il existe un indexe Clusteris\u00e9 sur SQL Server, ou que la table suit l’organisation Index Organized sur Oracle, cette r\u00e9utilisation d’espace ne peut s’effectuer puisque (par d\u00e9finition) les donn\u00e9es doivent \u00eatre physiquement tri\u00e9es selon la cl\u00e9 de l’indexe.L’espace libre d’une page ne pourra donc pas(sauf cas exceptionnels) \u00eatre allou\u00e9 \u00e0 une nouvelle ligne.<\/p>\n
Concr\u00e8tement, dans ce type d’organisation de table, si des donn\u00e9es sont r\u00e9guli\u00e8rement supprim\u00e9es de mani\u00e8re “al\u00e9atoire”, la fragmentation interne peut \u00eatre tr\u00e8s grande.<\/p>\n
L’exemple suivant correspond \u00e0 l’exemple pr\u00e9c\u00e9dent, mais la cl\u00e9 primaire est maintenant un indexe clusteris\u00e9…<\/p>\n
\r\n--Cr\u00e9ation 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)\r\nCREATE TABLE TEST_FRAG_CLUSTERED(ID int identity PRIMARY KEY CLUSTERED, COL char(3989) DEFAULT ' ')\r\nGO\r\n--Ajout de 1000 lignes dans cette table\r\nINSERT INTO TEST_FRAG_CLUSTERED DEFAULT VALUES\r\nGO 1000\r\n--On regarde le nombre de pages de la table\r\nselect I.type_desc,total_pages\r\nfrom sys.allocation_units AU\r\ninner join sys.partitions P ON P.hobt_id = AU.container_id\r\ninner join sys.indexes I ON (I.object_id =P.object_id AND I.index_id = P.index_id)\r\nWHERE P.object_id = OBJECT_ID('TEST_FRAG_CLUSTERED')\r\n\/*\r\ntype_desc total_pages\r\n------------------------------------------------------------ --------------------\r\nCLUSTERED 505\r\n*\/\r\nGO\r\n--On regarde l'indexe:\r\nselect index_depth,index_level,page_count from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('TEST_FRAG_CLUSTERED'),1,0,'DETAILED')\r\n\/*\r\nindex_depth index_level page_count\r\n----------- ----------- --------------------\r\n2 0 500\r\n2 1 1\r\n*\/\r\n--Les 10 premi\u00e8res lignes de la table (donc les 5 premi\u00e8res pages) sont bien les 10 premi\u00e8res ins\u00e9r\u00e9es.\r\nselect top 10 * FROM TEST_FRAG_CLUSTERED\r\n\/*\r\nID\r\n1\r\n2\r\n3\r\n4\r\n5\r\n6\r\n7\r\n8\r\n9\r\n10\r\n*\/\r\nGO\r\n--Suppression de la moiti\u00e9 des lignes (celles correspondant \u00e0 un ID pair)\r\ndelete FROM TEST_FRAG_CLUSTERED WHERE ID%2=0\r\nGO\r\n--Le nombre de pages n'a pas baiss\u00e9.\r\nselect I.type_desc,total_pages\r\nfrom sys.allocation_units AU\r\ninner join sys.partitions P ON P.hobt_id = AU.container_id\r\ninner join sys.indexes I ON (I.object_id =P.object_id AND I.index_id = P.index_id)\r\nWHERE P.object_id = OBJECT_ID('TEST_FRAG_CLUSTERED')\r\nGO\r\n\/*\r\ntype_desc total_pages\r\n------------------------------------------------------------ --------------------\r\nCLUSTERED 505\r\n*\/\r\n\r\n--On ins\u00e8re de nouveau 500 lignes dans la table\r\nINSERT INTO TEST_FRAG_CLUSTERED DEFAULT VALUES\r\nGO 500\r\n\r\n--Nouveau coup d'oeil \u00e0 l'occupation de l'objet, 256 nouvelles pages ont \u00e9t\u00e9 allou\u00e9es (254 pour les donn\u00e9es de la table, et 2 pour les branches de l'indexe)\r\nselect I.type_desc,total_pages\r\nfrom sys.allocation_units AU\r\ninner join sys.partitions P ON P.hobt_id = AU.container_id\r\ninner join sys.indexes I ON (I.object_id =P.object_id AND I.index_id = P.index_id)\r\nWHERE P.object_id = OBJECT_ID('TEST_FRAG_CLUSTERED')\r\n\/*\r\ntype_desc total_pages\r\n------------------------------------------------------------ --------------------\r\nCLUSTERED 761\r\n*\/\r\n-- L'indexe a grossi, puisque la page de racine ne peut adresser seule les 750 pages de donn\u00e9es\r\nselect index_depth,index_level,page_count from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('TEST_FRAG_CLUSTERED'),1,0,'DETAILED')\r\n\/*\r\nindex_depth index_level page_count\r\n----------- ----------- --------------------\r\n3 0 750\r\n3 1 2\r\n3 2 1\r\n*\/\r\nGO\r\n--bien entendu, les donn\u00e9es sont rest\u00e9es tri\u00e9es...\r\nselect top 10 * FROM TEST_FRAG_CLUSTERED\r\n\/*\r\nID\r\n1\r\n3\r\n5\r\n7\r\n9\r\n11\r\n13\r\n15\r\n17\r\n19\r\n*\/\r\nGO\r\n--La reconstruction de l'indexe permet de r\u00e9duire le nombre de pages allou\u00e9es\r\nALTER INDEX ALL ON TEST_FRAG_CLUSTERED REBUILD\r\nGO\r\nselect index_depth,index_level,page_count from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('TEST_FRAG_CLUSTERED'),1,0,'DETAILED')\r\n\/*\r\nindex_depth index_level page_count\r\n----------- ----------- --------------------\r\n2 0 501\r\n2 1 1\r\n*\/\r\nGO\r\nDROP TABLE TEST_FRAG_CLUSTERED\r\nGO<\/pre>\n <\/p>\n
Sur SQL Server, lorsqu’une cl\u00e9 primaire est cr\u00e9e, celle-ci correspond par d\u00e9faut \u00e0 un indexe clusteris\u00e9. Et comme la normalisation d’un mod\u00e8le de donn\u00e9es impose l’utilisation syst\u00e9matique d’une cl\u00e9 primaire, la quasi totalit\u00e9 des tables d’une base SQL Server aura un indexe clusteris\u00e9.<\/p>\n
Donc, par d\u00e9faut, les tables qui voient leur donn\u00e9es modifi\u00e9es sur SQL Server se fragmentent avec le temps, et les tables sur Oracle restent en g\u00e9n\u00e9ral non fragment\u00e9es.<\/h2>\n
<\/p>\n
L’organisation d’un indexe<\/h3>\n
L’indexe classique sur Oracle et SQL Server suit une organisation dite “Balanced Tree”. Un indexe est toujours \u00e9quilibr\u00e9, c’est \u00e0 dire que sa profondeur (le nombre de pages \u00e0 brasser depuis la racine jusqu’aux feuilles) est la m\u00eame quelque soit la valeur de la cl\u00e9.<\/p>\n
Afin de maintenir cet \u00e9quilibre tout en pr\u00e9servant l’ordre des valeurs de cl\u00e9, les moteurs ont recours au m\u00e9canisme de “Page Split” lors d’insertions ou modifications de donn\u00e9es. En deux mots, lorsqu’une page d’indexe d\u00e9j\u00e0 pleine doit contenir une nouvelle entr\u00e9e:<\/p>\n
<\/p>\n
Les r\u00e9f\u00e9rences aux donn\u00e9es dans l’indexe sont toujours tri\u00e9es selon la cl\u00e9 de l’indexe. L’espace libre dans une page ne peut donc pas \u00eatre r\u00e9utilis\u00e9 par d\u00e9faut. A noter que lorsqu’une page est compl\u00e8tement vid\u00e9e, elle n’est pas d\u00e9sallou\u00e9e et pourra \u00eatre r\u00e9utilis\u00e9e par le moteur (il s’agit des fameuses Deleted Leafs d’ indexe sur Oracle).<\/p>\n
Lorsqu’une table est tr\u00e8s fortement modifi\u00e9e, ses indexes peuvent avoir subit beaucoup de Page Splits et poss\u00e9der un grand nombre de pages vides non d\u00e9sallou\u00e9es. La profondeur de chaque indexe peut donc \u00eatre plus grande que sa valeur optimale et l’indexe peut occuper plus de place que n\u00e9cessaire.<\/p>\n
Pour un indexe classique, la perte de performance li\u00e9e \u00e0 la fragmentation d’un indexe est a priori tr\u00e8s faible.<\/p>\n
<\/p>\n
<\/p>\n
Les m\u00e9canismes de r\u00e9duction de la fragmentation sont diff\u00e9rents selon les moteurs.<\/p>\n
Un indexe peut \u00eatre reconstruit int\u00e9gralement gr\u00e2ce \u00e0 la commande ALTER INDEX REBUILD.<\/p>\n
Ses blocs vides peuvent \u00eatre d\u00e9sallou\u00e9s gr\u00e2ce \u00e0 la commande ALTER INDEX SHRINK SPACE (qui d\u00e9place les cl\u00e9s de l’indexe pour remplir au maximum les blocs, puis d\u00e9place les blocs allou\u00e9s vers le “d\u00e9but” de l’indexe, puis d\u00e9salloue les extensions vid\u00e9es).<\/p>\n
Les donn\u00e9es d’une table classique n’\u00e9tant pas tri\u00e9es, la d\u00e9fragmentation d’une table peut s’effectuer en d\u00e9pla\u00e7ant des lignes dans certains blocs afin de les remplir, puis \u00e9ventuellement de d\u00e9sallouer les blocs vides.<\/p>\n
La commande ALTER TABLE SHRINK SPACE permet d’effectuer ces op\u00e9rations.<\/p>\n
Attention, cette op\u00e9ration modifie l’identifiant physique de ligne d’une table ( ROWID); Les indexes utilisant cet identifiant, ils sont rendus invalides par cette op\u00e9ration et n\u00e9cessitent d’\u00eatre reconstruits.<\/p>\n
La vue V$INDEX_STATS<\/a> donne un grand nombre d’informations sur la fragmentation des indexes (ALTER INDEX VALIDATE STRUCTURE est n\u00e9cessaire pour que les informations concernant un indexe apparaissent dans cette vue).<\/p>\n <\/p>\n Un indexe (ou une table ayant un indexe clusteris\u00e9) peut \u00eatre reconstruit avec la commande ALTER INDEX REBUILD.<\/p>\n Lorsque la fragmentation n’est pas trop grande, il est possible d’effectuer une r\u00e9organisation (commande ALTER INDEX REORGANIZE). Cette op\u00e9ration ne va travailler que sur les feuilles de l’indexe. Elle est moins co\u00fbteuse en ressources mais moins performante que la reconstruction.<\/p>\n La fragmentation d’un indexe peut \u00eatre mesur\u00e9e via la fonction dynamique de gestion sys.dm_db_index_physical_stats<\/a>. Attention, l’utilisation de cette fonction peut \u00eatre co\u00fbteuse en ressources puisqu’elle n\u00e9cessite la lecture d’un tr\u00e8s grand nombre de pages d’indexes dans la base de donn\u00e9es.<\/p>\n <\/p>\n Si les m\u00e9canismes vus pr\u00e9c\u00e9demment dans la s\u00e9rie sont\u00a0relativement similaires, la fragmentation est per\u00e7ue et g\u00e9r\u00e9e de mani\u00e8re radicalement diff\u00e9rente entre SQL Server et Oracle. Qu’est-ce que la fragmentation ? On parle d’une fragmentation, mais il existe deux \u00a0fragmentations… Continuer la lecture SQL Server<\/h3>\n
<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"