[SQL Server] Index : performances d’un index column store

Ce billet donne un aperçu de l’intérêt d’un index column store au niveau de l’amélioration des performances des requêtes, notamment par rapport à un index en cluster. Il aborde également – et brièvement – le cas de la mise-à-jour d’un index column store en cluster (disponible à partir de SQL Server 2014).

Vous pouvez faire un tour ici pour avoir une idée de ce qu’est un index column store.

Présentation du contexte

Pour nos tests, nous allons utiliser AdventureWorksDW, récupérable ici.

La table de faits FactProductInventory sera utilisée pour référence.

 

Entrée dans le vif du sujet

    Impact de l’index column store sur les performances

En lançant la requête (avec buffer cache à froid, pour nos tests) :

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
SELECT ProductKey, MovementDate,UnitCost
FROM FactProductInventory
GO

 

Nous obtenons le plan d’exécution suivant :

 

Les informations détaillées de l’opérateur Clustered Index Scan donnent :

Ce que l’on remarque en particulier, c’est que le traitement des 776286 lignes de la table génère un coût estimé des opérations I/O de l’ordre de 2,85201, sur un total de 3,70609 pour l’opérateur, le reste étant des opérations CPU (0,854072). En d’autres termes, les opérations I/O constituent près de 77% du travail du scan de l’index en cluster de la table traitée.

Procédons maintenant à la création de l’index columnstore sur les colonnes ciblées par la requête :

CREATE NONCLUSTERED COLUMNSTORE INDEX NCSI_FactProductInventory
	ON FactProductInventory(ProductKey, MovementDate,UnitCost)
GO

Après avoir relancé notre requête, la plan d’exécution devient :

 

Dont voici les informations de l’opérateur Columnstore Index Scan utilisé par l’optimiseur de requêtes :

Comme on peut le souligner :

  • Le coût total des opérations effectuées est passé de 3,70609 à 1,09572.
  • Le coût des opérations I/O est passé de 2,85201 à 0,241644.
  • Et surtout, il y a moins d’opérations I/O que CPU (0,854072).

En clair, l’index column store améliore les performances des requêtes en compressant les données en mémoire, baissant ainsi le coût des opérations I/O.

 

Puis-je donc créer un index column store sur chacune de mes tables ? Cela dépend…

En supposant que la création d’un index columnstore respecte les limitations vues ici, quelques faits non-négligeables sont tout de même à tenir en compte :

  • La volumétrie.

Vous pouvez garder un œil sur la volumétrie en exécutant la requête suivante :

SELECT SUM(s.used_page_count)/ 128.0 Size_in_MB
FROM sys.indexes i
	JOIN sys.dm_db_partition_stats ddps ON i.object_id = ddps.object_id AND i.index_id = ddps.index_id
WHERE i.object_id = object_id('FactProductInventory')
AND i.type_desc = 'NONCLUSTERED COLUMNSTORE'
GO

 

  • La mise à jour de données (insertions, suppressions, modifications).

Tout index columnstore a un impact sur les requêtes de mise-à-jour. Pour s’en convaincre, lançons la requête suivante :

INSERT INTO FactProductInventory(ProductKey, MovementDate,UnitCost)
    VALUES(1,'2005-07-01','0,20')
GO

Un message d’erreur sera levé, empêchant la requête d’aller au bout :

Msg 35330, Level 15, State 1, Line 1
INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.

Comme l’indique ledit message, pour chaque requête de mise-à-jour lancée sur une table contenant un index columnstore, une désactivation préalable suivie d’une reconstruction en fin de requête est nécessaire. Ainsi, pour reprendre notre requête, cela donnerait :

ALTER INDEX NCSI_FactProductInventory ON FactProductInventory DISABLE
GO

INSERT INTO FactProductInventory(ProductKey, MovementDate,UnitCost)
    VALUES(1,'2005-07-01','0,20')
GO

ALTER INDEX NCSI_FactProductInventory
	ON FactProductInventory REBUILD PARTITION=ALL
GO

Mais quid de l’index column store en cluster qui prend en compte les mises-à-jour ?…

A partir de SQL Server 2014, l’index column store en cluster (inexistant sous SQL Server 2012) permet de prendre en compte les mises à jour des données, grâce à l’utilisation de delta stores et de delete bitmaps (voir ici, en fin de billet, pour une brève explication). Mais n’empêche pas la nécessité de reconstruire l’index, à un moment donné, avant tout pour des raisons… volumétriques, du fait de la possible accumulation d’enregistrements qui ne sont supprimés que logiquement. En effet, quand une ligne d’enregistrement est supprimée d’une table couverte par un index column store en cluster, elle est marquée logiquement (et non physiquement) comme « supprimé », et en aucun cas elle ne sera supprimée physiquement (sauf dans le delta store), tant que l’index n’aura pas subi de reconstruction.

La DMV sys.column_store_row_groups permet de connaître le nombre total d’enregistrements stockés (total_rows) et le nombre total de lignes marquées comme supprimées (deleted_rows), ainsi que la taille complète des enregistrements contenus dans chaque groupe de lignes de l’index (size_in_bytes). Il permet également d’avoir une idée du meilleur moment pour lancer la reconstruction d’un index column store en cluster afin de libérer de l’espace.

Cela sera détaillé dans un autre billet, via un exemple technique et plus parlant.

 

Pour aller plus loin…

Ce billet a permis de donner un petit aperçu de ce qu’un index column store peut apporter en matière de performances.

Gardez œil ici pour d’autres billets sur le même sujet.

 

Publicités

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