[SQL Server] Index : mise-à-jour des données d’un index column store

Ce billet porte sur la mise-à-jour d’un index column store (en cluster), possible à partir de SQL Server 2014.

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

 

Comment fonctionne la mise-à-jour des données d’un index column store en cluster ?

Comme vu ici, à partir de SQL Server 2014 est offerte la possibilité de disposer d’un index column store acceptant les mises-à-jour de données : le clustered
column store index, qui utilise, pour ce faire, des delta stores et des delete bitmaps.

La nuance à savoir est que les opérations de mise-à-jour n’affectent pas directement l’index column store en cluster, mais le delta store et le delete bitmap. Cela s’explique par le fait qu’une mise-à-jour directe dudit index rendrait les traitements des requêtes plus longues du fait de la longueur des opérations de compression de données.

Ainsi, chaque fois qu’une opération d’insertion est lancée sur une table couverte par un index column store en cluster, l’enregistrement est ajouté dans un des groupes de lignes du delta store, c’est-à-dire une structure de données en B-Tree (comme les indexes traditionnels). Et quand un groupe de lignes du delta store atteint sa limite de stockage (approximativement 1 million d’enregistrements), SQL Server le marque comme étant fermé, puis lance un processus d’arrière-plan appelé tuple mover qui va déplacer ledit groupe en format compressé dans l’index column store en cluster. Par défaut, le tuple mover consulte le delta store toutes les 5 minutes afin de déterminer quels sont les groupes de lignes fermés à déplacer.

Du côté de la suppression, il existe 2 cas de figure :

  • S’il s’agit d’un enregistrement déjà présent dans le delta store, celui-ci est supprimé, aussi bien logiquement que physiquement.
  • S’il s’agit d’un enregistrement présent dans le column store, il est alors marqué comme logiquement supprimé dans le delete bitmap et ne sera physiquement supprimé qu’en cas de reconstruction de l’index.

Et enfin, pour ce qui est de la modification d’un enregistrement, il s’agit d’un mix entre l’insertion et la suppression dans le cas d’un enregistrement présent dans le column store et une mise-à-jour directement s’il est présent dans le delta store.

Le schéma ci-dessous résume le fonctionnement des mises-à-jour opérées (I pour INSERT, U pour UPDATE et D pour DELETE) sur une table couverte par un index column store :


De la nécessité de reconstruire un index column store en cluster…

Pourquoi et quand reconstruire l’index column store en cluster ?

Comme évoqué plus haut, la suppression d’un enregistrement présent dans le delta store ne se fait pas physiquement, mais seulement logiquement. C’est-à-dire en le marquant comme supprimé dans le delete bitmap. Cela n’est pas problématique et permet à SQL Server de traiter ses opérations plus rapidement, la maintenance de mises-à-jour d’un index étant coûteuse par définition. Toutefois, l’accumulation d’un gros volume d’enregistrements supprimés peut nécessiter de prévoir une opération de reconstruction de l’index. Et rien de plus utile que les DMVs pour mieux déterminer le bon moment pour de reconstruire l’index traité.

Comme brièvement vu ici, 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). En outre, state_description peut être utilisé pour connaître l’état d’un groupe de lignes.

Quand la quantité d’enregistrements supprimés augmente dans le column store et correspond à un gros pourcentage du nombre total de lignes de données stockées, il est grand temps d’envisager une reconstruction de l’index afin de mieux se prémunir contre tout éventuel problème de performances I/O sur la table traitée

 

Eléments de démonstration sur la reconstruction d’un index column store en cluster

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

Créons un index column store en cluster sur la table Production.TransactionHistory (on suppose qu’elle est dépourvue d’indexes et de clés étrangères) :

CREATE CLUSTERED COLUMNSTORE INDEX CCSI_TransactionHistory
    ON Production.TransactionHistory
GO

 

Ou via SSMS :


Jetons un coup d’œil au nombre total d’enregistrements stockés :

SELECT total_rows, deleted_rows, size_in_bytes, state_description
FROM sys.column_store_row_groups
GO

 


Maintenant, supprimons des enregistrements (datant d’avant le 30/09/2007) :

DELETE Production.TransactionHistory
WHERE TransactionDate<'2007-09-30'
GO

 

Le lancement de notre DMV de vérification donnera le résultat suivant :


On note que 10900 lignes ont été marquées comme supprimées, dans le delete bitmap.

Mettons à jour des enregistrements (en mettant les quantités à 0, pour toutes les transactions de l’année 2008) :

UPDATE Production.TransactionHistory
SET Quantity=0
WHERE TransactionDate BETWEEN '2008-01-01' AND '2008-12-31'
GO

Relançons notre DMV :


7153 enregistrements ont été modifiés. L’état OPEN indique qu’un groupe de lignes a été ouvert dans le delta store suite à la modification d’enregistrements.

En outre, en comparaison avec le total_rows (113443), l’index contient une forte quantité de lignes supprimées (81499 suppressions – soit près de 71% du nombre total de lignes – dans le delete bitmap suite à la modification d’enregistrements en plus de la suppression faite initialement plus haut, en effet rappelons qu’une telle opération est un mix entre une insertion de données dans le delta store et une suppression logique dans le delete bitmap).

Pour remédier à cela, une reconstruction de l’index s’impose :

  • En T-SQL :
ALTER INDEX CCI_TransactionHistory ON Production.TransactionHistory REBUILD
GO
  • Via SSMS :


Si on relance notre DMV, le résultat sera le suivant :


On peut noter que les enregistrements marqués comme « supprimés » dans le delete bitmap ont été démarqués, et donc supprimés physiquement du column store suite à la reconstruction de l’index.

 

Pour aller plus loin…

Ce billet a permis de donner un petit aperçu du fonctionnement des mises-à-jour de données d’un index column store en cluster, et de la nécessité de le reconstruire.

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