Alter table rebuild

mercredi, mars 2, 2011
By Benjamin VESAN in SQL Server (bvesan@capdata-osmozium.com) [41 article(s)]

Apparue  avec SQL Server 2008, cette nouvelle option permet de reconstruire une table qui n’a pas d’index cluster.  Une table de ce type est appelé un heap. Avant cette version, la solution souvent utilisée pour reconstruire une table heap consiste à créer artificiellement un index cluster sur une ou plusieurs colonnes de la table puis de supprimer le nouvel index. Le choix des colonnes pour créer l’index cluster est parfois complexe, car il détermine l’ordre de stockage des lignes dans les pages de la table. La commande Alter table rebuild est intéressante à plus d’un titre :

  • Plus de question à se poser sur le choix des colonnes pour reconstruire la table
  • Elle est simple à utiliser : Alter table TABLE1 rebuild
  • Elle permet de diminuer le nombre de « forwarding record » dans les pages de données.
  • Lorsque l’on met à jour les données d’une ligne et qu’il ne reste pas suffisamment d’espace libre dans la page, SQL Server enregistre une information « forwarding record » qui contient un pointeur vers l’adresse de la page qui contient la suite de la ligne. Ce phénomène est pénalisant pour les performances, car il impose des déplacements coûteux entre les pages lors des lectures de lignes.
  • Elle permet de réduire le nombre de pages utilisées par une table après une réduction importante des données des lignes.
  • Les indexes non cluster sont reconstruits en même temps que la table
  • Si on est en version Enterprise, il est possible de reconstruire la table ONLINE , c’est à dire en optimisant le verrouillage de la table pour pénaliser le moins possible les accès à celle_ci lors de la reconstruction.
  • Voici 2 exemples d’utilisation

— #######################################################################
— # Demo1 : Reconstruire un HEAP pour réduire les « forwarding record »
— #######################################################################

-- creation base de tests
drop database B1
Create database B1
alter database B1 set recovery simple
use B1
go

-- creation table HEAP exemple
create table TABLE1(a numeric identity, b varchar(4000), c varchar(4000))
go

-- ajoute 1000 lignes dans la table
insert into TABLE1 values ('b','c')
go 1000

-- Inspection de son plan d'allocation
dbcc traceon (3604)dbcc ind('B1', 'TABLE1',-1)
PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID        
------- ----------- ------ ----------- ----------- ----------- --------------- --------------------
1       77          NULL   NULL        2105058535  0           1               72057594038779904  
1       55          1      77          2105058535  0           1               72057594038779904  
1       78          1      77          2105058535  0           1               72057594038779904  
1       79          1      77          2105058535  0           1               72057594038779904  
1       80          1      77          2105058535  0           1               72057594038779904   ...
(5 row(s) affected)
=> 5 pages (1 IAM et 4 Data)
-- Mise à jour de la table pour provoquer des débordements de lignes :
update TABLE1 set b=(replicate('b',1000)), c=(replicate('c',1000))

-- Nouveau plan d'allocation
dbcc ind('B1', 'TABLE1',-1)
PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID        
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------
1       77          NULL   NULL        2105058535  0           1               72057594038779904  
1       55          1      77          2105058535  0           1               72057594038779904  
1       78          1      77          2105058535  0           1               72057594038779904  
1       79          1      77          2105058535  0           1               72057594038779904  
1       80          1      77          2105058535  0           1               72057594038779904  
1       89          1      77          2105058535  0           1               72057594038779904  
1       90          1      77          2105058535  0           1               72057594038779904  
1       93          1      77          2105058535  0           1               72057594038779904  
1       94          1      77          2105058535  0           1               72057594038779904  
1       176         1      77          2105058535  0           1               72057594038779904  
1       177         1      77          2105058535  0           1               72057594038779904  
1       178         1      77          2105058535  0           1               72057594038779904  ...
...
--(336 row(s) affected)
--=> 336 pages (1 page IAM + 335 pages de donnees)
-- Affiche fragmentation
select object_name(F.object_id) Object, index_type_desc, page_count, record_count, forwarded_record_count, avg_fragmentation_in_percent from sys.indexes I
cross apply sys.dm_db_index_physical_stats(db_id(),object_id('TABLE1'), -1,NULL,'DETAILED') F where I.object_id = object_id('TABLE1')
Object                         index_type_desc                page_count           record_count         forwarded_record_count avg_fragmentation_in_percent
------------------------------ ------------------------------ -------------------- -------------------- ---------------------- ----------------------------
TABLE1                         HEAP                           335                  1993                 993                    4,44444444444444
=> 335 pages
=> 1993 records pour 1000 lignes
=> 993 forwarded_record_count - 993 lignes sur 1000 contiennent un pointeur vers une autre page qui contient la suite de la page, c'est pas terrible
=> fragmentation : 4.4 %

-- Affiche le contenu de la page 78
dbcc page('B1', 1, 78, 3)
Slot 0 Offset 0x258 Length 9
Record Type = FORWARDING_STUB        Record Attributes =                  Record Size = 9
Memory Dump @0x46E6C258
00000000:   040d0100 00010001 00†††††††††††††††††.........
Forwarding to  =  file 1 page 269 slot 1
Slot 1 Offset 0x261 Length 9
=> informations pour le row forwarding

-- reconstruit la table TABLE1
alter table TABLE1 rebuild

-- Nouveau plan d'allocation
dbcc ind('B1', 'TABLE1',-1)
PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type      
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- --------------------
1       110         NULL   NULL        2105058535  0           1               72057594038845440    In-row data          
1       109         1      110         2105058535  0           1               72057594038845440    In-row data          
1       504         1      110         2105058535  0           1               72057594038845440    In-row data          
1       505         1      110         2105058535  0           1               72057594038845440    In-row data          
...
(252 row(s) affected)
=> la table a été reconstruite a un nouvel emplacement, elle utilise 252 pages au lieu de 335
-- Affiche fragmentation
select object_name(F.object_id) Object, index_type_desc, page_count, record_count, forwarded_record_count, avg_fragmentation_in_percent from sys.indexes I
cross apply sys.dm_db_index_physical_stats(db_id(),object_id('TABLE1'), -1,NULL,'DETAILED') F where I.object_id = object_id('TABLE1')

Object                         index_type_desc                page_count           record_count         forwarded_record_count avg_fragmentation_in_percent
------------------------------ ------------------------------ -------------------- -------------------- ---------------------- ----------------------------
TABLE1                         HEAP                           251                  1000                 0                      3,03030303030303
=> 3 lignes par page, le niveau de fragmentation a légèrement baissé
=> et surtout il n'y a plus de forwarded record

#########################################
-- Demo2 : On continue l'exemple précédent, on va Reconstruire la table pour optimiser le nombre de pages après une Réduction des données dans les lignes:
#########################################
-- reduction des donnees dans les lignes
update TABLE1 set b='b', c='c'
(1000 row(s) affected)
-- Affiche fragmentation
select object_name(F.object_id) Object, avg_page_space_used_in_percent, index_type_desc, page_count, record_count, forwarded_record_count, avg_fragmentation_in_percent from sys.indexes I
cross apply sys.dm_db_index_physical_stats(db_id(),object_id('TABLE1'), -1,NULL,'DETAILED') F where I.object_id = object_id('TABLE1')

Object                         avg_page_space_used_in_percent index_type_desc                page_count           record_count         forwarded_record_count avg_fragmentation_in_percent
------------------------------ ------------------------------ ------------------------------ -------------------- -------------------- ---------------------- ---------------------------- TABLE1                         1,25517667407957               HEAP                           251                  1000                 0                      3,03030303030303
=> avg_page_space_used_in_percent : 1,25% d'espace utilise par page c'est faible !
-- reconstruction de la table
alter table TABLE1 rebuild
-- plan d'allocation
dbcc ind('B1', 'TABLE1',-1)
PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type      
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- --------------------
1       121         NULL   NULL        2105058535  0           1               72057594038976512    In-row data          
1       120         1      121         2105058535  0           1               72057594038976512    In-row data          
1       400         1      121         2105058535  0           1               72057594038976512    In-row data          
1       401         1      121         2105058535  0           1               72057594038976512    In-row data          
1       402         1      121         2105058535  0           1               72057594038976512    In-row data          
1       403         1      121         2105058535  0           1               72057594038976512    In-row data          
(6 row(s) affected)
=> A nouveau 6 pages
Object                         avg_page_space_used_in_percent index_type_desc                page_count           record_count         forwarded_record_count avg_fragmentation_in_percent
------------------------------ ------------------------------ ------------------------------ -------------------- -------------------- ---------------------- ----------------------------
TABLE1                         64,225352112676                HEAP                           5                    1000                 0                      50
=> avg_page_space_used_in_percent : 64,22 % c'est mieux !
fragmentation 50% : pas genant pour une table de cette taille (- de 8 pages)

Continuez votre lecture sur le blog :




Cliquer pour partager cet article sur Viadeo
Cliquer sur "CAPTURER" pour sauvegarder cet article dans Evernote Clip to Evernote

Tags: ,

Leave a Reply