Récupérer l’espace consommé par le versionning de lignes

Pour cet article, je pars du principe que vous êtes familier du mode d’isolation SNAPSHOT ou READ_COMMITTED_SNAPSHOT (RCSI) qui permettent à SQL Server de se rapprocher du mode READ_COMMITTED par défaut sous Oracle ou InnoDB. Pour plus d’informations, cf la page MSDN concernant ces modes: http://msdn.microsoft.com/en-us/library/tcbchxcb%28v=vs.80%29.aspx

Lorsque SNAPSHOT_ISOLATION ou READ_COMMITTED_SNAPSHOT est activé sur une base, les UPDATES / DELETES vont pousser les lignes originales dans le version store. Donc un des inconvénients d’utiliser ce mécanisme est la pression qui s’ajoute sur tempdb.

Un autre impact moins connu est l’accroissement de l’espace utilisé dans les lignes modifiées: 14 octets de plus vont être utilisés par ligne, 6 pour pour stocker l’identifiant de la transaction qui a modifié la ligne, et 8 pour stocker le RID de la copie dans le version store. On essaie de s’intéresser au nombre de pages avant et après modification en RCSI, et au nombre de page splits potentiels qui peuvent survenir lorsque l’on modifie des données en masse dans une base en mode RCSI.

On créé une table clusterisée d’une seule page que l’on va remplir au maximum. A tâtons on arrive à voir que 250 lignes permettent de remplir la table au maximum de la capacité d’une page:

create database testRCSI
GO
use testRCSI
GO
create table RCSI(a numeric identity, b varchar(10))
GO
insert into RCSI values (replicate('b',10))
GO 250
create unique clustered index PK_RCSI on RCSI(a)
GO
dbcc ind('testRCSI','RCSI',-1)
GO

/*
PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber (...)
------- ----------- ------ ----------- ----------- ----------- --------------- (...)
1       114         NULL   NULL        2073058421  1           1               (...)
1       109         1      114         2073058421  1           1               (...)

PartitionID          iam_chain_type       PageType IndexLevel NextPageFID (...)
-------------- -------------------- -------------------- -------- --------(...)
72057594038386688    In-row data          10       NULL       0           (...)
72057594038386688    In-row data          1        0          0           (...)

*/

select * from sys.dm_db_index_physical_stats(11,2073058421,1,NULL, 'DETAILED')
where Index_Level=0
GO

/* La sortie est simplifiée pour plus de lisibilité
avg_page_space_used_in_percent=98,8139362490734
max_record_size_in_bytes=30
*/

Donc nous savons que notre table est stockée sur la page (1:109), que la page est dense à 98,8% et que la taille max d’une ligne est 30 octets. On passe la base en mode RCSI et on note la valeur de Page Splits courante.

alter database testRCSI set read_committed_snapshot on
GO
select cntr_value from sys.dm_os_performance_counters where counter_name like 'Page Splits%'
GO
/* cntr_value
--------------------
4013
*/

Ensuite, on va mettre à jour les lignes par une valeur de dimension égale (on remplace le caractère ‘b’ par un ‘c’ par exemple), et on s’intéresse en premier lieu à la nouvelle taille max de chaque ligne:

update RCSI set b = (replicate('c',10))
GO
select * from sys.dm_db_index_physical_stats(11,2073058421,1,NULL, 'DETAILED')
where Index_Level=0
GO
 /* La sortie est simplifiée pour plus de lisibilité
 avg_page_space_used_in_percent=71,0155670867309 
 max_record_size_in_bytes=44
 */

On voit que chaque ligne a bien grossi de 14 octets. Le version store quant à lui a bien stocké les 250 lignes modifiées (il faut le faire dans la minute qui suit le commit sinon le version store est recyclé):

select version_store_reserved_page_count from sys.dm_db_file_space_usage
GO
/*
version_store_reserved_page_count
----------------------------------------------
16
*/

Si on regarde le plan d’allocation de notre table mise à jour:

dbcc ind('testRCSI','RCSI',-1)
GO

/*
PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber
------- ----------- ------ ----------- ----------- ----------- ---------------
1       114         NULL   NULL        2073058421  1           1              
1       109         1      114         2073058421  1           1              
1       41          1      114         2073058421  1           1              
1       73          1      114         2073058421  1           1              

PartitionID          iam_chain_type       PageType IndexLevel (...)
-------------------- -------------------- -------- ---------- (...)
72057594038386688    In-row data          10       NULL       (...)
72057594038386688    In-row data          1        0          (...)
72057594038386688    In-row data          2        1          (...)
72057594038386688    In-row data          1        0          (...)

*/

Une page root a été ajoutée pour l’index clusterisé (PageType=2) plus une nouvelle page leaf (PageType=1). On n’est plus sur une seule page de données mais sur deux maitenant pourtant pour la même quantité de données, ce qui signifie qu’un page split est intervenu pour couper notre page initiale en deux.  Un rapide coup d’oeil au compteur perfmon va nous le confirmer (je précise que l’instance est idle):

select cntr_value from sys.dm_os_performance_counters where counter_name like 'Page Splits%'
GO
/*
cntr_value
--------------------
4015
*/

Pour récupérer cet espace, reconstruire l’index cluster devrait suffire:

alter index PK_RCSI on RCSI rebuild
GO
dbcc ind('testRCSI','RCSI',-1)
GO

/*
PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber (...)
------- ----------- ------ ----------- ----------- ----------- --------------- (...)
1       89         NULL   NULL        2073058421  1           1               (...)
1       80         1      114         2073058421  1           1               (...)

PartitionID          iam_chain_type       PageType IndexLevel NextPageFID (...)
-------------- -------------------- -------------------- -------- --------(...)
72057594038386688    In-row data          10       NULL       0           (...)
72057594038386688    In-row data          1        0          0           (...)

*/

L’objet a été déplacé et réorganisé en entier (les pages sont différentes). On retrouve notre page unique du début. Quant à la taille des lignes, elle est revenue à sa valeur nominale:

select * from sys.dm_db_index_physical_stats(11,2073058421,1,NULL, 'DETAILED') where Index_Level=0
GO
/* La sortie est simplifiée pour plus de lisibilité
avg_page_space_used_in_percent=98,8139362490734
max_record_size_in_bytes=30
*/

Morale de l’histoire, attention aux page splits et à l’espace qu’occupent des lignes versionnées en mode SNAPSHOT_ISOLATION ou READ_COMMITTED_SNAPSHOT. Et reconstruire fréquemment les indexes sur ces tables permet de récupérer l’espace perdu.

A+. David B.

Continuez votre lecture sur le blog :

• Alter table rebuild (Benjamin VESAN) [SQL Server]
• Fragmentation sur des tables stockées en S-GAM (David Baffaleuf) [SQL Server]
• Intérêt de créer des indexes cluster uniques (David Baffaleuf) [SQL Server]
• Modifier PAGE_VERIFY après une migration depuis SQL 2000 (David Baffaleuf) [SQL Server]
• Error 8976 / 8978, problèmes de chaînage, comment récupérer les données (David Baffaleuf) [SQL Server]