Le Post Infeeny

Les articles des consultants et experts Infeeny

SQL Server – les columnstore index à la rescousse de la BI

Petit retour d’expérience sur l’usage des CCSI (Clustered Column Store Index) avec SQL Server 2014.

Avec un de nos clients, nous avons fait quelques bench sur le sujet. Sur une architecture équivalente, nous avons mis en place un index columnstore sur une table de fait contenant plus de 300 millions d’enregistrements. Malgré me dires et ceux de Microsoft, mes interlocuteurs étaient très sceptiques tout en fondant de grands espoirs.

La mise en œuvre n’a pris qu’une 10aine de minutes. Il a fallu dans un premier temps supprimer tous les indexes existants ainsi que les différentes clé étrangères et créer tout simplement le CCSI:

CREATE CLUSTERED COLUMNSTORE INDEX cci_Sale ON fac.Sale;

Le résultat ne s’est pas fait attendre, nous avons noté :

  • un gain impressionnant au niveau du stockage, la table qui pesait anciennement 47 Go (non compressée) n’en fait plus que 3 Go soit un facteur x15. Imaginez votre entrepôt de données pesant 1To reduit à une 100 de Go…
  • mais aussi un gain conséquent en terme de performance, en exécutant la même requête nous sommes passées de plus de 3min à 7 sec soit un facteur x25.

C’est à peu près ce que je constate en moyenne :

  • un facteur x10 en termes de stockage sur des tables non compressées
  • un facteur x4 sur des tables avec une compression par page
  • un gain de performance x20

Logique le fort taux de compression se retrouve en terme de performance au niveau des I/O. En effet, le moteur lit beaucoup moins de pages de données pour le retourner le même résultat.

De plus, le stockage des données se fait en colonne dans des blocs de segments contenant => plus besoin de s’embêter avec les indexes non clustered, ce qui allège aussi considérablement les plans de maintenance.

Par ailleurs, le moteur SQL utilise ces segments pour éliminer plus rapidement les données ne répondant pas aux prédicats imposés par votre requête.

Et vous, quand est-il sur vos projets ?

Pour vos premières lectures:

Pour ceux qui veulent approfondir le sujet, n’hésitez pas à consulter le blog de Niko Neugebauer (twitter)

N.B : les CCSI sont aussi disponibles sur les Azure SQL DBs 🙂

Alors qu’attendez-vous pour passer sur SQL Server 2014, la release 2016 ? Et pourquoi pas passer utiliser Azure SQL Database, pour bénéficier de toute la souplesse des solutions cloud !

FreD.

4 réponses à “SQL Server – les columnstore index à la rescousse de la BI

  1. BARBARIN DAVID 11 janvier 2016 à 9 09 11 01111

    Hello Fred, je pense qu’il serait intéressant de voir également les opérateurs de plans utilisés dans ton contexte et si ceux-ci utilisent le mode batch. En effet, même si effectivement la compression joue beaucoup, il faut également compter avec le mode batch qui permet d’utiliser de manière efficiente les architectures CPU.

    Merci pour le feedback en tout cas 🙂

    • fbrossard 11 janvier 2016 à 10 10 03 01031

      Salut David,
      En effet, les plans d’exécutions montraient clairement l’utilisation du Batch mode au niveau des opérations de type Hash Match pour les requêtes affichant les meilleurs gains de performance.
      A l’occasion, je ferai un article plus détaillé montrant les temps de réponse vs les modes d’exécutions (bacth vs row) illustré avec les plans d’exécutions.

  2. llemenaheze 11 janvier 2016 à 21 09 43 01431

    Salut Fred, en terme de chargement est ce qu’il y a une diminution des perf ou non? Etant donne qu’il n’y a plus qu’un index je pense pas mais on sait jamais.. et sinon est ce que tu as teste de processer une large dimension sur une table avec un CCSI (je pense au degenerated dim)? Vu que c’est des select distinct sur chaque colonnes j’imagine que les gains de perf sont enorme non?

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 :