La version 11 de SQL Server (aka MSSQL 2012 ou Denali) introduit un nouveau type d'index permettant de stocker les données en mode colonnes.
Les columnstore index, particulièrement adaptés aux requêtes décisionelles accélèrent ces requêtes pour plusieurs raisons :
- Les données stockées sont compressées assez fortement, ce qui entraîne moins d'IOs physiques et moins de consommation mémoire.
- Les données stockées en colonnes permettent un balayage limité des données en fonction des colonnes demandées dans la requête.
- Il n'est pas possible d'écrire sur une table ayant un index en colonne. Il est cependant possible de contourner ce type de limitation avec le partitionnement : on écrit dans un table intermdiaire sans index, on créer l'index sur la table intermédiaire, on switch la table intermédiaire avec une partition de la table cible.
- Il ne peut il y avoir qu'un seul index de type columnstore par table. Vu que c'est un stockage en mode colonne, on aura donc tendance à avoir un index incluant toutes les colonnes utiles de la table.
- Au moment de la création de l'index, il y a une forte demande de mémoire. La mémoire demander est exterieure au buffer pool (donc en plus du max memory/target)
 |
 |
 |
La requête basée sur la table originale (8 millions de lignes) renvoie un résultat en environ 21 secondes. |
|
|
On note : une forte consommation CPU (15s) De nombreux IOs logiques (150000) |
Les résultats avec l'index en colonne :
 |
La requête prend environ 2s en utilisant l'index en colonne. Soit 10 fois plus rapide !!! |
|
|
La requête ne consomme plus que 3.3s de cpu et seulement 43000 IO logiques. |
Calculons maintenant la taille de cet index en colonne :
WITH
T1 AS
(
SELECT
OBJECT_NAME(i.OBJECT_ID) table_name,
i.name index_name,
SUM(css.on_disk_size)/(1024.0*1024.0) segment_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.type_desc = 'NONCLUSTERED COLUMNSTORE'
GROUP BY
OBJECT_NAME(i.OBJECT_ID),
i.name
) ,
T2 AS
(
SELECT
OBJECT_NAME(i.OBJECT_ID) table_name,
i.name index_name,
SUM(csd.on_disk_size)/(1024.0*1024.0) dictionary_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.type_desc = 'NONCLUSTERED COLUMNSTORE'
GROUP BY
OBJECT_NAME(i.OBJECT_ID),
i.name
)
SELECT
t1.table_name,
t1.index_name,
segment_on_disk_size_MB,
dictionary_on_disk_size_MB,
(dictionary_on_disk_size_MB+segment_on_disk_size_MB) total_on_disk_size_MB
FROM T1 INNER join T2 ON (T1.table_name=T2.table_name and T1.index_name=T2.index_name)
 |