Prise en main de Columnstore pour l’analytique opérationnelle en temps réelGet started with Columnstore for real time operational analytics

S’APPLIQUE À : ouiSQL Server ouiAzure SQL Database nonAzure SQL Data Warehouse nonParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server 2016 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.SQL Server 2016 introduces real-time operational analytics, the ability to run both analytics and OLTP workloads on the same database tables at the same time. Outre l’exécution de l’analytique en temps réel, vous pouvez également éliminer le besoin d’opérations d’extraction, de transformation et de chargement (ETL) et d’un entrepôt de données.Besides running analytics in real-time, you can also eliminate the need for ETL and a data warehouse.

Description de l’analytique opérationnelle en temps réelReal-Time Operational Analytics Explained

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.Traditionally, businesses have had separate systems for operational (i.e. OLTP) and analytics workloads. 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.For such systems, Extract, Transform, and Load (ETL) jobs regularly move the data from the operational store to an analytics store. 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.The analytics data is usually stored in a data warehouse or data mart dedicated to running analytics queries. Même si cette solution constitue la norme, elle présente les trois principaux inconvénients suivants :While this solution has been the standard, it has these three key challenges:

  • Complexité.Complexity. L’implémentation des opérations ETL peut nécessiter un codage important, en particulier pour charger uniquement les lignes modifiées.Implementing ETL can require considerable coding especially to load only the modified rows. Il peut être difficile d’identifier les lignes qui ont été modifiées.It can be complex to identify which rows have been modified.

  • Coût.Cost. L’implémentation des opérations ETL implique le coût de l’achat de licences logicielles et matérielles supplémentaires.Implementing ETL requires the cost of purchasing additional hardware and software licenses.

  • Latence des données.Data Latency. L’implémentation des opérations ETL ajoute un délai pour l’exécution de l’analytique.Implementing ETL adds a time delay for running the analytics. 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.For example, if the ETL job runs at the at end of each business day, the analytics queries will run on data that is at least a day old. Pour de nombreuses entreprises, ce délai est inacceptable, car les affaires dépendent de l’analyse des données en temps réel.For many businesses this delay is unacceptable because the business depends on analyzing data in real-time. Par exemple, la détection des fraudes nécessite l’analytique en temps réel des données opérationnelles.For example, fraud-detection requires real-time analytics on operational data.

    vue d’ensemble de l’analytique opérationnelle en temps réelreal-time operational analytics overview

    L’analytique opérationnelle en temps réel offre une solution à ces inconvénients.Real-time operational analytics offers a solution to these challenges.
    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.There is no time delay when analytics and OLTP workloads run on the same underlying table. 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.For scenarios that can use real-time analytics, the costs and complexity are greatly reduced by eliminating the need for ETL and the need to purchase and maintain a separate data warehouse.

Note

L’analytique opérationnelle en temps réel cible le scénario d’une source de données unique comme une application ERP (Enterprise Resource Planning) sur laquelle vous pouvez exécuter à la fois les charges de travail opérationnelles et analytiques.Real-time operational analytics targets the scenario of a single data source such as an enterprise resource planning (ERP) application on which you can run both the operational and the analytics workload. Cela ne remplace pas la nécessité d’un entrepôt de données distinct quand vous avez devez intégrer des données provenant de plusieurs sources avant d’exécuter la charge de travail analytique ou que vous avez besoin de performances analytiques très élevées avec des données préagrégées telles que des cubes.This does not replace the need for a separate data warehouse when you need to integrate data from multiple sources before running the analytics workload or when you require extreme analytics performance using pre-aggregated data such as cubes.

L’analytique en temps réel utilise un index columnstore modifiable sur une table rowstore.Real-time analytics uses an updateable columnstore index on a rowstore table. 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.The columnstore index maintains a copy of the data, so the OLTP and analytics workloads run against separate copies of the data. Cela réduit l’impact sur les performances de ces deux charges de travail en cours d’exécution en même temps.This minimizes the performance impact of both workloads running at the same time. SQL Server gère automatiquement les modifications d’index pour que les changements OLTP soient toujours à jour pour l’analytique.SQL Server automatically maintains index changes so OLTP changes are always up-to-date for analytics. Grâce à cette conception, il est possible et pratique d’exécuter l’analytique en temps réel sur des données à jour.With this design, it is possible and practical to run analytics in real-time on up-to-date data. Cela fonctionne aussi bien pour les tables sur disque que pour les tables optimisées en mémoire.This works for both disk-based and memory-optimized tables.

Exemple de prise en mainGet Started Example

Pour commencer à utiliser l’analytique en temps réel :To get started with real-time analytics:

  1. Identifiez les tables de votre schéma opérationnel qui contiennent les données requises pour l’analytique.Identify the tables in your operational schema that contain data required for analytics.

  2. Pour chaque table, supprimez tous les index BTree principalement conçus pour accélérer l’analytique existante sur votre charge de travail OLTP.For each table, drop all btree indexes that are primarily designed to speed up existing analytics on your OLTP workload. Remplacez-les par un index columnstore unique.Replace them with a single columnstore index. Cela peut améliorer les performances globales de votre charge de travail OLTP, car il y aura moins d’index à gérer.This can improve the overall performance of your OLTP workload since there will be fewer indexes to maintain.

    --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 en mémoire permet l’analytique opérationnelle en intégrant des technologies OLTP et columnstore en mémoire pour offrir des performances élevées pour les charges de travail OLTP et analytiques.The columnstore index on an in-memory table allows operational analytics by integrating in-memory OLTP and in-memory columnstore technologies to deliver high performance for both OLTP and analytics workloads. L’index columnstore sur une table en mémoire doit inclure toutes les colonnes.The columnstore index on an in-memory table must include all the columns.

    -- 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 );  
    GO  
    
  3. Vous n’avez rien d’autre à faire !This is all you need to do!

    Vous êtes maintenant prêt à exécuter l’analytique opérationnelle en temps réel sans apporter aucune modification à votre application.You are now ready to run real-time operational analytics without making any changes to your application. Les requêtes analytiques sont exécutées sur l’index columnstore et les opérations OLTP continuent de s’exécuter sur les index BTree OLTP.Analytics queries will run against the columnstore index and OLTP operations will keep running against your OLTP btree indexes. Les charges de travail OLTP continuent de fonctionner, mais encourent une surcharge supplémentaire pour gérer l’index columnstore.The OLTP workloads will continue to perform, but will incur some additional overhead to maintain the columnstore index. Consultez les optimisations des performances dans la section suivante.See the performance optimizations in the next section.

Billets de blogBlog Posts

Lisez les billets de blog de Sunil Agarwal pour en savoir plus sur l’analytique opérationnelle en temps réel.Read Sunil Agarwal's blog posts to learn more about real-time operational analytics. 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.It might be easier to understand the performance tips sections if you look at the blog posts first.

Conseil en matière de performances 1 : Utiliser des index filtrés pour améliorer les performances des requêtesPerformance tip #1: Use filtered indexes to improve query performance

L’exécution de l’analytique opérationnelle en temps réel peut avoir un impact sur les performances de la charge de travail OLTP.Running real-time operational analytics can impact the performance of the OLTP workload. Cet impact doit être minime.This impact should be minimal. L’exemple ci-dessous montre comment utiliser des index filtrés pour réduire l’impact des index non cluster columnstore sur la charge de travail transactionnelle tout en offrant l’analytique en temps réel.The example below shows how to use filtered indexes to minimize impact of nonclustered columnstore index on transactional workload while still delivering analytics in real-time.

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.To minimize the overhead of maintaining a nonclustered columnstore index on an operational workload, you can use a filtered condition to create a nonclustered columnstore index only on the warm or slowly changing data. Par exemple, dans une application de gestion de commandes, vous pouvez créer un index non cluster columnstore sur les commandes qui ont déjà été expédiées.For example, in an order management application, you can create a nonclustered columnstore index on the orders that have already been shipped. 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.Once the order has shipped, it rarely changes and therefore can be considered warm data. Avec un index filtré, les données dans un index non cluster columnstore nécessitent moins de mises à jour, ce qui réduit l’impact sur la charge de travail transactionnelle.With Filtered index, the data in nonclustered columnstore index requires fewer updates thereby lowering the impact on transactional workload.

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.Analytics queries transparently access both warm and hot data as needed to provide real-time analytics. Si une partie importante de la charge de travail opérationnelle affecte les données « chaudes », ces opérations ne nécessitent pas de maintenance supplémentaire de l’index columnstore.If a significant part of the operational workload is touching the 'hot' data, those operations will not require additional maintenance of the columnstore index. Il est recommandé d’avoir un index cluster rowstore sur les colonnes utilisées dans la définition de l’index filtré.A best practice is to have a rowstore clustered index on the column(s) used in the filtered index definition. SQL Server utilise l’index cluster pour analyser rapidement les lignes qui n’ont pas respecté la condition filtrée.SQL Server uses the clustered index to quickly scan the rows that did not meet the filtered condition. Sans cet index cluster, une analyse complète de la table rowstore est nécessaire pour trouver les lignes qui peuvent avoir un impact négatif considérable sur les performances de la requête analytique.Without this clustered index, a full table scan of the rowstore table will be required to find these rows which can negatively impact the performance of analytics query significantly. En l’absence d’index cluster, vous pouvez créer un index BTree 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 BTree non cluster est coûteux.In the absence of clustered index, you could create a complementary filtered nonclustered btree index to identify such rows but it is not recommended because accessing large range of rows through nonclustered btree indexes is expensive.

Note

Un index non cluster columnstore filtré n’est pris en charge que sur les tables sur disque.A filtered nonclustered columnstore index is only supported on disk-based tables. Il n’est pas pris en charge sur les tables optimisées en mémoire.It is not supported on memory-optimized tables

Exemple A : Accès aux données chaudes à partir de l’index BTree, aux données tièdes à partir de l’index columnstoreExample A: Access hot data from btree index, warm data from columnstore index

Cet exemple utilise une condition filtrée (accountkey > 0) pour déterminer les lignes qui seront dans l’index columnstore.This example uses a filtered condition (accountkey > 0) to establish which rows will be in the columnstore index. L’objectif est de concevoir la condition filtrée et les requêtes suivantes pour accéder aux données « chaudes » qui changent fréquemment à partir de l’index BTree et pour accéder aux données « tièdes » plus stables à partir de l’index columnstore.The goal is to design the filtered condition and subsequent queries to access frequently changing “hot” data from the btree index, and to access the more stable “warm” data from the columnstore index.

Index combinés de données tièdes et chaudesCombined indexes for warm and hot data

Note

L’optimiseur de requête envisage, mais ne choisit pas toujours, l’index columnstore pour le plan de requête.The query optimizer will consider, but not always choose, the columnstore index for the query plan. Quand l’optimiseur de requête choisit l’index columnstore filtré, il associe en toute transparence à la fois les lignes de l’index columnstore et les lignes qui ne respectent pas la condition filtrée pour permettre l’analytique en temps réel.When the query optimizer chooses the filtered columnstore index, it transparently combines the rows both from columnstore index as well as the rows that do not meet the filtered condition to allow real-time analytics. Cela est différent d’un index non cluster filtré standard qui peut être utilisé uniquement dans les requêtes qui se limitent aux lignes présentes dans l’index.This is different from a regular nonclustered filtered index which can be used only in queries that restrict themselves to the rows present in the index.

--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 Fullfillment 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 analytique est exécutée avec le plan de requête suivant.The analytics query will execute with the following query plan. Vous pouvez voir que les lignes ne respectant pas la condition filtrée sont accessibles via l’index BTree cluster.You can see that the rows not meeting the filtered condition are accessed through clustered btree index.

Plan de requêteQuery plan

Consultez le blog pour plus d’informations sur l’ index non cluster columnstore filtré.Please refer to the blog for details on filtered nonclustered columnstore index.

Conseil en matière de performances 2 : Décharger l’analytique sur une base de données secondaire accessible en lecture Always OnPerformance tip #2: Offload analytics to Always On readable secondary

Même si vous pouvez réduire la maintenance des index columnstore à l’aide d’un index columnstore filtré, les requêtes analytiques peuvent toujours nécessiter d’importantes ressources de calcul (processeur, E/S, mémoire) qui ont un impact sur les performances de la charge de travail opérationnelle.Even though you can minimize the columnstore index maintenance by using a filtered columnstore index, the analytics queries can still require significant computing resources (CPU, IO, memory) which impact the operational workload performance. Pour les charges de travail les plus critiques, notre recommandation est d’utiliser la configuration Always On.For most mission critical workloads, our recommendation is to use the Always On configuration. Dans cette configuration, vous pouvez éliminer l’impact de l’exécution de l’analytique en la déchargeant sur une base de données secondaire accessible en lecture.In this configuration, you can eliminate the impact of running analytics by offloading it to a readable secondary.

Conseil en matière de performances 3 : Réduire la fragmentation de l’index en conservant les données chaudes dans les rowgroups deltaPerformance Tip #3: Reducing Index fragmentation by keeping hot data in delta rowgroups

La fragmentation des tables avec un index columnstore peut être importante (avec les lignes supprimées) si la charge de travail met à jour/supprime des lignes qui ont été compressées.Tables with columnstore index may get significantly fragmented (i.e. deleted rows) if the workload updates/deletes rows that have been compressed. Un index columnstore fragmenté entraîne une utilisation inefficace de la mémoire/du stockage.A fragmented columnstore index leads to inefficient utilization of memory/storage. Outre l’utilisation inefficace des ressources, l’impact est également négatif sur les performances des requêtes analytiques en raison d’E/S supplémentaires et de la nécessité de filtrer les lignes supprimées du jeu de résultats.Besides inefficient use of resources, it also negatively impacts the analytics query performance because of extra IO and the need to filter the deleted rows from the result set.

Les lignes supprimées ne sont pas physiquement retirées jusqu’à ce que vous exécutiez la défragmentation d’index avec la commande REORGANIZE ou que vous reconstruisiez l’index columnstore sur la table entière ou les partitions affectées.The deleted rows are not physically removed until you run index defragmentation with REORGANIZE command or rebuild the columnstore index on the entire table or the affected partition(s). Les opérations de réorganisation et de reconstruction d’index sont toutes deux des opérations coûteuses qui retirent des ressources qui pourraient autrement être utilisées pour la charge de travail.Both REORGANIZE and Index REBUILD are expensive operations taking resources away which otherwise could be used for the workload. En outre, si les lignes sont compressées trop tôt, il est possible qu’elles doivent être recompressées plusieurs fois en raison de mises à jour conduisant à une surcharge de compression gaspillée.Additionally, if rows compressed too early, it may need to be re-compressed multiple times due to updates leading to wasted compression overhead.
Vous pouvez réduire la fragmentation des index à l’aide de l’option COMPRESSION_DELAY.You can minimize index fragmentation using COMPRESSION_DELAY option.


-- 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);  

;  

Consultez le blog pour plus d’informations sur le délai de compression.Please refer to the blog for details on compression delay.

Voici les meilleures pratiques recommandéesHere are the recommended best practices

  • Charge de travail d’insertion/d’interrogation : si votre charge de travail insère et interroge principalement des données, l’option COMPRESSION_DELAY avec la valeur par défaut 0 est l’option recommandée.Insert/Query workload: If your workload is primarily inserting data and querying it, the default COMPRESSION_DELAY of 0 is the recommended option. Les lignes nouvellement insérées sont compressées quand 1 million de lignes ont été insérées dans un rowgroup delta unique.The newly inserted rows will get compressed once 1 million rows have been inserted into a single delta rowgroup.
    Parmi les exemples de cette charge de travail figurent (a) une charge de travail DW classique (b) une analyse des parcours de visite quand vous devez analyser le modèle de clic dans une application web.Some example of such workload are (a) traditional DW workload (b) click-stream analysis when you need to analyze the click pattern in a web application.

  • Charge de travail OLTP : si la charge de travail est de type DML lourd (autrement dit, de nombreuses mises à jour, suppressions et insertions associées), vous pouvez voir la fragmentation des index columnstore en examinant la vue de gestion de données sys.OLTP workload: If the workload is DML heavy (i.e. heavy mix of Update, Delete and Insert), you may see columnstore index fragmentation by examining the DMV sys. dm_db_column_store_row_group_physical_stats.dm_db_column_store_row_group_physical_stats. Si vous voyez que plus de 10 % des lignes sont marquées comme supprimées dans des rowgroups récemment compressés, vous pouvez utiliser l’option COMPRESSION_DELAY pour ajouter un délai quand les lignes sont éligibles pour la compression.If you see that > 10% rows are marked deleted in recently compressed rowgroups, you can use COMPRESSION_DELAY option to add time delay when rows become eligible for 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.For example, if for your workload, the newly inserted stays ‘hot’ (i.e. gets updated multiple times) for say 60 minutes, you should choose COMPRESSION_DELAY to be 60.

    Nous pensons que la plupart des clients n’auront besoin de rien faire.We expect most customers do not need to anything. La valeur par défaut de l’option COMPRESSION_DELAY doit leur convenir.The default value of COMPRESSION_DELAY option should work for them.
    Aux utilisateurs expérimentés, nous recommandons d’exécuter la requête ci-dessous et de collecter un pourcentage de lignes supprimées dans les 7 derniers jours.For advance users, we recommend running the query below and collect % of deleted rows over the last 7 days.

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 > GETDATE() - 7  
ORDER BY created_time DESC  

Si le nombre de lignes supprimées dans les rowgroups compressés est supérieur à 20 %, stable dans les anciens rowgroups avec une variation inférieure à 5 % (désignés sous le nom de rowgroups froids), définissez COMPRESSION_DELAY = (youngest_rowgroup_created_time – current_time).If the number of deleted rows in compressed rowgroups > 20%, plateauing in older rowgroups with < 5% variation (referred to as cold rowgroups) set COMPRESSION_DELAY = (youngest_rowgroup_created_time – current_time). Notez que cette approche fonctionne mieux avec une charge de travail stable et relativement homogène.Note that this approach works best with a stable and relatively homogeneous workload.

Voir aussiSee Also

Guide des index columnstore Columnstore Indexes Guide
Chargement de données d’index columnstore Columnstore Indexes Data Loading
Performances des requêtes d’index columnstore Columnstore Indexes Query Performance
Index columnstore pour l’entreposage des données Columnstore Indexes for Data Warehousing
Défragmentation des index columnstoreColumnstore Indexes Defragmentation