{"id":1065,"date":"2010-08-20T19:07:21","date_gmt":"2010-08-20T18:07:21","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=1065"},"modified":"2022-11-21T16:55:47","modified_gmt":"2022-11-21T15:55:47","slug":"fragmentation-sur-des-tables-stockees-en-s-gam","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/","title":{"rendered":"Fragmentation sur des tables stock\u00e9es en S-GAM"},"content":{"rendered":"<a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-twitter nolightbox\" data-provider=\"twitter\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Twitter\" href=\"https:\/\/twitter.com\/intent\/tweet?url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F1065&#038;text=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"twitter\" title=\"Share on Twitter\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-linkedin nolightbox\" data-provider=\"linkedin\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Linkedin\" href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F1065&#038;title=Fragmentation%20sur%20des%20tables%20stock%C3%A9es%20en%20S-GAM\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"linkedin\" title=\"Share on Linkedin\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-mail nolightbox\" data-provider=\"mail\" rel=\"nofollow\" title=\"Share by email\" href=\"mailto:?subject=Fragmentation%20sur%20des%20tables%20stock%C3%A9es%20en%20S-GAM&#038;body=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20:%20https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F1065\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"mail\" title=\"Share by email\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" \/><\/a><p>C&#8217;est une question que j&#8217;ai fr\u00e9quemment eu en formation \u00e0 la suite d&#8217;un TP sur la fragmentation des indexes.<\/p>\n<p>Soit une table &#8216;<em>FILLIALE<\/em>&#8216; avec la d\u00e9finition suivante:<\/p>\n<pre><span style=\"color: #008000;\">FILLIALE {\r\n        [ID_FILLIALE] [int] NOT NULL,\r\n\t[RAISOC] [varchar](50) NULL,\r\n\t[ADRESSE] [varchar](100) NULL,\r\n\t[CODEPOSTAL] [char](5) NULL,\r\n\t[VILLE] [varchar](50) NULL,\r\n\t[ID_GERANT] [int] NULL\r\n}\r\n<\/span><\/pre>\n<p>Elle contient 240 lignes, une cl\u00e9 primaire PK_FILLIALE sur ID_FILLIALE. La mesure du taux de fragmentation de l&#8217;index cluster remonte <strong>66,666666..7 %<\/strong> . L&#8217;intitul\u00e9 du TP indique d&#8217;utiliser ALTER INDEX REBUILD pour la fragmentation\u00a0 sup\u00e9rieure \u00e0 30%, ce serait donc le cas ici. Sauf qu&#8217;un ALTER INDEX REBUILD ne change rien \u00e0 cette valeur:<\/p>\n<pre><span style=\"color: #0000ff;\">select I.name, F.avg_fragmentation_in_percent from sys.indexes I\r\ncross apply sys.dm_db_index_physical_stats(db_id(),object_id('FILLIALE'),\r\n-1,NULL,DEFAULT) F\r\nwhere I.object_id = object_id('FILLIALE')<\/span>\r\n\r\n<em><span style=\"color: #3366ff;\">name            avg_fragmentation_in_percent\r\n--------------  ----------------------------\r\nPK_FILLIALE     66,6666666666667<\/span><\/em>\r\n\r\n<span style=\"color: #0000ff;\">ALTER INDEX PK_FILLIALE ON FILLIALE REBUILD\r\nGO<\/span>\r\n\r\n<span style=\"color: #0000ff;\">select I.name, F.avg_fragmentation_in_percent from sys.indexes I\r\ncross apply sys.dm_db_index_physical_stats(db_id(),object_id('FILLIALE'),\r\n-1,NULL,DEFAULT) F\r\nwhere I.object_id = object_id('FILLIALE')<\/span>\r\n<em><strong>\r\n<\/strong><span style=\"color: #3366ff;\">name            avg_fragmentation_in_percent\r\n--------------  ----------------------------\r\nPK_FILLIALE     66,6666666666667<\/span><\/em><\/pre>\n<p>D&#8217;o\u00f9 la question du stagiaire: &#8220;mais pourquoi si je lance 20 fois la reconstruction de l&#8217;index, la table est toujours fragment\u00e9e \u00e0 66,6% ?&#8221;<\/p>\n<h3>Repr\u00e9sentation physique de la table FILLIALE:<\/h3>\n<p>Petite piq\u00fbre de rappel sur le stockage: les donn\u00e9es sont stock\u00e9es dans des pages de 8192 octets, elles m\u00eames regroup\u00e9es dans des groupes de 8 pages que l&#8217;on appelle <em>extents (on voit aussi <\/em><em>allocation_units <\/em>ou <em>fragments<\/em>). Deux types d&#8217;extents coexistent dans un fichier de donn\u00e9es:<\/p>\n<ul>\n<li><strong>Les extents &#8216;uniformes&#8217;:<\/strong> sont allou\u00e9s pour les tables dont l&#8217;allocation d\u00e9passe 8 pages, donc 64+Kb. Ces pages sont g\u00e9r\u00e9es dans un plan d&#8217;allocation standard qu&#8217;on appelle GAM (pour Global Allocation Map).<\/li>\n<li><strong>Les extents &#8216;mixtes&#8217;:<\/strong> sont allou\u00e9s pour les tables dont l&#8217;allocation initiale est inf\u00e9rieure \u00e0 8 pages. Ces pages sont g\u00e9r\u00e9es dans un plan d&#8217;allocation sp\u00e9cial qu&#8217;on appelle S-GAM (pour Short-GAM ou Shared-GAM comme on veut, personne n&#8217;a jamais compris ce que S voulait vraiment dire, et chacun a sa version).<\/li>\n<\/ul>\n<p>Si\u00a0 on regarde de quelle mani\u00e8re est stock\u00e9e notre table FILLIALE:<\/p>\n<pre><span style=\"color: #0000ff;\">select I.name, F.index_id, F.index_depth, F.index_level, F.avg_fragmentation_in_percent,\r\nF.fragment_count, F.avg_fragment_size_in_pages, F.page_count, record_count\r\nfrom sys.indexes I\r\ncross apply sys.dm_db_index_physical_stats(db_id(),object_id('FILLIALE'),-1,NULL,'DETAILED') F\r\nwhere I.object_id = object_id('FILLIALE')<\/span>\r\n<em>\r\n<span style=\"color: #3366ff;\">name          index_id    index_depth index_level avg_fragmentation_in_percent fragment_count       (...)\r\n------------- ----------- ----------- ----------- ---------------------------- --------------------\r\nPK_FILLIALE   1           2           0           66,6666666666667             3\r\nPK_FILLIALE   1           2           1           0                            1                    \r\n\r\n(...) avg_fragment_size_in_pages page_count           record_count\r\n     -------------------------- -------------------- --------------------\r\n     1                          3                    240\r\n     1                          1                    3<\/span><\/em><\/pre>\n<p>On voit qu&#8217;elle est compos\u00e9e de 4 pages: une page avec un index_level \u00e0 1 qui d\u00e9signe la root page de l&#8217;index cluster. Elle contient 3 lignes (record_count=3) parce qu&#8217;elle pointe vers chacune des pages du niveau inf\u00e9rieur. En dessous (index_level=0), il y a 3 pages qui constituent le niveau feuille de l&#8217;index cluster, donc les donn\u00e9es. Elles contiennent les 240 lignes de notre table.<\/p>\n<p>Si FILLIALE est compos\u00e9e de 4 pages de donn\u00e9es + index, alors elle tombe invariablement dans la cat\u00e9gorie S-GAM. Les valeurs de <em>fragment_count<\/em> et <em>avg_fragment_size_in_pages<\/em> sur la premi\u00e8re ligne nous le montrent: 3 pages dans trois fragments (on rappelle fragment = extent), donc chacune dans le sien. Histoire de bien se repr\u00e9senter visuellement la fa\u00e7on dont la table est stock\u00e9e, on peut utiliser un dbcc ind:<\/p>\n<pre><span style=\"color: #0000ff;\">dbcc ind('CAPDATA','FILLIALE',-1)<\/span>\r\n\r\n<em><span style=\"color: #3366ff;\">PageFID PagePID\u00a0\u00a0\u00a0\u00a0 IAMFID IAMPID\u00a0\u00a0\u00a0\u00a0\u00a0 ObjectID\u00a0\u00a0\u00a0 IndexID\u00a0\u00a0\u00a0\u00a0 PartitionNumber PartitionID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (...)\r\n------- ----------- ------ ----------- ----------- ----------- --------------- --------------------\r\n<span style=\"color: #999999;\">1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 79\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\u00a0\u00a0 NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1189579276\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72057594040221696\u00a0\u00a0\u00a0 <\/span>\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 127\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0 79\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1189579276\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72057594040221696\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 94\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0 79\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1189579276\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72057594040221696\r\n<span style=\"color: #ff0000;\">1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 256\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0 79\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1189579276\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72057594040221696\u00a0\u00a0\u00a0 <\/span>\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 257\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0 79\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1189579276\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72057594040221696\u00a0\u00a0\u00a0 \r\n\r\n(...) iam_chain_type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID\r\n      -------------------- -------- ---------- ----------- ----------- ----------- -----------\r\n  <span style=\"color: #999999;\">    In-row data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0<\/span>\r\n      In-row data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 94\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\n      In-row data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 257\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 127\r\n   <span style=\"color: #ff0000;\">   In-row data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0<\/span>\r\n      In-row data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 94<\/span><\/em><\/pre>\n<p>La page 1:79 est la page d&#8217;allocation de l&#8217;objet, on n&#8217;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&#8217;ordre vers 1:127 -&gt; 1:94 -&gt; 1:257 comme suit:<\/p>\n<pre><strong><span style=\"color: #008000;\">\t\/  |1:127|\r\n\t     |\r\n<span style=\"color: #0000ff;\">|1:256| <\/span>-- <span style=\"color: #ff0000;\">|1:94|<\/span>\r\n\t     |\r\n\t\\  <span style=\"color: #0000ff;\">|1:257|<\/span><\/span><\/strong><\/pre>\n<p>En couleurs les 3 extents o\u00f9 se trouvent physiquement les pages: 1:94 dans un extent, 1:127 dans un autre, et 1:256 \/ 1:257 dans un troisi\u00e8me (elles se suivent et 1:256 est la premi\u00e8re page du 33i\u00e8me extent dans le fichier).<\/p>\n<h3>Calcul de la fragmentation:<\/h3>\n<p>Calculer la fragmentation d&#8217;un index revient \u00e0 calculer le nombre de pages dont la page suivante dans le plan d&#8217;allocation (IAM) n&#8217;est pas la page suivante dans l&#8217;extent:<\/p>\n<p>Si je reprends le cas de la table FILLIALE,le cha\u00eenage des pages de donn\u00e9es se fait comme suit:\u00a0 (1:127) -&gt; (1:94) -&gt; (1:257):<\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2010\/08\/filliale_frag.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1756\" title=\"filliale_frag\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2010\/08\/filliale_frag.jpg\" alt=\"\" width=\"419\" height=\"315\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2010\/08\/filliale_frag.jpg 419w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2010\/08\/filliale_frag-300x225.jpg 300w\" sizes=\"auto, (max-width: 419px) 100vw, 419px\" \/><\/a><\/p>\n<p>Alors que dans le cas id\u00e9al, le cha\u00eenage aurait \u00e9t\u00e9 par exemple (1:88 -&gt; 1:89 -&gt; 1:90). Dans ce cas la page suivante dans le plan d&#8217;allocation est bien la page suivante dans l&#8217;extent. Le probl\u00e8me avec les tables S-GAM est que rien ne garantit que les pages seront adjacentes, parce qu&#8217;elles sont allou\u00e9es dans des extents mixtes.<\/p>\n<p>On peut retrouver cette information avec <strong>DBCC SHOWCONTIG<\/strong>:<\/p>\n<pre><span style=\"color: #0000ff;\">dbcc showcontig('FILLIALE','PK_FILLIALE')<\/span>\r\n\r\n<em><span style=\"color: #3366ff;\">DBCC SHOWCONTIG analyse la table 'FILLIALE'...\r\nTable\u00a0: 'FILLIALE' (1189579276)\u00a0; index ID\u00a0: 1, base de donn\u00e9es ID\u00a0: 5\r\nAnalyse du niveau TABLE effectu\u00e9e.\r\n- Pages analys\u00e9es................................: 3\r\n- Extensions analys\u00e9es..............................: 3\r\n- Commutateurs d'extension..............................: 2\r\n- Moyenne des pages par extension........................: 1.0\r\n<span style=\"color: #3366ff;\"><strong>- Densit\u00e9 d'analyse [meilleure valeur:valeur r\u00e9elle].......: 33.33% [1:3]<\/strong><\/span>\r\n<span style=\"color: #ff0000;\">- Fragmentation d'analyse logique..................: 66.67%<\/span>\r\n- Fragmentation d'analyse d'extension...................: 66.67%\r\n- Moyenne d'octets libres par page.....................: 1877.0\r\n- Densit\u00e9 de page moyenne (compl\u00e8te).....................: 76.81%\r\nEx\u00e9cution de DBCC termin\u00e9e. Si DBCC vous a adress\u00e9 des messages d'erreur, contactez l'administrateur syst\u00e8me.<\/span><\/em><\/pre>\n<p>Sur 3 pages de donn\u00e9es, 2 pages (1:127 et 1:94) ont des next pages dans le plan d&#8217;allocation qui ne sont pas les pages suivantes dans l&#8217;extent. D&#8217;o\u00f9 le 2\/3 =&gt; 66,666%<\/p>\n<p>Dommage que SHOWCONTIG doive dispara\u00eetre \u00e0 terme, l&#8217;affichage me paraissait plus lisible que la DMV qui le remplace. Qui me suit pour une p\u00e9tition ??<\/p>\n<p>A+ David B.<\/p>\n<div id=\"_mcePaste\" style=\"overflow: hidden; position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px;\">\ufeff<\/div>\n<p><script src=\"https:\/\/tcr.tynt.com\/javascripts\/Tracer.js?user=d4FlbGI04r35lZadbi-bpO\" type=\"text\/javascript\"><\/script><\/p>\n<a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-twitter nolightbox\" data-provider=\"twitter\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Twitter\" href=\"https:\/\/twitter.com\/intent\/tweet?url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F1065&#038;text=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"twitter\" title=\"Share on Twitter\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-linkedin nolightbox\" data-provider=\"linkedin\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Linkedin\" href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F1065&#038;title=Fragmentation%20sur%20des%20tables%20stock%C3%A9es%20en%20S-GAM\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"linkedin\" title=\"Share on Linkedin\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-mail nolightbox\" data-provider=\"mail\" rel=\"nofollow\" title=\"Share by email\" href=\"mailto:?subject=Fragmentation%20sur%20des%20tables%20stock%C3%A9es%20en%20S-GAM&#038;body=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20:%20https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F1065\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"mail\" title=\"Share by email\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" \/><\/a>","protected":false},"excerpt":{"rendered":"<p>C&#8217;est une question que j&#8217;ai fr\u00e9quemment eu en formation \u00e0 la suite d&#8217;un TP sur la fragmentation des indexes. Soit une table &#8216;FILLIALE&#8216; avec la d\u00e9finition suivante: FILLIALE { [ID_FILLIALE] [int] NOT NULL, [RAISOC] [varchar](50) NULL, [ADRESSE] [varchar](100) NULL, [CODEPOSTAL]&hellip; <a href=\"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/\" class=\"more-link\">Continuer la lecture <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":7888,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[115,148,146,147],"class_list":["post-1065","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlserver","tag-dbcc-ind","tag-dbcc-showcontig","tag-fragmentation","tag-sgam"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Fragmentation sur des tables stock\u00e9es en S-GAM - Capdata TECH BLOG<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Fragmentation sur des tables stock\u00e9es en S-GAM - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"C&#8217;est une question que j&#8217;ai fr\u00e9quemment eu en formation \u00e0 la suite d&#8217;un TP sur la fragmentation des indexes. Soit une table &#8216;FILLIALE&#8216; avec la d\u00e9finition suivante: FILLIALE { [ID_FILLIALE] [int] NOT NULL, [RAISOC] [varchar](50) NULL, [ADRESSE] [varchar](100) NULL, [CODEPOSTAL]&hellip; Continuer la lecture &rarr;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2010-08-20T18:07:21+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-11-21T15:55:47+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2012\/04\/reorg.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"David Baffaleuf\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"David Baffaleuf\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/\"},\"author\":{\"name\":\"David Baffaleuf\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf\"},\"headline\":\"Fragmentation sur des tables stock\u00e9es en S-GAM\",\"datePublished\":\"2010-08-20T18:07:21+00:00\",\"dateModified\":\"2022-11-21T15:55:47+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/\"},\"wordCount\":662,\"commentCount\":2,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"dbcc ind\",\"dbcc showcontig\",\"fragmentation\",\"SGAM\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/\",\"name\":\"Fragmentation sur des tables stock\u00e9es en S-GAM - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2010-08-20T18:07:21+00:00\",\"dateModified\":\"2022-11-21T15:55:47+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Fragmentation sur des tables stock\u00e9es en S-GAM\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf\",\"name\":\"David Baffaleuf\",\"sameAs\":[\"http:\/\/www.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/dbaffaleuf\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Fragmentation sur des tables stock\u00e9es en S-GAM - Capdata TECH BLOG","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/","og_locale":"fr_FR","og_type":"article","og_title":"Fragmentation sur des tables stock\u00e9es en S-GAM - Capdata TECH BLOG","og_description":"C&#8217;est une question que j&#8217;ai fr\u00e9quemment eu en formation \u00e0 la suite d&#8217;un TP sur la fragmentation des indexes. Soit une table &#8216;FILLIALE&#8216; avec la d\u00e9finition suivante: FILLIALE { [ID_FILLIALE] [int] NOT NULL, [RAISOC] [varchar](50) NULL, [ADRESSE] [varchar](100) NULL, [CODEPOSTAL]&hellip; Continuer la lecture &rarr;","og_url":"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2010-08-20T18:07:21+00:00","article_modified_time":"2022-11-21T15:55:47+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2012\/04\/reorg.jpg","type":"image\/jpeg"}],"author":"David Baffaleuf","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"David Baffaleuf","Dur\u00e9e de lecture estim\u00e9e":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/"},"author":{"name":"David Baffaleuf","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf"},"headline":"Fragmentation sur des tables stock\u00e9es en S-GAM","datePublished":"2010-08-20T18:07:21+00:00","dateModified":"2022-11-21T15:55:47+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/"},"wordCount":662,"commentCount":2,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["dbcc ind","dbcc showcontig","fragmentation","SGAM"],"articleSection":["SQL Server"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/","url":"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/","name":"Fragmentation sur des tables stock\u00e9es en S-GAM - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2010-08-20T18:07:21+00:00","dateModified":"2022-11-21T15:55:47+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/fragmentation-sur-des-tables-stockees-en-s-gam\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Fragmentation sur des tables stock\u00e9es en S-GAM"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf","name":"David Baffaleuf","sameAs":["http:\/\/www.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/dbaffaleuf\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/1065","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=1065"}],"version-history":[{"count":54,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/1065\/revisions"}],"predecessor-version":[{"id":9513,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/1065\/revisions\/9513"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/7888"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=1065"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=1065"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=1065"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}