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 tablealter 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 :
- Fragmentation sur des tables stockées en S-GAM (David Baffaleuf) [SQL Server]
- Récupérer l’espace consommé par le versionning de lignes (David Baffaleuf) [SQL Server]
- Intérêt de créer des indexes cluster uniques (David Baffaleuf) [SQL Server]
- Msg 2508, Level 16, State 1: the In-Row data %% for object %% is incorrect (David Baffaleuf) [SQL Server]
- Modifier PAGE_VERIFY après une migration depuis SQL 2000 (David Baffaleuf) [SQL Server]