Le Post Infeeny

Les articles des consultants et experts Infeeny

[SQL] ColumnStore Indexes : The turbobooster Inside SQL 2012

Speaker : Klaus Aschenbrenner – Niveau : 300 – Durée : 75 mn

Objectif de la session

Comment améliorer la performance de votre DWH en utilisant les column store indexes.

Savoir quand les utiliser et en connaître les limites.

Introduction

On commence par une rapide présentnation sur ce nouveau type d’indes

  • stockage des données par colonne,
  • gros taux de compression,
  • nouveau mode de requêtage (par batch et non ligne à ligne)

C’est un vecteur de gain énorme sur les workloads DWH :

  • Aggregations
  • Utilisation des jointures sur un modèle en étoile
  • Filtres

Les avantages

  • Meilleure compression (utilisation du moteur Vertipaq) => moins d’I/O
  • N’utilise que les colonnes qui sont lues => meilleur gestion de la mémoire
  • Elimination par segment => très performant

Les limitations

  • On ne peut déclarer que des non-clustered indexes mais nous avons vu lors de la première Key Note que cette limitations sera supprimée prochainement (mais quand? Quelle version ?)
  • Les CSI ne peuvent être mis que sur des tables en read-only.
  • On peut déclarer qu’un seul CSI par table
  • Reste couteux en terme d’espace car ils ne sont pas encore disponibles en CLUSTERED indexes, on ne peut donc pour l’instant faire qie des Index scan sur ce type d’index.

Comparatif Row Store Vs Colum Store

Avec le Column Store, on ne lit que les pages de données dont nous avons besoin et pas toutes les pages contrairement au Row Store.

Les données sont organisées par page de colonne et non de ligne, on ne sollicite alors que les colonnes qui sont présentes dans l’ordre select.

Les types de données

  • Supportées : int,real, string, money, datetime, decimal <= 18 digits
  • Non supportés, decimal > 18 digits, XML, binary, CLS

Quelques  considérations

Dans une requête on peut spécifier de ne pas utiliser par les CSI avec

OPTION (IGNORE_NONCLUSTERD_COLUMSTORE_INDEX)

Comme on a qu’un seul CSI par table MS recommande de créer le CSI sur toute les colonnes d’une table.

Création d’un column store index est très consonmateur en CPU et mémoire.

Comment modifier les données dans une table avec un CSI

3 scénarios possibles

  • Déactiver le CSI, faire vos opérations UPDATE/DELETE/INSERT puis reconstruire le CSI (reoargniser un CSI ne sert à rien). La table grossissant à chaque fois l’opération devient de plus en plus longue.
  • Créer une grosse table avec CSI qui résulte d’un UNION ALL sur plusieurs tables plus petites (on appelle çà le Trickle Loading). On doit droper et recréer la table à chaque fois. La table grossissant à chaque fois l’opération devient de plus en plus longue.
  • Partition Switching (ma préféré et la seule viable à mon sens !!!). La table de staging doit avoir le même structure que notre table de données (pour que le partition switching fonctionne il faut ajouter une check constraint sur les données pour que SQL Server soit sr que les données contenues ne sont ). On insère les données dans la table de staging puis on crée  le même clustered index et le même CSI. Puis on switch les données de la table de staging dans la table partitionnée. Cela est quasi instantanée.

Segments de colonnes

  • Une segment contient les vleurs d’une colonne pour un jeux de lignes (envr. 1 million de ligne spar segment)
  • Les segments sont compressés
  • Chaque segment est stocké est stocké dans un LOB_DATA

Query Memory Grants

CSI à besoin de a query memory grants pour pouvoir créer le CSI sinon et bien çà plante !

  • sys_dm_exe_qery_memory_grant
  • 8Mo*DOP*Nb colonnes dans le CSI

Si la création de votre CSI plante, c’est que vous n’avez pas assez de mémoire. Pour contourner cela vous devz :

  • Faire une Alter workload_group
  • Modifier le Request_mas_memory_gran_percent (par défaut à 25% sur la démo nous l’avons passé à 60%)
  • Reconfigure les ressource gouvernor
  • Puis créer votre CSI

Démo

Les test sont effectués sur une machine avec 16 Go de RAM, 1 core i7 Q840 de 1.87 Ghz

La démo commence par un simple group by (ressources utilisées CPU Time 3110ms, temps d’exec 874 ms ):

sur le plan d’execution on se rend compte qu’on manipule 12 millions de lignes sur une table de 302 Go de données)

l’index cluster prend 40 Go, le CSI ne prend lui que 25Go (ce qui au vu des colonnes et du nombre de lignes est vraiment petit)

On voit ensuite l’implémentation de méthode de partition switching (le switch se fait en 2ms)

On peut voir le nombre de segments pour notre table CSI (48 segments)

SQL Server optimise les requêtes en faisant de l’élimination de données par segments. Ce qui fait que l’index scan n’est pas catastrophique car SQL Server élimine les données par segments.

Frédéric

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

%d blogueurs aiment cette page :