Introduzione a columnstore per l'analisi operativa in tempo realeGet started with Columnstore for real time operational analytics

QUESTO ARGOMENTO SI APPLICA A: sìSQL Server (a partire dalla versione 2016)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server 2016 introduce l'analisi operativa in tempo reale, cioè la possibilità di eseguire contemporaneamente analisi e carichi di lavoro OLTP nelle stesse tabelle di database.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. Oltre a eseguire analisi in tempo reale, è possibile anche eliminare la necessità di ETL e di un data warehouse.Besides running analytics in real-time, you can also eliminate the need for ETL and a data warehouse.

Descrizione dell'analisi operativa in tempo realeReal-Time Operational Analytics Explained

In passato le aziende usavano sistemi separati per i carichi di lavoro operativi (ad esempio OLTP) e di analisi.Traditionally, businesses have had separate systems for operational (i.e. OLTP) and analytics workloads. Per questi sistemi, i processi di estrazione, trasformazione e caricamento (ETL) spostano regolarmente i dati dall'archivio operativo in un archivio di analisi.For such systems, Extract, Transform, and Load (ETL) jobs regularly move the data from the operational store to an analytics store. I dati di analisi sono vengono in genere archiviati in un data warehouse o data mart dedicato all'esecuzione di query di analisi.The analytics data is usually stored in a data warehouse or data mart dedicated to running analytics queries. Anche se questa soluzione ha rappresentato lo standard, presentava tre problemi principali:While this solution has been the standard, it has these three key challenges:

  • Complessità.Complexity. L'implementazione di ETL può richiedere una notevole quantità di codifica, soprattutto per caricare solo le righe modificate.Implementing ETL can require considerable coding especially to load only the modified rows. L'identificazione delle righe che sono state modificate può risultare difficile.It can be complex to identify which rows have been modified.

  • Costi.Cost. L'implementazione di ETL richiede il costo di acquisto di licenze software e hardware aggiuntive.Implementing ETL requires the cost of purchasing additional hardware and software licenses.

  • Latenza dei dati.Data Latency. L'implementazione di ETL aggiunge un ritardo per l'esecuzione delle analisi.Implementing ETL adds a time delay for running the analytics. Ad esempio, se il processo ETL viene eseguito al termine di ogni giornata lavorativa, le query di analisi verranno eseguite sui dati che risalgono ad almeno un giorno prima.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. Per molte aziende questo ritardo è inaccettabile, perché l'attività dipende dall'analisi dei dati in tempo reale.For many businesses this delay is unacceptable because the business depends on analyzing data in real-time. Ad esempio, il rilevamento di frodi richiede l'analisi in tempo reale sui dati operativi.For example, fraud-detection requires real-time analytics on operational data.

    panoramica dell'analisi operativa in tempo realereal-time operational analytics overview

    L'analisi operativa in tempo reale offre una soluzione a questi problemi.Real-time operational analytics offers a solution to these challenges.
    Non si verifica alcun ritardo durante l'esecuzione di analisi e carichi di lavoro OLTP nella stessa tabella sottostante.There is no time delay when analytics and OLTP workloads run on the same underlying table. Per gli scenari in cui è possibile usare l'analisi in tempo reale, i costi e la complessità vengono notevolmente ridotti eliminando la necessità di ETL e di acquistare e gestire un data warehouse separato.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.

Nota

L'analisi operativa in tempo reale è destinata allo scenario di una singola origine dati, ad esempio un'applicazione ERP (Enterprise Resource Planning) in cui è possibile eseguire sia i carichi di lavoro operativi che i carichi di lavoro di analisi.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. Ciò non sostituisce la necessità di un data warehouse separato quando è necessario integrare dati da più origini prima di eseguire il carico di lavoro di analisi o quando sono necessarie prestazioni delle analisi estremamente elevate usando dati preaggregati, ad esempio cubi.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'analisi in tempo reale usa un indice columnstore aggiornabile in una tabella rowstore.Real-time analytics uses an updateable columnstore index on a rowstore table. L'indice columnstore gestisce una copia dei dati, quindi i carichi di lavoro OLTP e di analisi vengono eseguiti su copie separate dei dati.The columnstore index maintains a copy of the data, so the OLTP and analytics workloads run against separate copies of the data. In questo modo si riduce al minimo l'impatto sulle prestazioni causato dall'esecuzione contemporanea di entrambi i carichi di lavoro.This minimizes the performance impact of both workloads running at the same time. SQL Server gestisce automaticamente le modifiche di indice in modo le modifiche OLTP siano sempre aggiornate per l'analisi.SQL Server automatically maintains index changes so OLTP changes are always up-to-date for analytics. Con questa progettazione l'esecuzione di analisi in tempo reale su dati aggiornati è possibile e utile.With this design, it is possible and practical to run analytics in real-time on up-to-date data. Ciò funziona sia per le tabelle basate su disco che per le tabelle con ottimizzazione per la memoria.This works for both disk-based and memory-optimized tables.

Esempio introduttivoGet Started Example

Per iniziare a usare l'analisi in tempo reale:To get started with real-time analytics:

  1. Nello schema operativo identificare le tabelle che contengono i dati necessari per l'analisi.Identify the tables in your operational schema that contain data required for analytics.

  2. Per ogni tabella eliminare tutti gli indici Btree progettati principalmente per velocizzare l'analisi esistente sul carico di lavoro OLTP.For each table, drop all btree indexes that are primarily designed to speed up existing analytics on your OLTP workload. Sostituirli con un indice columnstore singolo.Replace them with a single columnstore index. Questo può migliorare le prestazioni complessive del carico di lavoro OLTP perché saranno presenti meno indici da gestire.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'indice columnstore in una tabella in memoria consente l'analisi operativa grazie all'integrazione di tecnologie OLTP in memoria e columnstore in memoria per fornire prestazioni elevate per carichi di lavoro OLTP e di analisi.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'indice columnstore in una tabella in memoria deve includere tutte le colonne.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. Non è necessario eseguire altre operazioni.This is all you need to do!

    A questo punto si è pronti per eseguire l'analisi operativa in tempo reale senza apportare modifiche all'applicazione.You are now ready to run real-time operational analytics without making any changes to your application. Le query di analisi verranno eseguite sull'indice columnstore e le operazioni OLTP continueranno a essere eseguite su indici Btree OLTP.Analytics queries will run against the columnstore index and OLTP operations will keep running against your OLTP btree indexes. I carichi di lavoro OLTP continueranno a essere eseguiti, ma la gestione dell'indice columnstore determinerà un sovraccarico aggiuntivo.The OLTP workloads will continue to perform, but will incur some additional overhead to maintain the columnstore index. Vedere le ottimizzazioni delle prestazioni nella sezione successiva.See the performance optimizations in the next section.

Post di blogBlog Posts

Leggere i post di blog di Sunil Agarwal per altre informazioni sull'analisi operativa in tempo reale.Read Sunil Agarwal's blog posts to learn more about real-time operational analytics. La lettura preliminare del blog potrebbe semplificare la comprensione delle sezioni relative ai suggerimenti sulle prestazioni.It might be easier to understand the performance tips sections if you look at the blog posts first.

Suggerimento per le prestazioni n. 1: usare indici filtrati per migliorare le prestazioni delle queryPerformance tip #1: Use filtered indexes to improve query performance

L'esecuzione dell'analisi operativa in tempo reale può compromettere le prestazioni del carico di lavoro OLTP.Running real-time operational analytics can impact the performance of the OLTP workload. Tale impatto dovrebbe essere minimo.This impact should be minimal. L'esempio mostra come usare gli indici filtrati per ridurre al minimo l'impatto dell'indice columnstore non cluster nel carico di lavoro transazionale offrendo comunque analisi in tempo reale.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.

Per ridurre al minimo l'overhead di gestione di un indice columnstore non cluster in un carico di lavoro operativo, è possibile usare una condizione filtrata per creare un indice columnstore non cluster solo per i dati meno attivi o a modifica lenta.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. Ad esempio, in un'applicazione di gestione degli ordini è possibile creare un indice columnstore non cluster negli ordini che sono già stati spediti.For example, in an order management application, you can create a nonclustered columnstore index on the orders that have already been shipped. Dopo la spedizione, raramente l'ordine viene modificato e quindi i dati possono essere considerati meno attivi.Once the order has shipped, it rarely changes and therefore can be considered warm data. Con l'indice filtrato i dati nell'indice columnstore non cluster richiedono meno aggiornamenti, riducendo in tal modo l'impatto sul carico di lavoro transazionale.With Filtered index, the data in nonclustered columnstore index requires fewer updates thereby lowering the impact on transactional workload.

Le query di analisi accedono in modo trasparente sia ai dati meno attivi che ai dati attivi in base alle esigenze per fornire analisi in tempo reale.Analytics queries transparently access both warm and hot data as needed to provide real-time analytics. Se una parte importante del carico di lavoro operativo riguarda la gestione dei dati attivi, le operazioni non richiederanno ulteriore manutenzione dell'indice 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. Una procedura consigliata prevede la creazione di un indice cluster rowstore per le colonne usate nella definizione dell'indice filtrato.A best practice is to have a rowstore clustered index on the column(s) used in the filtered index definition. SQL Server usa l'indice cluster per analizzare rapidamente le righe che non soddisfano la condizione filtrata.SQL Server uses the clustered index to quickly scan the rows that did not meet the filtered condition. Senza questo indice cluster, sarà necessario eseguire una scansione di tabella completa della tabella rowstore per trovare le righe che possono esercitare un elevato impatto negativo sulle prestazioni di una query di analisi.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. In assenza di un indice cluster si potrebbe creare un indice Btree non cluster filtrato complementare per identificare le righe, ma questa scelta non è consigliabile perché l'accesso a un ampio intervallo di righe usando indici Btree non cluster comporta costi elevati.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.

Nota

Un indice columnstore non cluster filtrato è supportato solo nelle tabelle basate su disco.A filtered nonclustered columnstore index is only supported on disk-based tables. Non è supportato nelle tabelle con ottimizzazione per la memoria.It is not supported on memory-optimized tables

Esempio A: accesso a dati attivi da un indice Btree, dati meno attivi dall'indice columnstoreExample A: Access hot data from btree index, warm data from columnstore index

Questo esempio usa una condizione filtrata (accountkey > 0) per stabilire quali righe saranno presenti nell'indice columnstore.This example uses a filtered condition (accountkey > 0) to establish which rows will be in the columnstore index. L'obiettivo è di progettare la condizione filtrata e le query successive per accedere a dati attivi, caratterizzati da modifiche frequenti, dall'indice Btree e di accedere ai dati meno attivi, che sono più stabili, dall'indice 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.

Indici combinati per dati attivi e meno attiviCombined indexes for warm and hot data

Nota

Query Optimizer prenderà in considerazione, ma non sempre sceglierà, l'indice columnstore per il piano di query.The query optimizer will consider, but not always choose, the columnstore index for the query plan. Quando Query Optimizer sceglie l'indice columnstore filtrato, combina in modo trasparente le righe dall'indice columnstore nonché le righe che non soddisfano la condizione filtrata per consentire l'analisi in tempo reale.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. Si tratta di un indice diverso da un normale indice filtrato non cluster che può essere usato solo nelle query limitate alle righe presenti nell'indice.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 query di analisi verrà eseguita con il piano di query seguente.The analytics query will execute with the following query plan. Come si può notare, è possibile accedere alle righe che non soddisfano la condizione filtrata solo usando l'indice Btree cluster.You can see that the rows not meeting the filtered condition are accessed through clustered btree index.

Piano della queryQuery plan

Consultare il blog per informazioni dettagliate sull' indice columnstore non cluster filtrato.Please refer to the blog for details on filtered nonclustered columnstore index.

Suggerimento per le prestazioni n. 2: offload dell'analisi a una replica secondaria leggibile AlwaysOnPerformance tip #2: Offload analytics to Always On readable secondary

Anche se è possibile ridurre al minimo la manutenzione degli indici columnstore usando un indice columnstore filtrato, le query di analisi richiedono comunque risorse di calcolo elevate (CPU, I/O, memoria) che influiscono sulle prestazioni del carico di lavoro operativo.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. Per i carichi di lavoro maggiormente mission-critical, si consiglia di usare la configurazione AlwaysOn.For most mission critical workloads, our recommendation is to use the Always On configuration. In questa configurazione è possibile eliminare l'impatto dell'esecuzione dell'analisi eseguendone l'offload a una replica secondaria leggibile.In this configuration, you can eliminate the impact of running analytics by offloading it to a readable secondary.

Suggerimento per le prestazioni n. 3: riduzione della frammentazione dell'indice, mantenendo i dati attivi nei rowgroup deltaPerformance Tip #3: Reducing Index fragmentation by keeping hot data in delta rowgroups

Le tabelle con indice columnstore possono subire una frammentazione elevata (righe eliminate) se il carico di lavoro aggiorna o elimina le righe che sono state compresse.Tables with columnstore index may get significantly fragmented (i.e. deleted rows) if the workload updates/deletes rows that have been compressed. Un indice columnstore frammentato determina un utilizzo inefficiente della memoria o dell'archiviazione.A fragmented columnstore index leads to inefficient utilization of memory/storage. Oltre all'uso inefficiente delle risorse, influisce negativamente sulle prestazioni delle query di analisi a causa dell'I/O aggiuntivo e della necessità di filtrare le righe eliminate dal set di risultati.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.

Le righe eliminate non vengono fisicamente rimosse fino a quando non si esegue la deframmentazione degli indici con il comando REORGANIZE o si ricompila l'indice columnstore nell'intera tabella o nelle partizioni interessate.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). REORGANIZE e INDEX REBUILD sono operazioni dispendiose in termini di risorse che diversamente potrebbero essere usate per il carico di lavoro.Both REORGANIZE and Index REBUILD are expensive operations taking resources away which otherwise could be used for the workload. Inoltre, se le righe vengono compresse troppo presto, potrebbe essere necessario ricomprimerle più volte a causa di aggiornamenti che determinano un overhead di compressione inutilizzata.Additionally, if rows compressed too early, it may need to be re-compressed multiple times due to updates leading to wasted compression overhead.
È possibile ridurre al minimo la frammentazione dell'indice usando l'opzione 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);  

;  

Consultare il blog per informazioni dettagliate sul ritardo di compressione.Please refer to the blog for details on compression delay.

Le procedure consigliate sono le seguenti:Here are the recommended best practices

  • Carico di lavoro costituito da inserimento/query: se il carico di lavoro è costituito principalmente dall'inserimento di dati e dall'esecuzione di query su tali dati, il valore COMPRESSION_DELAY predefinito di 0 è l'opzione consigliata.Insert/Query workload:If your workload is primarily inserting data and querying it, the default COMPRESSION_DELAY of 0 is the recommended option. Le nuove righe inserite verranno compresse dopo l'inserimento di 1 milione di righe in un singolo rowgroup delta.The newly inserted rows will get compressed once 1 million rows have been inserted into a single delta rowgroup.
    Alcuni esempi di tale carico di lavoro sono (a) carico di lavoro di data warehouse tradizionale (b) analisi del flusso di clic quando è necessario analizzare lo schema dei clic in un'applicazione 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.

  • Carico di lavoro OLTP : se il carico di lavoro comporta un numero elevato di istruzioni DML (combinazione di numerose istruzioni Update, Delete e Insert), è possibile osservare la frammentazione dell'indice columnstore esaminando DMV 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. Se si nota che una percentuale inferiore al 10% delle righe è contrassegnata come eliminata in rowgroup compressi di recente, è possibile usare l'opzione COMPRESSION_DELAY per aggiungere un ritardo quando le righe diventano idonee per la compressione.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. Se, ad esempio, il carico di lavoro appena inserito rimane attivo (vale a dire viene aggiornato più volte) per 60 minuti, è consigliabile scegliere 60 come 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.

    È probabile che nella maggior parte dei casi i clienti non debbano effettuare alcuna azione.We expect most customers do not need to anything. Il valore predefinito dell'opzione COMPRESSION_DELAY dovrebbe essere sufficiente.The default value of COMPRESSION_DELAY option should work for them.
    Agli utenti avanzati si consiglia di eseguire la query seguente e di raccogliere la percentuale delle righe eliminate negli ultimi 7 giorni.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  

Se il numero di righe eliminate in rowgroup compressi è maggiore del 20%, per stabilizzare i rowgroup precedenti con variante inferiore al 5% (indicati come rowgroup inattivi) impostare 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). Si noti che questo approccio funziona in modo ottimale con un carico di lavoro stabile e relativamente omogeneo.Note that this approach works best with a stable and relatively homogeneous workload.

Vedere ancheSee Also

Guida agli indici columnstore Columnstore Indexes Guide
Caricamento dati di indici columnstore Columnstore Indexes Data Loading
Prestazioni delle query per gli indici columnstore Columnstore Indexes Query Performance
Indici columnstore per il data warehousing Columnstore Indexes for Data Warehousing
Deframmentazione degli indici columnstore Columnstore Indexes Defragmentation