Index de columnstore en cluster sur de petites tables

Question

Les tables indexées par stockage de colonnes en cluster sont en général utiles pour les grandes tables. Idéalement avec des millions de lignes. Et aussi utile avec les requêtes, qui sélectionne uniquement le sous-ensemble de colonnes disponibles dans ces tables.

Que se passe-t-il si nous enfreignons ces deux «règles» / meilleures pratiques?

1. Comme avoir une table indexée de stockage de colonnes en cluster qui ne stockera que quelques milliers, ou des centaines de milliers de lignes au maximum.
2. Et exécuter des requêtes sur ces tables de stockage de colonnes en cluster où toutes les colonnes sont nécessaires.

Mes tests ne révèlent aucune dégradation des performances par rapport à la table d'index cluster stockée en ligne. Ce qui est génial dans notre cas.

Y a-t-il des effets «à long terme» enfreignant ces deux règles? Ou des pièges cachés qui ne sont pas encore apparus?

Contexte pourquoi est-il nécessaire: J'ai conçu un modèle de base de données qui sera utilisé pour de nombreuses instances de bases de données de fournisseurs différents. Le schéma reste le même dans chaque base de données, mais différents fournisseurs ont une quantité de données différente. Par conséquent, peu de petits fournisseurs peuvent se retrouver avec une petite quantité de données (<1 000 000) dans leurs tableaux. Je ne peux pas me permettre de conserver deux bases de données différentes pour le modèle de magasin de lignes et de magasin de colonnes.

Answer 1

Pour @YunusUYANIK signaler les inconvénients potentiels de la conception de votre schéma pour un seul côté, pourquoi ne pas créer à la fois des index rowstore et columnstore sur votre table adaptés aux deux scénarios? Bien sûr, vous pourriez finir par indexer les mêmes champs dans les deux sens, mais le principal inconvénient serait simplement l'utilisation accrue de l'espace de stockage, ce qui est généralement beaucoup moins préoccupant lors de la planification des performances.

Cela dépendra de votre schéma et de la quantité de données dans vos tables pour chaque fournisseur, vous devrez donc tester pour vous assurer que votre conception des index est utilisée dans les requêtes appropriées pour les différentes quantités de données en fonction de vos prédicats de fournisseur. . Dans le pire des cas, vous devrez peut-être parfois utiliser des indices d'index, mais je pense que si vous concevez correctement les deux types d'index, ce n'est pas très probable.

Answer 2

L'index Columnstore a un gros avantage sur la taille des données de compression. L'objectif général de l'index Columnstore est de lire rapidement un tas de données grâce à sa compression.

CCI est un index clusterisé Columnstore, un index clusterisé est un index clusterisé

L'index Columnstore compresse la taille des données de 4 Mo à 2 Mo.

Nous pouvons regarder la performance en deux tableaux et trois parties.

Le premier est l' SELECTopération minimale :

SELECT * FROM Users_CCI WITH(INDEX=CCI_Users) WHERE Id=12333

SELECT * FROM Users_Clustered WHERE Id=12333

Résultat : il y a une Columnstore Scanestimation ici et erronée. En outre, différence de lecture logique. Vous pouvez dire que cela n'a pas d'importance pour vous, mais, si vous utilisez une requête SELECT minimale, utilisez probablement des milliers de fois. Et cela affectera les performances totales.

Le second est l' UPDATEopération minimale :

UPDATE Users_CCI SET Age=10 WHERE  Id=2

UPDATE Users_Clustered SET Age=10 WHERE  Id=2

Résultat : il y a des différences de lecture, de CPU et d'heure comme nous pouvons le voir.

Le troisième est l' REBUILDopération:

USE [StackOverflow2013]
GO
ALTER INDEX [CCI_Users] ON [dbo].[Users_CCI] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE)
GO
USE [StackOverflow2013]
GO
ALTER INDEX [PK_Users_Clustered_Id] ON [dbo].[Users_Clustered] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

UPDATE Users_CCI SET Age=10 
UPDATE Users_Clustered SET Age=10 

Si je reconstruis et mets à jour toutes les lignes pour la fragmentation de l'index, je verrai un index Clustered Columnstore plus fragmenté que l'index clusterisé. Et je n'ai pas montré, mais le Clustered Columnstore Indexprocessus de reconstruction produit plus de journal des transactions Clustered Index.

Comme le dit le document

• Plus de 10% des opérations sur la table sont des mises à jour et des suppressions. Un grand nombre de mises à jour et de suppressions entraîne une fragmentation. La fragmentation affecte les taux de compression et les performances des requêtes jusqu'à ce que vous exécutiez une opération appelée réorganiser qui force toutes les données dans le columnstore et supprime la fragmentation. Pour plus d'informations, consultez Réduction de la fragmentation d'index dans l'index columnstore.

Si vous avez une petite table, vous n'avez pas besoin d'indexer Columnstore.