Histoire d’index : Stockage interne des index filtrés avec SQL Server

Les index filtrés ont été introduit à partir de la version SQL Server 2008. Il existe déjà un certain nombre d’exemples qui illustrent leur intérêt et je vous invite à les regarder si cela n’est déjà fait (voir le billet d’Elsuket). Mais qu’en est il du stockage ? En effet la question peut sembler légitime car les index filtrés ne comportent que les lignes de données qui correspondant à sa définition. Nous verrons comment SQL Server gère ces index en interne.

Le script suivant crée une table nommée test_filtered_index qui comporte un index filtré nommé idx_col3_filtered. Cet index permet de gérer l’unicité des valeurs non nuls de cette colonne. Au préalable une base nommée Internals est créée.

CREATE DATABASE Internals;
GO

USE Internals;
GO

CREATE TABLE test_filtered_index
(
col1 INT IDENTITY(1,1) PRIMARY KEY,
col2 INT NOT NULL,
col3 INT NULL
);

– Création d’un index filtré sur col3
– Cet index permet de gérer l’unicité des données
– de cette colonne pour les valeurs non nuls.
CREATE UNIQUE NONCLUSTERED INDEX idx_col3_filtered
ON test_filtered_index
(
col3
)
WHERE col3 IS NOT NULL;

 

Remplissons ensuite la table test_filtered_index avec un jeu de données. La table comportera 10000 lignes de données. 10% de ces lignes concerneront l’index filtré (soit 1001 lignes).

 

DECLARE @i INT;
SET @i = 0;

WHILE @i <  10000
BEGIN
    IF @i <= 1000
    BEGIN
         INSERT INTO test_filtered_index (col2, col3) VALUES (@i,@i);
         SET @i +=1;
    END
    ELSE
    BEGIN
         INSERT INTO test_filtered_index (col2) VALUES (@i);
         SET @i +=1;
    END  
END;

 

Nous pouvons dès à présent visualiser les métadonnées pour les index de la table. Nous avons 2 index (un index cluster et un index unique non cluster filtré).

SELECT
    o.name AS [object_name],
    i.index_id,
    i.type_desc,
    i.has_filter,
    i.filter_definition
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.object_id = o.object_id
WHERE o.name = ‘test_filtered_index';

qui donne le résultat suivant :

dbcc_index_filtered

LA 2ème ligne concerne notre index filtré. Nous pouvons voir sa définition grâce à la DMV sys.indexes et la colonne filter_definition. Regardons maintenant ce que cela donne en terme de stockage à l’aide de la DMF sys.dm_db_index_physical_stats :

SELECT
    OBJECT_NAME([object_id]) AS [object_name],
    index_id,
    index_type_desc,
    alloc_unit_type_desc,
    index_depth,
    index_level,
    page_count,
    record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(‘test_filtered_index’), null, null, ‘DETAILED’);

qui donne le résultat suivant :

dbcc_index_filtered_2

Premier constat : L’index filtré (index_id = 2) ne possède que les lignes de données qui le concernent (record_count = 1001). Du coup, il faut beaucoup moins de pages pour héberger ces lignes par rapport à un index classique. On peut le vérifier à l’aide des commandes DBCC IND et DBCC PAGE.

DBCC IND (‘Internals’, ‘test_filtered_index’, 2);
GO

qui donne le résultat suivant :

dbcc_index_filtered_3

On a bien 3 pages pour l’index filtré (1 page pour le niveau racine : pagePID = 598531 et 2 pages pour le niveau feuilles : pagePID = 598527 et 598532).

 Page racine :

DBCC PAGE (‘Internals’, 1, 598531, 3);
GO

qui donne le résultat suivant :

dbcc_index_filtered_4 

Au niveau racine, rien ne change. Nous retrouvons les colonnes composant l’index et les pointeurs vers les pages suivantes. La page 598532 possède en principe les lignes de données dont les valeurs de la colonne col3 sont comprises entre 578 et 1001.

 Page niveau feuille :

DBCC PAGE (‘Internals’, 1, 598532, 3);
GO

qui donne le résultat suivant :

dbcc_index_filtered_6

..

dbcc_index_filtered_5

On se retrouve avec un schéma classique d’une page de niveau feuille de n’importe quel autre index non cluster unique. On retrouve la clé d’index composant l’index non cluster et le RID de l’index cluster correspondant. Remarquez également que la dernière ligne de données correspond à la dernière valeur gérée par l’index filtré.

Comme nous venons de le voir, la création d’un index filtré induit que le niveau feuille de cet index ne comporte que les lignes de données correspondant à la définition de cet index.

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon
MVP SQL Server

Laisser un commentaire