La suppression des données d’une table peut se réaliser de 2 manières : à l’aide de l’instruction DELETE ou encore TRUNCATE. Ces dernières n’ont pas la même incidence en interne sur le stockage. Il faut également compter avec la structure de la table concernée. En effet une table peut posséder un index ou non. Nous couvrirons dans ce blog les différents comportements qu’il est possible de rencontrer.

Prenons tout d’abord une table HEAP (Pour rappel une telle table ne comporte pas d’index cluster) :

CREATE TABLE heap
(
texte VARCHAR(500)
);

… et insérons  le jeu de données suivant :

INSERT heap VALUES (REPLICATE(‘T’, 500));
GO 100000

Visualisons ensuite le nombre de lignes et de pages que cette table comporte :

SELECT
    partition_id,
    OBJECT_NAME(object_id) AS [object_name],
    index_id,
    used_page_count,
    row_count
FROM sys.dm_db_partition_stats
WHERE OBJECT_NAME(object_id) = ‘heap';

Le résultat est le suivant :

Procédons ensuite à la suppression des données à l’aide de l’instruction DELETE :

DELETE FROM heap;

On peut maintenant penser qu’aucune page de données n’est allouée mais nous pouvons observer le contraire à l’aide de la vue sys.dm_db_partition_stats :

SELECT
    partition_id,
    OBJECT_NAME(object_id) AS [object_name],
    index_id,
    used_page_count,
    row_count
FROM sys.dm_db_partition_stats
WHERE OBJECT_NAME(object_id) = ‘heap';

Le résultat est le suivant :

454 pages sont encore allouées à la table. On peut s’amuser à regarder une à une des pages via la commande non documentée DBCC PAGE et on pourra constater qu’aucune des ces pages ne contiennent de lignes de données. En réalité ce comportement est normal de la part du moteur. Dans le cas d’une table HEAP toutes les pages ne sont pas désallouées. Par conséquent celles-ci ne peuvent plus êtres utilisées par une table différente. Mais ce procédé permet de ne pas à avoir à réallouer un certain nombre de pages lorsqu’à nouveau des données seront insérées. Existe il un moyen de désallouer l’ensemble des pages d’une telle table ? La réponse est oui. On peut par exemple créer un index cluster sur la table et le supprimer ou encore utiliser l’instruction TRUNCATE qui désalloue l’ensemble des pages d’une table contrairement à une instruction DELETE qui supprime une ligne de données à la fois.

Par exemple l’instruction TRUNCATE …

TRUNCATE TABLE heap;

… donne le résultat suivant lorsqu’on utilise la DMV sys.dm_db_partition_stats comme vue précédemment :

On peut également constater que l’espace occupé par la table est maintenant nulle :

EXEC sp_spaceused ‘heap';

Le résultat est le suivant :

Passons maintenant à une table comportant un index cluster :

CREATE TABLE table_with_clustered_index
(
texte VARCHAR(500)
);

Ajoutons lui ensuite un index cluster. L’unicité de l’index n’a ici aucune importance.

CREATE CLUSTERED INDEX IDX_table_with_clustered_index
ON table_with_clustered_index (texte);

Ajoutons lui enfin un jeu de données similaire à la table HEAP :

INSERT table_with_clustered_index VALUES (REPLICATE(‘T’, 500));
GO 100000

Regardons tout d’abord comme précédemment le nombre de lignes et de pages que comporte la table :

SELECT
    partition_id,
    OBJECT_NAME(object_id) AS [object_name],
    index_id,
    used_page_count,
    row_count
FROM sys.dm_db_partition_stats
WHERE OBJECT_NAME(object_id) = ‘table_with_clustered_index';

Le résultat est le suivant :

Au passage on peut constater que le nombre de pages dans ce deuxième cas est plus important que pour une table HEAP. On l’explique très vite par le fait que l’index cluster comporte en plus des pages de données elles mêmes une page d’index racine et une ou plusieurs pages d’index de niveau intermédiaires. On peut voir en détail la structure de notre index à l’aide de la DMF sys.dm_db_index_physical_stats :

SELECT
    OBJECT_NAME(object_id) AS [object_name],
    index_id,
    index_depth,
    index_level,
    fragment_count,
    page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(‘table_with_clustered_index’), NULL, NULL, ‘DETAILED’);

Le résultat est le suivant :

L’index est composé de 6 niveaux. Procédons maintenant à la suppression des données de la table :

DELETE FROM table_with_clustered_index;

… et regardons à nouveau le nombre de pages que comporte notre table à l’aide de la DMV sys.dm_db_partition-stats :

Là aussi on remarque qu’un certain nombre de pages sont encore allouées à la table table_with_clustered_index. Les informations détaillées relative à  l’index sont les suivantes :

On remarque tout d’abord que la totalité des pages de niveau feuille moins une ont été désallouées. Lorsqu’il s’agit de pages d’index, il existe un thread de suppression des pages en arrière plan appelé « Ghost Thread ». En réalité lorsque les pages d’index sont concernés par une instruction DELETE celles-ci sont marquées comme enregistrement fantôme (voir ce billet). Notre thread supprime ensuite les pages de manière asynchrone en fonction de la charge présente sur le serveur. Ensuite on constate qu’une page de données de chaque niveau reste alloué à la table ou plutôt à notre index cluster. Il faut savoir que dans ce cas, une table vide contiendra au moins une page de données même si celle-ci est vide.

Qu’en est il pour les pages de niveau intermédiaire ou racine ? Celles-ci ne sont pas concernées par le processus de suppression asynchrone des pages. Lorsqu’il n’existe plus qu’une entrée correspondante à une page de niveau feuille dans la page de niveau intermédiaire, celle-ci est désallouée et l’unique entrée présente dans cette dernière est transférée dans la page de niveau intermédiaire la plus proche. C’est la raison pour laquelle il n’existe plus qu’une page pour chaque niveau intermédiaire. L’index dans notre cas possédant 6 niveaux, il restera au total 7 pages dont 1 page de niveau feuille + 5 de niveau intermédiaire + 1 page IAM. Vérifions le avec l’instruction DBCC IND

DBCC IND(‘DEMO’, ‘table_with_clustered_index’, -1);

qui donne le résultat suivant :

On peut observer que nous avons 1 page de données (PageType = 1), 5 pages d’index (PageType = 2) et une page IAM (PageType = 10). Alors la question qui vient naturellement après : comment désallouer l’ensemble des pages d’une table avec un index cluster ? Là aussi plusieurs façons de procéder : soit utiliser l’instruction TRUNCATE soit reconstruire l’index cluster.

Par exemple avec la reconstruction de notre index :

ALTER INDEX IDX_table_with_clustered_index
ON table_with_clustered_index REBUILD;

Le résultat avec la DMV sys.dm_db_partition_stats est le suivant :

L’espace occupé est également nul :

EXEC sp_spaceused ‘table_with_clustered_index';

Le résultat est le suivant :

Le comportement est le même en ce qui concerne des index non cluster associé à un index cluster ou une table HEAP. Je vous laisse le vérifier à titre d’exercice

Bonne suppression !!

David BARBARIN (Mikedavem)
MVP SQL Server