Les index columnstore en SQL Server 2012

Les index de type columnstore sont introduit en SQL Server 2012.

Un index columnstore optimisé en mémoire xVelocity regroupe et stocke les données de chaque colonne, puis joint l'ensemble des colonnes pour remplir l'index tout entier. Cela diffère des index classiques qui regroupent et stockent les données de chaque ligne, puis joignent l'ensemble des lignes pour remplir l'index tout entier.

Je vous propose faire un simple test pour voir l’efficacité.

Scénario

Afin de conserver notre test plus simple possible, j'ai créé une seule table de faits "sales1" qui a 2 mesures (prix et quantité) et 13 dimensions "dégradées", dont 10 n'ont pas du nom définitif (dim1..dim10).

CREATE TABLE dbo.sales1 (
    sale_date date,
    id_prod int,
    id_client int,
    id_dim1 int,
    id_dim2 int,
    id_dim3 int,
    id_dim4 int,
    id_dim5 int,
    id_dim6 int,
    id_dim7 int,
    id_dim8 int,
    id_dim9 int,
    id_dim10 int,
    qty int,
    price money
)

Puis on rempli cette table par les données aléatoires simulées les ventes pendant 5 ans depuis 2007. La modalité des dimensions est différent et paramétrée par les valeurs dans le script.

Pour ce test simple il nous sera suffisant 100 millions de lignes.

On crée l'index cluster sur la colonne de date en hypothèse qu'elle est utilisée dans la majorité des requêtes.

CREATE CLUSTERED INDEX IX1_sales1 ON dbo.sales1(sale_date)

Voici nos requêtes de test (n'oubliez pas DBCC DROPCLEANBUFFERS) :

-- Q1
SELECT
    id_prod,
    id_dim2,
    sum(qty * price)
FROM dbo.sales1
WHERE
    sale_date BETWEEN '20080101' AND '20090101' AND
    id_dim2 IN (3, 5, 12, 50, 52, 80)
GROUP BY id_prod, id_dim2

-- Q2
SELECT
    id_prod,
    id_dim2,
    sum(qty * price)
FROM dbo.sales1
WHERE
    sale_date BETWEEN '20080101' AND '20090101' AND
    id_prod IN (5, 10, 15, 20, 50, 100, 150, 200, 300, 400, 500)
GROUP BY id_prod, id_dim2

Télécharger les scripts.

Cas 1. Un seul index cluster

Nous allons lancer les requêtes Q1 et Q2 sans avoir les index supplémentaires sauf celui cluster sur la dimension de la date. Le plan et la performance seront identique pour les 2 requêtes.

Les I/O et le temps :

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sales1'. Scan count 7, logical reads 204483, physical reads 3, read-ahead reads 203468, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
 SQL Server Execution Times:
   CPU time = 4724 ms,  elapsed time = 28493 ms.

Voir l'espace alloué (une petite surcharge de la taille d'index puisque il n'est pas unique) :

table_name row_count  reserved_size_kb data_size_kb index_size_kb
---------- ---------- ---------------- ------------ -------------
sales1     100000000  9022176          8080688      23208       

Cas 2. Un index supplémentaire

Afin d'avoir l'index supplémentaire utile il nous faut inclure les colonnes sinon en combinaison d'index cluster l'optimiseur SQL le refusera (voir Multiples index simples en OLAP (Décisionnel)).

CREATE INDEX IX2_sales1 ON dbo.sales1(id_dim2) INCLUDE (id_prod, qty, price)

Nous avons un plan attendu pour Q1 :

Les I/O et le temps :

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sales1'. Scan count 23, logical reads 5626, physical reads 0, read-ahead reads 5541, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
 SQL Server Execution Times:
   CPU time = 653 ms,  elapsed time = 853 ms.

Félicitation, on gagne le temps ! Mais voyez l’espace alloué : ce n'est pas gratuit du tout.

table_name row_count  reserved_size_kb data_size_kb index_size_kb
---------- ---------- ---------------- ------------ -------------
sales1     100000000  9022176          8080688      3705304

En plus, cet index est inutile pour Q2, on est bien revenu vers le cas 1.

 SQL Server Execution Times:
   CPU time = 5350 ms,  elapsed time = 28201 ms.

Il ne s'agit donc de l'optimisation très spécifique est chère en terme d'espace.

Sous-cas 2.1. Plusieurs index supplémentaires

En décisionnel la recommandation "par défaut" est créer les index supplémentaires sur les colonnes de dimensions. Cette approche permet utiliser ces index dans certaines cas. Mais comme nos requêtes ont le filtre sur la colonne d'index cluster, l'optimiseur refusera ces index.

CREATE INDEX IX21_sales1 ON dbo.sales1(id_prod);
CREATE INDEX IX22_sales1 ON dbo.sales1(id_dim2);

Comme attendu, ni Q1 ni Q2 ne prennent pas en compte ces index et le plan/performance corresponde le cas 1.

Cas 3. Un index columnstore

Maintenant on rajout l'index "columnstore" (attention pour les ordinateurs en x86 / 4 Go de mémoires, pour éviter l'erreur de manque des ressources fermez tous les applications, rajouter l'option de démarrage "-m" et puis redémarrez le service SQL avant créer l'index).

CREATE NONCLUSTERED COLUMNSTORE INDEX IX3_sales1
ON dbo.sales1
(
    sale_date,
    id_prod,
    id_dim2,
    qty,
    price
)

Nous avons un plan identique pour Q1 et Q2 :

Également, la performance est la même pour Q1 et Q2. Nous avons significativement gagné le temps, en 20 fois.

Table 'sales1'. Scan count 6, logical reads 40576, physical reads 91, read-ahead reads 143036, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
 SQL Server Execution Times:
   CPU time = 311 ms,  elapsed time = 1541 ms.

Mais on est dans le cas générique. Un index sert à toute les requêtes dont les critères incluent dans les colonnes d'index.

Enfin, voir l'espace d'index columnstore :

SELECT SUM(on_disk_size_MB) AS TotalSizeInMB
FROM
(
   (SELECT SUM(css.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_segments AS css
        ON css.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('dbo.sales1') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
  UNION ALL
   (SELECT SUM(csd.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_dictionaries AS csd
        ON csd.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('dbo.sales1') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
) AS SegmentsPlusDictionary

TotalSizeInMB
---------------------------------------
874.483283996581

C'est volumineuse, mais n'est rien avoir par rapport des plusieurs index supplémentaires avec des colonnes inclues construits pour les cas spécifiques (voir cas 2).

En bref

Évidemment, notre gain de temps n'est pas gratuit, la volume montre la croissance significative comparable de la taille des données.

Par contre, l'approche "index columnstore" est générique et peut être réutilisé dans différents combinaison des critères et colonnes sélectionnées, alors que l'index simple (cas 2) reste spécifique pour la requête donnée.

Alors, je vous souhaite un bon courage en choix équilibre entre la rapidité et volume qui nous propose SQL Server 2012.

AttachmentSize
Package icon columnstore.zip2.93 KB