Bien démarrer avec les index columnstore pour l’analytique opérationnelle en temps réel

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de données SQL dans Microsoft Fabric

SQL Server 2016 (13.x) introduit l’analytique opérationnelle en temps réel, la possibilité d’exécuter à la fois des charges de travail analytiques et OLTP sur les mêmes tables de base de données en même temps. Outre l’analyse en temps réel, vous pouvez aussi vous passer de l’ETL et d’un entrepôt de données.

Analytique opérationnelle en temps réel expliquée

En règle générale, les entreprises ont des systèmes séparés pour les charges de travail opérationnelles (autrement dit, OLTP) et analytiques. Pour ces systèmes, les tâches d’extraction, de transformation et de chargement (ETL) déplacent régulièrement les données du magasin opérationnel vers un magasin d’analytiques. Les données analytiques sont généralement stockées dans un entrepôt de données ou un mini-Data Warehouse dédié à l’exécution de requêtes analytiques. Même si cette solution constitue la norme, elle présente les trois principaux inconvénients suivants :

• Complexity. L’implémentation des opérations ETL peut nécessiter un codage important, en particulier pour charger uniquement les lignes modifiées. Il peut être difficile d’identifier les lignes qui ont été modifiées.
• Cost. L’implémentation des opérations ETL implique le coût de l’achat de licences logicielles et matérielles supplémentaires.
• Latence des données. La mise en œuvre d’un processus ETL entraîne un délai dans l’exécution des analyses. Par exemple, si la tâche ETL est exécutée à la fin de chaque journée de travail, les requêtes analytiques sont exécutées sur des données qui ont au moins un jour. Pour de nombreuses entreprises, ce délai est inacceptable, car les affaires dépendent de l’analyse des données en temps réel. Par exemple, la détection des fraudes nécessite l’analytique en temps réel des données opérationnelles.

L’analytique opérationnelle en temps réel offre une solution à ces inconvénients.

Il n’existe aucun délai quand les charges de travail analytiques et OLTP sont exécutées sur la même table sous-jacente. Pour les scénarios qui peuvent utiliser l’analytique en temps réel, les coûts et la complexité sont considérablement réduits en éliminant le besoin d’opérations ETL et la nécessité d’acheter et de gérer un entrepôt de données distinct.

L’analytique en temps réel utilise un index columnstore non-cluster modifiable sur une table rowstore. L’index columnstore gère une copie des données pour que les charges de travail OLTP et analytiques soient exécutées sur des copies distinctes des données. Cela réduit l’impact sur les performances de ces deux charges de travail en cours d’exécution en même temps. Le moteur de base de données gère automatiquement les modifications d’index afin que les modifications OLTP soient toujours up-to-date pour l’analytique. Grâce à cette conception, il est possible et pratique d’exécuter l’analytique en temps réel sur des données à jour. Cela fonctionne aussi bien pour les tables sur disque que pour les tables optimisées en mémoire.

Exemple de démarrage

Pour commencer à utiliser l’analytique en temps réel :

1. Identifiez les tables de votre schéma opérationnel qui contiennent les données requises pour l’analytique.

2. Pour chaque table, supprimez tous les index B-tree principalement conçus pour accélérer les analyses existantes de votre charge de travail OLTP. Remplacez-les par un index columnstore non-cluster unique. Cela peut améliorer les performances globales de votre charge de travail OLTP, car il existe moins d’index à gérer.

   --This example creates a nonclustered columnstore index on an existing OLTP table.
   --Create the table
   CREATE TABLE t_account (
       accountkey int PRIMARY KEY,
       accountdescription nvarchar (50),
       accounttype nvarchar(50),
       unitsold int
   );
   
   --Create the columnstore index with a filtered condition
   CREATE NONCLUSTERED COLUMNSTORE INDEX account_NCCI
   ON t_account (accountkey, accountdescription, unitsold)
   ;
   

   L’index columnstore sur une table optimisée en mémoire permet l’analytique opérationnelle en intégrant des technologies OLTP et columnstore en mémoire pour offrir de hautes performances pour les charges de travail OLTP et analytiques. L’index columnstore sur une table optimisée pour la mémoire doit être l’index en cluster, en d’autres termes, il doit inclure toutes les colonnes.

   -- This example creates a memory-optimized table with a columnstore index.
   CREATE TABLE t_account (
       accountkey int NOT NULL PRIMARY KEY NONCLUSTERED,
       Accountdescription nvarchar (50),
       accounttype nvarchar(50),
       unitsold int,
       INDEX t_account_cci CLUSTERED COLUMNSTORE
       )
       WITH (MEMORY_OPTIMIZED = ON );
   

Vous êtes maintenant prêt à exécuter l’analytique opérationnelle en temps réel sans apporter aucune modification à votre application. Les requêtes analytiques s’exécuteront sur l’index columnstore, tandis que les opérations OLTP continueront de s’exécuter sur vos index B-tree OLTP. Les charges de travail OLTP continuent de fonctionner, mais entraînent une surcharge supplémentaire pour gérer l’index columnstore. Consultez les optimisations des performances dans la section suivante.

Articles de blog

Lisez les billets de blog suivants pour en savoir plus sur l’analytique opérationnelle en temps réel. Il peut être plus facile de comprendre les sections relatives aux conseils en matière de performances si vous consultez d’abord les billets de blog.

• Cas d’entreprise pour l’analytique opérationnelle en temps réel

• Utilisation d’un index columnstore non cluster pour l’analytique opérationnelle en temps réel

• Un exemple simple utilisant un index columnstore non clusterisé

• Comment SQL Server gère un index columnstore non cluster sur une charge de travail transactionnelle

• Réduction de l’impact de la maintenance des index columnstore non-clusterisés à l’aide d’un index filtré

• Réduction de l’impact de la maintenance d’un index columnstore non clusterisé à l’aide du délai de compression

• Réduction de l’impact d’une maintenance d’index columnstore non clusterisé à l’aide d’un délai de compression - indicateurs de performance

• Analytique opérationnelle en temps réel à l’aide de tables à mémoire optimisée

• Index columnstore et stratégie de fusion pour les groupes de lignes

Videos

La série de vidéos Data Exposed entre davantage dans les détails sur certaines fonctionnalités et considérations.

• Partie 1 : Comment Azure SQL active l’analytique opérationnelle en temps réel (HTAP)
• Partie 2 : Optimiser les bases de données et applications existantes avec l’analytique opérationnelle
• Partie 3 : Comment créer une analytique opérationnelle avec Window Functions.

Conseil en matière de performances 1 : Utiliser des index filtrés pour améliorer les performances des requêtes

L’exécution d’analyses opérationnelles en temps réel peut affecter la performance de la charge de travail OLTP. Cet impact doit être minime. L’exemple A montre comment utiliser des index filtrés pour réduire l’impact de l’index columnstore non cluster sur la charge de travail transactionnelle tout en fournissant des analyses en temps réel.

Pour réduire les frais de gestion d’un index non-cluster columnstore sur une charge de travail opérationnelle, vous pouvez utiliser une condition filtrée pour créer un index non-cluster columnstore uniquement sur les données tièdes ou à évolution lente. Par exemple, dans une application de gestion de commandes, vous pouvez créer un index columnstore non clusterisé sur les commandes qui ont déjà été expédiées. Une fois que la commande a été expédiée, elle est rarement modifiée et peut par conséquent être considérée comme des données tièdes. Avec un index filtré, les données de l’index columnstore non cluster nécessitent moins de mises à jour, réduisant ainsi l’impact sur la charge de travail transactionnelle.

Les requêtes analytiques accèdent en toute transparence aux données tièdes et chaudes selon les besoins pour fournir l’analytique en temps réel. Si une partie importante de la charge de travail opérationnelle touche les données « chaudes », ces opérations ne nécessitent pas de maintenance supplémentaire de l’index columnstore. Il est recommandé d’avoir un index cluster rowstore sur les colonnes utilisées dans la définition de l’index filtré. Le moteur de base de données utilise l’index cluster pour analyser rapidement les lignes qui ne répondent pas à la condition filtrée. Sans cet index regroupé, un balayage complet de la table rowstore est nécessaire pour trouver ces lignes, ce qui peut nuire à la performance des requêtes analytiques. En l’absence d’index cluster, vous pouvez créer un index d’arbre B non cluster filtré complémentaire pour identifier ces lignes, mais cela n’est pas recommandé, car l’accès à une grande plage de lignes via les index d’arbre B non cluster est coûteux.

Exemple A : Accéder aux données chaudes depuis l’index B-tree, aux données tièdes depuis l’index columnstore

Cet exemple utilise une condition filtrée (accountkey > 0) pour établir les lignes incluses dans l’index columnstore. L’objectif est de concevoir la condition de filtre et les requêtes ultérieures de manière à accéder aux données « chaudes », qui changent fréquemment, depuis l’index en arbre B+, et aux données « tièdes » plus stables depuis l’index columnstore.

-- Use a filtered condition to separate hot data in a rowstore table
-- from "warm" data in a columnstore index.

-- create the table
CREATE TABLE orders (
AccountKey int not null,
CustomerName nvarchar (50),
OrderNumber bigint,
PurchasePrice decimal (9,2),
OrderStatus smallint not null,
OrderStatusDesc nvarchar (50)
);

-- OrderStatusDesc  
-- 0 => 'Order Started'  
-- 1 => 'Order Closed'  
-- 2 => 'Order Paid'  
-- 3 => 'Order Fulfillment Wait'  
-- 4 => 'Order Shipped'  
-- 5 => 'Order Received'  

CREATE CLUSTERED INDEX orders_ci ON orders(OrderStatus);

--Create the columnstore index with a filtered condition
CREATE NONCLUSTERED COLUMNSTORE INDEX orders_ncci ON orders  (accountkey, customername, purchaseprice, orderstatus)
WHERE OrderStatus = 5;

-- The following query returns the total purchase done by customers for items > $100 .00
-- This query will pick  rows both from NCCI and from 'hot' rows that are not part of NCCI
SELECT TOP (5) CustomerName, SUM(PurchasePrice)
FROM orders
WHERE PurchasePrice > 100.0
GROUP BY CustomerName;

La requête d’analyse s’exécute avec le plan de requête suivant. Vous pouvez constater que les lignes qui ne satisfont pas au critère de filtrage sont accessibles par l’intermédiaire de l’index B-tree clusterisé.

Pour plus d’informations, consultez Blog : Index columnstore non-cluster filtré.

Conseil en matière de performances 2 : Décharger l’analytique sur une base de données secondaire accessible en lecture Always On

Même si vous pouvez minimiser la maintenance de l’index des colonnes en utilisant un index filtré des colonnes, les requêtes analytiques peuvent tout de même nécessiter des ressources informatiques importantes (CPU, E/S, MÉMOIRE) qui affectent la performance opérationnelle de la charge de travail. Pour les charges de travail les plus critiques, notre recommandation est d’utiliser la configuration Always On. Dans cette configuration, vous pouvez éliminer l’impact de l’exécution des analyses en les déportant vers une base de données secondaire accessible en lecture.

Conseil de performance #3 : Réduction de la fragmentation des index en conservant les données chaudes dans les rowgroups delta

Les tables avec l’index columnstore peuvent être considérablement fragmentées (c’est-à-dire des lignes supprimées) si la charge de travail met à jour/supprime des lignes qui ont été compressées. Un index columnstore fragmenté entraîne une utilisation inefficace de la mémoire/du stockage. Outre l’utilisation inefficace des ressources, cela affecte négativement la performance des requêtes analytiques en raison d’un surcoût d’E/S et de la nécessité de filtrer les lignes supprimées de l’ensemble de résultats.

Les lignes supprimées ne sont pas physiquement retirées tant que vous n’avez pas exécuté la défragmentation d’index avec la commande REORGANIZE, ou que vous n’avez pas reconstruit l’index columnstore sur la table entière ou les partitions affectées. Les index REORGANIZE et REBUILD sont tous deux coûteux en ressources, lesquelles pourraient autrement être affectées à la charge de travail. En outre, si les lignes sont compressées trop tôt, elles risquent de devoir être recompressées plusieurs fois en raison des mises à jour, ce qui entraîne un surcoût de compression inutile.

Vous pouvez réduire la fragmentation des index avec l’option COMPRESSION_DELAY.

-- Create a sample table
CREATE TABLE t_colstor (
accountkey int not null,
accountdescription nvarchar (50) not null,
accounttype nvarchar(50),
accountCodeAlternatekey int
);

-- Creating nonclustered columnstore index with COMPRESSION_DELAY. 
-- The columnstore index will keep the rows in closed delta rowgroup 
-- for 100 minutes after it has been marked closed.
CREATE NONCLUSTERED COLUMNSTORE INDEX t_colstor_cci ON t_colstor
(accountkey, accountdescription, accounttype)
WITH (DATA_COMPRESSION = COLUMNSTORE, COMPRESSION_DELAY = 100);

Pour plus d’informations, consultez Blog : Délai de compression.

Voici les bonnes pratiques recommandées :

• Charge de travail d’insertion/requête : Si votre charge de travail insère principalement des données et l’interroge, la valeur par défaut COMPRESSION_DELAY 0 est l’option recommandée. Les lignes nouvellement insérées seront compressées une fois qu’un million de lignes auront été insérées dans un seul groupe de lignes delta. Certains exemples de ces charges de travail sont une charge de travail DW traditionnelle ou une analyse de flux de sélection lorsque vous devez analyser le modèle de sélection dans une application web.

• Charge de travail OLTP : si la charge de travail est fortement axée sur les opérations DML (c’est-à-dire qu’elle comporte un grand nombre d’opérations de mise à jour, de suppression et d’insertion), vous pouvez constater une fragmentation de l’index columnstore en examinant la DMV sys.dm_db_column_store_row_group_physical_stats. Si vous voyez que > 10% de lignes sont marquées comme supprimées dans des groupes de lignes récemment compressés, vous pouvez utiliser COMPRESSION_DELAY l'option pour ajouter un délai lorsque les lignes deviennent prêtes pour la compression. Si les données récemment insérées pour votre charge de travail restent « chaudes » (autrement dit, si elles sont mises à jour plusieurs fois) pendant, par exemple, 60 minutes, vous devez choisir la valeur 60 pour COMPRESSION_DELAY.

La valeur par défaut de l’option COMPRESSION_DELAY doit fonctionner pour la plupart des clients.

Pour les utilisateurs avancés, nous vous recommandons d’exécuter la requête suivante et de collecter % de lignes supprimées au cours des sept derniers jours.

SELECT row_group_id,
       CAST(deleted_rows AS float)/CAST(total_rows AS float)*100 AS [% fragmented],
       created_time
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('FactOnlineSales2')
      AND state_desc = 'COMPRESSED'
      AND deleted_rows > 0
      AND created_time > DATEADD(day, -7, GETDATE())
ORDER BY created_time DESC;

Si le nombre de lignes supprimées dans les rowgroups compressés est > 20 %, stable dans les anciens rowgroups avec une variation de < 5 % (désignés sous le nom de rowgroups froids), définissez COMPRESSION_DELAY = (youngest_rowgroup_created_time – current_time). Cette approche fonctionne mieux avec une charge de travail stable et relativement homogène.

Contenu connexe

• Index columnstore : vue d’ensemble
• Index columnstore - Conseils en matière de chargement de données
• Index columnstore - Performances des requêtes
• Index columnstore dans l'entreposage de données
• Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources