Vote utilisateur: 5 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles activesEtoiles actives
 

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 y a cependant quelques restrictions liées à ce type d'index :
  • 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)
Voyons comment créer ce type d'index et comparons le à un index classique.
Avec SSMS on voit l'apparition de nouveau type d'index dont le Columnstore Index :

 

Création d'un nouvel index columnstore via SSMS

 

 

 

Il faut ensuite choisir les colonnes à indexer. Comme il ne peut il n'y avoir qu'un seul index de type columnstore, le choix s'oriente donc vers une indexation de toutes les colonnes utiles :

 

Choix des colonnes à indexer
Une fois l'index créé il est listé dans SSMS avec un icone particulier :
Icone d'un index en colone sur MSSQL 2012
Comparons les résultats d'une requête de type decisionnelle simple :
Un requête de comptage sans index en colonne La requête basée sur la table originale (8 millions de lignes) renvoie un résultat en environ 21 secondes.

 

IO et Time Statistics pour la requête sans index columnstore

On note :

une forte consommation CPU (15s)

De nombreux IOs logiques (150000)

 

Les résultats avec l'index en colonne :

Résultat de la requête avec l'index de type columnstore

La requête prend environ 2s en utilisant l'index en colonne.

Soit 10 fois plus rapide !!!

 

IO et Time Statistics pour la requête avec l'index en colonne

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)
  

 

 

 

Taille de l'index en colonne