Le Constat
Le modèle classique de SQL Server (Rowstore) est conçu pour l’OLTP (Online Transactional Processing) et donc un usage idéal pour des bases de données transactionnelles. Dans ce cas, la base stocke les données sous forme de pages de 8 KB contenant des lignes entières.
Pour un usage analytique, il est préférable de mettre en place l’index Columstore pour les tables de fait afin de régler deux problèmes qui sont :
- Le problème du Data Scanning : Dans un rapport de Business Intelligence, si vous voulez calculer la moyenne des ventes sur 30 millions de lignes, SQL Server doit charger en mémoire toute la ligne (info client, adresse, date, etc.) juste pour lire la colonne « Montant ».
- Le problème du goulot d’étranglement : En gardant l’exemple précédant, la requête génère un trafic I/O (lecture disque) colossal et sature la mémoire vive avec des données inutiles à l’instant T.
Définition de l’Index Columnstore
La rupture avec le modèle Rowstore (Lignes)
Dans un index classique (celui que SQL Server utilise depuis les années 80), les données sont stockées de manière horizontale.
Si vous avez une table Ventes, SQL Server écrit physiquement sur le disque :
- Ligne 1 : [Date, Client, Produit, Magasin, Montant]
- Ligne 2 : [Date, Client, Produit, Magasin, Montant]
- …et ainsi de suite.
Toutes ces informations sont regroupées dans des pages de 8 KB. Si vous ne voulez que la somme des Montants, SQL Server doit quand même charger en mémoire les colonnes Client, Produit et Magasin car elles sont collées physiquement au montant dans la page. C’est un gaspillage énorme de ressources I/O.
Le stockage par Colonnes (Columnstore)
L’index Columnstore renverse la table. Il sépare chaque colonne et les stocke de manière isolée.
- Fichier A : Toutes les Dates de la table.
- Fichier B : Tous les Produits.
- Fichier C : Tous les Montants.
Pourquoi est-ce une révolution pour l'analytique ?
- L’élimination des colonnes : Si votre requête SQL est SELECT SUM(Montant) FROM Ventes, le moteur de requête ne va lire que le fichier qui contient l’information de la colonne Montant. Il ignore totalement les autres colonnes. Sur une table de 50 colonnes, vous venez de diviser votre lecture disque par 50.
- Une compression chirurgicale : C’est le point clé. Dans une colonne Magasin, vous allez avoir des milliers de fois la valeur Paris ou Lyon. Comme les données de même type sont stockées ensemble, SQL Server utilise des algorithmes de compression très performants pour ne stocker la valeur Paris qu’une seule fois avec un nombre d’occurrences.
- Le découpage en Row Groups et Segments : Les lignes sont d’abord découpées en Row Groups (groupes d’environ 1 million de lignes).
- À l’intérieur d’un Row Group, chaque colonne devient un Segment.
- Chaque segment est compressé individuellement et stocké sous forme d’objet binaire.
Inscrivez-vous à la newsletter DeciVision !
Soyez notifiés de nos derniers articles de blog, de nos prochains webinars et nos actualités !
Les Avantages
Le Batch Mode Execution
Dans un index classique, SQL Server utilise le Row Mode. Pour chaque ligne d’une requête (disons 10 millions de lignes), le CPU doit exécuter une opération, passer à la ligne suivante, et recommencer. C’est extrêmement répétitif et lourd pour le processeur.
- Le concept : Le Batch Mode permet au CPU de traiter les données par paquets (généralement 900 à 1000 lignes à la fois).
- L’impact : Au lieu de dire 10 millions de fois additionne cette ligne, le CPU dit 10 000 fois additionne ce paquet de 1000.
- Le résultat : Une utilisation du CPU beaucoup plus efficace et une réduction drastique du nombre d’instructions. C’est ce qu’on appelle la vectorisation.
L'élimination de segments
Si le stockage par colonne permet de ne lire que les colonnes utiles, l’élimination de segments va encore plus loin : elle permet d’ignorer des pans entiers de lignes à l’intérieur de ces colonnes..
- Pour chaque Segment (une colonne dans un Row Group), SQL Server stocke les valeurs Min et Max.
- Exemple : Si vous cherchez les ventes du mois de Mars et qu’un segment indique Min: Janvier / Max: Février, SQL Server ne prend même pas la peine de lire ce segment. Il passe au suivant.
- Gain : Vous ne lisez physiquement que les données strictement nécessaires.
Une compression hors du commun
Parce que les données d’une même colonne se ressemblent (même type, valeurs souvent répétées), SQL Server peut appliquer des algorithmes de compression ultra-spécialisés :
- Dictionnaire : Remplace une chaîne de caractères longue (ex: « Département Logistique ») par un simple entier (ex: 1).
- RLE (Run-Length Encoding) : Si vous avez 5000 lignes avec la valeur « France », SQL Server stocke simplement France | 5000.
- Économie : Cela libère de l’espace disque et permet de garder beaucoup plus de données dans le Buffer Pool (RAM), évitant les allers-retours lents avec le stockage.
Réduction drastique de l'I/O (Entrées/Sorties)
C’est l’avantage le plus visible immédiatement. En combinant la lecture seule des colonnes utiles et la compression, une requête qui scannait autrefois 100 Go de données peut n’avoir plus qu’à en lire 2 ou 3 Go. Le gain de temps est mécanique.
Les Inconvénients et les Limites
La lourdeur des modifications (Insert/Update/Delete)
Le Columnstore déteste les petites modifications fréquentes. Pourquoi ? Parce qu’on ne peut pas modifier un segment compressé et en lecture seule à la volée.
- Le Delta Store : Quand vous insérez une ligne, elle ne va pas directement dans le segment compressé. Elle est stockée dans une table temporaire (le Delta Store). SQL Server attend d’avoir environ 1 million de lignes avant de les compresser ensemble.
- Le mécanisme du Delete : Supprimer une ligne ne l’efface pas physiquement du segment. SQL Server la marque simplement comme « supprimée » dans un Delete Bitmap. La ligne occupe toujours de l’espace disque et est sautée à la lecture.
- Fragmentation : Trop de suppressions ou de petites mises à jour finissent par fragmenter l’index, ce qui nécessite une reconstruction régulière (REORGANIZE ou REBUILD).
Inefficace pour les recherches unitaires
C’est le plus gros piège. Si votre application fait souvent des requêtes du type : SELECT * FROM Clients WHERE ClientID = 12345
- Le problème : Le Columnstore est optimisé pour scanner des millions de lignes sur quelques colonnes. Pour retrouver une seule ligne avec toutes ses colonnes, SQL Server doit ouvrir des dizaines de segments différents, les décompresser, et réassembler la ligne.
- Résultat : Un index B-Tree classique sera infiniment plus rapide. Le Columnstore est fait pour la BI, pas pour la gestion de profil utilisateur.
Une consommation CPU accrue
La magie de la compression a un prix : la décompression.
- Chaque fois que vous lisez des données, le processeur doit travailler pour décompresser les segments en temps réel.
- Si votre serveur est déjà saturé au niveau CPU, passer au Columnstore pourrait aggraver la situation, même si vous gagnez sur le disque (I/O).
Limitations techniques et types de données
Bien que Microsoft ait levé beaucoup de barrières avec les versions 2016, 2017 et 2019, certaines limites persistent :
- Types de données : Certains types ne sont pas supportés ou nuisent à la performance (ex: les anciens types ntext, text, image ou certains types de données très larges comme varchar(max)).
- Pas de clés primaires classiques : Sur un index Clustered Columnstore, vous ne pouvez pas avoir de contraintes d’unicité (Unique/Primary Key) aussi simplement que sur un index classique (cela nécessite souvent un index non-clustered supplémentaire, ce qui alourdit le stockage).
C’est pourquoi il n’est pas conseillé de passer au Columnstore pour une table qui ne dépasse pas 1 million de lignes. En dessous, les gains de compression et de Batch Mode ne compensent souvent pas la complexité de gestion de l’index.
Bonnes pratiques
Visez la taille critique
N’utilisez pas le Columnstore sur des tables de quelques milliers de lignes. Le moteur SQL est optimisé pour des Row Groups d’environ 1 million de lignes. En dessous, vous risquez de perdre en performance par rapport à un index B-Tree classique.
Soignez l'ordre d'insertion
Pour maximiser l’élimination de segments, insérez vos données de manière ordonnée (par exemple par Date ou par ID séquentiel). Cela permet de créer des segments avec des bornes Min/Max bien nettes, évitant ainsi à SQL Server de scanner des données inutiles.
Surveillez la fragmentation
Les DELETE et UPDATE marquent les lignes comme supprimées sans libérer l’espace. Si votre taux de lignes supprimées dépasse 10-15 %, lancez un ALTER INDEX … REORGANIZE. Cela forcera SQL Server à fusionner les petits Row Groups et à nettoyer les lignes supprimées.
Attention aux types de données
Évitez les colonnes varchar(max) ou les types XML/JSON au sein de l’index si possible. Ils ne bénéficient pas de la même compression et peuvent ralentir considérablement le Batch Mode.
