Index columnstore
Un index columnstore optimisé en mémoire xVelocity regroupe et stocke les données de chaque colonne, puis joint l'ensemble des colonnes pour remplir l'index tout entier. Cela diffère des index classiques qui regroupent et stockent les données de chaque ligne, puis joignent l'ensemble des lignes pour remplir l'index tout entier. Pour certains types de requêtes, le processeur de requêtes SQL Server peut tirer parti de la disposition de columnstore pour améliorer considérablement les délais d'exécution des requêtes. En raison de l'explosion de la demande en matière d'entrepôts de données, d'aide à la décision et d'applications décisionnelles, il est devenu urgent de pouvoir lire et convertir les jeux de données très volumineux en informations et en connaissances pertinentes. Cette augmentation des volumes de données et des attentes est difficile à gérer ou à optimiser via des améliorations de performances incrémentielles. La technologie relative aux index columnstore SQL Server est particulièrement adaptée aux jeux de données d'entrepôts de données classiques. Les index columnstore peuvent transformer l'expérience utilisateur des entrepôts de données en améliorant considérablement les performances des requêtes communes liées aux entrepôts de données, par exemple en matière de filtrage, d'agrégation, de regroupement et de jointure en étoile.
Sommaire
Principes de base
Principes de base : description des index columnstore
Principes de base : restrictions et limitations des index columnstore
Exemple de démonstration : index columnstore avec une table partitionnée
Principes de base : scénarios classiques d'index columnstore
Principes de base : optimisations de filtres Bitmap
Meilleures pratiques
Meilleures pratiques : mise à jour des données dans un index columnstore
Meilleures pratiques : choix des colonnes pour un index columnstore
Meilleures pratiques : tables partitionnées
Procédure
Procédure : créer un index columnstore
Procédure : déterminer la taille d'un index columnstore
Procédure : résoudre les problèmes de performances d'un index columnstore
Principes de base : description des index columnstore
Les index columnstore du Moteur de base de données SQL Server peuvent être utilisés pour accélérer de manière significative le temps de traitement des requêtes communes liées aux entrepôts de données. Les charges de travail classiques liées aux entrepôts de données impliquent la synthèse de grandes quantités de données. Les techniques généralement utilisées dans les systèmes de stockage de données et d'aide à la décision pour améliorer les performances sont les tables de synthèse précalculées, les vues indexées, les cubes OLAP, etc. Bien qu'elles puissent accélérer sensiblement le traitement des requêtes, ces techniques peuvent être inflexibles, difficiles à gérer, et doivent être conçues spécifiquement pour chaque problème de requête.
Prenons l'exemple d'une table de faits F1 avec des colonnes clés de dimension dk1 et dk2. M est une fonction d'agrégation telle que SUM. Au lieu de calculer M sur la colonne dk1 chaque fois qu'une requête faisant référence à M(dk1) est exécutée, une table de synthèse F2(dk1, M) peut être créée et utilisée de sorte que le résultat soit précalculé et que la requête puisse s'exécuter plus rapidement. Toutefois, si une nouvelle requête faisant référence à M(dk2) est nécessaire, une nouvelle table de synthèse F3(dk2, M) doit être créée avec ces informations. Lorsque le nombre de colonnes d'une table augmente avec de nombreuses fonctions possibles, cette approche devient difficile à gérer et ne couvre pas facilement toutes les requêtes nécessaires.
Cette charge de traitement peut être significative pour l'utilisateur. À l'aide des index columnstore SQL Server, les utilisateurs peuvent réduire la charge de traitement des autres solutions. Les index columnstore permettent également aux requêtes de calculer les résultats rapidement, de sorte qu'aucun précalcul n'est nécessaire.
Les principales caractéristiques de la technologie relative aux index columnstore SQL Server sont les suivantes :
Format de données en colonnes – Contrairement à l'organisation classique des données sous forme de lignes (appelée format rowstore), dans les systèmes de base de données en colonnes comme SQL Server qui incluent des index columnstore, les données sont regroupées et stockées colonne par colonne. Le traitement des requêtes SQL Server peut tirer parti de la nouvelle disposition des données et améliorer considérablement le délai d'exécution des requêtes.
Résultats de requête plus rapide – Les index columnstore peuvent produire des résultats plus rapides pour les raisons suivantes :
Seules les colonnes nécessaires doivent être lues. Par conséquent, moins de données sont lues du disque vers la mémoire avant d'être déplacées ensuite de la mémoire vers le cache de processeur.
Les colonnes sont fortement compressées. Cela réduit le nombre d'octets qui doivent être lus et déplacés.
La plupart des requêtes ne touchent pas à l'ensemble des colonnes de la table. Par conséquent, plusieurs colonnes ne sont jamais chargées en mémoire. Cette caractéristique, combinée à une excellente compression, permet d'améliorer l'utilisation du pool de mémoires tampons, ce qui réduit le nombre total d'E/S.
La technologie d'exécution de requête avancée traite les blocs de colonnes appelés lots de manière rationnalisée, ce qui réduit l'utilisation de l'UC.
Colonnes clés – Il n'existe pas de concept de colonnes clés dans un index columnstore ; par conséquent, la limitation relative au nombre de colonnes clés dans un index (16) ne s'applique pas aux index columnstore.
Clé d'index cluster – Si une table de base est un index cluster, toutes les colonnes de la clé de clustering doivent être présentes dans l'index columnstore non cluster. Si une colonne de la clé de clustering ne figure pas dans l'instruction de création d'index, elle est ajoutée automatiquement à l'index columnstore.
Partitionnement – Les index columnstore fonctionnent avec le partitionnement de table. Aucune modification de la syntaxe de partitionnement de table n'est requise. Un index columnstore sur une table partitionnée doit être aligné sur les partitions avec la table de base. Par conséquent, un index columnstore non cluster ne peut être créé sur une table partitionnée que si la colonne de partitionnement est l'une des colonnes de l'index columnstore.
Taille d'enregistrement – La limite de 900 octets relative à la taille d'enregistrement de la clé d'index ne s'applique pas aux index columnstore.
Traitement des requêtes – Conjointement avec l'index columnstore, SQL Server introduit le traitement par lots pour tirer parti de l'orientation en colonnes des données. La structure columnstore et le traitement par lots fournissent des gains de performances, mais l'examen des problèmes de performances peut s'avérer plus complexe qu'avec un seul facteur impliqué.
Mise à jour de table impossible – Pour SQL Server 2012, une table comportant un index columnstore ne peut pas être mise à jour. Pour obtenir des solutions de contournement, consultez Meilleures pratiques : mise à jour des données dans un index columnstore.
Pour connaître la syntaxe relative à la création d'un index columnstore, consultez CREATE COLUMNSTORE INDEX (Transact-SQL).
Types de données
Les types de données métiers communs peuvent être inclus dans un index columnstore. Les types de données suivantes peuvent être inclus dans un index columnstore.
char et varchar
nchar et nvarchar (sauf varchar(max) et nvarchar(max))
decimal (et numeric) (sauf avec une précision supérieure à 18 chiffres.)
int, bigint, smallint et tinyint
float (et real)
bit
money et smallmoney
Tous les types de données de date et d'heure (sauf datetimeoffset avec une échelle supérieure à 2)
Les types de données suivants ne peuvent pas être inclus dans un index columnstore :
binary et varbinary
ntext, text et image
varchar(max) et nvarchar(max)
uniqueidentifier
rowversion (et timestamp)
sql_variant
decimal (et numeric) avec une précision supérieure à 18 chiffres
datetimeoffset avec une échelle supérieure à 2
Types CLR (hierarchyid et types spatiaux)
xml
Risque de performances médiocres
Les performances des requêtes d'aide à la décision sont souvent améliorées lorsque des index columnstore sont utilisés sur des tables volumineuses ; toutefois, les performances de certaines requêtes et même de charges de travail entières peuvent se révéler très mauvaises. Avec une approche basée sur les coûts, l'optimiseur de requête choisit en général d'utiliser un index columnstore uniquement lorsque cela améliore les performances globales de la requête. Toutefois, les modèles de coûts appliqués par l'optimiseur sont approximatifs ; parfois, l'optimiseur choisit d'utiliser l'index columnstore pour une table alors qu'il serait préférable d'utiliser un index rowstore (arbre B (B-tree) ou segment de mémoire) pour accéder à la table. Si cela se produit, utilisez l'indicateur de requête IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX ou utilisez un indicateur d'index pour forcer l'optimiseur à se servir d'un index rowstore. L'optimiseur peut toujours inclure des informations provenant de l'index columnstore. Par conséquent, dans de rares cas, cette option peut ne pas résoudre le problème de performances. Si les performances de votre charge de travail ne sont pas améliorées par l'utilisation d'un index columnstore et si vous ne pouvez pas recourir à des indicateurs d'index pour résoudre le problème, supprimez l'index columnstore afin de revenir à un traitement rowstore.
Problèmes
Les index columnstore SQL Server et le traitement des requêtes à l'aide de colonnes sont optimisés pour les requêtes d'entrepôts de données classiques qui portent sur une table de faits volumineuse, réduite en tables de dimension plus petites (jointes selon une configuration de schéma en étoile) dont les données sont ensuite regroupées et agrégées. Bien que le nombre de lignes de la table de faits soit élevé, ces requêtes retournent généralement un jeu de résultats de petite taille en comparaison, car les données sont agrégées. Les performances des requêtes basées sur l'index columnstore peut être lentes lorsqu'une ou plusieurs des conditions suivantes sont vérifiées.
La taille du jeu de résultats est importante, car les données ne sont pas agrégées. (Le retour d'un jeu de résultats de grande taille est naturellement plus lent que le retour d'un jeu de résultats de petite taille.)
Il n'y a pas de jointure, de filtrage ou d'agrégation. Dans ce cas, il n'y a pas de traitement par lots. Par conséquent, les avantages que présente l'index columnstore se limitent à la compression et à la lecture d'un plus petit nombre de colonnes.
Deux tables de grande taille doivent être jointes, ce qui entraîne la création de tables de hachage volumineuses qui ne peuvent pas tenir en mémoire et doivent déborder sur le disque.
De nombreuses colonnes sont retournées, ce qui entraîne la récupération d'informations supplémentaires par rapport à l'index columnstore.
Une condition de jointure d'une table basée sur un index columnstore inclut plusieurs colonnes.
Vous pouvez utiliser les méthodes décrites précédemment dans cette section pour contourner le problème de lenteur du traitement de l'index columnstore, s'il se produit pour l'une des raisons suivantes.
Haut
Principes de base : restrictions et limitations des index columnstore
Restrictions de base
Un index columnstore :
Ne peut pas avoir plus de 1 024 colonnes.
Ne peut pas être un index cluster. Seuls les index columnstore non cluster sont disponibles.
Ne peut pas être un index unique.
Ne peut pas être créé sur une vue ou une vue indexée.
Ne peut pas inclure de colonne éparse.
Ne peut pas jouer le rôle d'une clé primaire ou d'une clé étrangère.
Ne peut pas être modifié à l'aide de l'instruction ALTER INDEX. Supprimez et recréez plutôt l'index columnstore. (Vous pouvez utiliser ALTER INDEX pour désactiver et reconstruire un index columnstore.)
Ne peut pas être créé avec le mot clé INCLUDE.
Ne peut pas inclure les mots clés ASC ou DESC pour le tri de l'index. Les index columnstore sont triés en fonction des algorithmes de compression. Le tri n'est pas autorisé dans l'index. Les valeurs sélectionnées à partir d'un index columnstore peuvent être triées par l'algorithme de recherche, mais vous devez utiliser la clause ORDER BY pour garantir le tri d'un jeu de résultats.
N'utilise pas ou ne conserve pas les statistiques à la manière d'un index traditionnel.
Ne peut pas contenir une colonne avec un attribut FILESTREAM. D'autres colonnes de la table qui ne sont pas utilisées dans l'index peuvent contenir l'attribut FILESTREAM.
Une table comportant un index columnstore ne peut pas être mise à jour.
Pour contourner ce problème, consultez Meilleures pratiques : mise à jour des données dans un index columnstore.
Effets de la limitation de mémoire
Les index columnstore sont optimisés pour le traitement en mémoire. SQL Server implémente des mécanismes qui permettent aux données et à la plupart des structures de données de déborder sur le disque en cas d'insuffisance de la mémoire disponible. Si de sévères restrictions de la mémoire s'imposent, le traitement utilise l'index rowstore. Dans certaines situations, l'index columnstore est choisi comme méthode d'accès mais la mémoire disponible est insuffisante pour générer les structures de données nécessaires. En raison du démarrage d'une opération columnstore qui prend ensuite par défaut la valeur d'un chemin de code plus lent, une baisse des performances peut être observée dans le cas des requêtes soumises à de sévères restrictions d'utilisation de la mémoire. Les besoins en mémoire effectifs d'une requête dépendent de la requête spécifique. La génération d'un index columnstore nécessite environ 8 mégaoctets multipliés par le nombre de colonnes de l'index multipliés par le degré de parallélisme. En règle générale, les besoins en mémoire augmentent parallèlement à la proportion de colonnes de chaînes. Par conséquent, la diminution du degré de parallélisme peut réduire les besoins en mémoire pour la génération de l'index columnstore.
L'évaluation de certaines expressions est plus rapide que pour d'autres.
Certaines expressions communes sont évaluées en mode batch et non en mode « une seule ligne à la fois » lorsque l'index columnstore est utilisé. Le mode batch fournit une accélération supplémentaire de la requête, en plus des avantages liés à l'utilisation d'un index columnstore. Tous les opérateurs d'exécution de la requête ne sont pas activés pour le traitement en mode batch.
L'index columnstore ne prend pas en charge SEEK.
S'il est prévu que la requête retourne une petite partie des lignes, il est peu probable que l'optimiseur sélectionne l'index columnstore (par exemple : requêtes de type aiguille dans une meule de foin). Si l'indicateur de table FORCESEEK est utilisé, l'optimiseur ne prend pas en compte l'index columnstore.
Les index columnstore ne peuvent pas être combinés avec les fonctionnalités suivantes :
La compression de pages et de lignes et le format de stockage vardecimal (un index columnstore est déjà compressé dans un format différent).
Réplication
Suivi des modifications
Capture de données modifiées
Filestream
Haut
Exemple de démonstration : index columnstore avec une table partitionnée
Les exemples de cette rubrique utilisent une table partitionnée nommée FactResellerSalesPtnd, créée dans l'exemple de base de données AdventureWorksDW2012 . Pour tester un index columnstore dans une table partitionnée, connectez-vous à la base de données AdventureWorksDW2012 et exécutez le code suivant pour créer une version partitionnée de la table de faits.
[!REMARQUE]
Pour plus d'informations sur les exemples de bases de données et sur les instructions relatives au téléchargement de la base de données, consultez Exemples de bases de données AdventureWorks.
Créer la table FactResellerSalesPtnd
Exécutez le code suivant pour créer une version partitionnée de la table FactResellerSales nommée FactResellerSalesPtnd.
USE AdventureWorksDW2012; GO CREATE PARTITION FUNCTION [ByOrderDateMonthPF](int) AS RANGE RIGHT FOR VALUES ( 20050701, 20050801, 20050901, 20051001, 20051101, 20051201, 20060101, 20060201, 20060301, 20060401, 20060501, 20060601, 20060701, 20060801, 20060901, 20061001, 20061101, 20061201, 20070101, 20070201, 20070301, 20070401, 20070501, 20070601, 20070701, 20070801, 20070901, 20071001, 20071101, 20071201, 20080101, 20080201, 20080301, 20080401, 20080501, 20080601, 20080701, 20080801, 20080901, 20081001, 20081101, 20081201 ) GO CREATE PARTITION SCHEME [ByOrderDateMonthRange] AS PARTITION [ByOrderDateMonthPF] ALL TO ([PRIMARY]) GO -- Create a partitioned version of the FactResellerSales table CREATE TABLE [dbo].[FactResellerSalesPtnd]( [ProductKey] [int] NOT NULL, [OrderDateKey] [int] NOT NULL, [DueDateKey] [int] NOT NULL, [ShipDateKey] [int] NOT NULL, [CustomerKey] [int] NOT NULL, [EmployeeKey] [int] NOT NULL, [PromotionKey] [int] NOT NULL, [CurrencyKey] [int] NOT NULL, [SalesTerritoryKey] [int] NOT NULL, [SalesOrderNumber] [nvarchar](20) NOT NULL, [SalesOrderLineNumber] [tinyint] NOT NULL, [RevisionNumber] [tinyint] NULL, [OrderQuantity] [smallint] NULL, [UnitPrice] [money] NULL, [ExtendedAmount] [money] NULL, [UnitPriceDiscountPct] [float] NULL, [DiscountAmount] [float] NULL, [ProductStandardCost] [money] NULL, [TotalProductCost] [money] NULL, [SalesAmount] [money] NULL, [TaxAmt] [money] NULL, [Freight] [money] NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [CustomerPONumber] [nvarchar](25) NULL, OrderDate [datetime] NULL, DueDate [datetime] NULL, ShipDate [datetime] NULL ) ON ByOrderDateMonthRange(OrderDateKey); GO -- Using simple or bulk logged recovery mode, and then the TABLOCK -- hint on the target table of the INSERT…SELECT is a best practice -- because it causes minimal logging and is therefore much faster. ALTER DATABASE AdventureWorksDW2012 SET RECOVERY SIMPLE; GO -- Copy the data from the FactResellerSales into the new table INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK) SELECT * FROM dbo.FactResellerSales; GO -- Create the columnstore index CREATE NONCLUSTERED COLUMNSTORE INDEX [csindx_FactResellerSalesPtnd] ON [FactResellerSalesPtnd] ( [ProductKey], [OrderDateKey], [DueDateKey], [ShipDateKey], [CustomerKey], [EmployeeKey], [PromotionKey], [CurrencyKey], [SalesTerritoryKey], [SalesOrderNumber], [SalesOrderLineNumber], [RevisionNumber], [OrderQuantity], [UnitPrice], [ExtendedAmount], [UnitPriceDiscountPct], [DiscountAmount], [ProductStandardCost], [TotalProductCost], [SalesAmount], [TaxAmt], [Freight], [CarrierTrackingNumber], [CustomerPONumber], [OrderDate], [DueDate], [ShipDate] );
Exécutez à présent une requête qui peut tirer parti de l'index columnstore et vérifiez si l'index columnstore est utilisé.
Tester l'index columnstore
Appuyez sur Ctrl+M, ou dans le menu Requête, sélectionnez Inclure le plan d'exécution réel. Cette opération active une représentation graphique du plan d'exécution réel utilisé par SQL Server Management Studio.
Dans la fenêtre Éditeur de requête, exécutez la requête suivante.
SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory FROM FactResellerSalesPtnd GROUP BY SalesTerritoryKey;Dans la fenêtre Résultats, sous l'onglet Plan d'exécution, assurez-vous que le plan de requête a sélectionné une analyse d'index de l'index non cluster csindx_FactResellerSalesPtnd.
[!REMARQUE]
Pour plus d'informations sur les icônes de plans d'exécution de requêtes graphiques, consultez Guide de référence des opérateurs Showplan logiques et physiques.
Haut
Principes de base : scénarios classiques d'index columnstore
En règle générale, les schémas de base de données en étoile et en flocon se trouvent dans les entrepôts de données et les mini-Data Warehouses dimensionnels, où la vitesse de l'extraction de données est plus importante que l'efficacité des manipulations de données. La technologie relative aux index columnstore dans SQL Server 2012 permet de détecter et d'accélérer les requêtes en ciblant les schémas en étoile et en flocon.
Exemples :
[!REMARQUE]
Le traitement par lots peut ne pas être utilisé dans les exemples suivants, car les tables ne sont pas suffisamment volumineuses. Le mode d'exécution par lots, par exemple le traitement parallèle, est utilisé uniquement pour les requêtes plus coûteuses.
A : Requête d'agrégation joignant deux tables
Prenons le cas d'une requête de jointure en étoile qui calcule le nombre de produits 215 vendus chaque trimestre. La table de faits nommée FactResellerSalesPtnd est partitionnée sur la colonne OrderDateKey. L'une des tables de dimension nommée DimDate est liée à la table de faits par une clé primaire (relation de clé étrangère sur la clé de date).
SELECT d.CalendarYear, d.CalendarQuarter, COUNT(*) AS NumberSold FROM dbo.FactResellerSalesPtnd AS f JOIN dbo.DimDate AS d ON f.OrderDateKey = d.DateKey WHERE ProductKey = 215 GROUP BY d.CalendarYear, d.CalendarQuarter ORDER BY d.CalendarYear, d.CalendarQuarter;
En outre, certaines requêtes peuvent uniquement cibler une seule table. Dans ce cas, SQL Server tente de tirer parti de la puissance de l'exécution par lots et de la technologie des index columnstore pour accélérer l'exécution de la requête.
B : Requête d'agrégation simple sur une seule table
La table de faits FactResellerSalesPtnd est partitionnée sur la colonne OrderDateKey. La requête suivante retourne le nombre de lignes et le nombre de commandes.
SELECT COUNT(*) AS NumberOfRows, COUNT(DISTINCT(f.SalesOrderNumber)) AS NumberOfOrders FROM dbo.FactResellerSalesPtnd AS f;Pour les requêtes d'entrepôts de données classiques, il est généralement possible d'obtenir un facteur de gain de vitesse compris entre 1,5 et 10 lorsque les index columnstore et le mode d'exécution par lots sont utilisés au cours de l'exécution de la requête. Pour certaines requêtes de jointure en étoile, le gain de vitesse est beaucoup plus important.
Principes de base : optimisations de filtres Bitmap
Outre la disposition des données dans un format en colonnes, SQL Server utilise des filtres Bitmap qui sont passés au moteur de stockage pour améliorer les performances lors de l'exécution de la requête. Les filtres Bitmap augmentent la vitesse d'exécution de la requête en réduisant le nombre de lignes incluses avant l'implémentation des jointures, ce qui permet de réduire en conséquence le nombre de lignes traitées par l'opérateur de jointure. L'information bitmap est créée du côté construction d'une jointure de hachage, mais les contrôles de bitmap effectifs sont effectués du côté sondage de la jointure de hachage. Vous pouvez observer l'utilisation de filtres Bitmap à l'aide du plan d'exécution graphique ou XML.
Meilleures pratiques : mise à jour des données dans un index columnstore
Les tables qui comportent un index columnstore ne peuvent pas être mises à jour. Il existe trois façons de contourner ce problème.
Pour mettre à jour une table avec un index columnstore, supprimez l'index columnstore, effectuez les opérations INSERT, DELETE, UPDATE ou MERGE requises, puis reconstruisez l'index columnstore.
Partitionnez la table et effectuez le basculement des partitions. Pour une insertion en bloc, insérez les données dans une table de mise en lots, construisez un index columnstore sur la table de mise en lots, puis effectuez le basculement de la table de mise en lots vers une partition vide. Pour d'autres mises à jour, effectuez le basculement d'une partition hors de la table principale vers une table de mise en lots, désactivez ou supprimez l'index columnstore sur la table de mise en lots, effectuez les opérations de mise à jour, reconstruisez ou recréez l'index columnstore sur la table de mise en lots, puis réeffectuez le basculement de la table de mise en lots vers la table principale.
Placez les données statiques dans une table principale avec un index columnstore, puis placez les nouvelles données et les données récentes susceptibles d'être modifiées dans une table distincte ayant le même schéma et sans index columnstore. Appliquez les mises à jour à la table à l'aide des données les plus récentes. Pour interroger les données, réécrivez la requête sous forme de deux requêtes, une pour chaque table, puis combinez les deux jeux de résultats avec UNION ALL. La sous-requête exécutée sur la grande table principale tire parti de l'index columnstore. Si la table modifiable est beaucoup plus petite, l'absence d'index columnstore a moins d'impact sur les performances. Lorsqu'il est également possible d'interroger une vue qui représente un UNION ALL des deux tables, le gain de performances n'est pas forcément visible. Les performances dépendent du plan de requête, lequel dépend de la requête, des données et des estimations de cardinalité. L'avantage de l'utilisation d'une vue est de permettre à un déclencheur INSTEADOF de cette vue de dévier les mises à jour vers la table qui ne dispose pas d'index columnstore. Par ailleurs, le mécanisme de la vue est transparent pour l'utilisateur et les applications. Si vous utilisez l'une de ces approches avec UNION ALL, testez les performances sur des requêtes classiques et déterminez si la commodité d'utilisation de cette approche l'emporte sur la perte des performances.
[!REMARQUE]
Ne créez pas d'index columnstore en tant que mécanisme permettant de rendre une table accessible en lecture seule. La restriction relative à la mise à jour des tables ayant un index columnstore n'est pas garantie dans les versions ultérieures. Lorsqu'un comportement en lecture seule est requis, il doit être appliqué via la création d'un groupe de fichiers en lecture seule et le déplacement de la table vers ce groupe de fichiers.
Meilleures pratiques : choix des colonnes pour un index columnstore
Une partie des gains de performances d'un index columnstore provient des techniques de compression qui réduisent le nombre de pages de données qui doivent être lues et manipulées pour le traitement de la requête. La compression fonctionne mieux sur les colonnes de caractères ou les colonnes numériques qui ont un grand nombre de valeurs dupliquées. Par exemple, les tables de dimension peuvent avoir des colonnes pour le code postal, la ville et la région de ventes. S'il existe de nombreux codes postaux pour chaque ville et s'il existe de nombreuses villes dans chaque région de ventes, la colonne de région de ventes est la plus compressée, la colonne de ville est un peu moins compressée et la colonne de code postal est la moins compressée. Bien que toutes les colonnes conviennent pour un index columnstore, l'ajout de la colonne du code de région de ventes à l'index columnstore permet d'obtenir le meilleur résultat en termes de compression, alors que la colonne de code postal correspond à la compression la plus faible.
Haut
Meilleures pratiques : tables partitionnées
Les index columnstore sont conçus pour prendre en charge les requêtes dans le cadre de scénarios d'entrepôts de données très volumineux, où le partitionnement est fréquent. Le partitionnement est recommandé lorsque les données d'une table ayant un index columnstore doivent être mises à jour périodiquement. Pour plus d'informations sur la mise à jour des partitions d'un index columnstore, consultez la section précédente Meilleures pratiques : mise à jour des données dans un index columnstore.
Haut
Procédure : créer un index columnstore
La création d'un index columnstore ressemble à la création de tout autre type d'index. Vous pouvez créer un index columnstore à l'aide de Transact-SQL ou à l'aide des outils graphiques de SQL Server Management Studio.
Création d'un index columnstore à l'aide de Transact-SQL
- Dans une fenêtre de l'Éditeur de requête, exécutez l'instruction CREATE COLUMNSTORE INDEX. Pour obtenir un exemple, consultez Créer la table FactResellerSalesPtnd ci-dessus. Pour plus d'informations, consultez CREATE COLUMNSTORE INDEX (Transact-SQL).
Création d'un index columnstore à l'aide de SQL Server Management Studio
À l'aide de Management Studio, utilisez l'Explorateur d'objets pour vous connecter à une instance du Moteur de base de données SQL Server.
Dans l'Explorateur d'objets, développez l'instance de SQL Server, développez Bases de données, développez une base de données, développez une table, cliquez avec le bouton droit sur une table, pointez sur Nouvel index, puis cliquez sur Index Columnstore non cluster.
Dans la boîte de dialogue Nom de l'index, sous l'onglet Général, tapez le nom du nouvel index, puis cliquez sur Ajouter.
Dans la boîte de dialogue Sélectionner les colonnes, sélectionnez les colonnes qui doivent participer à l'index columnstore, puis cliquez sur OK à deux reprises pour créer l'index.
Procédure : déterminer la taille d'un index columnstore
Un index columnstore comprend des segments et des dictionnaires. L'exemple suivant montre comment déterminer la taille totale d'un index columnstore (pour la table FactResellerSalesPtnd) en combinant les colonnes on_disk_size de sys.column_store_segments et sys.column_store_dictionaries.
SELECT SUM(on_disk_size_MB) AS TotalSizeInMB
FROM
(
(SELECT SUM(css.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
FROM sys.indexes AS i
JOIN sys.partitions AS p
ON i.object_id = p.object_id
JOIN sys.column_store_segments AS css
ON css.hobt_id = p.hobt_id
WHERE i.object_id = object_id('FactResellerSalesPtnd')
AND i.type_desc = 'NONCLUSTERED COLUMNSTORE')
UNION ALL
(SELECT SUM(csd.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
FROM sys.indexes AS i
JOIN sys.partitions AS p
ON i.object_id = p.object_id
JOIN sys.column_store_dictionaries AS csd
ON csd.hobt_id = p.hobt_id
WHERE i.object_id = object_id('FactResellerSalesPtnd')
AND i.type_desc = 'NONCLUSTERED COLUMNSTORE')
) AS SegmentsPlusDictionary
Procédure : résoudre les problèmes de performances d'un index columnstore
Pour déterminer si un index columnstore est utilisé, examinez le plan d'exécution de requête. Trois éléments sont présents en situation optimale.
L'index columnstore est présent dans le plan d'exécution de requête.
.gif "Analyse d'index columnstore")
icône d'opérateur d'analyse d'index columnstore
Si aucun index columnstore n'est utilisé et si vous pensez que la requête peut tirer parti du recours à un index columnstore, évaluez les performances de la requête tout en forçant l'utilisation de l'index columnstore à l'aide de l'indicateur WITH (INDEX(<indexname>)). L'exemple suivant illustre une requête avec un indicateur d'index.
SELECT d.CalendarYear, d.CalendarQuarter, COUNT(*) AS NumberSold FROM dbo.FactResellerSalesPtnd AS f WITH (INDEX(csindx_FactResellerSalesPtnd)) JOIN dbo.DimDate AS d ON f.OrderDateKey = d.DateKey WHERE ProductKey = 215 GROUP BY d.CalendarYear, d.CalendarQuarter ORDER BY d.CalendarYear, d.CalendarQuarter;Lorsque vous placez le pointeur sur l'icône d'index columnstore dans le plan de requête graphique, le mode d'exécution réel est indiqué en tant que mode d'exécution par lots au lieu du mode d'exécution par lignes.
Une icône d'opérateur physique bitmap est présente dans le plan d'exécution graphique pour indiquer que le filtre Bitmap réduit le nombre de lignes avant une opération de jointure.
.gif "Icône d'opérateur bitmap")
icône d'opérateur bitmap
Haut
Tâches associées
CREATE COLUMNSTORE INDEX (Transact-SQL)